[DB-SIG] autonumbers w/ODBC?

[M.-A. Lemburg]

"Michal Wallace (sabren)" wrote:
> 
> On Fri, 14 Apr 2000, M.-A. Lemburg wrote:
> 
> > >   Is it even possible to get a generated Autonumbe field via ODBC?
> > > Microsoft Access seems to be able to do it well enough, but how
> > > do you do it with mxODBC?
> >
> > You don't generate autonumber fields, you declare them when
> > you define the table schema. ODBC let's you do this just as
> > all other DB API interfaces, but the syntax is of course
> > DB specific and not all DBs support this feature.
> 
> Whoops! I meant the generated value from an autonumber field.  If I
> connect to SQL server or MySQL or whatever, I can add a record, and
> access knows/discovers the value of the new ID.
> 
> > Its simplest to use max(id) during insert and then apply
> > a few tries to catch simultaneous inserts in case they
> > should happen. Works everywhere and doesn't need auto-increment
> > columns.
> 
> how would you catch the simultaneous inserts?

Trial and error :-) ... I use a for loop which does a few
iteration of try:...except:... and fails after that.
 
> In any case, if for whatever reason the database designer has created
> an Autonumber field, I'd like my objects to handle it.. The max(ID)
> concept seems to have a lot of promise (thanks!)
> 
> Let's say I insert "Homer Simpson" into a table with an autonumber
> field. I can get the max(ID) with a select statement, unless If
> someone inserts another record right after me. I can cover that
> easily enough with something like:
> 
>   SELECT MAX(ID), first, last from names
>   WHERE first="Homer" and last="Simpson"
>   GROUP BY ID, first, last
> 
> .. But what if two people happen to insert Homer Simpson at the same
> time? I mean, if the other fields were always unique, I wouldn't
> necessarily need a primary key..

I was heading for emulating auto-increment columns, you seem
to be heading in a different direction: that of trying to
*use* these columns. I don't think you're going to get far
with this since that approach buys you three problems ;-)

1. to detect auto-increment columns
2. to properly insert data into a table using these
3. to get the auto-incremented value used by the DB

IMHO, it's better not to use them at all and roll your
own database invariant design. One way is using max(id)
for this, another the sequence table approach (you just
have to make sure it always contains data... not very hard,
though: you can use a hook which fills it on demand).
 
>   MySQL lets me skip all that and just call a function to get the last
> generated ID for my connection... I've seen Access get this result
> through ODBC, but I'm having a hard time figuring out how it works,
> Is there a function like this built into ODBC, or do you think they
> use a set of heuristics along the max(ID) lines..?

No. All ODBC gives you is an API which let's you query a
column to use for uniquely identifying a table row (not all
DBs provide ROWNO values).
 
-- 
Marc-Andre Lemburg
______________________________________________________________________
Business:                                      http://www.lemburg.com/
Python Pages:                           http://www.lemburg.com/python/