On Thursday, January 2, 2003, at 11:46 PM, Tom Sawyer wrote:
> as it stands when i insert a new record into a table, i then do:
> "SELECT
> currval('table_idfield_seq') as recid;" to get the latest automatically
> generated record id. and thus know which record i just inserted. but
> this
> seems to me like a jerry-rig. i don't like it and i doubt it works on
> anything but postgresql. what's the best way to deal with this? how
> does one
> get auto generated id fields in such a way as to be cross-compatible?
> or am i
> mistaken in using auto generated id feilds in the first place?
Let me answer your questions in the reverse order that you asked them
in:
First, you should be using auto generated id fields whenever you can.
They are fast, easy, and they stay out of the way. I even use them in
intersect tables. On a totally unrelated note: I recommend that you
use the Oracle naming convention, which is to give all auto increment
primary keys the same name; e.g., id. The DB2 convention (having a pk
named for the table and the id--e.g., docId or userId--strikes me as
just plain silly, since using this convention causes you to have to
constantly describe your tables while writing sql. (Although I prefer
DB2 as a database over Oracle as a database, I do prefer this one
Oracle convention--which is external to the actual databases.)
Second, whenever someone tells you that an object oriented language
can't create non-implementation specific interfaces, either they are
wrong or the language isn't really object oriented. Allowing for
non-implementation specific interfaces is what Object Oriented
languages do best (literally). So don't listen to all the nay-sayers
who claim that you can't write code that will get you across multiple
databases. Not only can it be done, but it is surprisingly easy.
There are several ways to do this from simple wrapper classes to whole
APIs. (I'm currently writing a whole API, which doesn't bare
reproducing on a mailing list.) The easiest way that comes to my mind
is to use a simple wrapper with an abstract method (mind you, the
following is way, way paired down so that it fits in this email):
require "dbi"
# DISCLAIMER: there may be some minor typos in this code,
# since I've written it off the top of my head and have
# not tried to run it. But it should give you the a
# clear idea of what I'm getting at.
class Connection
def initialize(dsn, user=nil, auth=nil, parms=nil)
@dbh = connect(dsn, user, auth, parms)
end
def quote(str)
return @dbh.quote(str)
end
def execute(sql, *vals)
return @dbh.execute(sql, *vals)
end
def executeInsert(sql, *vals)
raise(RuntimeError, "Abstract method. Implement in subclass")
end
def do(sql, *vals)
return @dbh.do(sql, *vals)
end
def select_one(sql, *vals)
return @dbh.select_one(sql, *vals)
end
# insert other DatabaseHandle methods here so that you wrap up
# the full complement of DatabaseHandle functions
def disconnect()
@dbh.disconnect()
end
end
# Now you can extend this class as follows:
# for mysql
class Connection_mysql < Connection
def executeInsert(sql, *vals)
self.do(sql, *vals)
return self.select_one("SELECT last_insert_id()")[0]
end
end
# for postgress
class Connection_pg < Connection
def executeInsert(sql, *vals)
self.do(sql, *vals)
return self.select_one("SELECT currval('table_idfield_seq')")[0]
end
end
You can implement other databases the same way. For example, in a
Connection_ora for Oracle, you'll want to grab the value from a
sequencer and with a Connection_db2 for DB2 you'll want to grab and
parse in the unique value.
Now you can either hard-code the type of Connection that you'll be want
(e.g., Connection_pg or Connection_mysql) into your code, or you can
create an static method (might I suggest calling it "connect") in your
Connection class that chooses which type of Connection object to return
based on the Driver being used (e.g., with a big case statement).
-------------------------------------------------------
David King Landrith
(w) 617.227.4469x213
(h) 617.696.7133
One useless man is a disgrace, two
are called a law firm, and three or more
become a congress -- John Adams
-------------------------------------------------------
public key available upon request