How to handle error in ibm_db python package while calling stored procedure?

Question

I'm trying call stored procedure using following code

conn = ibm_db.connect("database","username","password") sql = "CALL DB2INST1.KPI_VALIDATE()" stmt = ibm_db.exec_immediate(conn, sql)

But this procedure does not return any rows & It will only return code. Now I need to handle error whether procedure run successfully or not. Could anyone help me how to handle this?

Thanks

Answer 1

For test purposes, I've created a table:

db2 "create table so(c1 int not null primary key)"

and my procedure will simply insert a row into this table - this will allow me to easily force an error with a duplicate key:

db2 "create or replace procedure so_proc(in insert_val int)
    language sql
    insert into so values(insert_val)"

db2 "call so_proc(1)"

  Return Status = 0
db2 "call so_proc(1)"
SQL0803N  One or more values in the INSERT statement, UPDATE statement, or 
foreign key update caused by a DELETE statement are not valid because the 
primary key, unique constraint or unique index identified by "1" constrains 
table "DB2V115.SO" from having duplicate values for the index key.  
SQLSTATE=23505

now with Python:

conn = ibm_db.connect("DATABASE=SAMPLE;HOSTNAME=localhost;PORT=61115;UID=db2v115;PWD=xxxxx;","","")  
stmt = ibm_db.exec_immediate(conn, "CALL SO_PROC(2)")
stmt = ibm_db.exec_immediate(conn, "CALL SO_PROC(2)") 

Exception                                 Traceback (most recent call last)
<ipython-input-8-c1f4b252e70a> in <module>
----> 1 stmt = ibm_db.exec_immediate(conn, "CALL SO_PROC(2)")

Exception: [IBM][CLI Driver][DB2/LINUXX8664] SQL0803N  One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "DB2V115.SO" from having duplicate values for the index key.  SQLSTATE=23505 SQLCODE=-803

so if a procedure hits an exception then you'll get it, you just need to handle exception Try/Except block:

try:
    stmt = ibm_db.exec_immediate(conn, "CALL SO_PROC(2)")
except Exception:
    print("Procedure failed with sqlstate {}".format(ibm_db.stmt_error()))
    print("Error {}".format(ibm_db.stmt_errormsg()))

Procedure failed with sqlstate 23505
Error [IBM][CLI Driver][DB2/LINUXX8664] SQL0803N  One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "DB2V115.SO" from having duplicate values for the index key.  SQLSTATE=23505 SQLCODE=-803

Or you are actually interested with CALL return code/status? E.g.:

create or replace procedure so_proc_v2(in insert_val int)
    language sql
    if not exists (select 1 from so where c1 = insert_val)
    then 
        insert into so values(insert_val);
        return 0;
    else 
        return -1;
    end if@

test:

db2 "call so_proc_v2(10)"

  Return Status = 0

db2 "call so_proc_v2(10)"

  Return Status = -1

then this is a bit tricky. With CLI trace enabled (I have ibm_db installed in my local path so it fetched CLI package there too):

export LD_LIBRARY_PATH=$HOME/.local/lib/python3.7/site-packages/clidriver/lib/
$HOME/.local/lib/python3.7/site-packages/clidriver/bin/db2trc on -cli -f /tmp/cli/trc
<run_code>
$HOME/.local/lib/python3.7/site-packages/clidriver/bin/db2trc off
$HOME/.local/lib/python3.7/site-packages/clidriver/bin/db2trc fmt -cli /tmp/cli.trc /tmp/cli.fmt

trace does show the returns status:

SQLExecute( hStmt=1:8 )
    ---> Time elapsed - -7.762688E+006 seconds
( Row=1, iPar=1, fCType=SQL_C_LONG, rgbValue=10 )
( return=-1 )
( COMMIT REQUESTED=1 )
( COMMIT REPLY RECEIVED=1 )

but I don't see anywhere in python-ibmdb API a way to fetch it... (e.g. ibm_dbcallproc doesn't have such option). Which means, that unless I'm missing something, you would have to raise an issue on Github to extent the API