Closed GoogleCodeExporter closed 8 years ago
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
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
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
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
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
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
Original issue reported on code.google.com by
clay.ger...@gmail.com
on 12 Feb 2010 at 7:05