Camel SQL Server Transaction Manager

Question

I have two inserts that I want to have under transactional control. If one of them does not work, the other should not be executed / inserted. This works great when doing it right in MySQL, like this

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

Now, using a camel, I already tried

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

which creates 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 is still running and pasted.

I also found this http://camel.apache.org/transactional-client.html , but I use a plan, and these examples only look for spring. So if someone got a good example of doing this with a camel plan, that would be great.

Can someone help me do this on a camel?

Answer 1

How about using TX manager? Define an XA JDBC resource and use it with your calls, something like 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.

Answer 2

Make sure the "Engine" is InnoDB (not MyISAM). MyISAM silently ignores transaction statements ( START , COMMIT ).

Use the mysql command line tool and do SHOW CREATE TABLE ... The engine will be indicated at the end of the table.