Camel blueprint sql transaction manager

Question

I got two inserts which I want to have under transactional control. If one of them fails the other one also shall not be executed/inserted. This works fine when doing it right in the MySQL like this

START TRANSACTION;
INSERT INTO table (field) VALUES (value);
INSERT INTO table2 (field) VALUES (value);
COMMIT;

Now using camel I already tried

<to uri="sql:START TRANSACTION; INSERT INTO table (field) VALUES (value);INSERT INTO table2 (field) VALUES (value);COMMIT;"/>

which produces an sql syntax error. The second thing I tried is

<to uri="sql:START TRANSACTION"/>
<to uri="sql:INSERT INTO table (field) VALUES (value)"/>
<to uri="sql:INSERT INTO table2 (field) VALUES (value)"/>
<to uri="sql:COMMIT"/>

which works but if one insert fails the other one is still being executed and inserted.

I also found this http://camel.apache.org/transactional-client.html but I am using blueprint and these examples only seem to be for spring. So if anyone got a good example doing it with camel blueprint this would be great.

Can anyone help me to do this in camel?

Answer 1

What about using a TX manager? Define an XA JDBC resource and use it with your calls , something like here https://github.com/tmielke/fuse-demos/blob/master/Camel/Camel-JMS-JDBC-XA-TX/routing/src/main/resources/OSGI-INF/blueprint/camel-context.xml

G.