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
midrange.com
