MSSQL dialect issuing incorrect SQL sequence

[Former user]

This one may be due to pilot error, but I don't see where.

~Matt [e-mail]

meta = schema.MetaData(engine, reflect=False)

Base = declarative_base(metadata=meta)

aTable = Table("TS_USERS", Base.metadata, Column('TS_ID', Integer, primary_key=True), Column('TS_LOGINID', String), Column('TS_NAME', String), useexisting=True)

class TS_Users(Base): table = aTable

Create a session and use it to get data about issues

sm = orm.sessionmaker(bind=engine) session = sm()

print TS_Users.table.c

results = session.query(TS_Users).all()

for i, row in enumerate(results): print "%s %s %s" % (i, row.TS_LOGINID, row.TS_NAME)

produces the trace:

(venv_0.9.7)uji_pylons_0.9.7$ python Importer/importer/tests/alchemy_test4.py 'TS_USERS.TS_LOGINID', 'TS_USERS.TS_NAME' 2009-02-10 17:35:46,654 INFO sqlalchemy.engine.base.Engine.0x..b0 BEGIN 2009-02-10 17:35:46,655 INFO sqlalchemy.engine.base.Engine.0x..b0 SELECT TS_USERS.TS_ID AS TS_USERS_TS_ID, TS_USERS.TS_LOGINID AS TS_USERS_TS_LOGINID, TS_USERS.TS_NAME AS TS_USERS_TS_NAME FROM TS_USERS ORDER BY TS_USERS.TS_ID 2009-02-10 17:35:46,655 INFO sqlalchemy.engine.base.Engine.0x..b0 [] Traceback (most recent call last): File "Importer/importer/tests/alchemy_test4.py", line 41, in <module> results = session.query(TS_Users).all() File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-py2.5.egg/sqlalchemy/orm/query.py", line 878, in all return list(self) File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-py2.5.egg/sqlalchemy/orm/query.py", line 998, in iterate_instances fetch = cursor.fetchall() File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-py2.5.egg/sqlalchemy/engine/base.py", line 1627, in fetchall self.connection._handle_dbapi_exception(e, None, None, self.cursor) File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-py2.5.egg/sqlalchemy/engine/base.py", line 942, in _handle_dbapi_exception raise exceptions.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) No results. Previous SQL was not a query. None None

[Former user]

It's not just the declarative plugin since I tried it with objects and then with plain old connections. The problem from the log appears to be the empty SQL command sent after the valid SQL query, so that the last result is not that of a query

~Matt

[Mike Bayer]

is this again with the FreeTDS connection ?

[Former user]

Yes, FreeTDS. tsql works fine with the freetds.conf I've defined a DSN in. iodbctest works with the /Library/ODBC/odbc.ini and /Library/ODBC/odbcinst.ini configurations. And then the pyodbc tests seem to work, except for the ones that request write access for create table.

pyodbc-2.1.4$ python tests/dbapitests.py "DSN=DTV;UID=mdoar;PWD=secret;DRIVER={TDS};TDS_Version=7.0"

... Ran 35 tests in 10.150s

FAILED (errors=12)

I also confirmed that sending the SQL query using connection.execute("SELECT TS_USERS.TS_ID, TS_USERS.TS_LOGINID, TS_USERS.TS_NAME FROM TS_USERS") works as expected.

[Former user]

(original author: ram) Since SA is logging that empty query, it's unlikely that it's FreeTDS issuing some kind of behind-the-scenes query: that query is originating in SA.

Shouldn't that empty query be a "rollback"? Is there a switch that maybe suppresses the rollback query but wrongly issues some empty query in instead?

[Mike Bayer]

that all seems very strange. Is the issue reproducible with 0.5 ?

[Former user]

Hmm, I thought I was using 0.5.x but apparently easy_install chose 0.4.7. I'll investigate with 0.5.

(venv_0.9.7)tests$ python Python 2.5.2 (4497f5e30629488b3ac7d860d2dc55d225c1dde5:60911, Feb 22 2008, 07:57:53) 4.0.1 (Apple Computer, Inc. build 5363) on darwin Type "help", "copyright", "credits" or "license" for more information.

import sqlalchemy sqlalchemy.version '0.4.7p1'

[Former user]

Ok, this is working with 0.5.2:

easy_install pyodbc

v2.1.4

from sqlalchemy import orm from sqlalchemy.sql import and_, join, select from sqlalchemy import schema, types, Table, Column from sqlalchemy import Integer, String from sqlalchemy.engine import create_engine from sqlalchemy.ext.declarative import declarative_base

import urllib raw_cs = "DSN=DTV;UID=mdoar;PWD=secret;DRIVER={TDS};TDS_Version=7.0" cs = "mssql:///?odbc_connect=%s" % urllib.quote_plus(raw_cs) engine = create_engine(cs) connection = engine.connect()

