topic Re: Help with tMysqlRow - Unable to delete Data in Talend Studio

Help with tMysqlRow - Unable to delete Data

_AnonymousUser — Sat, 16 Nov 2024 13:20:51 GMT

Hi all,
I currently have a job where I have to upload CSV data into a database on a monthly basis. This job is working fine.
I now have a scenario where I am to re-enter a particular month data into the database.

For this -
a) I need to delete the same month data already present in the database.
b) Then re-upload the data for that particular month from the CSV file.

As such, I modified my current job and added a tMysqlRow component with a Delete query. I tried to place an "OnComponentOk" on my input CSV file to link with the tMysqlRow component.
When I run the job, I see duplicate records for a particular month in the database ( Old monthly data does not get deleted)
Could someone help me on this....
Best Regards,
Zohaib

Re: Help with tMysqlRow - Unable to delete Data

Anonymous — Tue, 20 Jul 2010 21:52:42 GMT

The OnComponentOK link does not always do what you expect. This has to do with the architecture of Talend-- each component consists of a BEGIN, MAIN, and END section. The OnComponentOK trigger fires when the MAIN section of the code completes without error. Because of this, I always recommend avoiding using this trigger type unless you are sure of what it will do for that specific component.
I would suggest reworking your job to avoid use of OnComponentOK. to accomplish this, I imagine a high-level job structure like this:
1) examine file and compare to control table. is this a new file?
2) if it exists in the control table, delete all rows in data table and control table with the same load_id. if it does not exist in the control table, do nothing
3) Create row for control table and Load the file.

Re: Help with tMysqlRow - Unable to delete Data

_AnonymousUser — Wed, 21 Jul 2010 16:49:44 GMT

Thanks for the reply.
I do not have a specific ID/Key field in the table. As such, I cannot determine, if the data in the CSV contains new rows or not.
Is there a way, where I can execute a delete operation, before the CSV data is loaded into the table?
Thanks again,
Zohaib

Re: Help with tMysqlRow - Unable to delete Data

Anonymous — Wed, 21 Jul 2010 19:03:27 GMT

yes. When you load the original csv, log the file name into a control table-- also you will want to generate a load_id that will be tagged onto every row of data loaded from that csv.
when you receive a file of the same name, you will be able to retrieve the load_id, and then use that to delete all data associated with the previous file before loading the new one.