topic Re: [resolved] Rollback entire transaction in Talend Studio

[resolved] Rollback entire transaction

Anonymous — Sat, 16 Nov 2024 13:34:32 GMT

Hi,
I have delimited file, mapped via tMap to 4 different Oracle output tables. If there is an error record in any of the records, I need the entire transaction to be rolled back. And I also want all the records that were rejected to be in the Oracle File.
Here's what I have so far:
tOracleConnection-->onSubjobOK-->tFileInputDelimited-->tMap-->OracleOutput1-->??????
If I have 5 records, and 2 were rejected, while 3 were good, how do I rollback the entire thing?
Records can be rejected for variety of reasons: formatting issues, or b/c they already exist in the database...
I put tRollback after tOracleOutput, but not sure how to specify a condition.
Thanks,
Boris

Re: [resolved] Rollback entire transaction

Serpico — Wed, 03 Feb 2010 16:08:37 GMT

Hi,
Here's a suggestion of what you could do:
1. Wire the Rejects link from each of the tOracleOutput to the OracleFile.
2. Have a PostJob flow as the following:
tPostJob ---onComponentOk--> tJava ----- if (number of lines in OracleFile > 0) ----> tOracleRollBack
----- if (number of lines in OracleFile <= 0) -----> tCommit
Note: You can get the number of lines in OracleFile through the following: ((Integer)globalMap.get("tFileOutputDelimited_1_NB_LINE")).intValue()
Hope this helps!
Regards,
Serpico

Re: [resolved] Rollback entire transaction

Anonymous — Wed, 03 Feb 2010 17:11:41 GMT

I tried without tJava by doing the following:
tOracleOutput-->Rejects-->tFileOutputDelimited-->runIf-->tOracleRollback.
in runIf I specified a condition: ((Integer)globalMap.get("tFileOutputDelimited_1_NB_LINE")) > 0
When I run it, it checks runif, marks it as True, but does not rollback the other records, that were inserted.

Re: [resolved] Rollback entire transaction

Serpico — Wed, 03 Feb 2010 18:06:15 GMT

You might want to increase the "commit every" field in the tOracleOutput.

Re: [resolved] Rollback entire transaction

Serpico — Wed, 03 Feb 2010 18:27:27 GMT

Makre sure the "Auto Commit" in your tOracleConnection is unchecked.

Re: [resolved] Rollback entire transaction

Anonymous — Wed, 03 Feb 2010 18:28:49 GMT

i have it set to 10000.
and I am only processing three test records as of now.

Re: [resolved] Rollback entire transaction

Anonymous — Wed, 03 Feb 2010 18:36:51 GMT

auto commit is unchecked.
What else could it be?

Re: [resolved] Rollback entire transaction

Serpico — Wed, 03 Feb 2010 18:39:46 GMT

Make sure then you "use an existing connection" in your tOracleOutput.

Re: [resolved] Rollback entire transaction

Anonymous — Wed, 03 Feb 2010 19:03:11 GMT

Serpico,
That was it!!! use existing connection box had to be checked.
Thanks for figuring this out.
I will go and try to put all of this in PostJob and Prejob.
Thanks again, I really appreciate this.

Re: [resolved] Rollback entire transaction

Anonymous — Wed, 03 Feb 2010 19:29:44 GMT

Do I need to put any code into tJava, or just into RunIf connectors?

Re: [resolved] Rollback entire transaction

Serpico — Wed, 03 Feb 2010 19:32:22 GMT

No need to put any code in tJava. I usually put this component as a pre-step to use the RunIf connectors.

Re: [resolved] Rollback entire transaction

Anonymous — Wed, 03 Feb 2010 19:48:43 GMT

So for two outputs, I specify:
((Integer)globalMap.get("tFileOutputDelimited_1_NB_LINE")) <= 0 &&
((Integer)globalMap.get("tFileOutputDelimited_2_NB_LINE")) <= 0
in the RunIf connector and get an error.
Is this not the correct way?

Re: [resolved] Rollback entire transaction

Anonymous — Wed, 03 Feb 2010 19:51:41 GMT

i guess this is correct: ((Integer)globalMap.get("tFileOutputDelimited_1_NB_LINE")+(Integer)globalMap.get("tFileOutputDelimited_2_NB_LINE")) <= 0

Re: [resolved] Rollback entire transaction

Serpico — Wed, 03 Feb 2010 19:55:49 GMT

((Integer)globalMap.get("tFileOutputDelimited_1_NB_LINE")) <= 0 &&
((Integer)globalMap.get("tFileOutputDelimited_2_NB_LINE")) <= 0

The above condition will call the Commit if it is true.
On the opposite, the following condition will call the RollBack if it is true:

((Integer)globalMap.get("tFileOutputDelimited_1_NB_LINE")) > 0 ||
((Integer)globalMap.get("tFileOutputDelimited_2_NB_LINE")) > 0