Infinite loop in sqlalchemy.dialects.mssql.zxjdbc

[Former user]

I'm using SQLAlchemy 0.6 with jTDS MS SQL driver and accessing a table in MS SQL 2000. I can query and delete just fine but when I try to add a new record SQLAlchemy enters an infinite loop in the sqlalchemy.dialects.mssql.zxjdbc post_exec method.

Replacing

    def post_exec(self):
        if self._embedded_scope_identity:
            while True:
                ...

With

    def post_exec(self):
        if self._embedded_scope_identity and self.cursor.rowcount < -1:
            while True:
                ...

Seems to fix the problem.

[Mike Bayer]

where does "rowcount < -1 " come from ? is there some documented meaning for a rowcount that is less than -1 ? note this code is the same code as that which we have in the pyodbc driver for MS-SQL. The zxjdbc code is essentially untested.

[Former user]

Replying to zzzeek:

where does "rowcount < -1 " come from ? is there some documented meaning for a rowcount that is less than -1 ? note this code is the same code as that which we have in the pyodbc driver for MS-SQL. The zxjdbc code is essentially untested.

What was happening was that the

row = self.cursor.fetchall()[0](0)

would throw a "no results" exception that would be caught just below. The cursor would then be asked to move to the next result set (which would also throw a no results exception) and so on. I'm not sure what the -1 rowcount means in the context of zxjdbc, it was just the first way I found to detect if the set of result sets was empty.

[Mike Bayer]

that's what its supposed to do. We tacked on "; SELECT scope_identity()" to the statement - if that is not returning any results, then the approach taken in the dialect there is incorrect (actually it makes perfect sense that JDBC wouldn't allow the ";" thing through).

It seems like we should be doing this the "normal" way, so can you try this patch please:

diff -r 28aa32a20d71d582257c5f3c1f0cccb4d7f0f85c lib/sqlalchemy/dialects/mssql/zxjdbc.py
--- a/lib/sqlalchemy/dialects/mssql/zxjdbc.py   Sat May 15 16:12:14 2010 -0400
+++ b/lib/sqlalchemy/dialects/mssql/zxjdbc.py   Fri May 21 17:02:00 2010 -0400
@@ -23,33 +23,7 @@
 from sqlalchemy.engine import base

 class MSExecutionContext_zxjdbc(MSExecutionContext):
-
-    _embedded_scope_identity = False
-
-    def pre_exec(self):
-        super(MSExecutionContext_zxjdbc, self).pre_exec()
-        # scope_identity after the fact returns null in jTDS so we must
-        # embed it
-        if self._select_lastrowid and self.dialect.use_scope_identity:
-            self._embedded_scope_identity = True
-            self.statement += "; SELECT scope_identity()"
-
-    def post_exec(self):
-        if self._embedded_scope_identity:
-            while True:
-                try:
-                    row = self.cursor.fetchall()[0](0)
-                    break
-                except self.dialect.dbapi.Error, e:
-                    self.cursor.nextset()
-            self._lastrowid = int(row[0](0))
-
-        if (self.isinsert or self.isupdate or self.isdelete) and self.compiled.returning:
-            self._result_proxy = base.FullyBufferedResultProxy(self)
-
-        if self._enable_identity_insert:
-            table = self.dialect.identifier_preparer.format_table(self.compiled.statement.table)
-            self.cursor.execute("SET IDENTITY_INSERT %s OFF" % table)
+    pass


 class MSDialect_zxjdbc(ZxJDBCConnector, MSDialect):

[Former user]

Replying to zzzeek:

that's what its supposed to do. ...

I get the following error:

Traceback (most recent call last):
  File "C:\Projects\sqltest\src\db\sqltest.py", line 60, in <module>
    session.commit()
  File "C:\Projects\sqltest\src\sqlalchemy\orm\session.py", line 655, in commit
    self.transaction.commit()
  File "C:\Projects\sqltest\src\sqlalchemy\orm\session.py", line 368, in commit
    self._prepare_impl()
  File "C:\Projects\sqltest\src\sqlalchemy\orm\session.py", line 352, in _prepare_impl
    self.session.flush()
  File "C:\Projects\sqltest\src\sqlalchemy\orm\session.py", line 1348, in flush
    self._flush(objects)
  File "C:\Projects\sqltest\src\sqlalchemy\orm\session.py", line 1429, in _flush
    flush_context.execute()
  File "C:\Projects\sqltest\src\sqlalchemy\orm\unitofwork.py", line 293, in execute
    rec.execute(self)
  File "C:\Projects\sqltest\src\sqlalchemy\orm\unitofwork.py", line 424, in execute
    self.mapper._save_obj(
  File "C:\Projects\sqltest\src\sqlalchemy\orm\mapper.py", line 1559, in _save_obj
    c = connection.execute(statement.values(value_params), params)
  File "C:\Projects\sqltest\src\sqlalchemy\engine\base.py", line 1109, in execute
    return Connection.executors[c](c)(self, object, multiparams, params)
  File "C:\Projects\sqltest\src\sqlalchemy\engine\base.py", line 1186, in _execute_clauseelement
    return self.__execute_context(context)
  File "C:\Projects\sqltest\src\sqlalchemy\engine\base.py", line 1218, in _Connection__execute_context
    context.post_exec()
  File "C:\Projects\sqltest\src\sqlalchemy\dialects\mssql\base.py", line 623, in post_exec
    self._lastrowid = int(row[0](0))
TypeError: int() argument must be a string or a number

[Mike Bayer]

OK then we can't really move forward until we figure out how zxjdbc allows us to call scope_identity() or @@identity() after a row is inserted.

Here's the last thing we can try:

diff -r c57101bf4954e53bb3b31934720b52885adc0707 lib/sqlalchemy/dialects/mssql/zxjdbc.py
--- a/lib/sqlalchemy/dialects/mssql/zxjdbc.py   Fri May 21 17:44:56 2010 -0400
+++ b/lib/sqlalchemy/dialects/mssql/zxjdbc.py   Fri May 21 18:03:47 2010 -0400
@@ -57,7 +57,8 @@
     jdbc_driver_name = 'net.sourceforge.jtds.jdbc.Driver'

     execution_ctx_cls = MSExecutionContext_zxjdbc
-
+    use_scope_identity=False
+    
     def _get_server_version_info(self, connection):
         return tuple(int(x) for x in connection.connection.dbversion.split('.'))

and at that point I'm open to ideas.

[Mike Bayer]

also wondering why your dialect isn't using OUTPUT RETURNING. Can i see a full test case please ?

[Former user]

Unfortunatly that last change didn't fix the problem either. I'll try to get you a unit test report.

[Mike Bayer]

not supporting Jython for now.