One Star

Get the LAST_INSERT_ID in auto_increment field

Hello everybody,
In one of my jobs, I need to get auto_incremented id of fresh inserted field in a table to use it as a value (foreign key) of an other fields.
For exemple, I have a table :
-----------------------
| user_id | name |
-----------------------
An other table :
-------------------------------------
| adress_id | user_id | adress |
-------------------------------------
'user_id' and 'adress_id' are auto incremented, and for each user inserted, I have to get the new "user_id" generated and then use it to add related adresses.
I use MySQL, and there's a LAST_INSERT_ID function that does exactly that. So my (naive) solution was to use a tMysqloutput component to insert user, link it with "onOk" trigger to a tMysqlInput component to execute "select LAST_INSERT_ID();" and so retrieve the last generated ID.
Well, in fact it seems that there is two problems :
- the first one, the "onOk" trigger is rised only one time at the end of insert of all rows. So I need to find a way to execute my query after each successfull insert (well, performance are not the key here, it's a migration pb, not a batch) :
- the second one is that LAST_INSERT_ID function return the last inserted id for the related connection. It's cool because it's multi-connection safe, but in my case, the tMysqlInput component always respond "0" ("no insert done here").
So, is someone have an idea of what I have to do ? I think that the use case is pretty common, so I think that I took the problem in the wrong way.
Any help will be really appreciated Smiley Happy
Francois
ps : the mysql LAST_INSERT_ID function : http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id
Tags (1)
14 REPLIES
One Star

Re: Get the LAST_INSERT_ID in auto_increment field

Ok, so I investigate a little bit my problem, and I found this solution (I use TOS 2.0.0M3, I don't know if it works on other version) :
- I use a tMap (what a surprise Smiley Happy to order the tDBoutput execution ;
- I the two DBoutput have ot share the same connection, so I have to tweak the perl generated code in this way. To achieve that, I export the job and modifiy the perl code by hand, what is quiet ugly.
The modification in code is :
- replace all occurrences of dbh_tDBOutput_1 by dbh_tDBOutput ;
- replace all occurrences of dbh_tDBOutput_2 by dbh_tDBOutput ;
- comment the second connection creation
#my $dbh_tDBOutput = DBI->connect(
# getConnectionString(
# driver => $desc_tDBOutput_2{driver},
# dbname => $desc_tDBOutput_2{dbname},
# dbhost => $desc_tDBOutput_2{dbhost},
# dbport => $desc_tDBOutput_2{dbport},
# ),
# $desc_tDBOutput_2{dbuser},
# $desc_tDBOutput_2{dbpass},
# {
# }
#)
# or die "can't connect to database";
#

- comment the first disconnect :
#$dbh_tDBOutput->disconnect();

Thanks to that, columns "piecejointe_id" in tDBoutput_2 get the last inserted id value, which is the inserted id of the last row of tDBoutput_1.
It should be great if an option was available to enable "connection sharing" between database component. I don't know how to do that, perhaps thanks to a "enable global connection" checkbox for connection configured in metadata.
Perhaps it could help somebody;
Francois
One Star

Re: Get the LAST_INSERT_ID in auto_increment field

Ok, in fact it's a little bit less awful to replace tDBOutput_2 by a tPerlRow component, and to hijack the db connection used in tDBOutput1 in it with that code (for example) :
my $last_insert_id_query = 'select last_insert_id()';
my $last_insert_id_command = $dbh_tDBOutput_1->prepare($last_insert_id_query);
$last_insert_id_command->execute();
my @last_insert_id_row = $last_insert_id_command->fetchrow_array();
#ok, we just retrivied the last inserted id, update it in output row.
@output_row = @input_row;
$output_row = $last_insert_id_row;

Here, $dbh_tDBOutput_1 is the connection used in tDBOutput component 1.
Employee

Re: Get the LAST_INSERT_ID in auto_increment field

I've tried to do what you ask (I also made many tries with several methods) and there is one thing I don't understand: why don't you reuse the (mantis_)bug_file_table original id? It would be so much simpler.
I have a quite clean way to do it, without any code modification, but it requires to store the mantis_bug_file_table.id in piecejointes table.
One Star

Re: Get the LAST_INSERT_ID in auto_increment field

why don't you reuse the (mantis_)bug_file_table original id? It would be so much simpler

Well... Because I can't Smiley Happy
First reason : the destination database already contains data, so we just can't match id to id. In fact, it's not a valid reason, we can match id to (say) id + 10.000.000. In fact, we do that every time we can.
But we migrate data from one tools to another, and there inner data model is quite different. For some "special" tables, I need to create rows that actually don't exist in the source database. So, at the end of the process, it's likely to be more "commentaire" than in original database. And I need to retrieve the id of these rows to use it in other tables/rows.
But I think that the solution with "tPerlRow" is quite acceptable, the perl code is not a problem : the first way to migrate these special tables was to develop a perl specific script... And anyway, the job will have to interact with data on filesystem, so I can't totally avoid to write perl Smiley Wink
Thanks for your answer !
Francois
Employee

Re: Get the LAST_INSERT_ID in auto_increment field

why don't you reuse the (mantis_)bug_file_table original id? It would be so much simpler

Well... Because I can't Smiley Happy
First reason : the destination database already contains data, so we just can't match id to id. In fact, it's not a valid reason, we can match id to (say) id + 10.000.000. In fact, we do that every time we can.

I understand this reason :-)
My advise is to keep the original id in a column original_id (in the same table or in a dedicated table "mapping_original_new" with 2 columns "new_id", "original_id"). You first fill the "piecesjointe" table. Then you read once more "bug_file_table", use a tMap with a lookup (join) on piecesjointe, make the join on original_id, to retrieve the new_id that you can send to "commentaires".
With this method, no need to have "harder to maintain" code in a tPerlRow.
mysql> desc piecejointes;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| piecejointe_id | int(11) | NO | PRI | NULL | auto_increment |
| file | varchar(255) | YES | | NULL | |
| bug_id | int(11) | YES | | NULL | |
| original_id | int(11) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> desc commentaires
-> ;
+----------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------+------+-----+---------+-------+
| piecejointe_id | int(11) | YES | | NULL | |
| demande_id | int(11) | YES | | NULL | |
+----------------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> desc mantis_bug_file_table;
+-------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| bug_id | int(10) unsigned | NO | MUL | 0 | |
| title | varchar(250) | NO | | NULL | |
| description | varchar(250) | NO | | NULL | |
| diskfile | varchar(250) | NO | | NULL | |
| filename | varchar(250) | NO | | NULL | |
| folder | varchar(250) | NO | | NULL | |
| filesize | int(11) | NO | | 0 | |
| file_type | varchar(250) | NO | | NULL | |
| date_added | datetime | NO | | 1970-01-01 00:00:01 | |
| content | longblob | NO | | NULL | |
+-------------+------------------+------+-----+---------------------+----------------+
11 rows in set (0.00 sec)
One Star

Re: Get the LAST_INSERT_ID in auto_increment field

I'm not sure I understand what you want me to do... In fact, I think I see, but I don't understand how it works. I can't modify the destination database (it's in production) to add columns to "piecejointes" table, so I need to use the other option you purpose : use a temporary tables "mapping_original_new" (same structure as your "piecejointes" :
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| piecejointe_id | int(11) | NO | PRI | NULL | auto_increment |
| file | varchar(255) | YES | | NULL | |
| bug_id | int(11) | YES | | NULL | |
| original_id | int(11) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+

So I begin to file this table, and for each row, I can retrieve the new "piecejointe_id" thank to a join on original_id, as you describe me. And finally, I have to copy data from temp table to real "piecejointes" table.
But the id retrieve by that method as nothing to do with the the "piecejointe_id" of the matching row in real "piecejointes" id... And I think I can't copu the piecejointe_id from temp table to real table, for the same reason as explain in my last post.
Well, the only way to make it work I see is :
1. begin to copy every row of real "piecejointes" table to "mapping_original_new" table (so that the existing piecejointe_id also exist in "mapping_original_new") ;
2. process as above
3. copy from "mapping_original_new" to "piecejointes"
But even as this, I think that I can get some side effects / unwanted cases. For example, what happen if between step 2 and 3, some "piecejointes" are inserted in the table by an other process ? They may steal our fresh new piecejointe_id, no ? Perhaps, I can add say 1000000 to id when I copy from the temp table to the real one (and same thing in "commentaires"). But well, it begins to become quite complex to avoid 3 line of perl...
Thanks you very mutch for your answer and the time spent on my problem, in any case your method will be usefull in an other mapping job Smiley Happy
Francois
Employee

Re: Get the LAST_INSERT_ID in auto_increment field

You're right, in case you can't add a piecejointes.original_id, my method can't work (because tMysqlOutput) can only insert in a single table. Your solution based on a tPerlRow is fine then :-)
I wanted to change your code to:
$output_row =
$dbh_tMysqlOutput_5->last_insert_id(undef, '%', 'piecejointes', 'piecejointe_id');

but this function is very "bugged" (when last_insert_id%100 == 0, the returned id is 0!!!) and does not work the same way with different databases (which is not a problem for you), while the pure SQL statement is working fine.
Very interesting topic, thanks for the discussion :-)
One Star

Re: Get the LAST_INSERT_ID in auto_increment field

Very interesting topic, thanks for the discussion :-)

