I'd appreciate a summary of which drivers support some form of ``last insert id'' and details of the interface they provide.
For MySQL:
Specifically... via an attribute or method?
Attribute: $h->{mysql_insertid}
at the sth or dbh level, or both?
It's available at the dbh level. If you issue the statement that generates an AUTO_INCREMENT value using a statement handle, the attribute also is available via that statement handle as well.
In both cases, the value is available only until you issue another statement. The dbh handle value is reset for each statement, no matter how you issue the statement. A value that is available via a statement handle remains available on that handle as long as you don't issue another statement with *that* handle. (For example, if you issue a statement with another statement handle, the first statement handle retains its attribute value.)
any other details that might be relevant...
Also, for databases that do _not_ support a ``last insert id'' concept in the client API, are there any SQL mechanisms for finding the last insert id?
SELECT LAST_INSERT_ID()
This differs from use of the client-side mysql_insertid attribute in that is is not reset for each statement; LAST_INSERT_ID() retains its value until you issue another statement that generates another AUTO_INCREMENT value.
LAST_INSERT_ID() is connection-specific. Activity by clients on other connections do not affect the value you get on your own connection, even if those clients generate new AUTO_INCREMENT values.
I'm currently (very vagely) thinking that it'll be a dbh level method something like this:
$id = $dbh->last_insert_id(); $id = $dbh->last_insert_id($table_name, $column_name);
where many drivers will ignore the parameters, but some might need them, for example to do:
select $column_name from $table_name where $column_name IS NULL
which is, I believe, the 'standard' ODBC way to get the last insert id. But I'm working from memory so could easily be wrong or confused here. (And, of course, the select would only reliably return the right value if AutoCommit is off so the table would still be locked after the insert.)
Speak up!
Thanks.
Tim.
p.s. I'm hoping DBI 1.35, which I've just released, will be fairly stable and I can aim to put new things like this, and 'take_imp_data' for Stas, into development releases leading up to a DBI 1.36.
