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()...
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: DBI and last_insert_id
by moritz (Cardinal) on Jan 28, 2009 at 21:11 UTC | |
by cog (Parson) on Jan 29, 2009 at 11:24 UTC | |
by moritz (Cardinal) on Jan 29, 2009 at 12:31 UTC | |
Re: DBI and last_insert_id
by perrin (Chancellor) on Jan 28, 2009 at 23:59 UTC | |
by cog (Parson) on Jan 29, 2009 at 11:26 UTC | |
by perrin (Chancellor) on Jan 29, 2009 at 16:56 UTC | |
by cog (Parson) on Jan 29, 2009 at 18:17 UTC | |
Re: DBI and last_insert_id
by graff (Chancellor) on Jan 29, 2009 at 08:05 UTC | |
Re: DBI and last_insert_id
by G@SP@R (Initiate) on Jan 29, 2009 at 13:30 UTC |
Back to
Seekers of Perl Wisdom