C.

It's not clear to me how well supported that is. I have heard that it isn't yet a bullet proof way to go about things.

Update: I just looked at the DBI that came installed on my installation, a Fedora Core 1 distro, and the DBI that comes installed on it is 1.37! :-( I am quite leery about relying on a version that even I don't have installed by default. I wonder how reasonable it is to stipulate that others upgrade their DBI to 1.38. *sigh*

I applaud your goal of DBD-agnosticism. But it's really hard to be DBD-agnostic without also being DBD-specific. Look at all the big cross-platform DB modules out there (Class::DBI for instance). What they do is have all DBD-specific features subclassed out. That's the "right" way to do it. In my own database module, I had to resort to something similar for getting the last-insert-id, as well as schema detection. It goes something like this:

package Foo;

sub new {
    my $class    = shift;
    my $dbh      = ...
    my $subclass = "$class::$dbh->{Driver}{Name}";

    eval "use $subclass; 1;"
        or croak "$dbh->{Driver}{Name} is unsupported";
    
    bless { dbh => $dbh }, $subclass;
}

sub dbh {
    $_[0]->{dbh};
}
[download]

package Foo::mysql;

sub insert_id {
    my $self = shift;
    $self->dbh->{mysql_insertid};
}

###########
package Foo::Pg;

## notice how Pg requires the table and column, while the
## other DBDs ignore these args

sub insert_id {
    my ($self, $table, $col) = @_;
    my $id;

    eval {
        my $seq = $table . '_' . $col . '_seq';
        my $sth = $self->dbh->prepare( "select currval('$seq')" );
        $sth->execute;
        ($id) = $sth->fetchrow_array;
        $sth->finish;
        1;
    } or die;
    
    return $id;
}

##########
package Foo::SQLite;

sub insert_id {
    my $self = shift;
    $self->dbh->func('last_insert_rowid');
}
[download]

Thanks, suggesting the paradigm of subclassing to deal with specific DBDs is helpful. I feel like that probably is the right way to deal with cross-DBD inconsistencies. I am already using this for some constructs in my code... For example, I have an abstract class called SQL::Conf that serves as a base for subclasses that parse the connection configurations for specific databases. There is a subclass of it called SQL::Conf::MySQL that parses .my.cnf files, and when you invoke the create_handle method of SQL::DBH it instantiates the appropriate SQL::Conf subclass based on the RDBMS that you specify either as an argument to the method, or as an environment variable. Right now, however, I'm not using such a paradigm for database schema detection... I guess I'm probably going to get burned when trying other databases. :-( Presently I have a SQL::Table class that takes just a handle and a table name, and issues a "describe table foo" query, and populates itself with the returned information. From what you've said, I suppose that that implementation will break when I try something other than MySQL. Ho-hum...

You might want to look at how Class::DBI solves this problem.

My question is, do you have a specific reason for creating something new instead of using Class::DBI?

The simple answer would be that "I hope to create something better". I am aware of the admonition "don't reinvent the wheel, unless you plan on inventing a better wheel". I hope that ultimately I will have created a better wheel, though I'm sure there will be much work involved, and am aware that ultimately Class::DBI may retain its dominance. Despite the possibility of my "failure", though, I would argue that there is much value to the existance of competing modules, as they will each force the other to adapt.

Thanks, that is quite helpful. I remember crossing this bridge before now, and I recall there being a troll under it.

As you have discovered getting this value from the various database server engines requires some very different approaches. The most generic approach is to simply do a SELECT max(idvalue) FROM table right after the insert. If you are running with AutoCommit OFF, and if the server supports transactions then this should return the correct value, as long as you COMMIT after the SELECT max(). Otherwise this is open to a race condition in the case of multiple inserts to that same table.

And FYI - for Sybase and MS-SQL the last inserted id (IDENTITY value) can be retrieved with a SELECT @@identity.

Michael

The obvious and simple fix is simply to perform a select right after the insert, calling up the auto-increment value that way. This mostly works, though I am concerned that it is going to result in my code executing twice as many SQL statements, and there is the further danger that the saved row is not unique.

This method is not safe in multi-process environments at all (CGI, mod_perl, etc) as the INSERT followed by the SELECT is not an atomic action. I would stay away from this kludge as it will only get you in trouble down the road.

On the other hand, I wonder what kind of overhead already exists as the result of the mysql_insertid's availability. Is DBD::mysql already transparently doing something along these lines, or is it somehow optimized?

No, there is no extra SQL call by DBD::Mysql (as best I know without looking at the source that is). I believe that in the client library for MySQL (which DBD::MySQL uses) the 'last_insert_id' is a function/variable/environmental-parameter of some kind. So its actually optimized in MySQL itself.

*snip a bunch of stuff* This still leaves a bitter taste in my mouth, but at least it would avoid the superfluity of executing a second statement that wasn't going to be of any value.

That whole idea leaves a bitter taste in my mouth as well. Anything that is assuming you don't have duplicate data (aside from the primary key) will get you in trouble.

So, what I would really like to know... Does some better, less hackish way exist to accomplish that which I have described, or am I stuck dealing with it in some way along the lines of what I have delineated herein?

Subclassing, subclassing, subclassing. There is no such thing as truely "vanilla" SQL (at least not that I have seen and which is usable in a real world situation), so the idea of complete DBD (therefore database) agnosticism is a fantasy. There really exists only one elegant solution, which is to subclass.

Also, keep in mind that all DBDs are not alike. Just because the DBI specification says it should do "A", doesn't mean the driver author has to actually implement that (or even better, have time to implement it). You would be surprised how minimal a DBD driver can be, and still "work" with DBI.

Well, what I was envisioning would be safe in a multi-process environment. It would not be selecting the max id of the table, but rather selecting the id of the row that was inserted by using as conditions the values of the fields of the object that was just saved. I didn't like it as a fix without the imposition that some other key be specified as unique, and furthermore it worries me because of the overhead of the extra query, particularly if it means piping a lot of data back to the server in the form of the conditions.

I upgraded to DBI 1.42, and tried using the last_insert_id() method, but alas it refused to do anything other than return undef. I don't know what is up with that... My version of DBD::mysql is one away from the latest version, so I tried installing the new one on the suspicion that this would fix it since the latest version of DBD:mysql was released right after the version of DBI that incorporated the last_insert_id() method, but the compile barfed. :-(

Well, what I was envisioning would be safe in a multi-process environment. It would not be selecting the max id of the table, but rather selecting the id of the row that was inserted by using as conditions the values of the fields of the object that was just saved

Even that is not safe, since you are relying on the fact that all your data (aside from the primary key) will be unique. This is okay if you have other uniquness constraints on the table, but otherwise you will eventually run into a problem with this. Most systems of this nature rely on the primary key as the natural unique identifier, I would really suggest you stick with that, and subclass for different DBDs.

-stvn