My favorites | Sign in
Project Home Downloads Wiki
READ-ONLY: This project has been archived. For more information see this post.
Search
for
  Advanced search   Search tips   Subscriptions
Issue 49: problem with variable assignment
2 people starred this issue and may be notified of changes. Back to list
Status:  Complete
Owner:  ----
Closed:  Aug 2010


 
Reported by steger.j...@gmail.com, May 8, 2009
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
Hmmm...  This works on Windows 7 with SQL Server Express 2008, pyodbc 2.1.5, and
Python 2.6.

Can you provide an ODBC trace?  I'll generate one on Windows and compare.
Status: Investigating
May 13, 2009
#2 steger.j...@gmail.com
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
Project Member #3 mkleehammer
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
#4 steger.j...@gmail.com
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
#5 long.hai...@gmail.com
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
#6 long.hai...@gmail.com
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
Project Member #7 mkleehammer
(No comment was entered for this change.)
Status: Done
Nov 21, 2010
Project Member #8 mkleehammer
(No comment was entered for this change.)
Status: Complete

Powered by Google Project Hosting