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/