From: Lennon Day-Reynolds <rcoder@...>
Date: 2004-07-29T09:31:15+09:00
Subject: Re: Stupid ODBC!

Sean,

I agree with you, and when I've inevitably rolled my own
object/relational mapping tools, I've included persistent OID handling
in that layer, rather than using any database-internal features.
However, that usually requires modifying the database schema in some
way -- i.e., adding a name-mangled table like '__my_oids__', and
managing it seperately from normal insert operations.

I can also understand why Active Record follows the "worse is better"
model in this case: since the most popular open source databases
(MySQL, PostgreSQL, and SQLite) support some sort of
auto-row/insert-id functionality, why not just take advantage of it?

Both approaches have their own costs and risks. On the one hand,
assuming that the functionality will be there can obviously burn you,
as I'm discovering trying to shoehorn in some sort of solution for SQL
Server. However, if you build your own solution, you either have to
modify the database schema, (which, in the case of the project I'm
hoping to use Active Record on, is absolutely *not* an option) or keep
the OID records somewhere other than the primary database, which
complicates transaction handling considerably.

Basically, it's just starting to look like Active Record may not work
perfectly for the project I'm working on right now, which is hardly
the end of the world.

Lennon

On Thu, 29 Jul 2004 09:14:40 +0900, Sean O'Dell <sean@celsoft.com> wrote:
> 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
> 
>