polaar wrote:
> I recently tried out sqlalchemy with mssql via pyodbc (after being
> bitten by the adodbapi bug with the truncated parameters), and noticed
> the following problem:
>
> On inserting records into tables with triggers, pyodbc fails on the
> 'select @@identity as lastrowid' statement with an 'invalid cursor
> state' error.
OK, I can't reproduce this (and there's a follow-on issue which
I'll pick up later). Just to clarify, I have this structure
compiled on the database:
<db>
IF OBJECT_ID ('test_audit') IS NOT NULL
DROP TABLE test_audit
GO
IF OBJECT_ID ('test') IS NOT NULL
DROP TABLE test
GO
CREATE TABLE
test
(
id INT NOT NULL IDENTITY PRIMARY KEY,
code VARCHAR (10) NOT NULL UNIQUE
)
GO
CREATE TABLE
test_audit
(
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>
That's a main table (test) an audit table (test_audit)
into which test-INSERTs are triggered. Now, in sqlalchemy:
<code>
from sqlalchemy import *
metadata = BoundMetaData ("mssql://VODEV1/TimHolding")
test = Table ("test", metadata, autoload=True)
result = test.insert ().execute (code = "ABC")
print result.last_inserted_ids ()
# => [1]
</code>
which is what I expected. If I explicitly set NOCOUNT OFF
for my session (in case it's on by default) using:
metadata.engine.raw_connection ().execute ("SET NOCOUNT OFF")
then it still works.
Is my case the situation you're describing? Or have I
misunderstood somthing?
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
-~----------~----~----~----~------~----~------~--~---