s = """ SELECT TS_USERS.TS_ID, TS_USERS.TS_LOGINID, TS_USERS.TS_NAME FROM TS_USERS """

results = connection.execute(s) for row in results: print row connection.close()

[Former user]

This is iodbc trace log for the closed cursor traceback with 0.5.2

[Former user]

Let me know if there's anything else I should attach to help debug this.

~Matt [e-mail]

[Mike Bayer]

absolutely. create a raw pyodbc script and attempt to issue the same series of SQL commands, i.e. execute() and then fetchall(). SQLAlchemy does very little here.

[Mike Bayer]

also if your issue is only in 0.4, then we're basically going to close it. Do you have any issues running with 0.5.2 ? it's not clear here.

[Former user]

I've upgraded to 0.5.2 just fine

[Former user]

Ok, here's a pyodbc test script. The first part is what I am seeing when I execute from within SA. The second part is the test. What is interesting is that if I execute an empty statement after the query in the test script, I get an invalid cursor state. If I don't execute the empty statement, the test passes just fine.

So, am I right in reading the line:

2009-02-11 14:17:22,486 INFO sqlalchemy.engine.base.Engine.0x...70b0 [SA sending an empty execute statement to pyodbc?

I changed the sessionmaker to have autocommit=True to get rid of transactions too.

~Matt

009-02-11 14:17:22,486 INFO sqlalchemy.engine.base.Engine.0x...70b0 SELECT TOP 1 [TTO_TOOLS](]

as).TS_ID AS TTO_TOOLS_TS_ID, TTO_TOOLS.TS_TITLE AS TTO_TOOLS_TS_TITLE, TTO_TOOLS.TS_TOOLNAME AS TTO_TOOLS_TS_TOOLNAME, TTO_TOOLS.TS_PROGID AS TTO_TOOLS_TS_PROGID FROM TTO_TOOLS 2009-02-11 14:17:22,486 INFO sqlalchemy.engine.base.Engine.0x...70b0 [14:17:22,540 WARNING sqlalchemy.pool.QueuePool.0x...8ef0 Error closing cursor: Attempt to use a closed cursor. Traceback (most recent call last): File "Importer/importer/tests/alchemy_test4.py", line 43, in <module> results = session.query(TS_Users).first() File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/query.py", line 1219, in first ret = list(self0:1) File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/query.py", line 1140, in getitem return list(res) File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/query.py", line 1334, in instances fetch = cursor.fetchall() File "/Users/mdoar/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/base.py", line 1642, in fetchall self.connection._handle_dbapi_exception(e, None, None, self.cursor, self.context) File "/Users/mdoar/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) sqlalchemy.exc.ProgrammingError: (ProgrammingError) Attempt to use a closed cursor. None None

import pyodbc

connection = pyodbc.connect("DSN=DTV;UID=mdoar;PWD=secret;DRIVER={TDS};TDS_Version=7.0")

result = connection.execute('SELECT TTO_TOOLS.TS_ID AS TTO_TOOLS_TS_ID, TTO_TOOLS.TS_TITLE AS TTO_TOOLS_TS_TITLE, TTO_TOOLS.TS_TOOLNAME AS TTO_TOOLS_TS_TOOLNAME, TTO_TOOLS.TS_PROGID AS TTO_TOOLS_TS_PROGID FROM TTO_TOOLS')

# Does the [after the SQL in the log mean that an empty string was sent? # If so, it seems to mess up the preceeding query: # pyodbc.ProgrammingError: ('24000', '24000 FreeTDSServerInvalid cursor state (0) (SQLExecDirectW)') #result2 = connection.execute("")

print result.fetchone()

[Michael Trier]

I apologize but it's not clear to me, is this issue not present on 0.5.2, or does it remain a problem on 0.5.2 as well?

[Former user]

It is present in 0.5.2

[Former user]

Just FYI, I can reproduce this with 0.5.4p2.

• Mac OS X 10.5.7
• iODBC
• pyODBC 2.1.6-beta0
• FreeTDS 0.82

Traceback:

