No results when using known good SQL code
5,979 views
Skip to first unread message
mortonj
Oct 27, 2011, 11:19:44 AM10/27/11
to pyodbc
Just joined, hello to everyone. My first attempt at sending this,
under the title "pyobdc error on python 3.2.2 (long)", using MS
Outlook seems to have vanished into thin air.
I’m hoping someone knows how to get around my problem which might be
related to pyobdc open issue #215. Like that issue, I am getting the
following error - but in my case it happens when passing well proven
MS-SQL code (to the same database/server via pyobdc):
pyodbc.ProgrammingError: No results. Previous SQL was not a query.
The proven code (in the “SQL_COMMANDS" variable, see below) runs to
completion on python 3.2.2 . No errors are being raised by the call
to ”cursor.execute”. The "no results" error message occurs, as you
would expect, at the call to “cursor.fetchone”.
What the SQL code does is pretty basic (returns a monthly table by
extracting daily counts from two databases). To do this the SQL code:
- selects from database AA, placing results in temporary table A,
- selects from database BB, placing results in temporary table B,
- inner joins temporary tables A and B, placing the results into
temporary table C, then
- returns the final results (the 3 fields in temporary table C).
First, I am wondering if my problem (seemingly related to the use of
the insert clause in SQL selects and/or the use of temporary tables in
SQL) is a pyobdc limitation, a SQL problem or an incorrect expectation
arising from an OBDC/pyobdc documentation issue.
Is there an “obvious” limitation (i.e. a hidden assumption or
undocumented requirement) on the number or type of SQL statements that
can be passed in one OBDC (specifically a pyobdc “cursor.execute”)
call? If so, where is this documented? I’ve done a lot of searching
and could not find anything on this very basic point.
I’ve been assuming there are no limitations and that all that
“cursor.execute” does is pass a character string to the server and
then receive the results returned by the (remote) server. If correct
this implies that everything (e.g. multiple commands) embedded inside
in the passed string will be processed by the remote SQL server - and
that every SQL command (and their options) known by that server can be
used. Which, in turn, implies that any SQL code known to run
correctly on the server should also work properly (ideally without any
changes) when passed to the same SQL server using “cursor.execute”.
The fact that I have been able to use temporary variables and insert
SQL comments into the SQL code supports this viewpoint - but the
results when using temporary tables contradicts it!
Experiments have shown that only the FIRST select statement will
return data - but ONLY when the insert cause of that select statement
is commented out. This is the only way I could get any data returned
to python. Any other change causes the above error - or worse.
The second select in the SQL code never returns data. Swapping the two
selects proved that there is data in both databases (the queries
return obviously different results to python).
I've also discovered that the presence of a SQL “use” statement
prevents the first select statement (with the insert commented out)
from working when using pyobdc. It also leads to the same error
message.
All other known good SQL queries on MS SQL server 2008 code work as
expected when run via pyobdc. The difference is that these queries
contain only one select statement and all of their "use" statements
have been commented out. All queries, including the problem one, use
SQL temporary variables that are set to the values that are passed at
the end of the "cursor.execute" command.
Could it be that pyobdc for python 3.X (or at least the precompiled 32
bit BETA install package for Wintel) is not yet able to handle the
results of complicated SQL queries (insert clauses, multiple selects,
temporary tables, etc.)? Perhaps pyobdc is assuming that the desired
data will be returned by the first - instead of the LAST - select
statement in the SQL code)? And are there any known issues when
connecting a 32-bit client to a 64-bit server?
More importantly, is there a simple workaround for python 3.2.2 when
connecting to a MS SQL 2008 server (e.g. perhapsby using some other
package, e.g. SQLAlchemy)?
Or am I missing something obvious? Any help would be appreciated.
Here are the technical details (followed by the code):
Python version: 3.2.2
PyOBDC version: pyodbc-py3-3.0.1-beta04.win32-py3.2.exe
Development platform: Pydev 2.2.2 on Eclipse 3.6.2
SQL client MS SQL Server 2008, 32-bit, version 10.0.4000.0
MDAC Version: 3.85.1132
O/S of Client: XP Pro version 2002 SP3
Remote SQL Server: MS SQL Server 2008 Enterprise Edition, 64-bit,
(10.0.4000.0)
O/S of remote server: Window NT 6.1 (7600)
The cast fields in the SQL selects are “datetime” fields. Casting is
unavoidable since the structure of the remote database is out of my
control. I am aware that the last SQL select could probably be
eliminated assuming the insert statement was removed from the second
last (the inner join) select statement.
import pyodbc
print( 'Start' )
cnxn = pyodbc.connect( Driver = '{SQL Server}', Server = 'xxx\\yyyy',
Database = 'OPMR', Trusted_Connection =
'yes' )
cursor = cnxn.cursor()
''' MS-SQL selection criteria (originally hard coded in MS-SQL)'''
Target_Year = 2011
Target_Month = 9
''' The following SQL code works when run inside MS SQL Server 2008
'''
''' but not when using pyobdc. The only way to return any data is '''
''' to comment out the insert portion of the FIRST select statement.
'''
''' All other changes cause an error message to appear. '''
SQL_COMMANDS = '''
/* USE OPMR -- redundant MS-SQL code that causes problems with pyobdc
*/
DECLARE @target_year int
DECLARE @target_month int
declare @oceanic_counts table
(
day_of_month date,
oceanic_count int
)
declare @CPDLC_counts table
(
day_of_month date,
CPDLC_count int
)
declare @monthly_counts table
(
day_of_month date,
CPDLC_count int,
oceanic_count int
)
/* The parameterized values (were hard coded in MS-SQL)*/
SET @target_year = ?
SET @target_month = ?
/* Daily Oceanic counts */
insert into @oceanic_counts( day_of_month, oceanic_count )
select cast ( Oceanic_POE_Date_Time_UTC as date ),
count( Flight_Rte_Fix_Txt )
from dbo.OPMR_IMP_FLIGHT_INFORMATION
WHERE ( ( year( Oceanic_POE_Date_Time_UTC ) = @target_year ) and
( month( Oceanic_POE_Date_Time_UTC ) = @target_month ) )
group by cast( Oceanic_POE_Date_Time_UTC as date )
/* Daily CPDLC counts */
insert into @CPDLC_counts( day_of_month, CPDLC_count )
select cast( CPDLC_Connection_Datetime as date ),
count( AC_Reg_Mark )
from dbo.OPMR_CPDLC_CONNECTION
WHERE ( ( year( CPDLC_Connection_Datetime ) = @target_year ) and
( month( CPDLC_Connection_Datetime ) = @target_month ) )
group by cast( CPDLC_Connection_Datetime as date )
/* Merge the monthly count tables */
insert into @monthly_counts( day_of_month, oceanic_count,
CPDLC_count )
select A.day_of_month,
A.oceanic_count,
B.CPDLC_count
from @oceanic_counts as A
inner join @CPDLC_counts as B
on ( A.day_of_month = B.day_of_month )
order by A.day_of_month
/* Return the final results to Python */
select day_of_month as Date, oceanic_count as Oceanic, CPDLC_count as
CPDLC
from @monthly_counts
order by day_of_month
'''
cursor.execute( SQL_COMMANDS, Target_Year, Target_Month )
i = 0
while 1:
row = cursor.fetchone()
if not row :
break
i = i + 1
print( i, row )
print ('end')
''' end of code '''
under the title "pyobdc error on python 3.2.2 (long)", using MS
Outlook seems to have vanished into thin air.
I’m hoping someone knows how to get around my problem which might be
related to pyobdc open issue #215. Like that issue, I am getting the
following error - but in my case it happens when passing well proven
MS-SQL code (to the same database/server via pyobdc):
pyodbc.ProgrammingError: No results. Previous SQL was not a query.
The proven code (in the “SQL_COMMANDS" variable, see below) runs to
completion on python 3.2.2 . No errors are being raised by the call
to ”cursor.execute”. The "no results" error message occurs, as you
would expect, at the call to “cursor.fetchone”.
What the SQL code does is pretty basic (returns a monthly table by
extracting daily counts from two databases). To do this the SQL code:
- selects from database AA, placing results in temporary table A,
- selects from database BB, placing results in temporary table B,
- inner joins temporary tables A and B, placing the results into
temporary table C, then
- returns the final results (the 3 fields in temporary table C).
First, I am wondering if my problem (seemingly related to the use of
the insert clause in SQL selects and/or the use of temporary tables in
SQL) is a pyobdc limitation, a SQL problem or an incorrect expectation
arising from an OBDC/pyobdc documentation issue.
Is there an “obvious” limitation (i.e. a hidden assumption or
undocumented requirement) on the number or type of SQL statements that
can be passed in one OBDC (specifically a pyobdc “cursor.execute”)
call? If so, where is this documented? I’ve done a lot of searching
and could not find anything on this very basic point.
I’ve been assuming there are no limitations and that all that
“cursor.execute” does is pass a character string to the server and
then receive the results returned by the (remote) server. If correct
this implies that everything (e.g. multiple commands) embedded inside
in the passed string will be processed by the remote SQL server - and
that every SQL command (and their options) known by that server can be
used. Which, in turn, implies that any SQL code known to run
correctly on the server should also work properly (ideally without any
changes) when passed to the same SQL server using “cursor.execute”.
The fact that I have been able to use temporary variables and insert
SQL comments into the SQL code supports this viewpoint - but the
results when using temporary tables contradicts it!
Experiments have shown that only the FIRST select statement will
return data - but ONLY when the insert cause of that select statement
is commented out. This is the only way I could get any data returned
to python. Any other change causes the above error - or worse.
The second select in the SQL code never returns data. Swapping the two
selects proved that there is data in both databases (the queries
return obviously different results to python).
I've also discovered that the presence of a SQL “use” statement
prevents the first select statement (with the insert commented out)
from working when using pyobdc. It also leads to the same error
message.
All other known good SQL queries on MS SQL server 2008 code work as
expected when run via pyobdc. The difference is that these queries
contain only one select statement and all of their "use" statements
have been commented out. All queries, including the problem one, use
SQL temporary variables that are set to the values that are passed at
the end of the "cursor.execute" command.
Could it be that pyobdc for python 3.X (or at least the precompiled 32
bit BETA install package for Wintel) is not yet able to handle the
results of complicated SQL queries (insert clauses, multiple selects,
temporary tables, etc.)? Perhaps pyobdc is assuming that the desired
data will be returned by the first - instead of the LAST - select
statement in the SQL code)? And are there any known issues when
connecting a 32-bit client to a 64-bit server?
More importantly, is there a simple workaround for python 3.2.2 when
connecting to a MS SQL 2008 server (e.g. perhapsby using some other
package, e.g. SQLAlchemy)?
Or am I missing something obvious? Any help would be appreciated.
Here are the technical details (followed by the code):
Python version: 3.2.2
PyOBDC version: pyodbc-py3-3.0.1-beta04.win32-py3.2.exe
Development platform: Pydev 2.2.2 on Eclipse 3.6.2
SQL client MS SQL Server 2008, 32-bit, version 10.0.4000.0
MDAC Version: 3.85.1132
O/S of Client: XP Pro version 2002 SP3
Remote SQL Server: MS SQL Server 2008 Enterprise Edition, 64-bit,
(10.0.4000.0)
O/S of remote server: Window NT 6.1 (7600)
The cast fields in the SQL selects are “datetime” fields. Casting is
unavoidable since the structure of the remote database is out of my
control. I am aware that the last SQL select could probably be
eliminated assuming the insert statement was removed from the second
last (the inner join) select statement.
import pyodbc
print( 'Start' )
cnxn = pyodbc.connect( Driver = '{SQL Server}', Server = 'xxx\\yyyy',
Database = 'OPMR', Trusted_Connection =
'yes' )
cursor = cnxn.cursor()
''' MS-SQL selection criteria (originally hard coded in MS-SQL)'''
Target_Year = 2011
Target_Month = 9
''' The following SQL code works when run inside MS SQL Server 2008
'''
''' but not when using pyobdc. The only way to return any data is '''
''' to comment out the insert portion of the FIRST select statement.
'''
''' All other changes cause an error message to appear. '''
SQL_COMMANDS = '''
/* USE OPMR -- redundant MS-SQL code that causes problems with pyobdc
*/
DECLARE @target_year int
DECLARE @target_month int
declare @oceanic_counts table
(
day_of_month date,
oceanic_count int
)
declare @CPDLC_counts table
(
day_of_month date,
CPDLC_count int
)
declare @monthly_counts table
(
day_of_month date,
CPDLC_count int,
oceanic_count int
)
/* The parameterized values (were hard coded in MS-SQL)*/
SET @target_year = ?
SET @target_month = ?
/* Daily Oceanic counts */
insert into @oceanic_counts( day_of_month, oceanic_count )
select cast ( Oceanic_POE_Date_Time_UTC as date ),
count( Flight_Rte_Fix_Txt )
from dbo.OPMR_IMP_FLIGHT_INFORMATION
WHERE ( ( year( Oceanic_POE_Date_Time_UTC ) = @target_year ) and
( month( Oceanic_POE_Date_Time_UTC ) = @target_month ) )
group by cast( Oceanic_POE_Date_Time_UTC as date )
/* Daily CPDLC counts */
insert into @CPDLC_counts( day_of_month, CPDLC_count )
select cast( CPDLC_Connection_Datetime as date ),
count( AC_Reg_Mark )
from dbo.OPMR_CPDLC_CONNECTION
WHERE ( ( year( CPDLC_Connection_Datetime ) = @target_year ) and
( month( CPDLC_Connection_Datetime ) = @target_month ) )
group by cast( CPDLC_Connection_Datetime as date )
/* Merge the monthly count tables */
insert into @monthly_counts( day_of_month, oceanic_count,
CPDLC_count )
select A.day_of_month,
A.oceanic_count,
B.CPDLC_count
from @oceanic_counts as A
inner join @CPDLC_counts as B
on ( A.day_of_month = B.day_of_month )
order by A.day_of_month
/* Return the final results to Python */
select day_of_month as Date, oceanic_count as Oceanic, CPDLC_count as
CPDLC
from @monthly_counts
order by day_of_month
'''
cursor.execute( SQL_COMMANDS, Target_Year, Target_Month )
i = 0
while 1:
row = cursor.fetchone()
if not row :
break
i = i + 1
print( i, row )
print ('end')
''' end of code '''
Michael Kleehammer
Oct 27, 2011, 5:09:06 PM10/27/11
to pyo...@googlegroups.com
Summary: Add "SET NOCOUNT ON" to the top of your stored procedure.
If you add SET NOCOUNT ON, the non-select results will not be returned.
You can also use Cursor.nextset() to "step over" non-select results.
I may consider automatically doing this unless a flag is used to turn it off.
mortonj
Oct 31, 2011, 9:20:31 AM10/31/11
to pyodbc
First off, thanks for taking the time to help. And thanks for
mentioning that particular SQL "feature".
I've tried both ideas, together and separately. I even tried
throwing in a couple of hundred "cursor.nextset()" statements to see
if I could force an exception to be thrown. No luck, the python code
always returns the "no results" error at the "cursor.fetchone()" line
no matter how many "cursor.nextset()" statements are inserted.
I do not have the rights to create stored SQL procedures. So I am
trying to get a similar effect by using pyobdc to submit the fixed
sequence of SQL statements. This approach works properly with all of
my other queries without needing either change.
The only obvious difference is that all of my other "cursor.execute()"
SQL queries contain only one select statement.
This difference makes me wonder if what I am trying to do (use more
than 1 select statement in a "cursor.execute" call) is confusing
pyobdc and pushing it beyond what it is designed to do.
Are there any other ideas out there of how to get what I want?
On Oct 27, 5:09 pm, Michael Kleehammer <mkleeham...@gmail.com> wrote:
> Summary: Add "SET NOCOUNT ON" to the top of your stored procedure.
>
> SQL Server has a very annoying habit of returning *everything* as part of
mentioning that particular SQL "feature".
I've tried both ideas, together and separately. I even tried
throwing in a couple of hundred "cursor.nextset()" statements to see
if I could force an exception to be thrown. No luck, the python code
always returns the "no results" error at the "cursor.fetchone()" line
no matter how many "cursor.nextset()" statements are inserted.
I do not have the rights to create stored SQL procedures. So I am
trying to get a similar effect by using pyobdc to submit the fixed
sequence of SQL statements. This approach works properly with all of
my other queries without needing either change.
The only obvious difference is that all of my other "cursor.execute()"
SQL queries contain only one select statement.
This difference makes me wonder if what I am trying to do (use more
than 1 select statement in a "cursor.execute" call) is confusing
pyobdc and pushing it beyond what it is designed to do.
Are there any other ideas out there of how to get what I want?
On Oct 27, 5:09 pm, Michael Kleehammer <mkleeham...@gmail.com> wrote:
> Summary: Add "SET NOCOUNT ON" to the top of your stored procedure.
>
python junkie
Apr 23, 2012, 10:23:40 PM4/23/12
to pyo...@googlegroups.com
I see that this post is several months old. Our development team has been using a combination of pyodbc and odbc modules for the past several years successfully.
I am wondering if we are pushing the boundaries of what pyodbc and odbc can do.
When I run some new scripts that we are testing, with temp tables, setting sql variables, and executing sql cursors, the code passes
straight through to the end as fast as it can read the code,
performs no action, and does not return any error.
One would think that it succeeded.
This is very bad behaviour on the part of the module.
I am able to get the code to work in a sql server console window, and by creating a stored procedure of the same code and executing it using
an odbc module.
Were you able to learn any more about this issue.
Chris Lambacher
Apr 23, 2012, 10:30:49 PM4/23/12
to pyodbc
Hi,
Anything that creates a message is likely to cause a problem. The
origninal poster had a print statement at the start of the query. This
is the likely culprit in this case, but I have run into issues where
SQL Server provides a helpful warning message. These messages can
normally be turned off. If you run the SQL verbatim in SQL Server
Management Studio and get the message tab showing up in the results
pane then you are in for trouble until you can make it go away.
-Chris
On Apr 23, 10:23 pm, python junkie <software.buy.des...@gmail.com>
wrote:
Anything that creates a message is likely to cause a problem. The
origninal poster had a print statement at the start of the query. This
is the likely culprit in this case, but I have run into issues where
SQL Server provides a helpful warning message. These messages can
normally be turned off. If you run the SQL verbatim in SQL Server
Management Studio and get the message tab showing up in the results
pane then you are in for trouble until you can make it go away.
-Chris
On Apr 23, 10:23 pm, python junkie <software.buy.des...@gmail.com>
wrote:
python junkie
Apr 23, 2012, 10:38:47 PM4/23/12
to pyo...@googlegroups.com
I would think (I would hope) that the pyodbc module would somehow indicate that the code did not execute.
The program acts as if it completed successfully and sends back an exit 0 in a very few seconds.
Even though it did no actual work
Chris Lambacher
Apr 24, 2012, 9:27:51 AM4/24/12
to pyodbc
Hi,
The original poster indicated that he got the following error which is
raised as an exception:
It sound like you are having a different problem. I am pretty sure no
one is going to be able to help you based solely on the description of
the problem you provided. More are neccessary, preferably a short
amount of code exhibiting the problem.
-Chris
On Apr 23, 10:38 pm, python junkie <software.buy.des...@gmail.com>
wrote:
The original poster indicated that he got the following error which is
raised as an exception:
pyodbc.ProgrammingError: No results. Previous SQL was not a query.
The causes of this error are what I addressed in my previous message.
It sound like you are having a different problem. I am pretty sure no
one is going to be able to help you based solely on the description of
the problem you provided. More are neccessary, preferably a short
amount of code exhibiting the problem.
-Chris
On Apr 23, 10:38 pm, python junkie <software.buy.des...@gmail.com>
wrote:
bencha...@gmail.com
Jan 3, 2014, 9:33:16 PM1/3/14
to pyo...@googlegroups.com
(Old thread, but just in case someone's watching)
I had similar error and discovered that my python application was sending the query as unicode, and pyodbc would only accept a string value. It ran successfully when I converted the SQL query from unicode to a string.
Reply all
Reply to author
Forward
0 new messages