DBI: last insert id

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

Replies are listed 'Best First'.
Re: DBI: last insert id by lachoy (Parson) on Apr 24, 2003 at 03:51 UTC
FWIW, if you're ever moving to a different system you might also find Generating portable SQL primary key sequences useful. Chris M-x auto-bs-mode	[reply]
Re: DBI: last insert id by dws (Chancellor) on Apr 24, 2003 at 01:04 UTC
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.	[reply]
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]
Re: Re: Re: DBI: last insert id by dws (Chancellor) on Apr 24, 2003 at 01:31 UTC
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]
Re: Re: Re: DBI: last insert id by The Mad Hatter (Priest) on Apr 24, 2003 at 01:30 UTC
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]
Re: DBI: last insert id by Improv (Pilgrim) on Apr 24, 2003 at 03:28 UTC
As a completely seperate way to do this, if it's ok that any updates to a row make it considered the 'last inserted', you can add a new column to your table of type TIMESTAMP, and mysql will keep it up to date as the most recent insert or update to that row. You could then select on that, and it'd be ok if it's from a separate session. Hope this is at least interesting, and maybe helpful :)	[reply]