Getting last MySQL id with Class::DBI

broquaint has asked for the wisdom of the Perl Monks concerning the following question:

Is there a way to get the LAST_INSERT_ID (a MySQL 'feature') using Class::DBI or Class::DBI::mysql? Currently I'm using this this piece of code to do the job

Class::DBI::mysql->set_sql('lastid', 'SELECT LAST_INSERT_ID()');

## hacked from the Class::DBI::mysql methods
sub Class::DBI::mysql::id {
  my $class = ref $_[0] || $_[0];
  my $sth;
  eval {
      $sth = $class->sql_lastid();
      $sth->execute();
  };
  if($@) {
      $class->DBIwarn("lastid");
      return;
  }

  my $id = $sth->fetch->[0];
  $sth->finish();

  return $id;
}
[download]

From the documentation and the code it isn't apparent that this is already implemented, or is this perhaps a misuse of Class::DBI?
TIA

_________ broquaint

update (me): title change (was Getting the latest id with Class::DBI)

Comment on Getting last MySQL id with Class::DBI Download Code

Replies are listed 'Best First'.
Re: Getting last MySQL id with Class::DBI by bart (Canon) on May 19, 2003 at 11:42 UTC
Well the underlying DBI handle has the mysql specific attribute "mysql_insertid" at it's disposal, perhaps you might be able to get access to it. Provided Class::DBI allows direct access to its DBI handle, of course.	[reply]
Re:Getting last MySQL id with Class::DBI by edoc (Chaplain) on May 19, 2003 at 17:07 UTC
It's like magic! 8) `my $id = My::Class::DBI::Module->create({ field => 'value' }); print "id: $id\n";` [download] cheers, J	[reply] [d/l]
Re: Getting last MySQL id with Class::DBI by Phaysis (Pilgrim) on May 20, 2003 at 14:02 UTC
Or, you could do what I do: Before I execute the insert query, I put a write lock on the table, perform the insert, and then do a select query for the id of the most recently inserted row (using a column in my table definition of the type `timestamp` and the clause `ORDER BY modified DESC LIMIT 1`). I then release the write lock allowing other processes to do their thing. Or, instead of relying on a `timestamp` column, you could possibly just order by the primary key, descending. This should return the highest key id, which'll possibly be the most-recently inserted row. This 4-step process works well for me, and from what I've gathered it's fairly portable across database engines. -Shawn / (P^h) Phaysis If idle hands are the tools of the devil, are idol tools the hands of god?	[reply] [d/l] [select]
Re: Getting last MySQL id with Class::DBI by awgibbs (Initiate) on May 19, 2003 at 15:50 UTC
I think your lack of atomicity is going to get you into trouble, though if you have a low traffic environment it won't happen often, and when it does you'll be quite perplexed. The problem will be when one process (we'll call it "process A") makes an insert, then another process ("process B") makes an insert, and then process A checks the last insert ID, getting back the insert ID for process B's operation, and not its own. This may in fact never happen if your environment isn't being hammered on too heavily, but if it does happen, it will result in a bug that will be very hard to reproduce. To be absolutely safe, you need either a database mechanism that both inserts and gets the insert ID atomically (you can do this with DBI as Bart suggests, but I don't know about your wrapper), or you need to generate your own insert ID randomly, try inserting into the table with it, and also do exception handling for the rare scenario of a collision, in which case you'll want to generate another id and try again.	[reply]
Re: Re: Getting last MySQL id with Class::DBI by broquaint (Abbot) on May 19, 2003 at 15:59 UTC
I think your lack of atomicity is going to get you into trouble Or I would if `MySQL` didn't do the right thing concerning `LAST_INSERT_ID` - it is maintained on a per connection basis, so unless I change connections or do some equally silly I should always get back the right ID. See. 8.1.12.3 How Can I Get the Unique ID for the Last Inserted Row? for more info. HTH `_________ broquaint`	[reply]