Issue 89: Invalid cursor state with FreeTDS
Status:  WontFix
Owner: ----
Closed:  Nov 2010
Reported by clay.ger...@gmail.com, Feb 12, 2010
What steps will reproduce the problem?
1. configure unixODBC with FreeTDS and install pyodbc
2. create a DSN-less connection to a Microsoft SQL Server database
3. execute two select statements in a row - WITHOUT consuming the results
in between

What is the expected output?
conn.execute('select 1')
<pyodbc.Cursor object at 0xa3611e0>
conn.execute('select 1')
<pyodbc.Cursor object at 0xa5be218>
 
What do you see instead?
conn.execute('select 1')
<pyodbc.Cursor object at 0xa3611e0>
conn.execute('select 1')
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)

/home/clayg/devenv/pyodbc/<ipython console> in <module>()

ProgrammingError: ('24000', '[24000] [FreeTDS][SQL Server]Invalid cursor
state (0) (SQLExecDirectW)')


What version of the product are you using? On what operating system?
sys.version
'2.6.4 (r264:75706, Dec  7 2009, 18:45:15) \n[GCC 4.4.1]'
pyodbc.version
'2.1.6-beta0'
$ cat /etc/*-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=9.10
DISTRIB_CODENAME=karmic
DISTRIB_DESCRIPTION="Ubuntu 9.10"
$ odbcinst --version
unixODBC 2.2.11
$ aptitude show freetds-common
Package: freetds-common
State: installed
Automatically installed: yes
Version: 0.82-6
Priority: optional
Section: libs
Maintainer: Ubuntu Developers <ubuntu-devel-discuss@lists.ubuntu.com>
Uncompressed Size: 102k
Replaces: libct3, libct4 (< 0.82-1)
Description: configuration files for FreeTDS SQL client libraries
 FreeTDS is an implementation of the Tabular DataStream protocol, used for
 connecting to MS SQL and Sybase servers over TCP/IP. 
 
 This package manages the configuration files that are common to all of the TDS
 client library implementations (CT-Lib, DB-Lib, and ODBC), stored in
 /etc/freetds/.
Homepage: http://www.freetds.org/


Please provide any additional information below.

In [1]: import pyodbc

In [2]: conn = pyodbc.connect("DRIVER={SQL
Server};SERVER=db.host.org;DATABASE=MyDatabaseName;UID=myusername;PWD=mypassword")

In [3]: results = conn.execute('select 1')

In [4]: results.fetchall()
Out[4]: [(1, )]

In [5]: results = conn.execute('select 1')

In [6]: results = conn.execute('select 1')
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)

/home/clayg/devenv/pyodbc/<ipython console> in <module>()

ProgrammingError: ('24000', '[24000] [FreeTDS][SQL Server]Invalid cursor
state (0) (SQLExecDirectW)')

In [7]: 


Aug 24, 2010
Project Member #1 mkleehammer
I'll play with this using some other drivers, but I notice you are using Connection.execute instead of Cursor.execute.  This may help you find a work around while I figure this out:

conn = pyodbc.connect(...)
cursor = conn.cursor()
cursor.execute(...)

Not trying to dodge the issue, but executing multiple times in a row is actually creating multiple cursors on the same connection.  Each call to Connection.execute creates a new cursor, calls Cursor.execute, and returns the Cursor.

Status: Investigating
Aug 24, 2010
#2 clay.ger...@gmail.com
Thanks for looking into this for me.

I'm using sqlalchemy, so typically the direct interaction on the cursor is abstracted from me.

Perhaps interestingly, if I don't store the intermediate cursor in variable - I don't have to call fetchall before ...

>>> conn.execute('select 1').fetchone()[0]1
>>> [x[0] for x in conn.execute('select 1')][0]
1
>>> conn.execute('select 1').next()[0]
1
>>> cur = conn.execute('select 1')
>>> cur.next()
(1, )
>>> conn.execute('select 1').fetchone()[0]
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.ProgrammingError: ('24000', '[24000] [FreeTDS][SQL Server]Invalid cursor state (0) (SQLExecDirectW)')
>>> cur.fetchall()
[]
>>> conn.execute('select 1').fetchone()[0]
1

I guess it just doesn't trust me that I'm done with that reference to the cursor?

Thanks again!
Oct 28, 2010
#3 pedri...@gmail.com
I am getting exactly the same error message using a relatively recent snapshot of freetds.  Using a cursor doesn't change the results, they continue to look pretty similar to clay.gerrards results:

def the_long_test(dsn):
    db = pyodbc.connect("DSN={0[DSN]};UID={0[UID]};PWD={0[PWD]};DATABASE={0[DATABASE]};APP=nortonp_python".format(dsn))
    cursor = db.cursor()
    q = cursor.execute("select 1")
    print q.fetchall()

guest_dsn = {...} #appropriate values, commented out
ejv_dsn = {...}
ms2k5_dsn = {...}
ms2k8_dsn = {...}

This only happens to be me on the sybase 12.5 connection. Unfortunately, this is a database controlled by a vendor, so I can't log into the box in an admin context, or do any diagnostics, really.

The output looks like this:

    checking sybase:
('24000', '[24000] [FreeTDS][SQL Server]Invalid cursor state (0) (SQLExecDirectW)')
Reality check: Does this build of freetds work anywhere?

    checking sybase 15:
[(1, )]
    checking mssql 2k5:
[(1, )]
    checking mssql 2k8:
[(1, )] 

Again, the second time a connection is attempted, it works.
Oct 28, 2010
#4 pedri...@gmail.com
After some more investigation, I found the sequence of events that seem to cause the failure.  I sent more info to the freetds list.  The message is at http://lists.ibiblio.org/pipermail/freetds/2010q4/026501.html.  If you could take a look and see if you think that this is something you should be dealing with, or if it is indeed in freetds' ODBC code, I'd appreciate it.  I'm not at all familiar with how this should work.
Nov 21, 2010
Project Member #5 mkleehammer
Very impressive.  Did you get a response?  I didn't see one on the thread, but they don't have a good search for their archives.

I think the problem is in the FreeTDS driver.  I've added a quick test to 2.1.9-beta07 and am not seeing anything that could cause the issue there.  (I've used multiple 'select 1' statements.  Do I need a SQLGetTypeInfo for an unknown type instead?)

I'm going to close this for now, but please reopen if it is determined to be a pyodbc issue.


Status: WontFix
Nov 29, 2010
#6 pedri...@gmail.com
I've brought the issue up again on the list - it fell off the edge, probably relative to other issues I was bringing up at the same time.  I've re-posted the issue, and we'll see how it works after that.

I'm not sure about the need for a SQLGetTypeInfo, it sort of happens in the background in my tests, but try overloading the same type if you try it.  I'm not sure how that's supposed to work but I'll provide whatever answers I can if it's helpful.