Calling RPG program from a Stored Procedure

[jerry.falatko@penske.com]

Testing a Stored Procedure that has IN/OUT or OUT parameters via STRSQL can be daunting. For that matter, I have never been able to get this to work. What does work for me is to use IBM's iSeries Navigator's "Run an SQL script". I suspect you will have much greater luck using this approach. For example, I have a stored procedure that has 4 character IN parameters and two OUT parameters (first is character, second is an integer). The OUT parameters necessitate the use of question-marks ( quoted for characters, unquoted for integers/numbers). The following is an example of the SQL script that works (in iSeries Navigator); call LIBRARY.SPNAME ('F_NAME', 'L_NAME', 'STATUS', 'Y', '?', ?); The iSeries Navigator results are as follows:

call LIBRARY.SPNAME ('F_NAME', 'L_NAME', 'STATUS', 'Y', '?', ?);

Output Parameter #5 = SUCCESS00 Output Parameter #6 = 62 Statement ran successfully (1732 ms) Hope this helps!

[Sushantha Karanth]

The external procedure you have is just that, a procedure. Instead, 1. Create a dummy SQL external procedure (UPDSAL in example below). 2. Call your RPGLE program (GETINCR) from withing this SQL procedure. Regards, Sushanth

---

Code

[Guest.Visitor]

I am stuck. I have created an RPGLE program with 11 parms and have created the stored procedure (external), so far, so good. So, now I would like to test the stored procedure. I have tried doing the CALL from inside the STRSQL command processor with no luck. Can I NOT test the CALL to the stored procedure through the iSERIES "query analyzer"? If so, will someone explain to me what I am doing wrong? Here is the "command" I am running from inside "STRSQL": CALL CUSTOM_ALL/POL_INFO ( 'CO00004', '2006012924', '03/05/1918' ) This is the error that is returned: Routine POL_VERIFY in CUSTOM_ALL not found with specified parameters The three above are INOUT parms, I have 8 that are defined as OUT only. ALL are character. I have tried "calling" the proc with the 8 OUT parms defined as dummy parms, same message. I thought, Maybe the procedure not in the qsys2 lib files. I checked and the procedure is registered in the sysroutine file and my parms are listed in the sys parm file. I created the procedure inside the strsql processor. All help and insight is appreciated.

[Guest.Visitor]

Jerry, thank you for your insight. I will try this. Sushantha Karanth, Your example does not fit my situation. But, thank you anyway for the SQL example.