Camel -Spring SQLException while using prepared statements

Question

Using Camel 2.13.1 with Spring 2.7.11 and receiving an SQL exception while trying to run a update query using sql component. Below is how my update query is added to the route and I'm passing the input parameter values to this the statement using a java.util.Map in the body.

Main problem : If I have only one Input param in the prepared statement then it works fine.. if I have multiple as in the below update query then it fails with the sql exception

<route id="ABC" >

   <from uri="direct:sqlInsert" />
   <process ref="sqlProcessor" />

   <to uri="sql:UPDATE myTable set key1=:#value1, key2=:#value2, key3=:#value3 where req1=:#reqValue1" />

</route>

Facing same issue even if I use camel-JDBC

<route id="ABC" >

      <from uri="direct:sqlInsert" />
      <process ref="sqlProcessor" />

      <setBody>
            <constant>UPDATE myTable set key1=:?value1, key2=:?value2, key3=:?value3 where req1=:?reqValue1</constant>
        </setBody>
        <to uri="jdbc:customDatasource?useHeadersAsParameters=true" />  

 </route>

This is the SQL exception I see all the time..

Execution of JMS message listener failed. Caused by: [org.apache.camel.RuntimeCamelException - org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist ]: org.apache.camel.RuntimeCamelException: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

Note: No issues with database connectivity and the database table

Answer 1

In this question someone had the same error. He solve it by changing argument type. Try to werify if your arguments are in good types. Try to hardcode all arguments without one, like you try, but change not hardcoded argument. That way you can determine which argument is causing error.

Answer 2

I also had the same problem, however, I found the solution for it.

sql:UPDATE myTable set key1=:#value1, key2=:#value2, key3=:#value3 where req1=CAST(:#reqValue1 as NCHAR(25))

In the where clause alone, ensure that you give the length of characters as seen in the query.

Let us know if it solves your problem as well.

Other: Its working i had the same problem and this resolve the error

Answer 3

We faced the same problem with params. Our application server was weblogic 12c. Strange, but downgrading to 11g solved this.

I hope this info helps you!