[DB-SIG] autonumbers w/ODBC?

[Michal Wallace (sabren)]

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?

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.. 

  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..?

Cheers,

- Michal
-------------------------------------------------------------------------
http://www.manifestation.com/         http://www.linkwatcher.com/metalog/
-------------------------------------------------------------------------