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