Talend community forum / [resolved] Using database transaction with tMomInput

hrishi_sir · 2009-08-06 11:08:16

Tags: [activeMQ, database, development, mssql, tMomInput]

Hi,

I'm trying to consume xml message from activeMQ. I want to apply some transformation on this xml & then I need to update it to 2 different database tables ( ms sql server).
I'm not sure how can I do it in single transaction. If update to any of the table fails, I want to rollback otherwise commit.
I want to commit/rollback for each XML message.

shong · 2009-08-06 11:20:06

Hello
After you extract the xml message from activeMQ, then link it to a tExtractXMLField component to parse this xml message and extract the useful data, eg:
tMysqlConnection_1(db1)
|
onSubjobok
|
tMysqlConnection_2(db2)
|
onsubjobok
|
tMomInput--tExtractXMLField-->tFileOutputDelimited(tempopary output file)
|
onsubjobok
|
tFileInputDelimited(read data from tempopary file)-->tMysqlOutput(db1)--OnComponentOk--tMysqlCommit(connection1)
--OnComponentError--tMysqlRollback(connection1)
|
onsubjobok
|
tFileInputDelimited(read data from tempopary file)-->tMysqlOutput(db2)--OnComponentOk--tMysqlCommit(connection2)
--OnComponentError--tMysqlRollback(connection2)

Best regards

shong

hrishi_sir · 2009-08-06 11:29:59

This is what I've done. but its not executing in 1 transaction.

Uploaded Images

shong · 2009-08-06 11:36:54

Hello
1)The component: txxxcommit and txxxrollback should be linked with 'oncomponentok'
2)For debug, you can add some tLogRow components in your job to print datas on console for the first time.

Best regards

shong

hrishi_sir · 2009-08-06 11:44:23

I tried that, but my requirement is

1> after receiving every message on the queue, I want to parse xml & want to update 2 tables. ( with different data)
2> I want to commit only if both the inserts are successful, otherwise I want to rollback.

If I use commit on 'component ok', then it puts it outside the activeMQ's while loop. That means it won't get committed immediately.

shong · 2009-08-07 09:08:20

Hello

1> after receiving every message on the queue, I want to parse xml & want to update 2 tables. ( with different data)
2> I want to commit only if both the inserts are successful, otherwise I want to rollback.

I know you request now, put these transcation:parse xml & want to update 2 tables in a child job.
In father job, share the database conection,
tMysqlConnection (select 'shared DB connecton option and type in a conn name, so the child job can use this connection)
|
tRunJob(run the child job)-->oncomponentok-->tMysqlCommit
|
oncomponenterror
|
tMysqlRollback

Best regards

shong

hrishi_sir · 2009-08-07 12:21:23

so where should I put my mominput component ( in parent job or child job)? Can you please explain me in detail as I'm still not able to execute it.

shong · 2009-08-07 12:24:19

Hello
Put it in child job.

Best regards

shong

hrishi_sir · 2009-08-07 13:30:04

Hi,

I've created the parent & child as shown in the attached diag. Still the problem is tMominput is a infinite loop so control never comes back to the parent job to commit the data.
I want to commit every message as it comes on the tMominput.

Uploaded Images

shong · 2009-08-10 07:21:59

Hello

I want to commit every message as it comes on the tMominput.

Using a tFlowToIterate to iterate each message and send it to child job. Please see my screenshot.

Best regards

shong

Uploaded Images

hrishi_sir · 2009-08-11 06:08:48

It's now working as expected. Thanks for your help.

hrishi_sir · 2009-08-11 14:44:58

Hi,

How can I re-arrange this flow to use Extend insert feature i.e. to use bulk insert ( committing after 1000 rows)

thanks,
Hrishi

wkyy · 2009-09-18 13:17:11

shong wrote:
Hello
1> after receiving every message on the queue, I want to parse xml & want to update 2 tables. ( with different data)
2> I want to commit only if both the inserts are successful, otherwise I want to rollback.
I know you request now, put these transcation:parse xml & want to update 2 tables in a child job.
In father job, share the database conection,
tMysqlConnection (select 'shared DB connecton option and type in a conn name, so the child job can use this connection)
|
tRunJob(run the child job)-->oncomponentok-->tMysqlCommit
|
oncomponenterror
|
tMysqlRollback

Best regards

shong

I have selected the 'shared DB connection option' and type in a conn name with double quotes in the parent job, but how can I use it in my child job?

In the child job, if I select 'Use an existing connection', Component List is shown, which allows me to select a connection in the child job, but I'm suppose to use the connection from the Parent job. How can I use the conn name from the parent job?

Thanks!

wkyy · 2009-09-21 11:03:31

oh.. found the answer in the User Guide. So i still need the connection component in the child

TomasAlmeida · 2010-07-23 11:43:20

Hi Wkyy/Shong, could you share the solution on this topic?

I am stucked on the "How can I use the conn name from the parent job?"

thanks

Announcement

Unanswered posts

#1 2009-08-06 11:08:16

[resolved] Using database transaction with tMomInput

#2 2009-08-06 11:20:06

Re: [resolved] Using database transaction with tMomInput

#3 2009-08-06 11:29:59

Re: [resolved] Using database transaction with tMomInput

#4 2009-08-06 11:36:54

Re: [resolved] Using database transaction with tMomInput

#5 2009-08-06 11:44:23

Re: [resolved] Using database transaction with tMomInput

#6 2009-08-07 09:08:20

Re: [resolved] Using database transaction with tMomInput

#7 2009-08-07 12:21:23

Re: [resolved] Using database transaction with tMomInput

#8 2009-08-07 12:24:19

Re: [resolved] Using database transaction with tMomInput

#9 2009-08-07 13:30:04

Re: [resolved] Using database transaction with tMomInput

#10 2009-08-10 07:21:59

Re: [resolved] Using database transaction with tMomInput

#11 2009-08-11 06:08:48

Re: [resolved] Using database transaction with tMomInput

#12 2009-08-11 14:44:58

Re: [resolved] Using database transaction with tMomInput

#13 2009-09-18 13:17:11

Re: [resolved] Using database transaction with tMomInput

shong wrote:

#14 2009-09-21 11:03:31

Re: [resolved] Using database transaction with tMomInput

#15 2010-07-23 11:43:20

Re: [resolved] Using database transaction with tMomInput

Board footer