Page 1 of 1
db2, unified odbc, and stored procedures
Posted: Thu Nov 21, 2002 4:49 pm
by Kent Kelly
We have many chunks of good business logic on our AS400 (iSeries) and would like to use them as stored procedures using PHP and Unified ODBC.
I've written test file, program, stored procedure, script. Almost everything works (connection is made, sql statement is prepared and executed, the stored procedure runs) except we don't get the return values.
The stored procedure writes out the input parameters it receives and the output parameters it returns. That's how I know my PHP script is successfully calling it.
(I've tested the stored procedure with VB and it works completely. )
Here is the relevant code:
$UserID = "JRUDIE";
$PgmApp = "PGINVENTORY";
$Results = "";
$AuthLevl = "";
$values = array( $UserID, $PgmApp, $Results, $AuthLevl);
/* Setup the SQL call to USERAUTH stored procedure */
$query = odbc_prepare( $db, "CALL GETAUTH (?, ?, ?, ?)") or die( "Bad Query");
/* Execute the call */
$results = odbc_execute( $query,&$values ) or die( "Bad Execution");
/* Display the results */
$MyResults = $values;
print "The call to UserAuth on MOM returned:<br>\n";
print( "$MyResults[0] $MyResults[1] $MyResults[2] $MyResults[3] <br>");
We get JRUDIE and PGINVENTORY and nothing else out of $MyResults.
So far, my search across the forums and web sites has turned up nothing of help. Would appreciate a correction or pointers to better articles, tutorials, examples, etc.
Thanks.
DB2
Posted: Tue Dec 31, 2002 9:12 pm
by fractalvibes
Kent - I use DB2 UDB everyday on both OS/390 and NT/Win2000 with ASP/ADO, but not with AS/400 or PHP(yet!). Can you run the SP ok via the Stored Procedure Builder contained in the DB2 client software? I am a little confused by the syntax you used for calling the SP. No differentiating between parameter types (in,out,inout)? You seem to be displaying the input parameters ok (of course!). One thought is that DB2 seems to prefer
non-null parameters, at least for in and inout params.
Also - isn't $myresults a recordset? Confusing, as I am used to dealing with Sp parameters and recordset as separate colections/objects...
Interested to hear if and how you resolved this!
Phil J.
This is a dead issue, it seems.
Posted: Fri Jan 03, 2003 5:06 pm
by Kent Kelly
Phil, thanks for the questions. Unfortunately, I don't know that I can answer them as I am fairly new to PHP. I followed the format (as best I could figure out) for calling a stored procedure using the Unified ODBC api's (which handles DB2 among others).
The DB2 for iSeries (AS400) is a slightly different beast from the rest of the DB2 family. That might account for some of the differences you noticed. Part of the stored procedure's business is to write a log entry of what it received as parameters and what it is returning as parameters. Reading the log tells me that PHP called the correct sp sent it good input parameters and the sp got the correct data to send back. That return data just never shows up in PHP.
The reason I called this a dead issue is that I did get to e-mail with an IBM'er who wrote an article on using PHP on the iSeries. He's traced the ODBC calls into the guts of things and found that only the input parameters are used; the output parameters are completely ignored.
Don't know if this is in the Unified ODBC api's or IBM's ODBC Driver for Linux implementation.
He had no proven work arounds. He did hear about two possibilities: 1. from PHP call a Perl program to do the stored procedure work; 2. instead of returning parameters, return a result set (which I haven't figured out how to do yet; don't you love hammering around on new stuff?).
We are playing with sockets (another area we know little about) to handle a program call on the iSeries. Haven't gotten further than returning one record (from the same stored procedure) and that all works. Not quite sure how to handle multiple records but that will come. Our need is in the near future but not immediately. So, . . . . .
Hope this helps. If you want to continue the thread, perhaps off line? Moderator, what's the protocol for this?
DB2 Stored Procs
Posted: Fri Jan 03, 2003 11:35 pm
by fractalvibes
Hi Kent,
we can continue this conversion offline if need be -
pjfjack@swbell.net is the address you'd want to use. In my PHP for-fun explorations I have just used the MySQL database, which, while lacking some things, seems very, very fast and easy to use. I think that if you will go to
http://www.idug.org and look at the link to the IBM DB2 Developers resource link and search there,
some articles have been written about using DB2 with PHP. That may be the very article you are referencing though.........
A DB2 store proc can recieve input paramters and return output parameters and modify inout parameters. A result set I would think should be handled in the typical PHP fashion for handling an array of records brought back.
Are you expecting just output parameters or a result set? Sounds like you just want some output parameters, so a result set might be overkill.
I use ASP/ADO for such things, so - a new learning experience!
One question - do you have the client DB2 software installed on your machine - DB2 control panel, stored procedure builder, etc. so that you can test the SP interactively? Does it work???
Let's get this puppy working!!!
Phil
off-line discussion of this topic
Posted: Thu Jan 09, 2003 2:09 pm
by Kent Kelly
Hey, Phil.
I've tried twice now to send you e-mail but it has bounced back both times. Don't know what's wrong there.
The news on my end is, after more hunting, the non-return of values from a stored procedure seems to be a known "bogus" bug. The API's are not written to handle return variables; they will not be re-written anytime soon; so, don't call this a bug.
I know the DB2 portion works: interactively, when called from VB, even when called from PHP. The Unified ODBC API's just leave me short.
One other point, the apparent result set ($results) isn't needed or used. I was just trying things. The same goes for the assignment of the $values array to $MyResults. That wasn't needed; I was just trying things. If the API returned variables, I could have printed $values[2] and $values[3] and gotten what I was looking for.
So it goes. Thanks for the responses and the interest.
DB2/PHP
Posted: Thu Jan 09, 2003 8:13 pm
by fractalvibes
Hi Kent,
Looking back at my post - I mis-typed my email address - it should be
pjfjacks@swbell.net.
Also - If you will email me at
PJackson@txfb-ins.com (work) I can send
more precise info about DB2 stored procs. Interesting about not being able to return values - so you'll have to return a resultset even if you are just looking for 1 returned value. Suspecting you are calling COBOL stored procs on AS400, but perhaps not? Anyway - take a look around
idug.org - esp. the link to the IBM developers resource center - IBM seems to awoken from a long slumber and realized how Monolitic they had become. They realized that people might be trying to use IBM products from a variety of platforms and environments, and are finally realizing the benefits to talking about such cross-platform interoperability. You may find other options - i.e. drivers and APIs and such that offer better performance and functionality.
Interested in hearing how this goes, and also from anyone else using DB2.
We are poised to go to version 8 on Win2000, looks like it will be even better then.
Phil