On Fri, Mar 07, 2003 at 11:12:00PM -0800, Jonathan Leffler wrote:Tim Bunce wrote: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).
Can you tell if the last insert generated a SERIAL or SERIAL8 id? Or would the application need to give you a hint?
I don't need an application hint, though I can't readily tell either.
The SERIAL I can guess pretty reliably simply by looking at the field in the SQLCA -- if it isn't zero, chances are a serial was inserted.
With SERIAL8, I have no information readily available.
However, the important thing is simply that the client code asks for the information, and I give it to them. They know that it an insert occurred, so 'the application gives me a hint' by asking for the information. This, along with Paul DuBois's observation that $dbh is better than $sth (at least, $sth only) because of $dbh->do() where there is no visible $sth to interrogate, suggests that $dbh is preferable to $sth. Trying to track SERIAL8 per statement handle would be a pain.
[...]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.
Yeap. That's pretty common.
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.
But that implies that the application *knows* it was a serial8. I can imagine that a table might be altered from serial to serial8, but you wouldn't want to have to change all the applications.
Interesting observation, but existing applications can't fish 64-bit integer values out of 32-bit storage reliably -- so they'd have to change. That, in conjunction with various other observations about the (lack of) usage of features that are not as operational as I'd like tends to suggest that such features are not very widely used anyway.
[...]
--
Jonathan Leffler ([EMAIL PROTECTED], [EMAIL PROTECTED]) #include <disclaimer.h>
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