Thank you for the time spent Smiley Happy
It's quite enjoyable to have a so good feedbback from developpers, FOSS developpers are not always so open minded with there users, especially for "business" oriented software (and I know what I'm talking about, I'm one of them Smiley Wink.
One Star

Re: Get the LAST_INSERT_ID in auto_increment field

I tried to implement your suggested solution, but failed (because I could not translate it to Java from Perl).
So I came up with another solution to this problem - using very simple stored procedure and tMysqlISP.
I wrote a stored procedure "InsertNewPerson" to create an "empty" record in the main table and return its id. You can then use this id to
1) populate the rest of the fields in this new record using "Update" function in (tMysqlOutput) (e.g. your Users table)
2) add this id as a foreign key in a related table (e.g. Address table)

Create New Procedure:
DELIMITER //
CREATE PROCEDURE InsertNewPerson (OUT newID BIGINT)
BEGIN
INSERT INTO edvpersons values ();
SELECT MAX(ID) FROM edvpersons INTO newId;
END; //
DELIMITER ;
One Star

Re: Get the LAST_INSERT_ID in auto_increment field

Here are the diagrams for my previous post
One Star

Re: Get the LAST_INSERT_ID in auto_increment field

Why dont you guys use 'tmysqllastinsertid' component. you just need to use same connection for insertion and in this component.
One Star

