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

[kadler]

You are passing "TESTSQL.DCM_OUT" in to a parameter marker. That will attempt to pass the literal value "TESTSQL.DCM_OUT" to that store procedure, not a reference to the variable named TESTSQL.DCM_OUT.

I think you'd need to change your SQL like so: sql = "{CALL TESTSQL.RETRIEVE_CERT_USAGE_INFO(TESTSQL.DCM_OUT,?,?,?,?)}"

[muggel042]

Thank you. It's working a little bit better, but the fetchval() function gives always "none" as a result.

I think I'll give up on this and wait for an official DB2 function or table on this.

[kadler]

A variable has to be initialized in the job before it can be used. If you do a SET TESTSQL.DCM_OUT = '' before calling
TESTSQL.RETRIEVE_CERT_USAGE_INFO, that will activate the variable in your job and then when you query it, it'll have a value.

[kadler]

I would also change APPLICATION_SELECTION_CRITERIA and ERROR_CODE to INTEGER and pass 0, eg.

#create or replace stored procedure for API call 
sql = """CREATE OR REPLACE PROCEDURE TESTSQL.RETRIEVE_CERT_USAGE_INFO (
                OUT RECEIVER_VARIABLE CHAR(32750) FOR BIT DATA,
                IN LENGTH_OF_RECEIVER_VARIABLE INTEGER,
                IN FORMAT_NAME CHAR(8),
                IN APPLICATION_SELECTION_CRITERIA INTEGER,
                IN ERROR_CODE INTEGER
             )
             LANGUAGE CL
             DYNAMIC RESULT SETS 1
             NOT DETERMINISTIC
             READS SQL DATA
             CALLED ON NULL INPUT
             EXTERNAL NAME 'QICSS/QYCDCUSG(QycdRetrieveCertUsageInfo)'
             PARAMETER STYLE GENERAL"""

db.execute(sql)
#create output variable
db.execute("CREATE OR REPLACE VARIABLE TESTSQL.DCM_OUT CHAR(32750)")
db.execute("SET TESTSQL.DCM_OUT = ''")

#execute stored procedure with all parameters, except output parameter global variable
sql = "{CALL TESTSQL.RETRIEVE_CERT_USAGE_INFO(TESTSQL.DCM_OUT,?,?,?,?)}"
params = (32750 ,'RCUI0200', 0, 0)
db.execute(sql,params)

#fetch variable content
data = db.execute("VALUES(TESTSQL.DCM_OUT)").fetchval()

print(data)

[muggel042]

Hi

I already knew you're the best, but please let me say it again: You're the best!

With your modifications I receive output, which I can now process.

Many thanks for all the help.