in reply to DBI: last insert id

I'm trying to do a select statement using dbi to get the last inserted id.

That trick tends to be very database dependent, and may be documented in the POD for the corresponding DBD. Perhaps if you would mention which database you're using, someone might know the specifics.

Replies are listed 'Best First'.
Re: Re: DBI: last insert id
by slackah (Initiate) on Apr 24, 2003 at 01:21 UTC
    Sorry i had thought that since i was using DBI that the database would not be a factor. I'm using mysql though. I've tried using the LAST_INSERT_ID() function suggested on mysql website but havn't gotten it to work.
    $sth=$dbh->prepare("UPDATE history SET history_conf = LAST_INSERT_ID() WHERE history_id = LAST_INSERT_ID()")
[reply]
      Consult the DBD::mysql POD (via "perldoc DBD::mysql"). It tells of an easier way. After an insert, 
        $dbh->{mysql_insertid}

      [download]
      holds the most recently assigned id. Your code might then be rewritten as 
        my $id = $dbh->{mysql_insertid};
  $sth = $dbh->prepare(<<"SQL");
UPDATE history
SET history_conf = ?
WHERE history_id = ?
SQL
  $sth->execute($id, $id);

      [download]
[reply]
[d/l]
[select]
      I'm not sure, but that may only work if you do an insert in the same session. Are you?

      In any case, that function works fine for me, but I'm using it right after I do my inserts.

[reply]

In Section Seekers of Perl Wisdom