1. Michael Bayer
  2. sqlalchemy
  3. Issues

Issues

Issue #1809 wontfix

Infinite loop in sqlalchemy.dialects.mssql.zxjdbc

Anonymous created an issue

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.

Comments (10)

  1. Michael Bayer repo owner

    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.

  2. Anonymous

    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.

  3. Michael Bayer repo owner
    • changed milestone to 0.6.1

    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):
    
  4. Anonymous

    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
    
  5. Michael Bayer repo owner

    OK then we can't really move forward until we figure out how zxjdbc allows us to call scope_identity() or @Identity, Lda. () 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.

  6. Anonymous

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

  7. Log in to comment