Help with tMysqlRow - Unable to delete Data

One Star

Help with tMysqlRow - Unable to delete Data

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

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.
One Star

Re: Help with tMysqlRow - Unable to delete Data

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

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.

Cloud Free Trial

Try Talend Cloud free for 30 days.

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.