[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 625
  • Last Modified:

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, $paramArray) {

$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:133)
*/

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.


0
DipehKhakhkhar
Asked:
DipehKhakhkhar
1 Solution
 
sachinwadhwaCommented:
Have you tried calling stored procedure from command line?


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.


0
 
DipehKhakhkharAuthor Commented:
Hi Sachin,

Thanks for replying.

Well i am able to call the same procedure in the following way

function executeProcedure($procName, $param1, $param2) {

$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!
0
 
DipehKhakhkharAuthor Commented:
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!
0
 
tliottaCommented:
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
0
 
GranModCommented:
PAQed with points refunded (50)

GranMod
Community Support Moderator
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now