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