getting results from IBM DB2 stored procedure

[DeltaFox0018]

Please first make sure you have looked at:

• Documentation: https://github.com/mkleehammer/pyodbc/wiki
• Other issues

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be sure to specify 32-bit Python or 64-bit:

• Python: 3.12.6
• pyodbc: 5.10
• OS: MacOS 15.0.1 (M1 Pro)
• DB: IBM DB2 (IBM Power 9)
• driver: IBM i Access ODBC Driver

Issue

I have a procedure with 3 parameters, 2 input and 1 output.

From DBeaver if I call the procedure as follows CALL my_procedure('user','psw', ?)

I have an output of the following type

Through pyodbc I configured the connection to the DB and ran the same call

`
cnxn = pyodbc.connect("DRIVER=IBM i Access ODBC Driver;SYSTEM=MYIP;UID=USER;PWD=PSW;")

cursor = cnxn.cursor()

# Parameters for the stored procedure
params = ('user', "psw", 0)

cursor.execute("{CALL my_procedure (?, ?, ?)}", params)
print(cursor.fetchall())

`

Running the print either you fetchall, fetchone or fetchval or the following error

Traceback (most recent call last): File "/Users/alessandrovolpato/Desktop/rapportini-backend/main.py", line 43, in <module> print(cursor.fetchone()) ^^^^^^^^^^^^^^^^^ pyodbc.ProgrammingError: No results. Previous SQL was not a query.

Do you know how I can retrieve the output of the procedure?

[gordthompson]

You might try this:

cursor.execute(sql)
try:
    rows = cursor.fetchall()
except pyodbc.ProgrammingError:
    cursor.nextset()
    rows = cursor.fetchall()
print(rows)

[DeltaFox0018]

I have tried but the error is the same

[gordthompson]

A more robust approach would be

cursor.execute(sql)
rows = []
fetching = True
while fetching:
    try:
        rows = cursor.fetchall()
        fetching = False
    except pyodbc.ProgrammingError:
        fetching = cursor.nextset()
print(rows)

[kadler]

pyodbc doesn't support output parameters. There's a workaround documented here