rollback parent and its related childs and continue processing next re

[Anonymous]

I iterate through a csv file and insert the data in parent and child table.If while inserting data in any child fails then it should rollback the current child and its parents remaining children which was inserted successfuly prior to the current child and continue iterating to the next parent.But when i try doing ,my processing is stopped on rollback

[alevy]

Screenshot your job design and have a look at https://community.talend.com/t5/Design-and-Development/Transaction-bracket-group/td-p/82492. If your output components do not have "Die on error" checked, you shouldn't have a problem.

[Anonymous]

updating screenshot image

[alevy]

Like I said: don't check "Die on error" in your output component. Instead connect tMysqlRollback to tMysqlOuput with a rejects flow.
BTW, isn't tMysqlCommit_2 redundant? There also seem to be a lot of unnecessary components: why do you need anything between tFixedFlowInput and tMysqlOuput in either subjob? And why do you need to read from the file twice?

[Anonymous]

hi,
thanks.The solution worked.
Regarding the csv file being read twice is because ,the same csv contains parent data and child data.So i need to insert parent once and insert its multiple childs with parent id.
So for the inserted parent i am filtering out the childs and inserted them.
I am new to talend open studio.So did not the if its wrong or right.Please advice on how to proceed.
I have attached the corrected job design and example of my csv file.

[alevy]

Ignoring the committing/rolling-back issue for which you should refer to the topic I linked to above, I would just do:
tFileInputDelimited --> tMap --parent--> tUniqueRow --> tMysqlOutput -main-> tMysqlLastInsertID --> tSetGlobalVar
--child--> tJavaRow --> tMysqlOutput
The tJavaRow is to add the LastInsertID stored in the globalMap by tSetGlobalVar to your child data as the child data in tMap is populated before the parent flow is executed.

[Anonymous]

hi,
I modified the job design as suggested by you.But wen i run the job it produced the following result in the console.
Starting job zipRollback3 at 11:05 04/10/2012.
connecting to socket on port 3708
connected
null
null
null
null
null
null
.-----+------+-----------+--------------+--------------.
| tLogRow_1 |
|=----+------+-----------+--------------+-------------=|
|legId|legRef|num_of_unit|delivery_units|staging_job_id|
|=----+------+-----------+--------------+-------------=|
|0 |null |null |null |null |
|0 |null |null |null |null |
|0 |null |null |null |null |
|0 |null |null |null |null |
|0 |null |null |null |null |
|0 |null |null |null |null |
'-----+------+-----------+--------------+--------------'
disconnected
Job zipRollback3 ended at 11:05 04/10/2012.

Also it only inserted parent data.Child data was not inserted.Am i doing something wrong.Please advise.

[alevy]

Since all your child data was null, I'm not surprised it rejected. I can't tell you why that is from the screenprint but I'm sure you can figure it out with some additional tLogRows.
Equally, I'm not sure where the other nulls are coming from. Again, I'm sure you can figure it out by deactivating parts of the job.
Where are you adding the LastInsertID to your child data? I said to do that in tJavaRow. Also, I said to connect tMysqlLastInsertID with a row from staging_job not OnComponentOK.
I still don't see why you need tConvertType, tMap_2 and tMap_3; it should all be handled by tMap_1.
There are also problems with how you're committing and rolling-back but I'd rather leave those until the rest is working

[Anonymous]

