[Zope] identity values from sql server , mxodbc da

[M.-A. Lemburg]

Josh Burvill wrote:
> Hi I am using zope 2.8.11
> 
> I am using mxodbc da to connect to sql server and I was wondering what is
> the best way to get the new value of the primary key (an identity field)
> after inserting.
> 
> It looks like there are 3 slightly different functions I could use:
> 
> """""""""""
> 
> SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because
> they return values that are inserted into identity columns.
> 
> IDENT_CURRENT is not limited by scope and session; it is limited to a
> specified table. IDENT_CURRENT returns the value generated for a specific
> table in any session and any scope. For more information, see IDENT_CURRENT
> (Transact-SQL) <http://msdn.microsoft.com/en-us/library/ms175098.aspx>.
> 
> SCOPE_IDENTITY and @@IDENTITY return the last identity values that are
> generated in any table in the current session. However, SCOPE_IDENTITY
> returns values inserted only within the current scope; @@IDENTITY is not
> limited to a specific scope.
> 
> """"""     (from http://msdn.microsoft.com/en-us/library/ms190315.aspx)
> It seems like scope_identity might be the best one to use, but is it
> possible that two zope transactions might occur within the one sql server
> "scope" and "session" and therefore get the wrong identity value depending
> on timing etc.

All of these methods have issues and there's always the possibility
of them returning NULL or, even worse, wrong values (e.g. due to
a trigger or stored procedure inserting rows as result of the initial
insert).

I'd suggest to consider a somewhat different approach:

Method 1:

When inserting a row into a table, you typically know that a
certain combination of column values has to be unique (perhaps
even protected by an additional constraint like a unique index).

After the insert query the identity column value by using this
column value combination.

That will work in all cases and is more robust than any of the
above helper functions.

Method 2:

Another strategy that we often use is based on probability:
instead of letting the database determine an identity value,
we chose one using a random number generator (RNG).

Chances are high that the identity value hasn't been used if
you use a good RNG, so it's very likely to be able to insert new
rows without problems.

In the event of a collision, the database will raise an error
and you can then choose a different identity value, again
based on an RNG.

Method 2 has the advantage of knowing the identity value
before the insert actually happens. That's often useful
and it (usually) avoids the extra query needed by method 1.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Apr 24 2010)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/