[resolved] how to control commit or rollback in line level

One Star

[resolved] how to control commit or rollback in line level

Hi,
I want to do something normal like insert two outputs of a tMap into two differents tables, but I want to control commit or rollback for every line of the file(if there is no reject for both the two tables, I commit, if not, rollback ), not at the end of all lines.
Is this possible? If yes, I should based on which condition to know if there is no rejects for both two tables?
Thanks in advance.

Accepted Solutions
Community Manager

Re: [resolved] how to control commit or rollback in line level

Hi
The globalMap is only available in the job itself, to pass a row to child job, you have to use context variables. I show an demo job in topic:
http://www.talendforge.org/forum/viewtopic.php?id=1654
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

All Replies

Re: [resolved] how to control commit or rollback in line level

sure. use an iterate link to process one row at a time.
so,
tFileInputDelimited--row-->tFlowToIterate---iterate--->tFixedFlowInput---row->tMap
in the tFixedFlowInput, retireve the column values that the tFlowToIterate stores from the globalMap. so if you have a column "some_data" in the row1 input to the tFlowToIterate, it will be in the global map with the key "row1.some_data". The tFixedFlowInput will (by default) output one row per iteration, and feed it to the rest of your job.
One Star

Re: [resolved] how to control commit or rollback in line level

Thanks a lot for your help.
But I still have a question, if I do something like this (see shotscreen), what should be the if conditions?
I tried the conditions:
for commit:
(Integer)globalMap.get("tMSSqlOutput_1_NB_LINE_REJECTED")<=0 &&
(Integer)globalMap.get("tMSSqlOutput_2_NB_LINE_REJECTED")<=0
for rollback
(Integer)globalMap.get("tMSSqlOutput_1_NB_LINE_REJECTED")>0 ||
(Integer)globalMap.get("tMSSqlOutput_2_NB_LINE_REJECTED")>0
But it does not work.
One Star

Re: [resolved] how to control commit or rollback in line level

the sceenshot
Community Manager

Re: [resolved] how to control commit or rollback in line level

Hi
As John said, iterate each row and pass each row to child job, the job design should be:
parent job:
tFileInputDelimited--main--tFlowToIterate--tRunJob_1
child job:
tMssqlConnection
|
onsubjobok
|
tFixedFlowInput--main--tMap---main--tMssqloutput_1
---main--tMssqlOutput_2
---onsubjobok---tMssqlcommit
---onsubjoberror--tMssqlRollback.
On tRunJob_1: call the child job and pass each row to child job, uncheck the box 'die on error'
on tMssqloutput_1 and tMssqloutput_2: check the box 'die on error'
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] how to control commit or rollback in line level

Thanks so much.
I have a last question, in the parent job in tFlowToIterate I store a value into the globalMap, and in the child job I can't retrive it in the tFixedFlowInput. I suppose that's because the parent job and child job don't share the same globalMap. So how can I get the value in the child job? I checked "transmit whole context" in tRunJob.
Community Manager

Re: [resolved] how to control commit or rollback in line level

Hi
The globalMap is only available in the job itself, to pass a row to child job, you have to use context variables. I show an demo job in topic:
http://www.talendforge.org/forum/viewtopic.php?id=1654
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] how to control commit or rollback in line level

Yes it works.
Thanks a lot.
Community Manager

Re: [resolved] how to control commit or rollback in line level

Hi
Thanks for you feedback, I am glad to see that you get it works now!
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business