Infinite loop in sqlalchemy.dialects.mssql.zxjdbc
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)
-
repo owner -
Account Deleted 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.
-
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):
-
Account Deleted 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
-
repo owner 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.
-
repo owner also wondering why your dialect isn't using OUTPUT RETURNING. Can i see a full test case please ?
-
Account Deleted Unfortunatly that last change didn't fix the problem either. I'll try to get you a unit test report.
-
repo owner - changed milestone to 0.6.xx
-
repo owner - changed milestone to 1.x.xx
-
repo owner - changed status to wontfix
not supporting Jython for now.
- Log in to comment
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.