I'd appreciate a summary of which drivers support some form of ``last insert id'' and details of the interface they provide.
Specifically... via an attribute or method? at the sth or dbh level, or both? any other details that might be relevant...
DBD::Informix has it for SERIAL columns. It is a value in the SQLCA which is accessed via either the statement or the database handle - same space (there is an SQLCA record per database handle).
It is volatile - that is, the next SQL operation on the same database handle destroys the information. It would not be desparately hard to provide a one-element cache per statement handle, but no-one using DBD::Informix has ever suggested it.
Now, there is also a SERIAL8 type. I had to go manual bashing to find out how to get the last insert ID for that, but there is a function that provides the information at the C interface -- I'd have to work out a way of handling that (not least because, although it is pointless, a single table can have both a SERIAL and a SERIAL8 column).
Finally, in IDS 9.40, there are sequences as found in Oracle and DB2. You manipulate those with seqname.currval and seqname.nextval and SELECT statements.
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?
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);
I'm neutral between $dbh and $sth. Obviously, it is a $sth that 'sets' the value, but there's typically just one place that stores it, effectively at the $dbh level, so there's room for arguing either way.
For the special case of SERIAL8, I can provide a suitable access method - probably $dbh->ix_last_serial8() via the nice new mechanism you've exposed for adding methods to the DBI. Or as an attribute such as $dbh->{ix_last_serial8} -- it's much the same.
For sequences, I am not sure whether it is necessary -- are you planning to provide access to sequences for DBD::Oracle? If so, does the table name map to the sequence name? If not (as I suspect), then am I right in thinking that you'd expect people to do an explicit SELECT seqname.currval FROM dual or some equivalent?
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.
Did my patch to data_sources make it? Curiosity is killing this cat, that's all.
--
Jonathan Leffler ([EMAIL PROTECTED], [EMAIL PROTECTED]) #include <disclaimer.h>
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
