callproc requires nextset() to skip empty result sets, execute('exec ...') does not

[GoogleCodeExporter]

The behavior of calling a stored procedure may be different when using callproc 
than when executing comparable SQL. In particular, if a stored procedure 
contains two select statements, the first not returning rows, then when using 
execute("exec ...") you can immediately call cursor.fetchall() and get the rows 
from the second select. If you use cursor.callproc() instead of 
cursor.execute() then you must explicitly skip the first (empty) result set by 
calling nextset().

What steps will reproduce the problem?
1.execute the attached sql to create the table and stored procedure
2.execute the attached python script

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

If we want behavior to be consistent between execute/callproc (and I think we 
do) then callproc should transparently skip over empty result sets--if we did 
this then we won't see the test script print out an exception.

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

Please provide any additional information below.

Original issue reported on code.google.com by jens.jor...@gmail.com on 10 Jul 2012 at 2:25

Attachments:

• callproc_demo.sql
• callproc_demo.py

[GoogleCodeExporter]

Could you please test this change 
https://github.com/ramiro/pymssql/commit/06faa105332965f27580f5eacf87aeeed29fad2
e and report back if it fixes the issue for you?

Original comment by cra...@gmail.com on 29 Sep 2012 at 9:45

[GoogleCodeExporter]

Tried it. But no, unfortunately it doesn't fix it. Actually with that change I 
get an additional error at the last part of the script: Attempt to initiate a 
new Adaptive Server operation withresults pending

Original comment by jens.jor...@gmail.com on 1 Oct 2012 at 3:08

[GoogleCodeExporter]

now use cursor.callproc, observe the exception
Traceback (most recent call last):
  File "/home/jorgensen/Downloads/callproc_demo.py", line 33, in <module>
    print c.fetchall()
  File "pymssql.pyx", line 460, in pymssql.Cursor.fetchall (pymssql.c:5782)
    raise OperationalError('Statement not executed or executed statement has no resultset')
OperationalError: Statement not executed or executed statement has no resultset

and now use cursor.callproc, but call cursor.nextset(), observe we then get rows
Traceback (most recent call last):
  File "/home/jorgensen/Downloads/callproc_demo.py", line 44, in <module>
    c.callproc(sp, ())
  File "pymssql.pyx", line 339, in pymssql.Cursor.callproc (pymssql.c:3550)
    proc = self._source._conn.init_procedure(procname)
  File "_mssql.pyx", line 1083, in _mssql.MSSQLConnection.init_procedure (_mssql.c:9941)
    return MSSQLStoredProcedure(procname, self)
  File "_mssql.pyx", line 1166, in _mssql.MSSQLStoredProcedure.__init__ (_mssql.c:10518)
    check_cancel_and_raise(rtc, self.conn)
  File "_mssql.pyx", line 1338, in _mssql.check_cancel_and_raise (_mssql.c:12123)
    return maybe_raise_MSSQLDatabaseException(conn)
  File "_mssql.pyx", line 1380, in _mssql.maybe_raise_MSSQLDatabaseException (_mssql.c:12694)
    raise ex
_mssql.MSSQLDatabaseException: (20019, 'DB-Lib error message 20019, severity 
7:\nAttempt to initiate a new Adaptive Server operation with results pending\n')

Original comment by jens.jor...@gmail.com on 1 Oct 2012 at 7:32

[GoogleCodeExporter]

What RDBMS are you using? MS SQL or one from Sybase? That "Adaptive Server 
operation" seems to indicate the latter.

Original comment by cra...@gmail.com on 12 Oct 2012 at 1:30

[GoogleCodeExporter]

SQL Server 9.0.5000 (or that's what I see in SQL Server Management Studio 
anyhow). Is there anything you could suggest to debug this on my end? I'm happy 
to trying some other things out to see if I can fix it. Maybe you could 
describe to me what you think the problem is?

Original comment by jens.jor...@gmail.com on 12 Oct 2012 at 2:01

[GoogleCodeExporter]

Couldn't find info on how this should be handled in PEP 249. 

Pyodbc doesn't support callproc. 

Postgres doesn't support multiple result sets. 

Anyone know how other DB-API implementations (MySQL, etc.?) handle this?

I feel like consistency is probably desired but I don't know which behavior is 
correct - the skipping of the empty result set seems like it could be 
undesirable as it makes it impossible to know which of the multiple queries 
returned results. 

I'm not inclined to fix this unless I see evidence of how it should behave. 

Original comment by msabr...@gmail.com on 25 Sep 2013 at 2:32

[GoogleCodeExporter]

This issue was migrated to GitHub at 
https://github.com/pymssql/pymssql/issues/98 -- let's do further comments there 
(or maybe both places?)

Original comment by msabr...@gmail.com on 25 Sep 2013 at 3:13

[GoogleCodeExporter]

OK, I realized that both the OP and I were a little confused and the issue is 
different than what we originally thought.

The stored proc that the OP used **does not return two resultsets**. It returns 
only one.

You can see this by executing a proc like this in SSMS:

```SQL
IF OBJECT_ID('callproc_demo_jens', 'P') IS NOT NULL
    DROP PROCEDURE callproc_demo_jens
GO

CREATE PROCEDURE callproc_demo_jens AS
BEGIN
    SET NOCOUNT ON
    DECLARE @sname varchar(30)
    SELECT  @sname = @@servername  -- this does not return a resultset
    SELECT name FROM (VALUES('Tom'), ('Dick'), ('Harry')) AS tbl(name) WHERE name = 'Mike'  -- this returns an empty resultset
    SELECT name FROM (VALUES('Tom'), ('Dick'), ('Harry')) AS tbl(name)  -- this returns a resultset with 3 rows
END
GO

EXEC callproc_demo_jens

If you execute this you will see that it returns two resultsets -- the first SELECT does not return a resultset because it SELECTs into a variable. The second SELECT returns a resultset with 0 rows. The third SELECT returns a resultset with 3 rows.

This means that the OP discovered a problem with pymssql but it's a little different than what we thought.

The OP reported that for callproc he needed to call nextset to skip the empty resultset (and that with execute it was automatically skipping the resultset). In fact, his stored proc did not return an empty resultset, it returned only one resultset with 3 rows. So execute was not skipping an empty resultset -- it was returning the only resultset. However, there is a problem because callproc was not returning these rows unless you call nextset first -- that is a bug. So I think I will close this issue, because it's a little confusing and file a newer one that is clearer...

Original comment by `msabr...@gmail.com` on 28 Sep 2013 at 9:43
* Changed state: **Invalid**

[GoogleCodeExporter]

The new issue that I filed is:

https://github.com/pymssql/pymssql/issues/134

Original comment by msabr...@gmail.com on 30 Sep 2013 at 5:17