One Star

mapping foreign key in Tmap with 2 mysqloutput

Hello ,
I have an insert process that I do with Tmap . one of my TmysqlOuput has an auto generated key that I want to recuperate in one field of the other TmysqlOuput
Thanks
16 REPLIES
Seven Stars

Re: mapping foreign key in Tmap with 2 mysqloutput

The fastest running way is to simply have a second subjob where you use the first table as a lookup to get the ID.
The slower way is to make sure both tMysqlOutput components share a connection, in the first uncheck "Extend Insert" and in the second use the "Additional columns" to replace the field that should contain the key with the SQL expression "LAST_INSERT_ID()".
One Star

Re: mapping foreign key in Tmap with 2 mysqloutput

Thank you but when I try to use TmysqlLastInsertId it generates an
java.lang.NullPointerException . Have you an idea how to resolve it .
Seven Stars

Re: mapping foreign key in Tmap with 2 mysqloutput

I didn't say to use TmysqlLastInsertId.
But if you are trying to use a value from that component (being on the first output branch from tMap) in a second output table in tMap it won't work, because all tMap output tables are populated before any of the following components are executed instead of only populating each output table immediately before executing the components that use that output - see Jira. Which is why I suggested the approach I did.
One Star

Re: mapping foreign key in Tmap with 2 mysqloutput

Hello ,
This is Tmap setting . How I will do it ?
Seven Stars

Re: mapping foreign key in Tmap with 2 mysqloutput

Go back and read my previous post again. You need to use the "Additional columns" in tMysqlOutput to populate the FK, not tMap.
One Star

Re: mapping foreign key in Tmap with 2 mysqloutput

thank you , but when I try to replace the field that should contain the key with the SQL expression "LAST_INSERT_ID()" it generate Column 'filterCriteriaId' specified twice
Seven Stars

Re: mapping foreign key in Tmap with 2 mysqloutput

Screenprint the settings?
One Star

Re: mapping foreign key in Tmap with 2 mysqloutput

hello , when I changed the name of the new column it generate :
Unknown column 'new' in 'field list'.
thank you very very much
Seven Stars

Re: mapping foreign key in Tmap with 2 mysqloutput

Did you read the help on this functionality? The "Name" is the field name in your DB which this SQL expression will populate. The "Reference column" is the column name in your schema used by the "Position". The "Position" is where in your SQL statement to place this SQL expression.
I would be expecting you to "Replace" filterCriteriaId, in which case the Name should also be "filterCriteriaId".
What you are doing is trying to populate a column called "new" with LAST_INSERT_ID() and include that before filterCriteriaId in your SQL statement i.e. INSERT INTO (lastStatusChange, statusCode, new, filterCriteriaId) VALUES (?,?,LAST_INSERT_ID(),?).
If you look at the code generated, you'll see exactly what effect your changes have on the prepared statement used.
One Star

Re: mapping foreign key in Tmap with 2 mysqloutput

hello , but I tried that first and it gerenate an error : Column 'filterCriteriaId' specified twice that's why I changed the name of the column
Seven Stars

Re: mapping foreign key in Tmap with 2 mysqloutput

Did you set the "Position" to "Replace"? In your screenshot, it's set to "Before".
One Star

Re: mapping foreign key in Tmap with 2 mysqloutput

hello , I'm very sorry of the disturbance , it's my first experience with Talend . But when I set the "Position" to "Replace it's generate an error like : Cannot add or update a child row: a foreign key constraint fails (`datadb`.`filtercriteriarepositoryelement`, CONSTRAINT `FK_filterCriteriaId` FOREIGN KEY (`filterCriteriaId`) REFERENCES `filtercriteria` (`filterCriteriaId`) ON DELETE CASCADE ON UPDATE NO AC)
Seven Stars

Re: mapping foreign key in Tmap with 2 mysqloutput

That's an MySQL error and I don't use MySQL but it looks like it's suggesting the FK is invalid. Did you follow my other instructions: make sure both tMysqlOutput components share a connection and uncheck "Extend Insert"?
Test if the filterCriteriaId is being retrieved correctly by replacing your tMysqlOutput with tFlowToIterate -> tMysqlInput (also sharing a connection; "select last_insert_id()") --> tLogRow.
One Star

Re: mapping foreign key in Tmap with 2 mysqloutput

hi  guys kindly help after finishing mapping ....how can i create the fields on the mapped data back to the databases.. 
Four Stars

Re: mapping foreign key in Tmap with 2 mysqloutput

Connect your mapped fields to txxxDBOutput component...
What is the problem that you are facing?
Vaibhav
One Star

Re: mapping foreign key in Tmap with 2 mysqloutput

bonjour 
je suis debutant en talend et je voudrais créee une table a partir de talend avec une clé etrangere
mon probleme est que je sais pas comment specifier la contrainte de clé etrangere  a partir de tmap 
pourriez vous m'aider ???
merci