Question on MySQL database and commit

[mr_burns]

I have a tDBOutput component configured for a connection to a MySQL database:

  

In the component tab of tDBOutput under 'Advanced settings' I have an option called 'Commit every' which by default is set to 10000:

 

So far no problem. Now I want to use an existing database conection and activated 'Use an existing connection':

 

If I now look inside the 'Advanced settings' section the 'Commit every' disappeared:

 

I want to set the No. of lines when a commit should happen.

I looked every properties of the tDBConnection as well but there I also cannot find 'Commit every'.

Then I added a tDBCommit component but also here no property called 'Commit every' :-(.

Where please can I set the 'Commit every' now?

[jlolling · Accepted solution]

Yes, this is because Talend has decided to do so. It would be possible to have this option also while using a separate connection. The problem they try to prevent is, the connection can be used in various components and you have to look into every component if you have a commit where it should not be the case.

Thats why the insisted in using separate tDBCommit components when using tDBConnection.

It would be the best you try to load everything on one transaction of if needed connect a tJavaRow after the tDBConnection and count here the records and connect the with an Iterate a tJava and from here connect the tDBCommit with an if trigger.

[Anonymous]

Hello,

If your are using tMySqlConnection, your are supposed to add tMySqlCommit / tMySql Rollback components to handle yourself your transactions.

Adding a main row after your tMySqlOutput to a tMySqlConnection will commit row by row.

Hope this Mysql related topic helps.

https://help.talend.com/r/en-US/8.0/mysql/closing-a-database-connection-created-by-a-tmysql-connecti...

Best regards

Sabrina

[mr_burns]

@jkolling, @xdshi,

OK thanks!