You are not logged in.

Unanswered posts



Important! This site has been replaced. All content here is read-only. Please visit our brand-new community at https://community.talend.com/. We look forward to hearing from you there!



#1 2013-07-10 11:25:37

useruser
Member
31 posts

useruser said:

mapping foreign key in Tmap with 2 mysqloutput

Tags: [mysql, schema]

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

Offline

#2 2013-07-11 02:21:22

alevy
Member
1923 posts

alevy said:

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()".

Offline

#3 2013-07-11 09:36:46

useruser
Member
31 posts

useruser said:

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 .

Offline

#4 2013-07-11 09:49:13

alevy
Member
1923 posts

alevy said:

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.

Offline

#5 2013-07-11 10:15:55

useruser
Member
31 posts

useruser said:

Re: mapping foreign key in Tmap with 2 mysqloutput

Hello ,
This is Tmap setting . How I will do it ?
mini_112906_Tmap.png

Last edited by useruser (2013-07-11 10:19:24)

Offline

#6 2013-07-11 10:24:45

alevy
Member
1923 posts

alevy said:

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.

Offline

#7 2013-07-11 10:44:45

useruser
Member
31 posts

useruser said:

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

Offline

#8 2013-07-11 10:47:06

alevy
Member
1923 posts

alevy said:

Re: mapping foreign key in Tmap with 2 mysqloutput

Screenprint the settings?

Offline

#9 2013-07-11 11:19:34

useruser
Member
31 posts

useruser said:

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
mini_112917_TmysqlOutPut2.png

Offline

#10 2013-07-12 00:11:30

alevy
Member
1923 posts

alevy said:

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 [ChildTable] (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.

Offline

#11 2013-07-12 08:53:38

useruser
Member
31 posts

useruser said:

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

Offline

#12 2013-07-12 08:54:48

alevy
Member
1923 posts

alevy said:

Re: mapping foreign key in Tmap with 2 mysqloutput

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

Offline

#13 2013-07-12 09:27:19

useruser
Member
31 posts

useruser said:

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)

Offline

#14 2013-07-12 13:37:38

alevy
Member
1923 posts

alevy said:

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.

Offline

#15 2014-09-30 10:04:17

onseri
Member
1 post

onseri said:

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

Offline

#16 2014-09-30 11:03:51

sanvaibhav
Member
1719 posts

sanvaibhav said:

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


Talend Certified Consultant

Offline

#17 2016-07-23 08:58:55

yazidbalde
Member
1 post

yazidbalde said:

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

Offline

Board footer

Talend Contributor Agreement - Talend Website Privacy Policy