topic Re: [resolved] MySQL last insert id in Talend Studio

[resolved] MySQL last insert id

Anonymous — Sat, 16 Nov 2024 14:08:15 GMT

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.

Re: [resolved] MySQL last insert id

Anonymous — Wed, 03 Dec 2008 22:17:01 GMT

Hi,
first you seem to have an error on your tMySQLOutput component (see red cross).
Despite this, in the db table, make sure your id is set to autoincrement.

Re: [resolved] MySQL last insert id

Anonymous — Fri, 05 Dec 2008 07:16:12 GMT

Long-time lurker, first-time poster...
I'm having the same issue with virtually the same setup, though I don't have any compile problems in my job. In my tMySqlLastInsertId_1 component, I have tMySqlConnection_1 in my "Link with" field and I'm using a built-in schema. Any advice on this would be extremely helpful.

Re: [resolved] MySQL last insert id

Anonymous — Fri, 05 Dec 2008 08:17:08 GMT

Hello milou
Please see my screenshot.
Best regards
shong

Re: [resolved] MySQL last insert id

Anonymous — Fri, 05 Dec 2008 09:43:19 GMT

Hello shong,
I try to reproduce your example.
See my screenshot.
I've just replace the tRowGenerator componant by a tFileInputExcel.
When I run the job, I see 2 problems:
- I have value 0 instead of null for last_insert_id column in console.
- If you look at the statistics, 20 rows have been inserted into database.
But only one row is displayed in console for last_insert_id...
Do you know why ?
Thx for your help.

Re: [resolved] MySQL last insert id

Anonymous — Sat, 06 Dec 2008 05:47:04 GMT

Hello
Can you show us your table structure? Is there a column defined as auto_increment in your talbe?
Best regards
shong

Re: [resolved] MySQL last insert id

Anonymous — Mon, 08 Dec 2008 10:05:36 GMT

Hi shong,
I've uploaded 2 screenshots:
- structure of my table (view from phpmyadmin)
- configuration of tMysqlOutput component (which insert value in my database)
You can see that my table has an autoincrement column as primary key.

Re: [resolved] MySQL last insert id

Anonymous — Tue, 09 Dec 2008 02:33:00 GMT

Hello milou
I have run my job again with a table created on phpmyadmin and it works fine.
Please send me your job via email.
Best regards
shong

Re: [resolved] MySQL last insert id

Anonymous — Tue, 09 Dec 2008 21:45:00 GMT

Hi shong,
I sent you my job by email this morning.

Re: [resolved] MySQL last insert id

Anonymous — Wed, 10 Dec 2008 08:44:29 GMT

Hi shong,
I update my job and found what it doesn't work:
I must check option "use an existing connection" in the tMysqlOutput component
and select the good tMysqlConnection in drop down list.
So it works !
I get a not null last inserted id when I execute the job.
But if the job insert more than one row in my table, I only get the inserted id for the last row.
How can I get the id for each row ?
It is possible ?

Re: [resolved] MySQL last insert id

Anonymous — Thu, 11 Dec 2008 03:15:36 GMT

Hello

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

Re: [resolved] MySQL last insert id

Anonymous — Thu, 11 Dec 2008 08:34:30 GMT

Hi shong,
I will test your solution asap.
Many thanks for your help.

Re: [resolved] MySQL last insert id

Anonymous — Thu, 11 Dec 2008 13:14:53 GMT

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

Re: [resolved] MySQL last insert id

Anonymous — Tue, 16 Dec 2008 20:04:38 GMT

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

Re: [resolved] MySQL last insert id

Anonymous — Thu, 05 Mar 2009 12:58:25 GMT

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 ?

Re: [resolved] MySQL last insert id

_AnonymousUser — Mon, 03 Aug 2009 10:54:50 GMT

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

Re: [resolved] MySQL last insert id

Anonymous — Wed, 21 Sep 2011 17:49:20 GMT

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.

Re: [resolved] MySQL last insert id

Anonymous — Tue, 12 Jun 2012 14:39:43 GMT

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

Re: [resolved] MySQL last insert id

_AnonymousUser — Wed, 23 Jul 2014 08:04:13 GMT

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