From: Carl Youngblood <carl.youngblood@...>
Date: 2004-07-29T10:04:07+09:00
Subject: Re: Stupid ODBC!

Why not take the approach of using a autoid function if you have it
but creating an extra table for storing counters if you don't?  This
seems like a perfectly acceptable solution.

On Thu, 29 Jul 2004 09:31:15 +0900, Lennon Day-Reynolds
<rcoder@gmail.com> wrote:
> 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
> >
> >
> 
>