DipehKhakhkhar
asked on
Abstraction for executing db2 procedure from PHP PDO
Hi,
I am trying to write an abstraction for executing db2 procedures names which will accept n number of input parameters. From my application program i am calling these abstract method with different procedure name and different parameters. I am using PHP PDO to access db2 from php.
function executeProcedure($procName
$stmt = $db->prepare("CALL $procname()");
// call the stored procedure
$procRetVal = $stmt->execute($paramArray
}
The above statement throws the following error
/*
Failed: SQLSTATE[42724]: <<Unknown error>>: -444 [IBM][CLI Driver][DB2/NT] SQL0444N Routine "*w_system" (specific name "SQL060522191204960") is implemented with code in library or path "\insert_new_system", function "insert_new_system" which cannot be accessed. Reason code: "4". SQLSTATE=42724 (SQLExecute[-444] at ext\pdo_odbc\odbc_stmt.c:1
*/
If this is an improper place to ask this question, i apologize for it and will remove this question as soon as somebody informs me. Otherwise any help to write this abstraction will be appreciated.
Thanks in advance.
I am trying to write an abstraction for executing db2 procedures names which will accept n number of input parameters. From my application program i am calling these abstract method with different procedure name and different parameters. I am using PHP PDO to access db2 from php.
function executeProcedure($procName
$stmt = $db->prepare("CALL $procname()");
// call the stored procedure
$procRetVal = $stmt->execute($paramArray
}
The above statement throws the following error
/*
Failed: SQLSTATE[42724]: <<Unknown error>>: -444 [IBM][CLI Driver][DB2/NT] SQL0444N Routine "*w_system" (specific name "SQL060522191204960") is implemented with code in library or path "\insert_new_system", function "insert_new_system" which cannot be accessed. Reason code: "4". SQLSTATE=42724 (SQLExecute[-444] at ext\pdo_odbc\odbc_stmt.c:1
*/
If this is an improper place to ask this question, i apologize for it and will remove this question as soon as somebody informs me. Otherwise any help to write this abstraction will be appreciated.
Thanks in advance.
ASKER
Hi Sachin,
Thanks for replying.
Well i am able to call the same procedure in the following way
function executeProcedure($procName
$stmt = $db->prepare("CALL $procname($param1, $param2)");
// call the stored procedure
$procRetVal = $stmt->execute();
}
By listing the procedure parameters i am able to call that procedure and it works fine. But since i am abstracting procedure call , i don't know how many parameters will be there in that procedure. I am not sure whether eval function in php will help me to generate code dynamically and execute it.
I hope i am able to explain you the problem.
Thanks!
Thanks for replying.
Well i am able to call the same procedure in the following way
function executeProcedure($procName
$stmt = $db->prepare("CALL $procname($param1, $param2)");
// call the stored procedure
$procRetVal = $stmt->execute();
}
By listing the procedure parameters i am able to call that procedure and it works fine. But since i am abstracting procedure call , i don't know how many parameters will be there in that procedure. I am not sure whether eval function in php will help me to generate code dynamically and execute it.
I hope i am able to explain you the problem.
Thanks!
ASKER
Hi,
I have found the solution, since i was not passing any parameter in the prepare method it was not understanding why i am sending array in the execute method.
First of all you need to create parameter string dynamically and using it in the prepare statement.
for($i = 0; $i < count($paramArray); $i++ ) {
$placeHolder = "aram".$i;
$paramString .= " ".$placeHolder.",";
}
$paramString = substr($paramString, 0, strlen($paramString) -1);
$stmt = $db->prepare("CALL $procname($paramString)");
$procRetVal = $stmt->execute($paramArray
This works fine.
Thanks!
I have found the solution, since i was not passing any parameter in the prepare method it was not understanding why i am sending array in the execute method.
First of all you need to create parameter string dynamically and using it in the prepare statement.
for($i = 0; $i < count($paramArray); $i++ ) {
$placeHolder = "aram".$i;
$paramString .= " ".$placeHolder.",";
}
$paramString = substr($paramString, 0, strlen($paramString) -1);
$stmt = $db->prepare("CALL $procname($paramString)");
$procRetVal = $stmt->execute($paramArray
This works fine.
Thanks!
Just adding a comment since I don't know enough about your db2 implementation...
DB2 stored procs on OS/400 have signatures that are calculated over the number and types of the parameters. You can have multiple stored procs of the same name but with different (possibly overloaded) parms and DB2 will locate the particular one that matches the parms that you supply with the CALL statement.
If you wanted to generate a dynamic CALL, you would perhaps retrieve the definition of the stored proc from the SYSPROCS view in the DB2 system catalog. You could then use the parm attributes from there to construct the appropriate CALL.
If you issue a CALL with parms that don't match any signatures for the name of the stored proc, you will get a "proc not found" kind of error.
From the error that you show, I'd suspect something similar.
Tom
DB2 stored procs on OS/400 have signatures that are calculated over the number and types of the parameters. You can have multiple stored procs of the same name but with different (possibly overloaded) parms and DB2 will locate the particular one that matches the parms that you supply with the CALL statement.
If you wanted to generate a dynamic CALL, you would perhaps retrieve the definition of the stored proc from the SYSPROCS view in the DB2 system catalog. You could then use the parm attributes from there to construct the appropriate CALL.
If you issue a CALL with parms that don't match any signatures for the name of the stored proc, you will get a "proc not found" kind of error.
From the error that you show, I'd suspect something similar.
Tom
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here is the error description:
The file in "<library-or-path>" could not be found. The routine definition or the location of the routine may need to be
corrected, or the routine may need to be re-linked. In a partitioned database, the file must be accessible as "<library-or-path>" on all partitions of the database.
In addition to the above, this reason code can result if the routine requires a shared library or DLL, and the shared library cannot be located (using the concatenation of directories specified in the LIBPATH environment variable in UNIX-based systems, the PATH environment variable in INTEL systems). There can be multiple levels of this indirection involved, leading to this reason code. For example, routine body X can be found, and it needs shared library Y which can also be found. But Y needs Z, and Z cannot be located, and this will result in SQL0444N reason code 4.