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,

Is this the entire PHP script? Could you post the SQL DDL that defines the stored procedure? Also, the parameter list in the RPG code? Just edit your question and add that. It's hard to tell where the problem might be without seeing all the pieces. — Buck Calabro, Feb 18 '15 at 20:47
Thanks for your quick replying. The "External" Stored Procedure has been functioning by other Java program to execute that. So there is no doubt within this row RPG code. Yes, I can call this row RPG program from PHP with same way as here, $result=db2_exec($connect, "Call LIB.RPGProgram"); But as long as it's converted to be an External Stored Procedure by Linoma Software's Surveyor/400, I can't call it. thanks, — beerneko, Feb 18 '15 at 21:01
Did you check for a parameter missmatch between the RPG and the External procedure definition? It seems the External stored procedure is looking for two parameters while the program only has one. — jmarkmurphy, Apr 04 '15 at 15:44

score 1 · Accepted Answer · answered Feb 18 '15 at 21:28

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.

score 1 · Answer 2 · edited Mar 08 '15 at 13:03

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

How to call IBM External Stored Procedure from PHP

2 Answers2