topic Re: [resolved] Delete MySQL row on condition in Talend Studio

[resolved] Delete MySQL row on condition

Anonymous — Sat, 16 Nov 2024 12:00:52 GMT

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 ?

Re: [resolved] Delete MySQL row on condition

Anonymous — Tue, 28 May 2013 16:43:50 GMT

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.

Re: [resolved] Delete MySQL row on condition

Anonymous — Wed, 29 May 2013 10:50:08 GMT

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.

Re: [resolved] Delete MySQL row on condition

alevy — Thu, 30 May 2013 08:56:25 GMT

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.

Re: [resolved] Delete MySQL row on condition

Anonymous — Thu, 30 May 2013 14:46:42 GMT

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 ?

Re: [resolved] Delete MySQL row on condition

alevy — Fri, 31 May 2013 00:17:48 GMT

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.

Re: [resolved] Delete MySQL row on condition

Anonymous — Fri, 31 May 2013 16:07:39 GMT

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