1

I'm new in Camel and i try to process SQL data. If the SQL consume (select) is done, i try to update the consumed rows, but i just get a "bad SQL grammar" exception.

I use the Apache Camel SQL-Component, where the starting endpoint is a sql select statement. To mark them as consumed, i use the onConsume parameter of the SQL-Component. In the select, v_table is the view of the original table t_table, which is used afterwards in the update. So the id of a row in v_table and t_table is the same. To update not all rows in t_table, i use the where condition with where id = :#id.

String sqlSelect = "select * from v_table where camel_is_read = 0";
String sqlUpdate = "update t_table set camel_is_read = 1, date_checked = sysdate where id = :#id";
from("sql:"+sqlSelect+"?dataSource=myDataSource&onConsume="+sqlUpdate)
.process(new Processor() {
    public void process(Exchange exchange) throws Exception {
        System.out.println(exchange.getIn().getBody().toString());
    }
})                    
.errorHandler(deadLetterChannel("direct:moveFailedOut").useOriginalMessage())
.bean("orderToJms")
.to(jmsURI)
.bean("validate")
.to(ftpOut);

If i execute this, i get the follwoing exception:

WARN  Error executing onConsume/onConsumeFailed query update t_table set camel_is_read = 1, date_checked = sysdate where id = :?id. Caused by: [org.springframework.jdbc.BadSqlGrammarException - PreparedStatementCallback; bad SQL grammar [update t_table set camel_is_read = 1, date_checked = sysdate where id = ?]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00904: "YSDATEHERE": ungültiger Bezeichner
]
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [update t_table set camel_is_read = 1, date_checked = sysdate where id = ? exception is java.sql.SQLSyntaxErrorException: ORA-00904: "YSDATEHERE": ungültiger Bezeichner

    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:237)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)
    at org.apache.camel.component.sql.DefaultSqlProcessingStrategy.commit(DefaultSqlProcessingStrategy.java:46)
    at org.apache.camel.component.sql.SqlConsumer.processBatch(SqlConsumer.java:195)
    at org.apache.camel.component.sql.SqlConsumer$1.doInPreparedStatement(SqlConsumer.java:118)
    at org.apache.camel.component.sql.SqlConsumer$1.doInPreparedStatement(SqlConsumer.java:91)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:589)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)
    at org.apache.camel.component.sql.SqlConsumer.poll(SqlConsumer.java:91)
    at org.apache.camel.impl.ScheduledPollConsumer.doRun(ScheduledPollConsumer.java:174)
    at org.apache.camel.impl.ScheduledPollConsumer.run(ScheduledPollConsumer.java:101)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
    at java.util.concurrent.FutureTask$Sync.innerRunAndReset(FutureTask.java:351)
    at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:178)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:724)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "YSDATEHERE": ungültiger Bezeichner

I tried to execute the update manually in the database with a test-id (e. g. "3"), which works, so the generall SQL grammar should be fine. So it seems for me, that Camel can't replace the :#id parameter.

I added a processor, to check the outcome of the select:

{ID=3, [...] CAMEL_IS_READ=0}

Here i can see, that the select successfully catched the necessary id. I don't understand why Camel is not able to replace the :#id parameter with the id value of 3. Does anyone know how to fix this problem? I used this and this as a rough example/template. Or is this approach wrong in general?

Zeussi
  • 571
  • 1
  • 9
  • 22
  • sysdate should that be in upper case ? – Claus Ibsen Aug 19 '16 at 15:20
  • Hi @ClausIbsen, thank you for your response (and i like your book :). I actually tried it with SYSDATE instead of sysdate, but it didn't show effect. I also tried the whole update statement directly in my oracle database, where it works. It also works without the id parameter (but that is not the goal, i just want to update the processed rows). – Zeussi Aug 22 '16 at 07:29
  • What version of Camel. You can try upgrade. – Claus Ibsen Aug 22 '16 at 14:30
  • 1
    The error message says it cannot identify "YSDATEHERE". That looks suspiciously similar to "SYSDATE WHERE", with the first character of each word removed! – Darius X. Aug 22 '16 at 19:30
  • Sorry for my late response. @ClausIbsen i used camel-sql in version 2.14.1 and updated camel-core and camel-sql to 2.17.3. I still get the same error. I found out, it doesn't matter if i use the correct ":#id" parameter, with "#id" or "#" i get the same db-error. But if i use a correct id like: "... sysdate where id = 9" it works correctly. With the hint from Darius X. i changed the statement to ".. date_checked = '29.08.2016' where id = :#id" and it works! So it seems, the additional camel code for parameters has a problem with Oracles SYSDATE? How i can use paramters and sysdate together? – Zeussi Aug 29 '16 at 13:24
  • The cause is not this bug? Even on my system, ORA-00904 has occurred. https://support.oracle.com/knowledge/Middleware/2169848_1.html – hanamiche Jul 11 '19 at 01:07

1 Answers1

2

Finally it works for me by surrounding the sysdate statement with the to_date() function:

String sqlSelect = "select * from v_table where camel_is_read = 0";
String sqlUpdate = "update t_table set camel_is_read = 1, date_checked = to_date(sysdate) where id = :#id";
from("sql:"+sqlSelect+"?dataSource=myDataSource&onConsume="+sqlUpdate)
.process(new Processor() {
    public void process(Exchange exchange) throws Exception {
        System.out.println(exchange.getIn().getBody().toString());
    }
})                    
.errorHandler(deadLetterChannel("direct:moveFailedOut").useOriginalMessage())
.bean("orderToJms")
.to(jmsURI)
.bean("validate")
.to(ftpOut);

Some notes, what doesn't work / is not a good idea (in my opinion) and saves you maybe time:
In some situations onConsumeBatchComplete could be a solution. If you know the condition, you can maybe implicate, that if all rows with this condition are passed through camel, this rows can be updated. This works together with sysdate and doesn't need the id placeholder. Disadvantage of this solution is, that it is implicate and if in the same time new rows are added, which camel didnt't pass, they will be also updated after the batch is completed. So i can't recommend this.

It also doesn't work to set the time in Java, like this:

String sqlUpdate = "update t_table date_checked = " + today + " where id = :#id";

If you don't restart your route every day, the camel route is active over some days, weeks or years. In this case, the camel route will be generated on startup and after this, there is the "todays" date always fix with this days date. So it will always use the same date for the database update.

Zeussi
  • 571
  • 1
  • 9
  • 22