Pyodbc really doesn't like extraneous information to be returned with its (ONE AND ONLY ONE!) result set from a stored procedure.
In my latest fight with pyodbc, I had a stored proc returning a rowset with a few thousand rows. This worked fine most of the time, except in a few cases with certain input parameters. In those cases, the cursor returned by execute() would contain an int, so any cursor method I called on it would break.
At first I thought I might have hit some kind of row size limit or rowcount limit, but realized the result set wasn't big enough for that to be the case. The proc wasn't throwing off any errors when run in SQL Management Studio with the same input parameters, so that wasn't the problem.
Then I noticed that it was returning a single warning message in the Messages tab.
Warning: Null value is eliminated by an aggregate or other SET operation.
This is a fairly common warning in SQL land, so I hadn't paid any attention to it. But it is possible to suppress, using the following statement:
SET ANSI_WARNINGS OFF
Sure enough, this did the trick. The moral of this story (just like last time) is that pyodbc wants a single rowset and that's it; returning any data in any of the alternate ODBC channels seems to break it, or at least, not yield the cursor in the result set that I expected.
Subscribe to:
Post Comments (Atom)
2 comments:
Turning off extraneous info is a good idea for pyodbc, but it does allow you to return more than 1 item form a stored procedure. You just need to use Cursor.nextset() to step from one to the other.
See http://code.google.com/p/pyodbc/wiki/Cursor
The problem with the extraneous info is you then need to remember how many extraneous things to expect, which changes over time as you modify your procedures -- very difficult to maintain.
Also note that it is db-specific. SQL Server is pretty bad about returning everything, but then again if you want some of that (how many rows were deleted in your temp table), it's good to have.
Thanks, Michael. I eventually encountered that after a few more passes through the pyodbc documentation, but I forgot to update this post. I appreciate the note!
Post a Comment