Talend community forum / [resolved] Rollback entire transaction

boris_volf · 2010-02-03 15:10:17

Tags: [delimited file, development, error management, oracle, schema]

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

Serpico · 2010-02-03 17:08:37

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

boris_volf · 2010-02-03 18:11:41

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.

Serpico · 2010-02-03 19:06:15

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

Serpico · 2010-02-03 19:27:27

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

boris_volf · 2010-02-03 19:28:49

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

boris_volf · 2010-02-03 19:36:51

auto commit is unchecked.

What else could it be?

Serpico · 2010-02-03 19:39:46

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

boris_volf · 2010-02-03 20:03:11

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.

boris_volf · 2010-02-03 20:29:44

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

Serpico · 2010-02-03 20:32:22

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

boris_volf · 2010-02-03 20:48:43

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?

boris_volf · 2010-02-03 20:51:41

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

Serpico · 2010-02-03 20:55:49

boris_volf wrote:
((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:

Code:

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

Announcement

Unanswered posts

#1 2010-02-03 15:10:17

[resolved] Rollback entire transaction

#2 2010-02-03 17:08:37

Re: [resolved] Rollback entire transaction

#3 2010-02-03 18:11:41

Re: [resolved] Rollback entire transaction

#4 2010-02-03 19:06:15

Re: [resolved] Rollback entire transaction

#5 2010-02-03 19:27:27

Re: [resolved] Rollback entire transaction

#6 2010-02-03 19:28:49

Re: [resolved] Rollback entire transaction

#7 2010-02-03 19:36:51

Re: [resolved] Rollback entire transaction

#8 2010-02-03 19:39:46

Re: [resolved] Rollback entire transaction

#9 2010-02-03 20:03:11

Re: [resolved] Rollback entire transaction

#10 2010-02-03 20:29:44

Re: [resolved] Rollback entire transaction

#11 2010-02-03 20:32:22

Re: [resolved] Rollback entire transaction

#12 2010-02-03 20:48:43

Re: [resolved] Rollback entire transaction

#13 2010-02-03 20:51:41

Re: [resolved] Rollback entire transaction

#14 2010-02-03 20:55:49

Re: [resolved] Rollback entire transaction

boris_volf wrote:

Code:

Board footer