I am trying to execute a sql deletion statement within the route. The query contains an IN clause and I want to pass a Set of values to the IN clause. I am using camel-core & camel-jdbc 2.18.1 and camel-sql 2.18.0
from("direct:deleteRoute").setBody(simple("DELETE FROM TABLE_NAME WHERE ID IN (:#in:idSet)")).to(Constants.JDBC_DATA_SOURCE).end();
Value for idSet is passed in the header of the exchange with key idSet from the route, which calls direct:deleteRoute.
But when I execute I get the following exception.
java.sql.SQLException: A syntax error has occurred.
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:408)
at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3132)
at com.informix.jdbc.IfxSqli.D(IfxSqli.java:3412)
at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2325)
at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2250)
at com.informix.jdbc.IfxSqli.executeExecute(IfxSqli.java:2153)
at com.informix.jdbc.IfxSqli.executeExecute(IfxSqli.java:2133)
at com.informix.jdbc.IfxResultSet.b(IfxResultSet.java:362)
at com.informix.jdbc.IfxStatement.a(IfxStatement.java:1144)
at com.informix.jdbc.IfxStatement.executeImpl(IfxStatement.java:1120)
at com.informix.jdbc.IfxStatement.c(IfxStatement.java:866)
at com.informix.jdbc.IfxStatement.execute(IfxStatement.java:784)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
at com.sun.proxy.$Proxy105.execute(Unknown Source)
at org.apache.camel.component.jdbc.JdbcProducer.doCreateAndExecuteSqlStatement(JdbcProducer.java:220)
at org.apache.camel.component.jdbc.JdbcProducer.createAndExecuteSqlStatement(JdbcProducer.java:125)
at org.apache.camel.component.jdbc.JdbcProducer.processingSqlBySettingAutoCommit(JdbcProducer.java:86)
at org.apache.camel.component.jdbc.JdbcProducer.process(JdbcProducer.java:67)
at org.apache.camel.util.AsyncProcessorConverterHelper$ProcessorToAsyncProcessorBridge.process(AsyncProcessorConverterHelper.java:61)
at org.apache.camel.processor.SendProcessor.process(SendProcessor.java:145)
at org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:77)
at org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:542)
at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:197)
at org.apache.camel.processor.Pipeline.process(Pipeline.java:120)
at org.apache.camel.processor.Pipeline.process(Pipeline.java:83)
at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:197)
at org.apache.camel.component.direct.DirectProducer.process(DirectProducer.java:62)
at org.apache.camel.processor.SendProcessor.process(SendProcessor.java:145)
at org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:77)
at org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:542)
at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:197)
at org.apache.camel.processor.Pipeline.process(Pipeline.java:120)
at org.apache.camel.processor.Pipeline.process(Pipeline.java:83)
at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:197)
at org.apache.camel.component.direct.DirectProducer.process(DirectProducer.java:62)
at org.apache.camel.processor.SendProcessor.process(SendProcessor.java:145)
at org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:77)
at org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:542)
at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:197)
at org.apache.camel.processor.Pipeline.process(Pipeline.java:120)
at org.apache.camel.processor.Pipeline.process(Pipeline.java:83)
at org.apache.camel.processor.ChoiceProcessor.process(ChoiceProcessor.java:117)
at org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:77)
at org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:542)
at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:197)
at org.apache.camel.processor.Pipeline.process(Pipeline.java:120)
at org.apache.camel.processor.Pipeline.process(Pipeline.java:83)
at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:197)
at org.apache.camel.component.timer.TimerConsumer.sendTimerExchange(TimerConsumer.java:192)
at org.apache.camel.component.timer.TimerConsumer$1.run(TimerConsumer.java:76)
at java.util.TimerThread.mainLoop(Timer.java:555)
at java.util.TimerThread.run(Timer.java:505)
Caused by: java.sql.SQLException: null
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:408)
at com.informix.jdbc.IfxSqli.D(IfxSqli.java:3417)
... 53 common frames omitted
This stacktrace comes from camel-jdbc component. This component uses the standard JDBC API, so it takes some limitations from it. One of this is, that you cannot pass collection as single parameter.
In Camel, you can implement custom JdbcPrepareStatementStrategy, register it with uri parameter prepareStatementStrategy to provide custom logic of parsing query, but I wouldn't suggest it. This can lead to lot of traps.
Better use camel-sql component, which uses spring-jdbc and allows this natively since Apache Camel 2.17.
Example with constant prepared statement:
from("direct:deleteIds")
.to("sql:DELETE FROM TABLE_NAME WHERE ID IN (:#in:idSet)");
Example with statement created from body:
from("direct:deleteIds")
.setBody(simple("DELETE FROM TABLE_NAME WHERE ID IN (:?in:idSet)")) // Note questionmark here for specifying parameter. This is difference against query in uri.
.to("direct:executeQuery");
from("direct:executeQuery")
.setHeader(SqlConstants.SQL_QUERY, body())
.to("sql:dummy?dataSource=#datasource");