cog has asked for the wisdom of the Perl Monks concerning the following question:
Here's a shorter version of what I was trying to do:
my $sql = "INSERT INTO table_name" . " (a, b, c)" . " VALUE (?, ?, ?)" . " ON DUPLICATE KEY" . " UPDATE field_three = ?"; my $sth = $dbh->prepare( $sql ); for my $entry ( keys %entries ) { $sth->execute( $entries{'a'}, $entries{'b'}, $entries{'b'} ); my $id = $dbh->last_insert_id( undef, undef, undef, undef ); # use $id to insert stuff in another table }
Where the id is an auto-increment column, and also the primary key.
The problem is that last_insert_id() not always gives me the right id. (concurrency? transaction issues?)
I actually tried doing a SELECT on the table right after that INSERT, but since I'm doing this inside a transaction, the SELECT clause returns nothing...
Any ideas on how to go around this? Am I missing something trivial?
Both DBI's and MySQL's documentation point to a few CAVEATS around last_insert_id()...
Back to
Seekers of Perl Wisdom