reading cursor.return_value breaks fetches

[VFLashM]

User calls stored procedure with callproc. Stored procedure returns resultset and return value. If return value is read before resultset, resultsset is always empty. It's not clear to me if it can be fixed easily. I suppose it should be at least asserted.

import sys
import pytds

assert len(sys.argv) == 5, 'Provide 4 db connection arguments'

with pytds.connect(*sys.argv[1:]) as connection:
    with connection.cursor() as cursor:
        cursor.execute("""
create proc TestProcedure
as
begin
    select 1 union select 2 union select 3
    select 'a' union select 'b' union select 'c'
    return 9
end
""")
        cursor.callproc('TestProcedure')

        # HERE:        
        # print cursor.return_value # if uncommented, breaks fetchall

        print cursor.fetchall()   # returns 1,2,3
        cursor.nextset()
        print cursor.fetchall()   # returns a,b,c
        print cursor.return_value # returns 9

    connection.rollback()

[denisenkom]

It cannot behave correctly, because return value is sent at the end of the tds stream, basically you cannot read it before you read all returned rows. Throwing an exception is also not good, it would require client code to first to iterate through all results, and this may break some code that relies on it.

I can document this behavior, if you agree with current implementation.

Also I would suggest to avoid using "return" statement, because it is not DBAPI compliant, if possible.

[VFLashM]

I understand that it's how protocol works, there is nothing we can do to fix it completely. But is it possible to assert on fetch after return_value? Currently if you fetch after checking return_value, it silently returns nothing. It would be great if it threw an error like the one that says "Previous statement didn't produce any results", but mentioned that return_value might be the cause of that problem.