DBI provides a
last_insert_id method as of v1.38, but I haven't had luck in getting it to work, at least with my
DBD::mysql version (2.9003).
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};
}
Then you have a few small DBD-specific modules implementing
insert_id:
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');
}
(In my own module, I don't actually bless the objects into the subclass, but I do store the name of the DBD subclass and call
insert_id as a class method when I need it)
Subclassing is clean and easy to maintain, plus it encourages me to stretch support to as many DBDs as possible.
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.