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

The error from Oracle tells you that the table myTable does not exists — Claus Ibsen, Oct 09 '15 at 15:18
@ClausIbsen Unfortunately, that is not the case here.. If I pass only one parameter to the prepared statement, hardcoding the other, it executes fine. Only if I have multiple input parameters I see this issue. — Bharath ABK, Oct 09 '15 at 17:36

score 0 · Answer 1 · edited May 23 '17 at 10:28

0

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.

edited May 23 '17 at 10:28

Community

1
1

answered Oct 13 '15 at 09:56

dey

3,022
1
15
25

The issue I reported is a different compared to the one in the link you provided.. I have the database connectivity and valid table structure and pretty everything correct .. the SQL executes perfectly fine if I have one input param to prepared statement and it is not accepting if there are multiple.. This issue is something related to camel and not database.. – Bharath ABK Nov 02 '15 at 21:24

score 0 · Answer 2 · answered Apr 04 '16 at 13:56

0

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!

answered Apr 04 '16 at 13:56

Gavi

11
2

score 0 · Answer 3 · edited Feb 14 '18 at 19:10

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

Camel -Spring SQLException while using prepared statements

3 Answers3