2

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)'
mpdmp
  • 21
  • 3
  • At this point we're looking at writing a helper in C#/mono or java since we know that those should be able to talk to the server, whereas the iAccess connector for Linux at the least refuses to work with calls to OUT producing procedures. We have not tested with other procedure types. – mpdmp Jan 05 '16 at 23:22
  • I wrote a java application using jt400.jar that "just works," so long as I specify the libraries it can use, and also set naming=sql. – mpdmp Jan 12 '16 at 22:54
  • For php, it seems that iAccess doesn't play well with odbc for parameterized queries, or that it doesn't get along with the way pdo does things. I plan to contact the mailing lists of both projects with some details and will update this page if I ever get it working natively under php. – mpdmp Jan 12 '16 at 22:55
  • Regardless of many other things, if we need to think about accessing a given stored proc, you need to include the definition of the stored proc interface. Either show that part of the source that created the stored proc or retrieve it from DB2 and show that. Paste the lines into the question. There will probably be no useful progress without it. – user2338816 Feb 17 '16 at 06:47
  • Which is impossible as the software is not our own and 3rd party, contracted, with no source available. I already tried to get db2 to show me the source, but this is a program, not a stored proc. Apologies if my terms are off as I'm more used to other db systems. – mpdmp Feb 18 '16 at 14:45
  • Without a stored proc definition, OUT values cannot be returned. SQL (DB2) has no clue what to do with a value without a definition in the DBMS. The stored proc definition tells DB2 what the program output parameter looks like so that DB2 knows how to handle it. The stored proc definition is essentially just a "wrapper" for a separately compiled program object in this case. The answer from @KevinAdler is appropriate. Any definitions and errors from that should be posted so we can help. – user2338816 Feb 19 '16 at 12:30

1 Answers1

1

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';
Community
  • 1
  • 1
Kevin Adler
  • 641
  • 5
  • 16
  • I know this should work, in theory, but it also doesn't. I suspect it could be because, as I recently learned, this isn't a traditional stored procedure, but some sort of program hook into the ancient managment system we use. Adding comment above. – mpdmp Feb 09 '16 at 14:48
  • If I switch to using a single quote encapsulated string for the call, it returns that the name " is not allowed... I highly suspect this is a character encoding bug, as this little java app I wrote to do this works perfectly. – mpdmp Feb 09 '16 at 14:53
  • Oh, yeah the double quotes are used for delimited identifiers so would resolve to a column name or variable named *STRANGEPARAMNAMEHERE. Using single quotes will give you that as a string. – Kevin Adler Feb 16 '16 at 22:55
  • If you're calling a program and not a stored procedure, you're going to have a tough time getting output parameters to work. I'd suggest creating an [SQL Procedure](http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzcrtpef.htm?lang=en) definition for the program that you can call like: `create or replace procedure qgpl.procnamehere(in c char(10), out d char(10)) language CL parameter style general external name 'QGPL/PROCNAMEHERE';` – Kevin Adler Feb 16 '16 at 23:00
  • Unfortunately, I believe my first attempts tried that already too... I'm starting to think I'm looking at a nonstandard configuration. The DBA has two different methods that point to this program and neither will play nice in php, no matter how many times I attempt to massage the params. – mpdmp Feb 18 '16 at 14:43