I finally understand the differences between SQL Stored Procedure and External Stored Procedure which is based on RPG or any other AS400 objects. Now I tried to add the call statement to execute the External Stored Procedure which has only one out parameter and is generated by RPG program as below.
PHP:
$server="server";
$user="user";
$pass="pass";
$connect = db2_connect($server,$user,$pass);
$RTVTYP = "D";
$RTRNFLD = "";
$strSql = 'CALL LIB.TEST_SP(?,?)';
$stmt = db2_prepare($connect, $strSql);
db2_bind_param($stmt, 1, "RTVTYP", DB2_PARAM_IN, DB2_CHAR);
db2_bind_param($stmt, 2, "RTRNFLD", DB2_PARAM_OUT, DB2_CHAR);
$result = db2_execute($stmt);
print $result;
print $RTRNFLD;
db2_close($connect);
RPG Programmer gave me the code, and she said she used Surveyor/400 to convert into External Stored Procedure.
RPG:
d rpgprogram pr
d rtvtyp_ like(rtvtyp)
d rpgprogram pi
d rtvtyp 1
/free
exsr initial;
exsr process;
exec sql set result sets array :web_data for 1 rows;
return;
//***************************************************
begsr process;
select;
when rtvtyp = 'D';
rtrnfld = 'Dog';
when rtvtyp = 'C';
rtrnfld = 'Cat';
other;
rtrnfld = 'Invalid';
endsl;
endsr;
begsr initial;
w1size = %size(m2errds:*all);
p2err = %alloc(w1size);
clear m2errds;
endsr;
/end-free
DDL by Surveyor/400:
LIB.TEST_SP
General
Procedure: TEST_SP
Maximum number of result sets:1
Data access: MOdifies SQL data
Specific name: TEST_SP
Parameters
RTVTYP Character 1 IN
RTRNFLD Character 10 OUT
Parameter style:
Simple, no null values allowed
External Program
Program: rpgprogram
Schema: LIB
Language: RPGLE
Connection is successes, $result back as "1", but nothing $RTRNFLD returns.
Thank you for your help,
Without code to look at, it's very difficult to tell what may be the problem. Here is working code from my system.
The PHP:
$my_var = "1";
$strSql = 'CALL BUCK.PHPTEST(?)';
$stmt = db2_prepare ( $conn, $strSql );
db2_bind_param($stmt, 1, "my_var", DB2_PARAM_INOUT, DB2_CHAR);
$result = db2_execute ( $stmt );
The DDL:
CREATE PROCEDURE buck/phptest(
inout parm_inout varchar (5) ccsid 37)
LANGUAGE RPGLE
NOT DETERMINISTIC
CALLED ON NULL INPUT
EXTERNAL NAME buck/phptest
PARAMETER STYLE GENERAL
The RPGLE:
d parm_inout s 5a varying
c *entry plist
c parm parm_inout
c/free
parm_inout = 'Wow';
*inlr = *on;
return;
/end-free
When this runs in my test web page, $result is '1' and $my_var is 'Wow'. I am not calling the RPG program directly, I am calling the SQL External stored procedure which invokes the RPG program.
I think I found why the code doesn't work.
If you are using older than ZEND Server 7, or 6.3, you need to have the fix to make this work.(Please take a peek at the Zend Forums: http://forums.zend.com/viewtopic.php?f=77&t=115183&p=223528&hilit=external+stored+procedure#p223693) Currently we are still using Zend Server 6.3.0 Free Edition, and it looks like that the fix is not included. So obviously some of the calling DEMOs from XML Toolkit don't work neither.
http://forums.zend.com/viewtopic.php?f=77&t=115183&p=223528&hilit=external+stored+procedure#p223693