MSSQL fetching inserted ID when table has a trigger

[paj]

MSSQL has trouble fetching the inserted ID, when you insert into a table with a trigger. In fact, it's only some triggers - those that cause a row to also be inserted in a different table.

There was a good thread on the mailing list: http://tinyurl.com/356mtb

Turns out there's two problems here. First, pyodbc cannot cope with the multiple result sets that are returned. A workaround for this is to issue "set nocount on" which prevents this. Issuing that isn't a problem, as pyodbc doesn't reliably return rowcount anyway.

Secondly, adodbapi/pymssql (and pyodbc with the workaround) return the identity for the wrong table. This is because they use @@identity; scope_identity() should returns the correct value. However, this only works on pymssql; on adodbapi/pyodbc it always returns null.

This patch adds a unit test for the condition. It adds a url flag, use_scope_identity, and if using pymssql and use_scope_identity=1, the unit test passes. The patch also automatically issues "set nocount on" for pyodbc. However, the unit test still fails.

So, while the patch is a start, we need to work with the dbapi authors to fully resolve this.

[paj]

Ok, here's a twist, scope_identity() can work in pyodbc, this prints "1":

import pyodbc db = pyodbc.connect('Driver={SQL Server};Server=.;Database=test;TrustedConnection=Yes') cur = db.cursor() cur.execute('create table test (id int identity(1,1), val varchar(50))') cur.execute("insert into test (val) values ('abc')") cur.execute('select scope_identity() as fred') print cur.fetchone()0 cur.execute('drop table test')

So why, oh why, does it not work inside SQLAlchemy. Hmmmmmm

[paj]

import pyodbc
db = pyodbc.connect('Driver={SQL Server};Server=.;Database=test;TrustedConnection=Yes')
cur = db.cursor()
cur.execute('create table test (id int identity(1,1), val varchar(50))')
cur.execute("insert into test (val) values ('abc')")
cur.execute('select scope_identity() as fred')
print cur.fetchone()[0](0)
cur.execute('drop table test')

[paj]

Ah, but if the insert statement uses bind parameters, the problem does happen!

cur.execute("insert into test (val) values (?)", 'abc') cur.execute('select scope_identity()') -> None

[paj]

I've posted this on the pyodbc tracker: http://sourceforge.net/tracker/index.php?func=detail&aid=1703193&group_id=162557&atid=824254

[paj]

No answer so far; I suggest we apply what we've got so far and leave the ticket open but at low priority.

[Former user]

(original author: ram) Committed in 2577, thanks. I made @@scope_identity the default: it's been in since MSSQL 2000.

Leaving ticket open as suggested for now.

[Former user]

It is a scope problem. ODBC executes insert statements through the procedure sp_executesql. (I think the underlying SqlClient calls the procedure) The insert statement is executed within the scope of the procedure. The next execute with a select scope_identity() will be executed within another scope. No id is returned. Both statements have to be executed with one call to execute(): "insert ... select ?=scope_identity()" Two statements in one call will be executed in the same scope inside the procedure sp_executesql. The id is returned as result set in my own test applications. Not as out parameter.

Regards, Michael

[paj]

Michael Jahn has done some great work moving this forward. He has created a patch for pyodbc that adds the nextset() method, and a patch for SQLAlchemy to make use of this. Right at the moment, the patch causes a number of other unit tests to fail. I will be looking at how to reduce them as I get a chance.

Binaries for Michael's revised version of pyodbc are attached to this ticket (Windows, Python 2.4 and 2.5). So is his original patch.

[paj]

Ok, I've done some more work on this and now it only causes a handful of unit test failures (and then only when use_scope_identity=1) so it's just about ready to commit. Before I do, I'm just going to see if I can nail the last few failures.

[paj]

Ok, they were pretty trivial failures in the end, just the SQL test cases needed fixing to expect the "; select scope_identity()".

Committed in 2875 Thanks Michael!

[paj]

PyODBC 2.0.37 has just been released, with the nextset method included. With changeset 3215 SA automatically sets use_scope_identity if a suitable version of PyODBC is installed.

[Mike Bayer]

Removing milestone: 0.4.0 (automated comment)