in reply to Re: Building DBD::InterBase with last_insert_id
in thread Building DBD::InterBase with last_insert_id

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.

Replies are listed 'Best First'.
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]
      Actually there is a DBIx::Class branch that implements a firbird backend - I just don't know if it is merged yet. I used an earlier version of that backend, and it worked fine for my project.

      I suggest to join the developer's channel on irc.perl.org (#dbix-class) and ask Caelum about the status.

      Perl 6 - links to (nearly) everything that is Perl 6.
[reply]

In Section Seekers of Perl Wisdom