MySQL Forums
Forum List  »  Perl

insert works but last_insert_id() returns 0 the first time; works correctly thereafter
Posted by: K Jaco
Date: March 12, 2008 02:51PM

(If this post belongs somewhere else, please let me know how to move it.)

I am inserting a record into table 1 and wish to use its auto-incrementing id as a foreign key in table 2. I need to do several insertions to table 1, and for each of those I will do several insertions to table 2. So I use a for loop to insert one row to table 1, get the last_insert_id(), then use that in a for loop for all the insertions to table 2.

I have a test after getting the last_insert_id:

$sth = $conn->prepare("select last_insert_id()");
$sth->execute();
$res = $sth->fetchrow_hashref;

if ($res->{'last_insert_id()'}) {
# here I am collecting the data for table 2
$data2{table1_id} = $res->{'last_insert_id()'};
} else {
die "Couldn't get the last insert id.\n";
}
# for loop here to build rest of $data2 and insert it into table 2

The first problem is that while the first insertion to table 1 works (the record is indeed inserted), the first call of last_insert_id() returns 0. Thus the if clause fails and we do not make it to the inner for loop.

The real kicker is that in subsequent iterations through the outer for loop, insertion again works correctly BUT I get the correct value for last_insert_id(). Therefore the inner for loop executes, and rows are properly inserted to table 2. Thus I know the code must be correct, or it would never work.

I have verified there is not a new connection between inserting to table 1 and asking for the last_insert_id (though there is a new connection made for each insertion to table 1). I have used Data::Dumper to verify the contents of my hashes are correct. I also tried using $dbh->{mysql_insertid} (and appropriate code changes) with the exact same results.

Can anyone please point me in the right direction to figure out why this isn't working correctly? I have not seen this exact problem on this forum or anywhere else, and none of the solutions given for similar problems corrected my problem.

Thanks so much!

(I am using 5.0.45.)



Edited 2 time(s). Last edit at 03/13/2008 09:33AM by K Jaco.

Options: ReplyQuote


Subject
Written By
Posted
insert works but last_insert_id() returns 0 the first time; works correctly thereafter
March 12, 2008 02:51PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.