How to call IBM External Stored Procedure from PHP

Question

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,

Answer 1

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.