Monday, September 10, 2012

Talend and SQL Transactions

It would be great if when setting a Talend Connection to a Database the "Auto Commit" setting in Advanced Settings of the tMySQLConnection would drive more in the rest of the components using such connection. Unfortunately at least for the tMySQLRow component that is not true.

I started putting together a proof of concept to demonstrate a bug in Talend Open Studio Version: 4.2.3 tMysqlRow component. Basically it does not support transactions.

So here is a project with two jobs. The first called "mysql_pojo_transaction" just uses JDBC while the second called "mysql_transaction" uses tMySQLRow to insert two identical records (just a name column) in table "names" in a mysql "test" database.

Using "mysql_transaction" project I was expecting to check and uncheck Autocommit checkbox from Advanced settings in the tMysqlConnection and see how a record will exist in "names" table only when autocommit is checked. However a record will exist there in any case:

Using "mysql_pojo_transaction" project and commenting out setAtocommit, commit and rollback lines will result in one record while uncommenting results in no records. This would be the expected behaviour in tMySQLRow I guess:

This project is useful as a test for a couple of reasons. First it shows how to prepare a test database from within talend without the need of external files (through the use of tFixedFlowInput which generates the statements to be run by a tMySQLRow component). Second it shows how to generate input data via tRowGenerator (Set schema with field "name", Use as Funtion "Talend Trim", use as Funtion Parameter origin the value "Foo". Clicking on Preview should show two rows with the word "Foo") without the need of extra files to insert records later on using again a tMysqlRow.

No comments: