From: Sean O'Dell <sean@...>
Date: 2004-07-29T09:14:40+09:00
Subject: Re: Stupid ODBC!

On Wednesday 28 July 2004 15:04, Lennon Day-Reynolds wrote:
> So, in response to David's call for contributions of adapters for
> Active Record, I've been working on an ODBC adapter. Unfortunately,
> I've come up against a limitation of the ODBC spec when compared to
> most native database APIs there appears to be no standard way of
> getting an identifier for the last row inserted. Since one of the
> primary methods that an Active Record database adapter must implement
> ('insert') is expected to return this value, I appear to be stuck for
> the moment.

Just to throw a little confusion on this subject:

A number of database engine autoid implementations I've seen generate a unique 
ID by locating the highest existing ID and incrementing it by one.  It's 
possibly for the same ID to be re-used after some records are deleted and new 
ones created.  If you have records linked across tables using those autoid's, 
that can be a problem.

What I almost always do is generate a new, unique ID myself at the application 
layer or in a table made for tracking unique IDs (always increment, never 
decrement).  When you INSERT a new record, give it that unique ID in your 
INSERT, then simply SELECT it afterwards WHERE id= the new ID you gave it.

I wouldn't depend on any engine's autoid feature, even if it works 
TheRightWay(tm), if you're at all concerned about portability.

	Sean O'Dell