[DB-SIG] autonumbers w/ODBC?

Frank McGeough Frank McGeough" <fm@synchrologic.com
Fri, 14 Apr 2000 08:15:08 -0400

Getting autoincrement values is unfortunately very db specific. I think you
will find most databases have some means to achieve this but they vary
fairly wildly. There is no standard odbc means of retrieving this.

Just to give you more to think about in your noble quest.

For Microsoft SQL Server (I believe Sybase Sql Server works similarly) you
can do : "SELECT @@identity" which returns the number last used by the
current connection. This is probably similar to what you do in Access.

For Oracle. There aren't autoincrement numbers. You can either use the ROWID
as a unique number or do a select out of a special "sequence" table that
returns a new number everytime. I believe this is the traditional method for
generating these type of numbers for Oracle.

----- Original Message -----
From: Michal Wallace (sabren) <sabren@manifestation.com>
To: <db-sig@python.org>
Sent: Friday, April 14, 2000 6:35 AM
Subject: Re: [DB-SIG] autonumbers w/ODBC?

> 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/
> -------------------------------------------------------------------------
> _______________________________________________
> DB-SIG maillist  -  DB-SIG@python.org
> http://www.python.org/mailman/listinfo/db-sig