tmap and tmysql insert or update

One Star

tmap and tmysql insert or update

Hi there, I have a problem that I?ve tried to solve, I believe, in all the possible ways but I haven?t got it.
In my project I have a database A where I have some information about the company, besides I have a daily text file which is put every day in a folder. This daily text file contains some records that can exist or not in the database (if the record of the file exist in the database A, this must be updated in the database and if not the record has to be inserted in the database A. The field that is used for knowing which record has to be updated is PERS_ID, but this field is only in the database A so I have to compare some fields of the text file with the fields of the database to get the PERS_ID(left join).
I do this operation using a tMap and then I pass the information forward to the database for making an update or insert but these ones don?t work. They are not updating or inserting nothing.
With the schema shown in the figure 1, I believed that my project was fixed but it isn?t. I got can't execute update query
Issuing rollback() for database handle being DESTROY'd without explicit disconnect(). as message
Tags (1)
Community Manager

Re: tmap and tmysql insert or update

If you are working in Perl, try to change the filter condition in the tMap with:
$arref == PERSON.pers_id
One Star

Re: tmap and tmysql insert or update

Thanks for replying esabot. I've changed the filter condition as you said but I got as an error the following message can't execute update query
Issuing rollback() for database handle being DESTROY'd without explicit disconnect().
A thread exited while 2 threads were running.

What else can I do??

Thanks in advance
Community Manager

Re: tmap and tmysql insert or update

Hi Ricardo,

After a further look at your issue/screnshots, but I'm not sure to grab this right.
I don't get where the PERSON table comes from (in the condition filter)?
I mean the condition cannot be met this way, as the Person table and the Pers_id don't seem to be defined correctly in the Output and in the Input .
Why do you need to use the tmap3 (second last screenshot)?
Are the tMysqlInput and tMysqlOutput tables the same? are they both the PERSON one?
cheers,
Elisa
One Star

Re: tmap and tmysql insert or update

yeah the tMysqlInput and tMysqlOutput tables are the same. I'm doing this only for knowing which record I have to update depending on the PERS_ID.
About the tmap3, I used because I need the data of the fields name and last_name in lower. At the beginning I tried using a tPerl but it didnt work so I decided to used a tmap.
How can the condition be for updating my records?And I would like to know if I put a filter condition, can this make a problem when I'm using an update or insert function in tMysqlOutput ?
Thanks in advance
Ricardo
Community Manager

Re: tmap and tmysql insert or update

Ricardo,
pers_id column is actually useless in this case as this column is not present in the txt file, so it cannot be found through the lookup (Lookup means you create a join between two schemas using common columns).
Obviously you naturally used the Name, LastName and Birthday columns as lookup, to
retrieve the existing entries (the ones that need to be updated).
Why not trying to implement an Inner Join (button in the lookup table) and create a new DB output (but still to your DB) to gather the rejection from the inner join? This rejection corresponds to the entries that could not be looked up, hence these are the new entries that need to be inserted into the DB.
Then depending on the way your database has been created (are there primary keys..., is pers_id a db primary key...), you might be able to increment the Pers_id and allocate it to the new entries while inserting them into the DB. But this is no longer a Talend topic, but rather a DB issue...
HTH
Elisa
One Star

Re: tmap and tmysql insert or update

Thanks for all your help... I've tried to prove what you recommended me and it's working,well only 50% because only is updating the records, and now I got a new problem. In the moment when it's trying to insert a new record, the job fails Smiley Sad. I got the error message
DBD::mysql::st execute failed: Lock wait timeout exceeded; try restarting transaction at C:\Archivos de programa\TOS-2.0.0\workspace\.Perl\PRUEBAENNUEVO.job_Copy_of_projectro.pl line 909, <GEN1> line 38.
can't execute insert query
Issuing rollback() for database handle being DESTROY'd without explicit disconnect().
Issuing rollback() for database handle being DESTROY'd without explicit disconnect().
A thread exited while 2 threads were running.
I think I got this error because first it was updating the records and suddenly a new record to be inserted appeared, and the database was locked doing the updating of the records......
Community Manager

Re: tmap and tmysql insert or update

You could try to carry out the update and insert in two stages. You have to first write the rejection into a delimited file then run a second sub-job doing the insert operation to your DB.
The sequence is set through the "Then Run" (or "Run before" depending on your version of TOS) trigger link.
See screenshot attached