[Dbix-class] RT #21260: Problem with last_insert_rowid and DBD::ODBC

[Marc Mims]

[CC-ed to the list in case mst or others care to weigh in.]

Eriam,

I was perusing RT and saw your bug report:
http://rt.cpan.org/Public/Bug/Display.html?id=21260


Storage::DBI::ODBC attempts to determine the backend database type and
reblesses to an appropriate class if one exists.  If it cannot determine
the type, or if there is no DBI extension for it, it just falls back to
Storage::DBI's _dbh_last_insert_id.  That defaults to the SQLite's
last_insert_rowid.

I could provide an ODBC default for _dbh_last_insert_id, but there is no
generic, reliable way to get the last insert id via ODBC.  From DBI.pm:

   --------------------------------------------------------------------------------
   "last_insert_id"

     $rv = $dbh->last_insert_id($catalog, $schema, $table, $field);
     $rv = $dbh->last_insert_id($catalog, $schema, $table, $field, \%attr);

   Returns a value 'identifying' the row just inserted, if possible.
   Typically this would be a value assigned by the database server
   to a column with an auto_increment or serial type.  Returns undef
   if the driver does not support the method or can't determine the
   value.

   The $catalog, $schema, $table, and $field parameters may be
   required for some drivers (see below).  If you don't know the
   parameter values and your driver does not need them, then use
   "undef" for each.

   There are several caveats to be aware of with this method if you
   want to use it for portable applications:

   * For some drivers the value may only available immediately after
     the insert statement has executed (e.g., mysql, Informix).

   * For some drivers the $catalog, $schema, $table, and $field
     parameters are required, for others they are ignored (e.g.,
     mysql).

   * Drivers may return an indeterminate value if no insert has been
     performed yet.

   * For some drivers the value may only be available if
     placeholders have not been used (e.g., Sybase, MS SQL). In this
     case the value returned would be from the last non- placeholder
     insert statement.

   * Some drivers may need driver-specific hints about how to get
    the value. For example, being told the name of the database
    'sequence' object that holds the value. Any such hints are
    passed as driver-specific attributes in the \%attr parameter.

   * If the underlying database offers nothing better, then some
    drivers may attempt to implement this method by executing
    ""select max($field) from $table"". Drivers using any approach
    like this should issue a warning if "AutoCommit" is true
    because it is generally unsafe - another process may have
    modified the table between your insert and the select. For
    situations where you know it is safe, such as when you have
    locked the table, you can silence the warning by passing "Warn"
    => 0 in \%attr.

   * If no insert has been performed yet, or the last insert failed,
    then the value is implementation defined.

   Given all the caveats above, it's clear that this method must be used with care.
   --------------------------------------------------------------------------------

So, what's the right thing to do here?  Perhaps a warning in _rebless:

    warn "No DBI extension for this DB backend, using DBI's
        last_insert_id as a last resort\n";

Then in _dbh_last_insert_id:

    return $dbh->last_insert_id(undef, undef, $table, $column);

What backend database were you using when you encountered the error?
Providing a specific extension for it would be best.  Two exist,
currently:

    http://search.cpan.org/src/ASH/DBIx-Class-0.08010/lib/DBIx/Class/Storage/DBI/ODBC/DB2_400_SQL.pm
    http://search.cpan.org/src/ASH/DBIx-Class-0.08010/lib/DBIx/Class/Storage/DBI/ODBC/Microsoft_SQL_Server.pm

And as you can see from the source, they have VERY different
implementations for obtaining a reliable last insert id.

    -Marc