>>> mo = cnx.query(gpmodel.MOP_Order_MSTR).first()
2009-07-07 11:24:41,329 INFO sqlalchemy.engine.base.Engine.0x...b090 SELECT TOP 1 [WO010032](WO010032).[MANUFACTUREORDER_I](MANUFACTUREORDER_I) AS [WO010032_MANUFACTUREORDER_I](WO010032_MANUFACTUREORDER_I), [WO010032](WO010032).[DSCRIPTN](DSCRIPTN) AS [WO010032_DSCRIPTN](WO010032_DSCRIPTN), [WO010032](WO010032).[MANUFACTUREORDERST_I](MANUFACTUREORDERST_I) AS [WO010032_MANUFACTUREORDERST_I](WO010032_MANUFACTUREORDERST_I), [WO010032](WO010032).[ITEMNMBR](ITEMNMBR) AS [WO010032_ITEMNMBR](WO010032_ITEMNMBR), [WO010032](WO010032).[ROUTINGNAME_I](ROUTINGNAME_I) AS [WO010032_ROUTINGNAME_I](WO010032_ROUTINGNAME_I), [WO010032](WO010032).[ENDQTY_I](ENDQTY_I) AS [WO010032_ENDQTY_I](WO010032_ENDQTY_I), [WO010032](WO010032).[STARTQTY_I](STARTQTY_I) AS [WO010032_STARTQTY_I](WO010032_STARTQTY_I), [WO010032](WO010032).[STRTDATE](STRTDATE) AS [WO010032_STRTDATE](WO010032_STRTDATE), [WO010032](WO010032).[STARTTIME_I](STARTTIME_I) AS [WO010032_STARTTIME_I](WO010032_STARTTIME_I), [WO010032](WO010032).[ENDDATE](ENDDATE) AS [WO010032_ENDDATE](WO010032_ENDDATE), [WO010032](WO010032).[DRAWFROMSITE_I](DRAWFROMSITE_I) AS [WO010032_DRAWFROMSITE_I](WO010032_DRAWFROMSITE_I), [WO010032](WO010032).[USERID](USERID) AS [WO010032_USERID](WO010032_USERID), [WO010032](WO010032).[SCHEDULEMETHOD_I](SCHEDULEMETHOD_I) AS [WO010032_SCHEDULEMETHOD_I](WO010032_SCHEDULEMETHOD_I), [WO010032](WO010032).[SCHEDULINGPREFEREN_I](SCHEDULINGPREFEREN_I) AS [WO010032_SCHEDULINGPREFEREN_I](WO010032_SCHEDULINGPREFEREN_I), [WO010032](WO010032).[POSTTOSITE_I](POSTTOSITE_I) AS [WO010032_POSTTOSITE_I](WO010032_POSTTOSITE_I), [WO010032](WO010032).[MANUFACTUREORDERPRI_I](MANUFACTUREORDERPRI_I) AS [WO010032_MANUFACTUREORDERPRI_I](WO010032_MANUFACTUREORDERPRI_I), [WO010032](WO010032).[OUTSOURCED_I](OUTSOURCED_I) AS [WO010032_OUTSOURCED_I](WO010032_OUTSOURCED_I), [WO010032](WO010032).[BOMCAT_I](BOMCAT_I) AS [WO010032_BOMCAT_I](WO010032_BOMCAT_I)
FROM [WO010032](WO010032)
2009-07-07 11:24:41,330 INFO sqlalchemy.engine.base.Engine.0x...b090 [11:24:41,331 WARNING sqlalchemy.pool.QueuePool.0x...8ad0 Error closing cursor: Attempt to use a closed cursor.
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Volumes/Users/jchampton/gp10doc/lib/python2.5/site-packages/SQLAlchemy-0.5.4p2-py2.5.egg/sqlalchemy/orm/query.py", line 1226, in first
    ret = list(self[0:1](]
2009-07-07))
  File "/Volumes/Users/jchampton/gp10doc/lib/python2.5/site-packages/SQLAlchemy-0.5.4p2-py2.5.egg/sqlalchemy/orm/query.py", line 1147, in __getitem__
    return list(res)
  File "/Volumes/Users/jchampton/gp10doc/lib/python2.5/site-packages/SQLAlchemy-0.5.4p2-py2.5.egg/sqlalchemy/orm/query.py", line 1341, in instances
    fetch = cursor.fetchall()
  File "/Volumes/Users/jchampton/gp10doc/lib/python2.5/site-packages/SQLAlchemy-0.5.4p2-py2.5.egg/sqlalchemy/engine/base.py", line 1642, in fetchall
    self.connection._handle_dbapi_exception(e, None, None, self.cursor, self.context)
  File "/Volumes/Users/jchampton/gp10doc/lib/python2.5/site-packages/SQLAlchemy-0.5.4p2-py2.5.egg/sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception
    raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) Attempt to use a closed cursor. None None

(Un)fortunately this code works* on a Linux server with same versions of pyODBC, SQLAlchemy, and FreeTDS. The main difference is unixODBC vs iODBC and FreeTDS being compiled on linux vs OS X.

• I am having other issues with the combination of pyODBC, FreeTDS and SQLAlchemy on Linux, but I'll track those down and report them in separate tickets if needed.

[Mike Bayer]

this is likely fixed in the latest 0.6 branch. Please try that.

[Former user]

I tried out the 0.6 branch and it indeed fixed the issue. Thank for the response

[Mike Bayer]

Removing milestone: 0.6.0 (automated comment)