You can get the last insert id from mySQL on a per-handle basis by asking for:

Which should get around the problem, unless you're using Apache::DBI or something else that pools database handles.

update: remembered something i saw in the lower reaches of Class::DBI::Extension: you can lock a mysql table using:

and later

But it's a rather drastic thing to do, and one of the main reasons some people are rude about MySQL.

Hrrmm... alas, when I try this on the same basis as the methods I did above, I still end up returning the last ID inserted, rather than the ID inserted by the querying script. I fear there may be no alternative to table locking.

§ George Sherston

I see a bit of PostgreSQL discussion here

You can get what you want with a sequence in PgSQL.

#earlier in SQL
CREATE TABLE tbl ( id serial, col2 text, col3 text );
NOTICE:  CREATE TABLE will create implicit sequence 'tbl_id_seq' for S
+ERIAL column 'tbl.id'
[download]

my $myid;
my $dbh=DBI->connect("DBI:Pg(AutoCommit=>0,RaiseError=>1):dbname=foo")
+;
eval {
    $dbh->do("INSERT INTO tbl ( col2, col3 ) VALUES ( 'val', 'val3' )"
+;
    my $sth=$dbh->prepare("SELECT currval('tbl_id_seq')");
    $sth->execute;
    $sth->bind_columns( \($myid ));
    $sth->fetch;
    $dbh->commit;
};

if($@)
{
    $dbh->rollback;
    #enlightening error message goes here
}

# $myid has your id in it here.
[download]

You would put a sequence type as a column in the table with autoincrement running etc then it would have a name and you select currval('your_sequence_name_here') which PostgreSQL guarantees will be the one from the most recently inserted row in the current transaction.

Also I like to avoid wildcards in my SQL statements from inside scrips so that its obvious the intent of the script without having to look in the database to find out what columns etc are being affected.

Actually even without transactions, I am pretty sure currval() will return the last id that your process used on the given sequence.

pmas
To make errors is human. But to make million errors per second, you need a computer.

MySQL does indeed support transactions but it requires you use the right table type (preferably BDB or InnoDB). So you may have to do some re-compilation/installation before you can get transactions working, but they're definitely there.
HTH

broquaint

Glad to hear it's not just me. The version of MySQL I have, which is quite recent, certainly doesn't support transactions.

§ George Sherston

$dbh->do("LOCK TABLES tbl WRITE");
$dbh->do("INSERT INTO tbl VALUES(NULL,'foo','bar')");
my $ref = $dbh->selectcol_arrayref("
    SELECT LAST_INSERT_ID() FROM tbl");
$dbh->do("UNLOCK TABLES");
[download]

DATABASE HANDLES
The DBD::mysql driver supports the following attributes of database ha
+ndles (read only):

    $infoString = $dbh->{'info'};
    $threadId = $dbh->{'thread_id'};
    $insertId = $dbh->{'mysql_insertid'}
These correspond to mysql_info(), mysql_thread_id() and mysql_insertid
+(), respectively.

insertid
MySQL has the ability to choose unique key values automatically. If th
+is happened, the new ID will be stored in this attribute. This attrib
+u
+te is not valid for DBD::mSQL. An alternative way for accessing this 
+a
+ttribute is via $dbh->{'mysql_insertid'}. (Note we are using the $dbh
+ 
+in this case!)
[download]