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};
}

[download]
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');
}

[download]
(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.

blokhead

In reply to Re: Auto-Increment and DBD Agnosticism by blokhead
in thread Auto-Increment and DBD Agnosticism by skyknight

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.