Re: Calling a stored procedure that exists on the As/400 from PHP

The error I'm getting with the code I posted below (with a odbc_prepare)
is SQL0204- XXX111 in GEMLIB type *N not found...
Which is strange, when I would do the odbc_exec, it did run the program
(the program outputs a spool file...)

I also tried the example you provided (use cast), but I was receiving
SQL0104 - Token CAST was not valid. Valid tokens: <INTEGER> <CHARSTRING> DLVALUE DATE TIME TIMESTAMP.,

At this point I am wondering if I should change how My RPGLE program is written (to be a program with a procedure interface), or
change how my SQL stored procedure is created???



-Thanks so much for your patience

Gerald


CRPence wrote:

Since the original problematic invocation seemed to have sent only one parameter [no comma] and that did not fail with -440 [apparently it was -469], perhaps the CREATE PROCEDURE was not correct. That also in part describes my alluding to that procedure name possibly being overloaded. The string that I infer was passed originally was [note the embedded apostrophe]:
"30019590 ' "

Also as I understand, the odbc_execute must be used instead of odbc_exec, because the latter is an "execute immediate" which is an implicit prepare with execute. Of course must get past the prepare error first. Which specific SQL error is unstated. Was it -440 or something else?

To effect proper type resolution [to avoid type mismatch and thus sql0440] you could try CAST of the parameter markers. For example prepare the following instead:

'CALL GEMLIB.XXX111(cast(? as char(15)), cast(? as char(30)))'

Regards, Chuck

Gqcy wrote:

Sorry for my short reply.

I have tried, and now understand how the problem with me
declaring the call with literals...
I have changed my php to try to do this:
<?
$Item = '30019590 ' ;
$Desc = ' ' ;
$con = as400connect();
$stmt = odbc_prepare($con, 'CALL GEMLIB.XXX111(? , ?)');
$SResultq = odbc_exec($stmt, array($Item,$Desc)) or die ("db Query Error");

But alas...now I get a SQL error, on procedure not found
in the prepare statement...

Gqcy wrote:

This is where I am getting confused. I think this is the reason
some examples do the "question mark thingie" to position and
load the parameters, with a odbc_prepare...
But I haven't found out how to get that to work either I see
that in some examples of using odbc_prepare it tells me that if
you want to use INOUT, or OUT, you may need to use the "native extensions" for that database...


CRPence wrote:

Is that "CALL GEMLIB.XXX111('$Item''$Desc')" or "CALL GEMLIB.XXX111('$Item' '$Desc')"? Either way is problematic. The apostrophes suggest the values passed are literals.? A literal is not addressable for OUT; nor would an omitted parameter be addressable, but that would require the procedure was overloaded.

Regards, Chuck

Gqcy wrote:

CRPence wrote:

i.e. time would be better spent actually examining the what
& why of any difficulties in using the ODBC with stored procedures.

here is where I get stuck:

how my procedure is created:
CREATE PROCEDURE XXX111
(IN PARM1 CHAR(15),
INOUT PARM2 CHAR(30))
EXTERNAL NAME GEMLIB.XXX111
LANGUAGE RPGLE ;

My PHP:

<? $Item = '30019590 ' ;
$Desc = ' ' ;
$con = as400connect();
$sqlqte = "CALL GEMLIB.XXX111('". $Item . "''" . $Desc. "') ";
SResultq = odbc_exec($con, $sqlqte) or die ("db Query Error");
echo 'SResultq = ' . $SResultq ;
echo 'Description: ' . $Desc ;
?>

My RPGLE program:
FXXXL01 IF E k disk
FQSYSPRT O F 132 printer
**
C *ENTRY PLIST
C Parm inprod 15
C Parm outdesc 30
**
C inprod CHAIN(e) XXXL01
C if %found(XXXL01)
C Eval outdesc = XDESC
C EndIF
C Except detail
C Eval *inLR = *on
**
oQSYSPRT E Detail 1
o INPROD +0001
o OutDesc +0001


RESULTS:
My program gets called. INPROD = '30019590'.
HOWEVER I get a PARM error, OUTDESC is NOT ADDRESSABLE.