transaction question

[CrazyBird]

Hello!

I have a problem to understand the database transaction management in JasperETL.

What I need is a transaction for every sentence read from a CSV- file.

Until now I used tOracleConnect and tOracleCommit/Rollback but I just read that  this results in a commit (or rollback) including all changes made in the whole job.

This would be bad if I have thousands of lines to process.

Which is "The better way"? Problem is If I uncheck the "Use an existing connection" within the Oracle DB - components (tOracleInput, tOracleOutput etc) I have to insert all connection data within EVERY component.

Do you have any suggestions for me?

Tank you in advance!

[CrazyBird]

I'm processing a CSV- file and I want to create Database entries into different tables from these entries. Per CSV- line there should be exactly ONE database transaction.

Problem is now, that I tried to use tOracleOutput components connected to the tMap- component. Now, the transformation will never stop (no error, no exit of the program). I tried to debug the code and saw, that the jobs "hangs" when trying to execute THIS stament:

insertedCount_tOracleOutput_2 = insertedCount_tOracleOutput_2 + pstmt_tOracleOutput_2.executeUpdate();

which is part of the secont tOracleOutput - component - see attached graph.

 

If I use tOracleConnectiona and tOracleOutput the INSERT works but than the commit would take place after the last line of the CSV- file has been processed, and no atomic insertion is made.

 

Any ideas?

Thank you!

[CrazyBird]

I'm processing a CSV- file and I want to create Database entries into different tables from these entries. Per CSV- line there should be exactly ONE database transaction.

Problem is now, that I tried to use tOracleOutput components connected to the tMap- component. Now, the transformation will never stop (no error, no exit of the program). I tried to debug the code and saw, that the jobs "hangs" when trying to execute THIS stament:

insertedCount_tOracleOutput_2 = insertedCount_tOracleOutput_2 + pstmt_tOracleOutput_2.executeUpdate();

which is part of the secont tOracleOutput - component - see attached graph.

 

If I use tOracleConnectiona and tOracleOutput the INSERT works but than the commit would take place after the last line of the CSV- file has been processed, and no atomic insertion is made.

 

Any ideas?

Thank you!

[CrazyBird]

Well I now know the cause of this problem:

 

every tOracleOutput -component - if not used with tOracleConnection and tOracleCommit/tOracleRollback - opens a new database connection and with that also a new transaction.

 

In my case that means, that the first tOracleOutput opens a connection to insert a row into a table, but does not close the transaction, than the second tOracleOutput- component tries to insert another row into another table, but this transaction is blocked by the first one.

 

So this way is also not the right one for me. What I would need is ONE database transaction over 5 tOracleOutput- statements, and that for each row from a tFileInputDelimited - component.

 

Are there ways to do so?

 

Thanks in adavance,

Stefan