Commits

Mike Bayer committed a98001d

- [bug] Repaired the use_scope_identity
create_engine() flag when using the pyodbc
dialect. Previously this flag would be
ignored if set to False. When set to False,
you'll get "SELECT @@identity" after each
INSERT to get at the last inserted ID,
for those tables which have "implicit_returning"
set to False.

Comments (0)

Files changed (4)

     passes Python unicode literals to PyODBC 
     or not.
 
+  - [bug] Repaired the use_scope_identity
+    create_engine() flag when using the pyodbc
+    dialect.  Previously this flag would be
+    ignored if set to False.  When set to False,
+    you'll get "SELECT @@identity" after each 
+    INSERT to get at the last inserted ID,
+    for those tables which have "implicit_returning"
+    set to False.
+
 - postgresql
   - [feature] Added new for_update/with_lockmode()
     options for Postgresql: for_update="read"/

lib/sqlalchemy/dialects/mssql/base.py

 
 Not necessarily specific to SQLAlchemy, SQL Server has a default transaction 
 isolation mode that locks entire tables, and causes even mildly concurrent
-applications to have long held locks and frequent deadlocks.   
+applications to have long held locks and frequent deadlocks.
 Enabling snapshot isolation for the database as a whole is recommended 
 for modern levels of concurrency support.  This is accomplished via the 
 following ALTER DATABASE commands executed at the SQL prompt::
 should be used when IN/NOT IN are desired.
 
 For the time being, the existing behavior prevents a comparison
-between scalar select and another value that actually wants to use ``=``.  
+between scalar select and another value that actually wants to use ``=``.
 To remove this behavior in a forwards-compatible way, apply this
 compilation rule by placing the following code at the module import
 level::
     from sqlalchemy.ext.compiler import compiles
     from sqlalchemy.sql.expression import _BinaryExpression
     from sqlalchemy.sql.compiler import SQLCompiler
-    
+
     @compiles(_BinaryExpression, 'mssql')
     def override_legacy_binary(element, compiler, **kw):
         return SQLCompiler.visit_binary(compiler, element, **kw)
                                         not self.executemany
 
             if self._enable_identity_insert:
-                self.cursor.execute("SET IDENTITY_INSERT %s ON" % 
-                    self.dialect.identifier_preparer.format_table(tbl))
+                self.root_connection._cursor_execute(self.cursor, 
+                    "SET IDENTITY_INSERT %s ON" % 
+                    self.dialect.identifier_preparer.format_table(tbl),
+                    ())
 
     def post_exec(self):
         """Disable IDENTITY_INSERT if enabled."""
 
+        conn = self.root_connection
         if self._select_lastrowid:
             if self.dialect.use_scope_identity:
-                self.cursor.execute(
-                "SELECT scope_identity() AS lastrowid", ())
+                conn._cursor_execute(self.cursor, 
+                    "SELECT scope_identity() AS lastrowid", ())
             else:
-                self.cursor.execute("SELECT @@identity AS lastrowid", ())
+                conn._cursor_execute(self.cursor, 
+                    "SELECT @@identity AS lastrowid", ())
             # fetchall() ensures the cursor is consumed without closing it
             row = self.cursor.fetchall()[0]
             self._lastrowid = int(row[0])
             self._result_proxy = base.FullyBufferedResultProxy(self)
 
         if self._enable_identity_insert:
-            self.cursor.execute(
+            conn._cursor_execute(self.cursor, 
                         "SET IDENTITY_INSERT %s OFF" %
                             self.dialect.identifier_preparer.
-                                format_table(self.compiled.statement.table)
+                                format_table(self.compiled.statement.table),
+                        ()
                         )
 
     def get_lastrowid(self):

lib/sqlalchemy/dialects/mssql/pyodbc.py

     def __init__(self, description_encoding='latin-1', **params):
         super(MSDialect_pyodbc, self).__init__(**params)
         self.description_encoding = description_encoding
-        self.use_scope_identity = self.dbapi and \
+        self.use_scope_identity = self.use_scope_identity and \
+                        self.dbapi and \
                         hasattr(self.dbapi.Cursor, 'nextset')
         self._need_decimal_fix = self.dbapi and \
                             self._dbapi_version() < (2, 1, 8)

test/dialect/test_mssql.py

         finally:
             meta.drop_all()
 
-class QueryTest(fixtures.TestBase):
+from test.lib.assertsql import ExactSQL
+class QueryTest(testing.AssertsExecutionResults, fixtures.TestBase):
     __only_on__ = 'mssql'
 
     def test_fetchid_trigger(self):
             con.execute("""drop trigger paj""")
             meta.drop_all()
 
+    @testing.provide_metadata
+    def test_disable_scope_identity(self):
+        engine = engines.testing_engine(options={"use_scope_identity":False})
+        metadata = self.metadata
+        metadata.bind = engine
+        t1 = Table('t1', metadata,
+                Column('id', Integer, primary_key=True),
+                implicit_returning=False
+        )
+        metadata.create_all()
+
+        self.assert_sql_execution(
+                testing.db,
+                lambda: engine.execute(t1.insert()),
+                ExactSQL("INSERT INTO t1 DEFAULT VALUES"),
+                # we dont have an event for 
+                # "SELECT @@IDENTITY" part here.
+                # this will be in 0.8 with #2459
+        )
+        assert not engine.dialect.use_scope_identity
+
     def test_insertid_schema(self):
         meta = MetaData(testing.db)
         con = testing.db.connect()