Hi, I try to use the tMysqlLastInsertId componant but I don't understand how to use it. Description of the job I've made: - Read a spreadsheet file - Insert data into database - Log id of inserted data You can see a screenshot of my job in attachment. The problem is that the autogenerated "Last_insert_id" column contains always the value "0" instead of the value of autoincrement. Does anyone know how can I get the value of autoincrement ? Thx in advance for your help.
How can I get the id for each row ? It is possible ?
Yes, we can get all the new inserted id. There is a global variable can be used to get the first new inserted id:
tMysqlOutput_1_NB_LINE: the total number of new inserted rows.
For more information, please see my screenshots.
Best regards
shong
Hi shong,
I'm very happy to say it works very well !!
But I've just changed a component in your example to make it working for me.
My table contains 50 lines and has been truncated sometimes.
The value of id for "autoincrement" column is different from the line number.
Example:
Line 1: id=8810
Line 2: id=8811
...
When I execute the job, I can see in output:
first insert id: 8820
last insert id: 8839
tsampleRow expression: 8820..8839
And no line are displayed in the tLogRow componant.
I known why: tSampleRow filters on line number, not on value.
So it will filter from line 8820 to line 8839.
And I want to filter from value 8820 to value 8839.
I've just replaced tSampleRow by tFilterRow and put this condition:
myAutoIncrementColumn >= context.firstInsertID and myAutoIncrementColumn <= context.lastInsertID
and I've the list of id previously inserted.
Thx for your help and time.
I hope we keep in touch ... 🙂
Hi together, maybe I missed one point. But to get the "lastInsertedId" for each row you could just change the "onSubJobOk" and just continue in the data flow. Bye Volker
This solution does not work in a concurrent access environment !
any other db client could have inserted any identity value in the meantime.
could it be possible to modify all sql output component so to gain access to the identity column in a extra schema field ?
Hello,
I've got the same problem. When I try the example that illustrates tMysqlLastInsertId or your example, I only retrieve the same identifier 100 times. In my opinion, the job cannot retrieve the last identifier created by autoincrement when it's in the same transaction than insertion in Talend for Mysql, unlike the last_insert_id() of mysql wich works unitary.
.------------------------------.
| #1. tLogRow_2 |
+----------------+-------------+
| key | value |
+----------------+-------------+
| id | 0 |
| nom | Jean-Michel |
| last_insert_id | 0 |
+----------------+-------------+
.---------------------------------.
| #2. tLogRow_2 |
+----------------+----------------+
| key | value |
+----------------+----------------+
| id | 0 |
| nom | Jean-Micheline |
| last_insert_id | 0 |
+----------------+----------------+
last id inserted retrieve after commit:
14|Jean-Micheline
This problem seems to be pretty old:
https://community.talend.com/t5/Archive/Get-the-LAST-INSERT-ID-in-auto-increment-field/td-p/59074
Hey guys. I have something similar going on and I think I found another solution to the problem. Let's say I was inserting 3 rows. After inserting said rows let's say their newly inserted IDs are 11, 12, and 13. Upon using logrow to see what was being retrieved by the lastinsertid component I would see the new insert ids 0, 11, and 12. I fixed this problem by adding a "mysqlcommit" component between the output component and the lastinsertid component with the option to disconnect the session turned off. Upon making this change, the lastinsertids came back accurately. This really is NOT an optimal solution for the problem as I have to do a commit for the connection, which may not be optimal depending on what it is you're trying to achieve.
Hi all, I had the same issue (having the insert into a table, retrieve the id and use it in the rest of the treatment). Instead of having to define a tConnection + tCommit + tLastInserteId + all subjobs + (...) , I've simply used a Numeric.sequence in the tMap variable. I first retrieve the last index of the table I want to insert into and add to this index the Numeric.sequence. The new index can be used therafter and works for each row (no need to insert all and then retrieve all). This might not solve eveybody's problem but hopefully it should help some. 🙂
Hi all, If you use this component with tMySqlOutput, verify that the Extend Insert check box in the Advanced Settings tab is not selected. Extend Insert allows you to make a batch insertion, however, if the check box is selected, only the ID of the last line in the last batch will be returned. I think this will help you people Thank you
