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

Dear fellow monks,

On an earlier posting I asked for the most portable way of getting the field names of an SQL table through DBI. Now I want to know the simplest/most portable way of getting the last inserted id of a table that has been set up to do auto-increment of primary keys. I don't want to be pessimitic, but previously I've only seen driver dependant ways of doing this (e.g. the LAST_INSERT_ID() call in MySQL) and now face the same problem with DBD::ODBC and hope somebody knows how to do it in a cross-platform way.

Thanks in advance for any help.BR>
Brother Greg

Replies are listed 'Best First'.
Re: Finding last inserted id through DBI
by DrManhattan (Chaplain) on Aug 09, 2000 at 08:02 UTC

    SQL to the effect of "SELECT * FROM tablename ORDER BY timestampfieldname DESC LIMIT 1" should do what you want.

    It ought to work on any SQL database, but I won't swear to it as I don't have a non-MySQL server to test it on.

    -Matt

[reply]
RE: Finding last inserted id through DBI
by Yohimbe (Pilgrim) on Aug 10, 2000 at 00:00 UTC
    One of my favorite ways is: 
    $uniqueid= time . $$;
my $sql <<EOF;
INSERT INTO FOO (a,b,c) VALUES ($uniqueid,$foo,$bar);
EOF
$dbh->do($sql);
$sql="SELECT a,key from FOO where a=$uniqueid;
my $sth=$dbh->prepare_cached($sql);
$sth->execute();
my $cursor = $sth->fetchrow_hashref;
my $id=$cursor{"key};

    [download]

    --
    Jay "Yohimbe" Thorne, alpha geek for UserFriendly
[reply]
[d/l]
Re: Finding last inserted id through DBI
by Anonymous Monk on Aug 09, 2000 at 11:37 UTC
    Just a question to DrManhattan, but how do you ensure that you're program hasn't been executed and inserted another line in the amount of time it takes to do the insert, select sequence?
[reply]
      do it within the same transaction?
[reply]
RE: Finding last inserted id through DBI
by t0mas (Priest) on Aug 09, 2000 at 12:43 UTC
    If I assume you want to use the value to insert a new row, I would go for something like (pseudo code): 
    # Reset number of tries
$trycount=0;

{ 
  # Increase number of tries
  $trycount++;

  # Make sure we don't try forever
  last if ($trycount > 10)

  # Put last id + 1 in $id
  SELECT MAX(id)+1 FROM table;

  # Try to insert new row and check for errors
  INSERT INTO table (id,col1,coln) VALUES ($id,$col1,$coln);

  # Try again
  redo if (error eq "Unable to insert due to duplicate values");
}

# Handle too many tries
die "Too many tries" if ($trycount > 10);

    [download]
    The things you need to know is the key (id) of the table and how DBD:ODBC reports a failure to insert because a row is already there (someone else was faster).

    /brother t0mas
[reply]
[d/l]
Re: Finding last inserted id through DBI
by prodevel (Scribe) on Aug 09, 2000 at 09:01 UTC
    select sequence.curval works natively in Oracle...
[reply]

Back to Seekers of Perl Wisdom