Talend community forum / Commit every... with existing connection

timson1 · 2008-05-14 23:25:42

Tags: [database, delimited file, oracle, perl, tos2.3.3]

I am trying to load data into oracle using TOS 2.3.3.

1) I have main connection that goes from my file to tOracleOutput.
2) I use the existing connection.
3) I want to commit every 10000 rows.

Is there a way to implement this kind of load in Talend?
I understand that I can do it if I don't use existing connection (there is a Commit Every... field on the Advanced Settings tab, but it disappears when I switch to "use existing connection"). Why can't I do it with the existing connection? Is there a reason or am I missing something?

plegall · 2008-05-15 00:15:42

timson1 wrote:
Why can't I do it with the existing connection? Is there a reason or am I missing something?

It's a design choice from us the developers. tOracleConnection/tOracleCommit/tOracleRollback where designed so that user can control the transactions.

Workaround:

tFileInputDelimited --row--> tOracleOutput --row--> tFilterRow --row--> tOracleCommit

in the tFilterRow, advanced mode:

Code:

sequence('abcd', 1, 1) % 10_000 == 0

Don't forget to add a tOracleCommit after the subjob, so that last lines are commited.

May I ask you to add a feature request "add a commit every in t*Commit"? I think it would be much more user friendly than the tip I just gave you.

timson1 · 2008-05-15 15:46:43

plegall wrote:
timson1 wrote:
Why can't I do it with the existing connection? Is there a reason or am I missing something?
It's a design choice from us the developers. tOracleConnection/tOracleCommit/tOracleRollback where designed so that user can control the transactions.

Workaround:

tFileInputDelimited --row--> tOracleOutput --row--> tFilterRow --row--> tOracleCommit

in the tFilterRow, advanced mode:
Code:
sequence('abcd', 1, 1) % 10_000 == 0
Don't forget to add a tOracleCommit after the subjob, so that last lines are commited.

May I ask you to add a feature request "add a commit every in t*Commit"? I think it would be much more user friendly than the tip I just gave you.

Excellent! I will use this trick for now and wait for the new feature.
Submitted [Bugtracker, feature 3797, open] Add Commit Every... to tOracleCommit component

predictor · 2011-12-01 11:26:35

How to perform such trick with java tFilterRow?

YvesT · 2011-12-01 15:00:50

Hi,

In tFilterRow, Advanced:

Numeric.sequence("abcd", 1, 1) % 500 == 0

Kind regards,
Yves

predictor · 2011-12-02 05:23:40

YvesT wrote:
Numeric.sequence("abcd", 1, 1) % 500 == 0

It works.
Thank you very much!

wani0102 · 2012-03-21 09:45:45

what is "abcd"?

janhess · 2012-03-21 10:07:10

The first 4 letters of the alphabet.

Actually it's the sequence reference name.

Announcement

Unanswered posts

#1 2008-05-14 23:25:42

Commit every... with existing connection

#2 2008-05-15 00:15:42

Re: Commit every... with existing connection

timson1 wrote:

Code:

#3 2008-05-15 15:46:43

Re: Commit every... with existing connection

plegall wrote:

timson1 wrote:

Code:

#4 2011-12-01 11:26:35

Re: Commit every... with existing connection

#5 2011-12-01 15:00:50

Re: Commit every... with existing connection

#6 2011-12-02 05:23:40

Re: Commit every... with existing connection

YvesT wrote:

#7 2012-03-21 09:45:45

Re: Commit every... with existing connection

#8 2012-03-21 10:07:10

Re: Commit every... with existing connection

Board footer