Re: Get the LAST_INSERT_ID in auto_increment field

I don't know if i really get your problem but if i have correctly understood your problem here is what i propose:
Use a tmysqlInput to get the your last Id and put it in a context variable using a tjavarow (for instance context.lastId).
Then in the tmap which is used to insert the values, create a variable with Numeric.sequence("sequence",context.lastId,1). Link the output with this variable (meaning link this variable with the field autoincremented field).
One Star

Re: Get the LAST_INSERT_ID in auto_increment field

What happens if you are loading data into a target database that can be used by a web application so somebody can be creating new records at anytime.. Isn't there away to get the auto incremental id of the inserted record like it is possible to be done in java with a prepared statement?
surely the tMysqlLastInsertId would work but the thing is that it returns 0 no matter if you are using the same connection for the insert.
we have to find an easy and natural way to solve this guys.
One Star

Re: Get the LAST_INSERT_ID in auto_increment field

I got the tMysqlLastInsertId to work, to make it work do the following.

1) Use a TMysqlConnection to create the connection at the beginning. (use auto commit false to be able to rollback as we ar working with miltiple liked inserts)
2) run your insert
3) use the tMysqlLastInsertId connected from a OnComponentOK link or an OnSubjobOK link, not in a main line and it will work.
4) run your next insert using the Last Insert ID,
5) Run a Commit or Rollback using tMySQLRollBack or TMySqlCommit depending on what you want to do.

Note: Note that when ever you run a transaction even if you roll it back, and the insert is not reflected in the database, the auto incremental used will be blocked as used by the database, so if your previous was 140 and you rollback 141, the next execution of an insert in the database will use 142. so 141 wont exist.