Call to a stored procedure that returns an empty result set also fails to return OUTPUT parameters and the return value

[artgoldberg]

Hello pyocbc. Thanks for creating and sharing this great package!

I'm using pyodbc to call an SP in a SQL Server database and following the excellent Calling Stored Procedures documentation. The SP returns a result set from a SELECT, two OUTPUT parameters, and a return value. The Python code obtains these in 4 result sets, except when the result set from the SELECT is empty. In that case the Python code obtains no result sets. This surprises me, because I expect the Python code to obtain 3 result sets:

• Return value
• First OUTPUT parameter
• Second OUTPUT parameter

This is the SP:

CREATE OR ALTER PROCEDURE protect_testing.SelectPeople
@LastName VARCHAR(30),
@Output1 VARCHAR(60) OUTPUT,
@Output2 VARCHAR(30) OUTPUT
AS
BEGIN
    SET NOCOUNT ON; -- With this statement here, the calling Python code does not need to provide it

    SELECT * FROM protect_testing.Persons WHERE LastName = @LastName;
    SET @Output1 = N'Input value for @LastName: "' + @LastName + '".';
    SET @Output2 = 'Value for @Output2';
    RETURN -1

END

This is the Python that uses pyodbc to call the SP:

import pyodbc

# Connection variables
msdw_dev = 'hidden.for.security.org'
database = 'API'

# search variable
last_names = ('Goldberg',
              'No person',
              'Smith')

try:
    # Connection string
    conn_str = (
        'DRIVER={ODBC Driver 17 for SQL Server};'
        f'SERVER={msdw_dev};'
        f'DATABASE={database};'
        'Trusted_Connection=yes;')

    # Connect
    connection = pyodbc.connect(conn_str)
    cursor = connection.cursor()

    for last_name in last_names:
        print()
        print('New search with last name: ', last_name)
        # Prepare the stored procedure execution script and parameter values
        sql = '''
        DECLARE @out1 VARCHAR(60);
        DECLARE @out2 VARCHAR(30);

        DECLARE @rv int;

        EXEC @rv = protect_testing.SelectPeople @LastName = ?,
                                                @Output1 = @out1 OUTPUT,
                                                @Output2 = @out2 OUTPUT;

        SELECT @rv AS return_value;
        SELECT @out1 AS output_1;
        SELECT @out2 AS output_2;
        '''

        params = (last_name)

        # Execute Stored Procedure with parameter
        cursor.execute(sql, params)

        # Iterate the cursor
        rows = cursor.fetchall()
        while rows:
            print('\nResult set:')
            print(rows)
            if cursor.nextset():
                rows = cursor.fetchall()
            else:
                rows = None

    # Close the cursor and delete it
    cursor.close()
    del cursor

    # Close the database connection
    connection.close()

except Exception as e:
    print("Error: %s" % e)

And this is its output:

New search with last name:  Goldberg

Result set:
[(1, 'Arthur', 'Goldberg', 'None of your business', 'NYC'), (2, 'Joe', 'Goldberg', 'None of your business again', 'NYC'), (5, 'FN', 'Goldberg', None, None)]

Result set:
[(-1, )]

Result set:
[('Input value for @LastName: "Goldberg".', )]

Result set:
[('Value for @Output2', )]

New search with last name:  No person

New search with last name:  Smith

Result set:
[(3, 'Sally', 'Smith', '', 'LA')]

Result set:
[(-1, )]

Result set:
[('Input value for @LastName: "Smith".', )]

Result set:
[('Value for @Output2', )]

The SELECT finds no records when the last name is No person, and cursor.fetchall() obtains no rows. I expect that an error in my code loses the other three result sets. What is it please?

Thanks
Arthur

[v-chojas]

Could you post an ODBC trace?

[artgoldberg]

Hi @v-chojas . Yes, I would be happy to post an ODBC trace. But my odbcinst.ini file has been moved to the wrong directory. (The reason why is a long story.) Do you know where it belongs, please?

Thanks
Arthur

[artgoldberg]

@v-chojas Never mind, I figured it out.
A

[gordthompson]

If the query returns an empty list then rows is a "falsy" value:

>>> rows = [("gord",)]
>>> bool(rows)
True
>>> rows = []
>>> bool(rows)
False

That will cause your while rows: loop to exit.

[gordthompson]

@artgoldberg - I have updated the sample code in the wiki:

https://github.com/mkleehammer/pyodbc/wiki/Calling-Stored-Procedures#output-parameters-and-return-values

[artgoldberg]

Hi @gordthompson . Good point that a Python empty list is false, and thanks for fixing the sample code.

Regards
A

[artgoldberg]

Thanks @v-chojas

This OCBC trace file records one run of the program above.

Regards
Arthur

odbctrace.log

[v-chojas]

I think Gord answered the question about your code; as for odbcinst.ini, i assume you're using Linux so that file is usually in /etc. You can use odbcinst -j to determine its location, providing there's only one installation of unixODBC on the system.