Unable to execute DB2 the stored procedure which are having out parameters of cursor type.
SambaSivaDE opened this issue · 6 comments
I am trying to execute the stored procedure which is having 3 params in which 2 of input params and 1 is output params.
the out param is cursor type.
I am unable to define the cursor type of the out param in prepare statement. when i am trying to bind the param it was giving below error
Error:
Error: Describe Param Failed: [IBM][CLI Driver][DB2/NT64] SQL0440N No authorized routine named "GETEMPLOYEESBYDEPARTMENT2" of type "PROCEDURE" having compatible arguments was found. SQLSTATE=42884 SQLCODE=-440
Python Script:
department = 'IT'
salary_threshold = 50000.0
var1 = 0
params = (department,salary_threshold,var1)
stmt = ibm_db.callproc(conn,"GetEmployeesByDepartment2",params)
var1 is the output parameter of type cursor. which i am not able to define and bind for execute statement.
Kindly provide the solution or work around to achieve.
The error message that you see (SQL0440N) is the expected behaviour because python ibm_db has no ability to directly represent Db2-cursors as either input-parameters or output-parameters to Db2-routines.
Your ibm_db.callproc statement is not binding the parameter as a cursor type (which might be your intention), but instead most likely as a numeric type, and as no such routine exists with that signature then Db2 will return SQL0440N.
Separately, in Db2-LUW, strongly-typed cursors and weakly-typed cursors are only passable between routines when both caller and callee are compiled-SQL routines, and when matching cursor-types are involved (otherwise Db2-LUW throws exceptions).
Thanks for the response.
It seems like we can't use ibm_db/ibm_db_dbi module to execute the stored proc which is having out param of cursor type.
Can you please provide the work around, to achieve this. Since most of the tools don't directly support execution of db2 stored procs. So we are planning to achieve this in python.
kindly share any possible way to execute the stored proc.
This might be an example of an XY problem.
Choose a design that works with the available tools.
python ibm_db.callproc() lets you call stored procedures and pass a wide variety of input/output parameter types (but not native Db2-cursors). It also lets the called routine return one or more result-sets by leaving cursors opened on return from the procedure.
Since a cursor represents a set of rows, you can supply that same set of rows by other means (e.g. result-set, sanitized-query, prepopulated temp/session table etc).
@SambaSivaDE, Can you please share update on this issue? Thanks
@SambaSivaDE, Thank you for confirmation. As it is working for you, we can close the issue?
Thanks