[resolved] Delete MySQL row on condition

[Anonymous]

Hi,
I have a simple tMap between 2 different MySQL databases.
When a row from DB1 (input) is inserted (after mapping) into DB2 (output) with no error, I want the row from DB1 to be deleted. However, the ID from DB1 is not sent to DB2, and I don't know how to get this ID.
Is anyone can help me ?

[Anonymous]

There are two obvious ways to do this:
1) Iterate over each row from DB1. For each row, do the transform save the ID to a context variable. If successfully inserted to DB2, execute a delete statement on DB1 using the saved ID.
2) Maintain a list of ID's from DB1 that were successfully inserted, Then delete after all rows have been processed. This can be done within memory in Talend by using a tHashOutput/tHashInput pair. Alternatively, you could write the successful inserts ID's to file and re-read it once done.

[Anonymous]

Thank you. I tried the first solution.
See what I've done in attached screenshot .
I save the ID in the tMap and I use it in the tMySQLRow (deleteProcessedEvent)
But I don't know how to execute deleteProcessedEvent for each row inserted.
I tried OnComponentOk but it is executed once the all rows have been inserted, so it delete only the last one.

[alevy · Accepted solution]

Use a row link instead of OnComponentOk.
You can perhaps also pass the ID to the tMysqlOuput for DB2 but use the "Field options" in the advanced settings to not insert it, then it is available to pass through to another tMysqlOuput for the deletion from DB1.
Or just have a second flow from the tMap to another tMysqlOuput for the deletion from DB1.

[Anonymous]

Thank you! It works!
However, how do I know if the row is deleted only if the insert succeed ?
What happen if an error occurs ? Is the row deleted anyway ?

[alevy]

If the insert fails, the tMysqlOuput will not pass a row on to any following components and so they will not do anything. This is easy enough to test.

[Anonymous]

Thank you for you answer.
Indeed, I could test it by myself.