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 303: table variable creation and query fails
1 person starred this issue and may be notified of changes. Back to list
Status:  NoFix
Owner:  ----
Closed:  Mar 2013


 
Reported by dmlvia...@gmail.com, Dec 19, 2012
What steps will reproduce the problem?

In [22]:cx = pyodbc.connect('DSN=<dsn>;Description=<desc>;UID=<uid>;PWD=<pwd>;WSID=<wsid>;DATABASE=<database>;QuotedId=Yes;AnsiNPW=No')

In [23]: cursor = cx.cursor()

In [24]: cursor.execute("""
   ....: DECLARE @LOG TABLE 
   ....: 	(
   ....: 	ID varchar(10)
   ....: 	)
   ....:  
   ....: INSERT INTO @LOG (ID)
   ....: SELECT TOP 10 ID
   ....: FROM NAME
   ....: 
   ....: SELECT ID
   ....: FROM @LOG
   ....: 
   ....: """)
Out[24]: <pyodbc.Cursor object at 0x044EB1A8>

In [25]: cursor.fetchall()
------------------------------------------------------------
Traceback (most recent call last):
  File "<ipython console>", line 1, in <module>
ProgrammingError: No results.  Previous SQL was not a query.

What is the expected output? What do you see instead?

In [26]: cursor.execute("""
   ....: SELECT TOP 10 ID
   ....: FROM NAME
   ....: """)
Out[26]: <pyodbc.Cursor object at 0x044EB1A8>

In [27]: cursor.fetchall()
Out[27]: 
[('1', ),
 ('10', ),
 ('100000', ),
 ('100001', ),
 ('100002', ),
 ('100003', ),
 ('100004', ),
 ('100005', ),
 ('100006', ),
 ('100007', )]

What version of the product are you using? On what operating system?

pyodbc-3.0.6.win32-py2.7.exe on Windows 7, querying MS SQL Server 2000

Please provide any additional information below.

I was able to successfully run the above query with Eclipse SQL Explorer, which also connects via ODBC. I believe this issue might be related to  issue 215 .
Mar 28, 2013
Project Member #1 mkleehammer
I think what is happening is that 3 SQL statements are being executed, so you should get 3 "results".  The first one isn't a select statement, so it returns the error you see.

The most obvious, portable solution is to use 3 calls to execute(), and it would also be my recommendation long term.

However, for cases where you really want this to work, you probably need to call cursor.next() to move from one result set to the next.  I'm not sure if you need to call it once or twice -- I know you'll get results for the INSERT statement.  In particular, you should get the number of rows inserted in cursor.rowcount.

You can also put SET NOCOUNT ON at the top to disable the generation of results for non-query operations.

I'm going to close this because I'm pretty confident that is the issue.  Please re-open if next() does not fix it.  Good luck.
Status: NoFix

Powered by Google Project Hosting