cursor.callproc() ==> "CLI0150E Driver not capable. SQLSTATE=HYC00 SQLCODE=-99999'"

[TDKZG]

I'm trying to call store procedure with 3 input parameters, but always getting this error:

ibm_db_dbi.DatabaseError: ibm_db_dbi::DatabaseError: Exception('Describe Param Failed: [IBM][CLI Driver] CLI0150E Driver not capable. SQLSTATE=HYC00 SQLCODE=-99999')

Am I doing everything right regarding code and syntax? Any suggestions how to debug it? I'm using Python 3.7.7 and ibm_db_dbi v3.0.1 Tnx

import ibm_db_dbi

#---- DB2 -------------
db_name = 'XXX'
hostname = 'XXX'
port = '50000'
user_id = 'XXX'
password = 'XXX' 
spName = "XXX"
returnCode = False
resultSet = None

try:
    conn = ibm_db_dbi.connect('database=' + db_name + 
    ';hostname=' + hostname + 
    ';port=' + port + 
    ';protocol=tcpip;uid=' + user_id + 
    ';pwd=' + password, '', '')
except:     
    print('DB2 error' )

# INPUT PAR.
P_NUMBER = '12345678901'
P_YEAR = '2019'
P_CODE = '00010'

spParamValues = P_NUMBER, P_YEAR, P_CODE

cursor = conn.cursor()

resultSet = cursor.callproc(spName, spParamValues)

if resultSet is None:
    conn.close()
    exit(-1)
for item in resultSet:
    print(str(item))

[imavo]

Instead of: spParamValues = P_NUMBER, P_YEAR, P_CODE try making a tuple like this: spParamValues = ( P_NUMBER, P_YEAR, P_CODE)

[TDKZG]

Instead of: spParamValues = P_NUMBER, P_YEAR, P_CODE try making a tuple like this: spParamValues = ( P_NUMBER, P_YEAR, P_CODE)

No change. Same error CLI0150E Driver not capable....

[SabaKauser]

using a tuple should work. It works for me: cur = conn.cursor()

stmt="""Create PROCEDURE proc (OUT out_id INTEGER, OUT out_name CHAR(30)) DYNAMIC RESULT SETS 1 LANGUAGE SQL BEGIN declare c1 cursor with return for Select ID, NAME from animals where id=4;open c1;set out_id=100; set out_name='test'; END""" try: print("Creating stored procedure") cur.execute(stmt) print("Done!\n") except Exception as e: print("ERROR: Could not create procedure") print(e)

out1=0 out2="" spParamValues = (out1, out2) try: print("Calling stored procedure")

stmt=cur.callproc('proc',(out1,out2))

stmt=cur.callproc('proc', spParamValues) print("Persons retrieved by the stored procedure:") row=cur.fetchall() print("first result set") while row: for i in row: print(i) row = cur.fetchall() except Exception as e: print("ERROR: Could not call procedure") print(e)

Can you share the SP definition?

[TDKZG]

CREATE PROCEDURE XYZ.XYZ
   (IN P_NUMBER CHAR(11),
    IN P_YEAR CHAR(4),
    IN P_CODE CHAR(5),
    OUT P_INDICATOR SMALLINT,
    OUT P_SQLCODE INTEGER,
    OUT P_MESSAGE VARCHAR(100))

    VERSION V1
    QUALIFIER DEV
    ISOLATION LEVEL UR 
    PACKAGE OWNER XYZ
    LANGUAGE SQL
    CURRENT DATA NO
    VALIDATE BIND
    ASUTIME LIMIT 10000
    -- WLM ENVIRONMENT FOR DEBUG MODE WLMENV1
        -- ALLOW DEBUG MODE

P1: BEGIN
.
.
.

I cant go into details of SP code public, but this is definition part. SP is working properly when called from other sources, but when I try with Python I get error.

[SabaKauser]

Can you collect db2 traces and share. to collect traces: cd clidriver install path, found under site-packages of ibm_db install location cd adm db2trc on -f trc.dmp run your application db2trc off db2trc flw trc.dmp trc.flw db2trc fmt trc.dmp trc.fmt db2trc fmt -c trc.dmp trc.fmtc db2trc fmt -cli trc.dmp trc.cli and share the trc.flw, trc.fmt, trc.fmtc and trc.cli files

