CODEWITHSUNDEEP
phpsqlpdodb2ibm-midrange
mpdmp
mpdmp

Reputation: 21

php pdo & iSeriesAccess ODBC - Calling Stored Procedures with OUT Parameters Does Not Work

We use a very old system at work to manage the most important parts of the business...I have no choice in this matter.

As a result, I have managed to get php's pdo driver working with the iSeriesAccess database driver, and so far as selecting and inserting goes, it works well enough with a few caveats not worth mentioning.

Using this code, I am unable to get a stored procedure that outputs to an OUT parameter to execute due to an error I have yet to solve.

    $proc = $this->link->prepare("CALL QGPL.PROCNAMEHERE(\"*STRANGEPARAMNAMEHERE\",@output,' ')");
    $proc->execute();
    $proc->closeCursor();

    $output = $this->link->query("select @output")->fetch(PDO::FETCH_ASSOC);
    var_dump($output);

The resulting error:

Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 0 [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0206 - Column or global variable @OUTPUT not found.

I know that another individual in our company who writes in java successfully calls this procedure in his code, however I realize he probably has different drivers to use.

This problem is driving me mad, to the point I have tried manually calling it in DBeaver using a declared variable...which results in even stranger errors.

Using as suggested below, I get

Results in 

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 0 [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0206 - Column or global variable *RTNORD not found. (SQLPrepare[0] at /build/php5-pO28mL/php5-5.5.9+dfsg/ext/pdo_odbc/odbc_driver.c:206)'

Upvotes: 2

Views: 1099

Answers (1)

Korinne Adler
Korinne Adler

Reputation: 676

It looks like you're attempting to use MySQL session variables in your code, which won't work on DB2.

I would look at PDO's bindParam, especially examples 2 and 3. Something like this should work:

$bufsize = 100; // Adjust according to how big the output can be
$proc = $this->link->prepare("CALL QGPL.PROCNAMEHERE('*STRANGEPARAMNAMEHERE', ?,' ')");
$proc->bindParam(1, $output, PDO::PARAM_STR|PDO::PARAM_OUTPUT, $bufsize);
$proc->execute();
$proc->closeCursor();

var_dump($output);

You may need to create an SQL Procedure definition if you're just calling a program, otherwise you can't prepare the statement (since SQL won't know what the parameters are defined as). See this section of the IBM i SQL Reference, eg.

CREATE OR REPLACE PROCEDURE QGPL.PROCNAMEHERE(IN C CHAR(20), OUT D CHAR(100))
LANGUAGE CL
PARAMETER STYLE GENERAL
EXTERNAL NAME 'QGPL/PROCNAMEHERE';

Upvotes: 1

Related Questions