MSSQL dialect issuing incorrect SQL sequence
This one may be due to pilot error, but I don't see where.
~Matt mdoar@pobox.com
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
Comments (22)
-
Account Deleted -
repo owner - changed watchers to mdoar@pobox.com
is this again with the FreeTDS connection ?
-
repo owner - changed title to MSSQL dialect issuing incorrect SQL sequence
-
Account Deleted 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.
-
Account Deleted (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?
-
repo owner that all seems very strange. Is the issue reproducible with 0.5 ?
-
Account Deleted 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'
-
Account Deleted 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()
-
Account Deleted - attached odbctrace.log
This is iodbc trace log for the closed cursor traceback with 0.5.2
-
Account Deleted Let me know if there's anything else I should attach to help debug this.
~Matt mdoar@pobox.com
-
repo owner 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.
-
repo owner 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.
-
Account Deleted I've upgraded to 0.5.2 just fine
-
Account Deleted 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()
-
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?
-
Account Deleted It is present in 0.5.2
-
repo owner - changed milestone to 0.5.xx
-
Account Deleted 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.
-
repo owner - changed milestone to 0.6.0
this is likely fixed in the latest 0.6 branch. Please try that.
-
Account Deleted I tried out the 0.6 branch and it indeed fixed the issue. Thank for the response
-
repo owner - changed status to resolved
-
repo owner - removed milestone
Removing milestone: 0.6.0 (automated comment)
- Log in to comment
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