AW: Calling RPGLE subrotine from SQL Proc with retrun value

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@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Wes
Gesendet: Freitag, 16. Dezember 2005 22:52
An: rpg400-l@xxxxxxxxxxxx
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
.
.
 P GetPrg          B                   Export
 D                 PI            10
.
.
C                   return    PgmName

The error is:

Message ID . . . . . . :   MCH3601       Severity . . . . . . . :   40
Message type . . . . . :   Escape
Date sent  . . . . . . :   05/12/16      Time sent  . . . . . . :   14:29:31

Message . . . . :   Pointer not set for location referenced.
Cause . . . . . :   A pointer was used, either directly or as a basing
  pointer, that has not been set to an address.

Could you please point me in the right direction how I could retrunt he
value please?

Thanks,

Wes
PS. I'm new the RPGLE and trying to get my head around it...






--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.