Re: Calling RPGLE subrotine from SQL Proc with retrun value

Thanks for both of your replies. I got the program working by changing to
SQL function and creating a local variable in the RPGLE program. Thanks
again.

Wes

"HauserSSS" <Hauser-YeaMbNrzpuQTVIZRl6KS/w@xxxxxxxxxxxxxxxx> wrote in
message news:<HFEAIBMAHGFKNPHBOMFIGEGICPAA.Hauser@xxxxxxxxxxxxxxx>...
Hi Wes,

you created a stored procedure.
A stored procedure can only have input and output parameters, but no return
value.

The prototype for the procedure, you registered looks as follows:
C  MyProc           PR
C                                     10A

This is quite different from your prototype.

In your RPG procedure you use a return value instead of parameters.
That's why you have to create an user defined function (UDF)
instead of a stored procedure.

Create Function Datawh/Stack
       Returns( Char(10) )
       Language RPGLE
       Deterministic
       No SQL
       Called on NULL Input
       DisAllow Parallel
       External Name 'WESD/CALLSTACK(GETPRG)'
       Parameter Style SQL ;

A User Defined function (UDF) must be called as follows:
 Create Procedure DataWH/Testing ()
   LANGUAGE SQL
   MODIFIES SQL DATA
 Begin
   declare Test char(10);
   set Test = 'Test';
   insert into datawh/Testing values (Stack(Test));
 End

For more information about Stored Procedures and User Defined Functions
look at one of the following redbooks:
1. Modernizing IBM eServer iSeries Application Data Access
   - A Roadmap Cornerstone
   http://www.redbooks.ibm.com/abstracts/sg246393.html?Open

2. Stored Procedures, Triggers and User Defined Functions
   on DB2 Universal Database for iSeries
   http://www.redbooks.ibm.com/abstracts/sg246503.html?Open

Birgitta

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces-Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxxxxxxx
[mailto:rpg400-l-bounces-Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxxxxxxx]Im Auftrag
von Wes
Gesendet: Freitag, 16. Dezember 2005 22:52
An: rpg400-l-Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxxxxxxx
Betreff: Calling RPGLE subrotine from SQL Proc with retrun value


Hi List,

I'm trying to find out what program is changing a closed sale. The file is
updated from by various programs by calling a file server so the audit file
keeps pointing to the file server program. I've found a nice RPGLE program
that uses the QWVRCSTK API to list the call stack (from iSeries News web
site). I've modified the program so it would only return the interactive
program. The problem I'm having is with the SQL procudure. I have created a
SQL exteranal proc:

Create procedure Datawh/Stack (char(10))
    language rpgle parameter style general
    no sql
    external name 'WESD/CALLSTACK(GETPRG)'


and I'm trying to call it from SQl Proc:

 Create Procedure DataWH/Testing () LANGUAGE SQL
   MODIFIES SQL DATA
 Begin
   declare Test char(10);
   set Test = 'Test';
   call stack(Test);
   insert into datawh/Testing values (Test);
 End


but I get an error in the subroutie in the RPGLE when it's trying to return
the value:


 D GetPrg          PR            10