| Issue 49: | problem with variable assignment | |
| 2 people starred this issue and may be notified of changes. | Back to list |
Hello,
What steps will reproduce the problem?
1. connected to MsSQL server
2. run the query including some assigmnets
3. runtime error raised
Example:
DECLARE @l int
SET @l = 15
SELECT @l
What is the expected output? What do you see instead?
Output should be 15.
Traceback (most recent call last):
File "./get_all.py", line 29, in <module>
for r in result:
pyodbc.ProgrammingError: No results. Previous SQL was not a query.
What version of the product are you using? On what operating system?
* Ubuntu, 9.04.
* libodbc++-dev 0.2.3+0.2.4pre compiled from sources without the unicode
* pyodbc-2.1.5 compilation with python2.6 fails;
* compiling with python25 is OK, however linking against -lodbc does not
work, so I replaced it with -lodbc++-mt
* tdsodbc 0.82-4 (from the distribution)
Please provide any additional information below.
Normal SELECT/INSERT queries are OK.
Thanks,
Józsi
May 11, 2009
Project Member
#1
mkleehammer
Status:
Investigating
May 13, 2009
Hello mkleehammer,
some more information about the SQL server:
Microsoft SQL Server Developer Edition (64-bit)
Microsoft Windows NT 6.0 (6002)
NT x64
10.0.2531.0
Running the tracer the following output was generated server side:
Audit Login -- network protocol: TCP/IP
set quoted_identifier off
set arithabort off
set numeric_roundabort off
set ansi_warnings off
set ansi_padding off
set ansi_nulls off
set concat_null_yields_null off
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
steger 227446 68 2009-05-13 16:53:53.590
SQL:BatchStarting use nmVO steger 227446 68 2009-05-13 16:53:53.590
SQL:BatchCompleted use nmVO steger 0 0 0 0 227446 68 2009-05-13 16:53:53.590
2009-05-13 16:53:53.590
SQL:BatchStarting SET IMPLICIT_TRANSACTIONS ON steger 227446 68 2009-05-13
16:53:53.590
SQL:BatchCompleted SET IMPLICIT_TRANSACTIONS ON steger 0 0 0 0 227446 68 2009-05-13
16:53:53.590 2009-05-13 16:53:53.590
SQL:BatchStarting EXEC sp_datatype_info 93,3 steger 227446 68 2009-05-13
16:53:53.597
SQL:BatchCompleted EXEC sp_datatype_info 93,3 steger 0 74 0 1 227446 68 2009-05-13
16:53:53.597 2009-05-13 16:53:53.597
SQL:BatchStarting SELECT nodeID, label, ip, hostname FROM Shared.getEtomicInfo()
steger 227446 68 2009-05-13 16:53:53.607
SQL:BatchCompleted SELECT nodeID, label, ip, hostname FROM Shared.getEtomicInfo()
steger 0 38 0 1 227446 68 2009-05-13 16:53:53.607 2009-05-13 16:53:53.607
SQL:BatchStarting
DECLARE @l int
SET @l = 15
SELECT @l as x
steger 227446 68 2009-05-13 16:53:53.617
SQL:BatchCompleted
DECLARE @l int
SET @l = 15
SELECT @l as x
steger 0 0 0 0 227446 68 2009-05-13 16:53:53.617 2009-05-13 16:53:53.617
Audit Logout steger 0 112 0 173 227446 68 2009-05-13 16:53:53.590 2009-05-13
16:53:53.763
In the meantime at client I got:
Traceback (most recent call last):
File "./get_all.py", line 34, in <module>
for r in result:
pyodbc.ProgrammingError: No results. Previous SQL was not a query.
In the code it was like
...
cnxn = connect("DRIVER={FreeTDS};DSN=%s;UID=%s;PWD=%s" % (dsn, username, password))
result=cnxn.cursor().execute("""
DECLARE @l int
SET @l = 15
SELECT @l as x
""")
And you cannot iterate over result.
Thanks for the investigation in advance,
Józsi
May 13, 2009
The server side trace looks normal. Can you try a client-side trace? It's looking
like either TDS or the driver.
Here's the test I ran on Windows:
from pyodbc import connect
cnxn = connect("DRIVER={SQL
Server};server=localhost;database=forecast;trusted_connection=yes")
cursor = cnxn.cursor()
cursor.execute("""
DECLARE @l int
SET @l = 15
SELECT @l as x
""")
print cursor.description
> (('x', <type 'int'>, None, 10, None, None, True),)
May 27, 2009
Hello mkleehammer,
I have a different connection string:
cnxn = connect("DRIVER={FreeTDS};DSN=XXXXXXXX;UID=XXXXXXXXXXX;PWD=XXXXXXXXXXXX")
otherwise than that everything is the same, and the cursor.description is
> None
I wonder how we can check if it is TDS/FreeTDS issue or not.
Thanks,
Józsi
Nov 30, 2009
I am seeing this issue on windows connecting to an SQL Server 2008 instance.
The connection string looks like:
'DRIVER={SQL
Server};TDS_Version=8.0;SERVER=%(host)s;DATABASE=%(database)s;UID=%(username)s;PWD=%(password)s'
cursor.description is None
I've tried both calling code like the above, and wrapping it in a stored procedure
and calling that with both the EXEC and {CALL ...} syntax. I have tried with and
without column aliasing and with and without an explicit cast to int.
A DB side trace shows all statements executing as expected. Although I have to admit
I have not idea what the sp_prepexec SP calls are supposed to look like.
Nov 30, 2009
hmmm, I solved the problem immediately after posting. Adding a SET NOCOUNT ON to the SP I'd wrapped the code in made the problem go away. It seems with NO COUNT SQL Server was returning a resultset that was not a resultset...
Aug 24, 2010
(No comment was entered for this change.)
Status:
Done
Nov 21, 2010
(No comment was entered for this change.)
Status:
Complete
|