Hi
I modified to consider row instead on onComponentOk.On doing so it was not giving me the appropiate result.It produced the following result
connecting to socket on port 3988
connected
.--------------+----------------------+--------------.
| tLogRow_2 |
|=-------------+----------------------+-------------=|
|staging_job_id|prime_contract_job_ref|last_insert_id|
|=-------------+----------------------+-------------=|
|0 |JOB0021 |0 |
|0 |JOB0022 |0 |
|0 |JOB0023 |0 |
'--------------+----------------------+--------------'
.-----+------+---------+------------------------.
| tLogRow_4 |
|=----+------+---------+-----------------------=|
|jobId|legRef|num_units|prime_contractor_job_ref|
|=----+------+---------+-----------------------=|
|0 |111 |4 |JOB0021 |
|0 |222 |3 |JOB0021 |
|0 |112 |4 |JOB0022 |
|0 |333 | |JOB0022 |
|0 |333 | |JOB0022 |
|0 |444 |4 |JOB0023 |
|0 |445 |3 |JOB0023 |
'-----+------+---------+------------------------'
.-----+------+-----------+--------------+--------------.
| tLogRow_1 |
|=----+------+-----------+--------------+-------------=|
|legId|legRef|num_of_unit|delivery_units|staging_job_id|
|=----+------+-----------+--------------+-------------=|
|0 |111 |4 | |0 |
|0 |222 |3 | |0 |
|0 |112 |4 | |0 |
|0 |333 | | |0 |
|0 |333 | | |0 |
|0 |444 |4 | |0 |
|0 |445 |3 | |0 |
'-----+------+-----------+--------------+--------------'
disconnected
Job zipRollback3 ended at 13:04 04/10/2012.
Also looking at the logRow it looks as if its inserting all parent data and then inserted all child data.
I have added the parent id in child data.Please let me know if its the wrong way.

[Anonymous]

hi,
I modified the design.If all data is correct then insertion takes place fine.But if one of the child fails ,then the parent and all the previously inserted record is rollbacked.
connecting to socket on port 4054
connected
.----------------------+------------.
| tLogRow_1 |
|=---------------------+-----------=|
|prime_contract_job_ref|job_currency|
|=---------------------+-----------=|
|JOB0021 |rs |
|JOB0022 |dh |
|JOB0023 |rs |
'----------------------+------------'
.--------------+--------------.
| tLogRow_4 |
|=-------------+-------------=|
|staging_job_id|last_insert_id|
|=-------------+-------------=|
|0 |360 |
|0 |361 |
|0 |362 |
'--------------+--------------'
.-----+------+---------+------------------------.
| tLogRow_2 |
|=----+------+---------+-----------------------=|
|jobId|legRef|num_units|prime_contractor_job_ref|
|=----+------+---------+-----------------------=|
|360 |111 |4 |JOB0021 |
|360 |222 |3 |JOB0021 |
|361 |112 |4 |JOB0022 |
|361 |333 | |JOB0022 |
|361 |333 |6 |JOB0022 |
|362 |444 |4 |JOB0023 |
|362 |445 |3 |JOB0023 |
'-----+------+---------+------------------------'
.-----+------+-----------+--------------+--------------.
| tLogRow_3 |
|=----+------+-----------+--------------+-------------=|
|legId|legRef|num_of_unit|delivery_units|staging_job_id|
|=----+------+-----------+--------------+-------------=|
|0 |111 |4 | |360 |
|0 |222 |3 | |360 |
|0 |112 |4 | |361 |
|0 |333 |null | |361 |
|0 |333 |6 | |361 |
|0 |444 |4 | |362 |
|0 |445 |3 | |362 |
'-----+------+-----------+--------------+--------------'
.-----+------+-----------+--------------+--------------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------.
| tLogRow_5 |
|=----+------+-----------+--------------+--------------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------=|
|legId|legRef|num_of_unit|delivery_units|staging_job_id|errorCode|errorMessage |
|=----+------+-----------+--------------+--------------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------=|
|0 |333 |null | |361 |null |Column 'num_of_unit' cannot be null - Line: 3 |
|0 |333 |6 | |361 |null |Cannot add or update a child row: a foreign key constraint fails (`stratus1`.`staging_leg`, CONSTRAINT `FK_staging_leg_1` FOREIGN KEY (`staging_job_id`) REFERENCES `staging_job` (`staging_job_id`)) - Line: 4|
'-----+------+-----------+--------------+--------------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------'
disconnected

Also as observed it looks that it first inserts all parent and then tries to insert all child.My requirement is that it should first insert 1 parent,then its related childs,commit them once successful and then proceed to next.So that even if next record fails then the previously inserted records are not affected.Also the remaining records(remaing parent and child data) should proceed for processing.That is why initially i had been reading the file twice.But as per my requirement i will be extracting a zip file and reading it for inserting it into parent and child tables.Hence require to read the file once.
Please advise