[Ruby-DBI-devel] Common Last Index Method

ruby-talk
1

Postgres returns an OID as a result of INSERT statements. It is in-band with the execution. It may be the only DB that does this and is the most compelling case. Support for auto-increment is easy as you illustrate. As for Oracle and other DBs that require the pre-fetch of sequences for that kind of tracking, it is probably better to leave get_last_rowid as raise "unimplemented".

Dan

···

On Aug 31, 2004, at 10:34, Brian Candler wrote:

However, my experience is limited mainly to oracle, mysql and sqlite (in
particular I've hardly used pgsql at all) so I don't know how widespread the
ability to "give me the primary key for the most recently-inserted row" is.

I would be quite happy if DBD::Foo had a "get_last_rowid" method, which
would not exist in the base class, so would raise an exception if you tried
to use it on some other database which doesn't support this idea.

But I think this is an example of something which fits the extensible-DBD
concept nicely:

  module DBD
    class Mysql
      def get_last_rowid
        select_one("select last_insert_id()")[0]
      end
    end
    class Sqlite
      def get_last_rowid
        select_one("select last_insert_rowid()")[0].to_i
      end
    end
  end

Note that Sqlite returns everything as strings, so an integer column returns
"1234" instead of 1234. That adds further difficultly to making code
portable between databases :frowning:

2

Dan Janowski schrieb:

Postgres returns an OID as a result of INSERT statements. It is in-band with the execution. It may be the only DB that does this and is the most compelling case. Support for auto-increment is easy as you illustrate. As for Oracle and other DBs that require the pre-fetch of sequences for that kind of tracking, it is probably better to leave get_last_rowid as raise "unimplemented".

Since many, many years, in Oracle-SQL you can do

   INSERT INTO ... VALUES ( ... ) RETURNING <columns> INTO <output-vars>

This even works for INSERTs that create more than one row.

Regards,
Pit