[sqlalchemy] Re: MSSQL identity inserts [was: pyodbc and tables with triggers]
Hi Paul, Could this be one of those situations where MSSQL returns multiple result sets? If the select for @@identity / @scope_identity() generates another result set, MSSQL want a full fetch on the previous fetch before issuing a new one. Fix would be client-side cursors for adodbapi, pyodbc would need to implement nextset(). But it's hard for me to believe the problem is as simple as a select @@identity inside a transaction - surely some one (or one of the tests) would have hit that one before. Rick On 3/12/07, Paul Johnston [EMAIL PROTECTED] wrote: Hi, I've had a go at implementing scope_identity, with mixed results. It works fine with pymssql With adodbapi, scope_identity() always returns None Same problem with pyodbc, and I uncovered a worse problem lurking (it exists with @@identity) If you do an insert inside a transaction (i.e. autocommit doesn't happen), when it tries to extract the ID, you get the dreaded Invalid cursor state error. So, for the time being I think we should hold fire on scope_identity. I will see if I can figure out what's up with adodbapi/pyodbc. Paul Tim Golden wrote: I've looked through the mailing list and can't see this issue raised there so... There is a known issue with retrieving the id of the last inserted row under MSSQL where IDENTITY cols are used and there are triggers involved. It's pretty easy to demonstrate. If I have this construction: --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL identity inserts [was: pyodbc and tables with triggers]
OK, I replied to the other thread already, which is really the same issue.
See my response there about backward-compatibility.
At any rate, we could make it a connection variable like
auto_identity_insert. Patches welcome.
Rick
On 3/7/07, Tim Golden [EMAIL PROTECTED] wrote:
I've looked through the mailing list and can't see
this issue raised there so...
There is a known issue with retrieving the id of the
last inserted row under MSSQL where IDENTITY cols are
used and there are triggers involved. It's pretty easy
to demonstrate. If I have this construction:
db
CREATE TABLE
test
(
id INT NOT NULL IDENTITY PRIMARY KEY,
code VARCHAR (10) NOT NULL UNIQUE
)
GO
CREATE TABLE
test_audit
(
id INT NOT NULL IDENTITY (100, 1) PRIMARY KEY,
test_id INT NOT NULL FOREIGN KEY REFERENCES test (id),
inserted_on DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
inserted_by VARCHAR (60) NOT NULL DEFAULT SYSTEM_USER
)
GO
CREATE TRIGGER tr_test_i ON test FOR INSERT AS
INSERT INTO test_audit (test_id) SELECT id FROM inserted
GO
/db
and insert something into test:
db
INSERT INTO test (code) VALUES ('ABC')
SELECT @@IDENTITY
SELECT * FROM test
/db
The last id is 100 (the IDENTITY col from [test_audit])
while, as far as the user's concerned, [test] was the
only table affected. In sqlalchemy terms, this means
that the last_inserted_ids () could return misleading
values:
python
from sqlalchemy import *
db = BoundMetaData (mssql://VODEV1/TimHolding)
test = Table (test, db, autoload=True)
r = test.insert ().execute (code=DEF)
print r.last_inserted_ids ()
# = [101]
list (test.select ().execute (id=101))
# = []
/python
What are the alternatives? Well, there are two:
IDENT_CURRENT ('tablename')
gives the last identity value assigned to this table
*in any session* (alertrace condition/alert)
or
SCOPE_IDENTITY ()
which seems to be what we're after here; it's like @@IDENTITY
but for the same scope (not a widely-used term in SQL
Server circles, as far as I know). The documentation
specifically gives this case as an example.
Looks to me like this would be the best bet for sqlalchemy's
purposes, but I'm sure there's a downside somewhere ;)
Comments?
TJG
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL identity inserts [was: pyodbc and tables with triggers]
Rick Morrison wrote: OK, I replied to the other thread already, which is really the same issue. See my response there about backward-compatibility. At any rate, we could make it a connection variable like auto_identity_insert. Patches welcome. I'm happy to provide a patch. Not sure about the connection variable. Ah, I see, you mean because of backwards compat. But isn't the problem that if we just leave the @@IDENTITY as now, it's a danger waiting to happen, especially if the returned id happens to be a valid id for the table you *think* it's for? Not really sure what to offer here: 1) I can provide a patch, replacing @@IDENTITY by SCOPE_IDENTITY throughout. 2) I can provide a patch allowing connection-level determination of whether @@IDENTITY or SCOPE_IDENTITY is to be used. (Which assumes the client module knows what that's about). 3) I can provide a patch which attempts to work out which one would be allowed from some DB context (or just trying it to see!) The problem with (1) is that, if Rick's right, it won't work with MSSQL = 7. The problem with any of (2) or (3) where @@IDENTITY ends up being used is that we might be silently and dangerously returning wrong data. TJG --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
