时间戳

首页 > 解决方案 > 调用存储过程时如何处理ibm_db python包中的错误？

python-2.7 - 调用存储过程时如何处理ibm_db python包中的错误？

问题描述

我正在尝试使用以下代码调用存储过程

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

但是这个过程不返回任何行，它只会返回代码。现在我需要处理程序是否成功运行的错误。谁能帮我处理这个问题？

谢谢

标签： python-2.7stored-proceduresdb2db2-woc

解决方案

出于测试目的，我创建了一个表：

db2 "create table so(c1 int not null primary 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

现在使用 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

所以如果一个过程遇到异常，那么你会得到它，你只需要处理异常Try/Except块：

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

或者您实际上对CALL返回码/状态感兴趣？例如：

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@

测试：

db2 "call so_proc_v2(10)"

  Return Status = 0

db2 "call so_proc_v2(10)"

  Return Status = -1

那么这有点棘手。启用 CLI 跟踪（我已经ibm_db安装在我的本地路径中，所以它也在那里获取了 CLI 包）：

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

跟踪确实显示了返回状态：

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 )

但我在python-ibmdb API的任何地方都看不到获取它的方法......（例如ibm_dbcallproc，没有这样的选项）。这意味着，除非我遗漏了什么，否则您必须在 Github 上提出问题以扩展 API

推荐阅读