Building DBD::InterBase with last_insert

moritz has asked for the wisdom of the Perl Monks concerning the following question:

I'm in the middle of porting a DBIx::Class based application to the firebird database, and thus DBD::InterBase.

On insert statements I get the error message

Can't locate DBI object method "last_insert_rowid" via package "DBD::I
+nterBase::db" at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1275
+.
[download]

which means that DBD::InterBase doesn't seem to support the last_insert_id method as DBIx::Class expects.

Ironically during the build process a file InterBase.xsi is generated, which contains such a method:

#ifdef dbd_db_last_insert_id

void
last_insert_id(dbh, catalog, schema, table, field, attr=Nullsv)
    SV *        dbh
    SV *        catalog
    SV *        schema
    SV *        table
    SV *        field
    SV *        attr
    CODE:
    {
    D_imp_dbh(dbh);
    ST(0) = dbd_db_last_insert_id(dbh, imp_dbh, catalog, schema, table
+, field, attr);
    }

#endif
[download]

So there seems to be support for last_insert_id, but it's not enabled. How can I enable it? If I simply remove the #ifdef and #endif lines from that file, I get this message many times during make test:

install_driver(InterBase) failed: Can't load '/home/moritz/tmp/DBD-Int
+erBase-0.48/blib/arch/auto/DBD/InterBase/InterBase.so' for module DBD
+::InterBase: /home/moritz/tmp/DBD-InterBase-0.48/blib/arch/auto/DBD/I
+nterBase/InterBase.so: undefined symbol: dbd_db_last_insert_id at /us
+r/lib/perl/5.10/DynaLoader.pm line 196.
[download]

Or is there any other simple way to get DBIx::Class + DBD::InterBase working, without digging deep into both and writing a DBIx::Class::Storage::DBI::Interbase module or so?

Comment on Building DBD::InterBase with last_insert_id Select or Download Code

Replies are listed 'Best First'.
Re: Building DBD::InterBase with last_insert_id by mje (Curate) on Feb 04, 2010 at 10:27 UTC
Try looking in dbdimp.h (if there is one) and probably near the end will be a load of lines like this: `#define dbd_db_commit odbc_db_commit` [download] I think you need to add one for dbd_db_last_insert_id. UPDATE: changed dbd_last_insert_id to add the "_db". UPDATE2: see DBD::SQLite	[reply] [d/l]
Re^2: Building DBD::InterBase with last_insert_id by moritz (Cardinal) on Feb 04, 2010 at 11:49 UTC
Thanks, this looks like a promising approach, and it does compile OK with the `#define` line added - but it still doesn't solve the original problem: the method still doesn't exist. My next approach is to actually write `DBIx::Class::Storage::DBI::InterBase` - hopefully with support from our local firebird guru.	[reply] [d/l] [select]
Re^3: Building DBD::InterBase with last_insert_id by mje (Curate) on Feb 04, 2010 at 13:33 UTC
When I originally posted I assumed whoever had written DBD::Interbase had already coded the method but it was not being picked up. last_insert_id in DBI is not well supported as it is generally quite difficult to implement. For instance, DBD::ODBC does not support it as each ODBC driver uses a different method to obtain the last identity value e.g., select @@identity or select id from sequence_name or countless other ways. I don't think DBD::Oracle supports it either as generally you use sequences in Oracle to generate unique ids and put them in a trigger. Lastly, the problem with many databases is that if you attempt to get the identity after the insert someone else may have used another one in the mean time. I suspect this latter issue will be the one that will hamper you in Firebird as I believe it uses generators and the only way to know for sure what the value is, is to create it yourself then use it in the insert - perhaps in a procedure. A colleague tells me FB 2 supports the "returning" clause which is a lot neater but not going to help with DBIs last_insert_id. Years ago when we started a project here that was to work with MySQL, Oracle, ODBC and DB2 we quickly found last_insert_id was next to useless and we created our own database independent method with database specific bits under the hood. For DB2 it did a select identity_val_local() from table, MySQL was select LAST_INSERT_ID() and Oracle was select LastInsertID() from dual where we wrote the LastInsertID function for Oracle which simply held an identity in a long held in the package. We no longer use it as it was just at the initial prototyping stage where writing the SQL in Perl was quicker and now all our SQL is in procedures.	[reply]
Re^3: Building DBD::InterBase with last_insert_id by roey (Initiate) on Apr 01, 2010 at 08:07 UTC
Firebird uses Generators to create auto increment fields. It involves creating a generator and a trigger - see http://www.firebirdfaq.org/faq29/ I am very interested in Firebird support for DBIx::Class maybe we can join forces to implement what is needed ?	[reply]
Re^4: Building DBD::InterBase with last_insert_id by moritz (Cardinal) on Apr 01, 2010 at 10:33 UTC
Re: Building DBD::InterBase with last_insert_id by hangon (Deacon) on Feb 04, 2010 at 17:02 UTC
You might take a look at DBIx::Sequence. It may take a little more work than you wanted, but's a nice portable way to get around the problems with autonumbered columns & the last_insert_rowid method.	[reply]