Invalid cursor state with FreeTDS

[GoogleCodeExporter]

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]: 

Original issue reported on code.google.com by clay.ger...@gmail.com on 12 Feb 2010 at 7:05

[GoogleCodeExporter]

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.

Original comment by mkleehammer on 24 Aug 2010 at 4:11

• Changed state: Investigating

[GoogleCodeExporter]

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!

Original comment by clay.ger...@gmail.com on 24 Aug 2010 at 8:48

[GoogleCodeExporter]

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.

Original comment by pedri...@gmail.com on 28 Oct 2010 at 10:20

[GoogleCodeExporter]

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.

Original comment by pedri...@gmail.com on 29 Oct 2010 at 12:47

[GoogleCodeExporter]

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.

Original comment by mkleehammer on 21 Nov 2010 at 6:37

• Changed state: WontFix

[GoogleCodeExporter]

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.

Original comment by pedri...@gmail.com on 29 Nov 2010 at 8:27