If you are on MAC: cd clidriver install path cd adm db2trc on -l 2m

db2trc dump trc.dmp db2trc off db2trc flw trc.dmp trc.flw db2trc fmt trc.dmp trc.fmt db2trc fmt -c trc.dmp trc.fmtc db2trc fmt -cli trc.dmp trc.cli and share the trc.flw, trc.fmt, trc.fmtc and trc.cli files

[TDKZG]

In my clidriver install path I don't have adm folder so I cant do "cd adm" part. This is my folder structure:

Inside bin folder I have

So I went from there and after executing db2trc on -f trc.dmp I got:

Mind that I'm not admin on my PC and currently I can't ask admin to login and try with his credentials. Maybe this is the problem with db2trc? I tried to change buffer size but it didn't help, I got the same message. Any suggestions?

[imavo]

Your stored-procedure has three input parameters and three output parameters .

So there are a total of six parameters for your procedure.

You need to follow the callproc interface specification when using it.

Please also tell us the output of the db2level command (just paste the plain text that it shows) on your workstation.

Please also tell us the Db2-server platform (is it Db2 for Z/OS or i-series ), and the Db2-server VERSION

[TDKZG]

Your stored-procedure has three input parameters and three output parameters .

So there are a total of six parameters for your procedure.

You need to follow the callproc interface specification when using it.

If you mean spParamValues variable has to be a tuple with 6 parameters (IN and OUT), I tried only with IN parameters AND with IN and OUT (all 6) --> same error.

BUT I found my error. spName variable has to be in this format: collection_name.sp_name In my case I was using only sp_name. BUT#2, Now Im getting another error:

Please also tell us the output of the db2level command (just paste the plain text that it shows) on your workstation.

Please also tell us the Db2-server platform (is it Db2 for Z/OS or i-series ), and the Db2-server VERSION

DB2-server platform is for Z/OS and Db2-server ver is 11.01.0005

[imavo]

You need to qualify the stored-procedure name only when its qualifier (= it's schema) differs from your default schema or your current function path. That is not a python or ibm_db_dbi matter.

The specification for the DBI callproc interface is that the return (other than the result set ) is a copy of the input parameters with the INOUT and OUT parameters possibly modified by the stored procedure.

So try using syntax like this:

spParamValues = (P_NUMBER, P_YEAR, P_CODE, p_indicator, p_sqlcode, p_message) p_number, p_year, p_code, out_p_indicator, out_p_sqlcode, out_p_message = cur.callproc(spName, spParamValues)

where you created variables out_p_indicator, out_p_sqlcode, out_p_message to store the values of the output parameter, with matching data types. You can then test the values of the output variables just by referencing them in comparisons.

If the stored procedure opened a cursor then you also must process (fetch) its result set in the usual way, and handle multiple-result-set cursors if your stored-procedure returns more than one result-set.

[TDKZG]

You need to qualify the stored-procedure name only when its qualifier (= it's schema) differs from your default schema or your current function path. That is not a python or ibm_db_dbi matter.

The specification for the DBI callproc interface is that the return (other than the result set ) is a copy of the input parameters with the INOUT and OUT parameters possibly modified by the stored procedure.

So try using syntax like this:

spParamValues = (P_NUMBER, P_YEAR, P_CODE, p_indicator, p_sqlcode, p_message) p_number, p_year, p_code, out_p_indicator, out_p_sqlcode, out_p_message = cur.callproc(spName, spParamValues)

where you created variables out_p_indicator, out_p_sqlcode, out_p_message to store the values of the output parameter, with matching data types. You can then test the values of the output variables just by referencing them in comparisons.

If the stored procedure opened a cursor then you also must process (fetch) its result set in the usual way, and handle multiple-result-set cursors if your stored-procedure returns more than one result-set.

@imavo Thank you! Now it's working. The problem was in this part:

spParamValues = P_NUMBER, P_YEAR, P_CODE
cursor = conn.cursor()
resultSet = cursor.callproc(spName, spParamValues)

I didn't put all 6 parameters in tuple and also i don't need resultSet variable, I can make it like this:

spParamValues = cursor.callproc(spName, spParamValues)

Thank you very much for your time! Best