How to start transaction in Camel sql consumer and use it further

Question

I am using Camel for integrating Spring Boot application with other domains. I have a simple route configured using SQL Component for polling and updating DB:

from("sql:"+selectSQL+"?consumer.delay=20000&onConsume="+updateSQL)
   .log("---select sql done")
   .to("sql:" + insertSQL);

and SQLs are like these:

String selectSQL = "SELECT * FROM T1 WHERE PROCESSED is null and ROWNUM <4 for update skip locked";
String updateSQL = "update T1 set PROCESSED='TRUE' where id = :#id";
String insertSQL = "insert into T2 (col_name...) values (col_value...)";

And I would like these SQLs to run inside 1 transaction for single polling request. I know that I can make route transacted with .transacted("propagationPolicy") but I can't put it before from.

Is it possible make every polling try transactional?

PS. I have an workaround with timer component and separate route for each SQL but I wonder is it possible to solve my problem using only 1 route as above.

score 1 · Accepted Answer · answered Jan 19 '18 at 12:34

After digging in the Camel documentation and source codes I came to conclusion that what I asked is not possible to achieve. So let me post my workaround solution.
The idea is to use one of components with scheduling like timer component. Next transaction could be started, inside which all DB operation are done:

from("timer:pollingTimer?period=20s")
    .transacted("propagationRequired")
    .to("sql:" + selectSQL)
    .to("sql:" + updateSQL)
    .to("sql:" + insertSQL)
    .log("processing is done");

Eventually some processors could be applied between DB operations to correctly process selected rows.

How to start transaction in Camel sql consumer and use it further

1 Answers1