Re: Stored Procedures

Pat;

Thanks for the reply

<You can create procedures either on green screen, via STRSQL, through
<iSeries Navigator or the RUNSQLSTM command using a text member in a
<source file. I usually use Navigator but sometimes I use Surveyor/400.

<To call a stored procedure from RPG:
<   C/Exec SQL
<   C+  Call MyProcedure(:Parm)
 <  C/End-Exec

<Looking at your example I am sure why you want to use a stored
<procedure?>

Actually the reason I want to do this with RPG is only so I can get the
stored procedure tested for a network project coming up, and I just want
to get  myself up to speed, and have the  stored procedure ready before
the network pc people get involve.

For practice, I did a sample stored procedure out of the IBM manual, using
rpg to call a stored procedure, which worked fine, so I moved on to the
below project I really wanted to do.

The program I use to call the stored procedure is STR003
The stored procedure name is STR004SP, and it calls rpg program STR004
If I call the stored procedure thru STR003, It doesn't call STR004, and
fails somewhere.
If I modify STR003 to not call the stored procedure, but to call STR004
directly, it works fine, and returns the refund amount.

I only want the rpg program to pass the parameters to the stored
procedure, so it will call the program to do some calculations on the
iSeries, and return two values, one of them being a calculated refund.

here is the call in the rpg program and the associated prototype:

    D GetPgm          PR                  EXTPGM('STR004SP')
    D  COCD                          2
    D  AGNT                         10
    D  POLM                         10
    D  POLS                          3
    D  COVC                          3
    D  CANDT                         8
    D  REF                           9
    D  RTNCD                         2

    C                   CALLP     GetPgm(PXCOCD:PXAGNT:PXPOLM:PXPOLS
    C                                     :PXCOVC:PXCANDT:PXREF:PXRTNC

I also tried calling the stored procedure using iseries navigator sql
script.
Currently getting the below error:

Connected to relational database Swbcdev on Swbcdev as Crprdqua -
647431/Quser/Qzdasoinit

CALL CRPRDLIBTS.STR004SP('EL', '0131', '31478', '000', '100',

'20090319', '0000000.00', '  ')

SQL State: 58004
Vendor Code: -901
Message: [SQL0901] SQL system error. Cause . . . . . :   An SQL system
error has occurred.  The current SQL statement cannot be completed
successfully.  The error will not prevent other SQL statements from being
processed. Previous messages may indicate that there is a problem with the
SQL statement and SQL did not correctly diagnose the error. The previous
message identifier was MCH2601. Internal error type 7018 has occurred. If
precompiling, processing will not continue beyond this statement. Recovery
 . . . :   See the previous messages to determine if there is a problem
with the SQL statement. To view the messages, use the DSPJOBLOG command if
running interactively, or the WRKJOB command to view the output of a
precompile.  An application program receiving this return code may attempt
further SQL statements.  Correct any errors and try the request again.

A system error (that does not necessarily preclude the successful
execution of subsequent SQL statements) occurred. SQLSTATE 58004, when
combined with SQLCODE -4301, indicates this meaning for the failure: Java
interpreter startup or communication failed.


what this means, I have no idea.


 C/EXEC SQL
 C+      DROP PROCEDURE CRPRDLIBTS/STR004SP
 C/END-EXEC
 C/EXEC SQL
 C+  SET PATH PRCUSTTS, PRCUSTOM, SWFILES, CRPRDLIBTS, CRPRDLIB
 C/END-EXEC
 C/EXEC SQL
 C+  CREATE PROCEDURE CRPRDLIBTS/STR004SP
 C+   (COCD       IN    CHAR(2),
 C+    AGNT       IN    CHAR(10),
 C+    POLM       IN    CHAR(10),
 C+    POLS       IN    CHAR(3),
 C+    COVC       IN    CHAR(3),
 C+    CANDTE     IN    CHAR(8),
 C+    REF        INOUT CHAR(9),
 C+    RTN        INOUT CHAR(02))
 C+   (SPECIFIC CRPRDLIBTS/STR004SP NOT DETERMINISTIC NO SQL
C+    RESULT SETS 2
C+    EXTERNAL NAME CRPRDLIBTS/STR004   LANGUAGE RPGLE  SIMPLE CALL)
C/END-EXEC
C*EXEC SQL
C*    COMMENT ON SPECIFIC PROCEDURE CRPRDLIBTS.STR004SP
C*     IS 'Stored Procedure for Refund Calculation'
C*END-EXEC
C                   EVAL      *INLR     =    *ON

I'm hoping you see something I don't
 Appreciate you help.



Darrell Lee
Information Technology
Extension 17127
--
This is the RPG programming on the IBM i / System i (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.