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?
> 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.
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.
> 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?
I'll probably duck supporting named sequence for the time being.
But it would be worth looking at at some point in the future.
Tim.
