Darrell,
Below is a test program I just wrote that calls a stored procedure.
H DftActGrp(*No) ActGrp(*CALLER)
//
//**********************************************************************
* Comment
D Parameters Ds
D Parm1 3A
Inz('042') Parm = In
D Parm2 3A
Inz('035') Parm = In
D Parm3 9A
Inz('000000000') Parm = In
D Parm4 6A
Inz('HR03') Parm = In
D Parm5 3A
Inz('001') Parm = In
D Parm6 2A
Inz('HR') Parm = In
D Parm7 2A
Inz('02') Parm = In
D Parm8 2A
Inz(*Blanks) Parm = InOut
D Parm9 1A
Inz(*Blanks) Parm = InOut
D Parm10 2A
Inz(*Blanks) Parm = InOut
D Parm11 2A
Inz(*Blanks) Parm = InOut
//
//**********************************************************************
**********************
// Calculation specifications
//**********************************************************************
**********************
/Free
Exec Sql
Call SP_AttStat(:Parm1, :Parm2, :Parm3, :Parm4,
:Parm5, :Parm6, :Parm7, :Parm8,
:Parm9, :Parm10, :Parm11);
Return;
/End-Free
SP_ATTStat is a stored procedure that calls an RPGLE program and returns
values in Parm8, Parm9, Parm10 and Parm11. It ran and returned the
correct values I was looking for.
Make sure the values you are passing and their lengths correspond with
the values and lengths expected.
Regards,
Pat Landrum
Senior Programmer/Analyst
Hanover County Public Schools
200 Berkley Street
Ashland, VA 23005
Email: plandrum@xxxxxxx
Phone: 804-365-4658 Fax: 804-365-4628
Never trust a computer you can't throw out a window - Steve Wozniak
Notice: This message or any accompanying documents may contain
confidential or privileged information of Hanover County Public Schools.
If you are not the intended recipient, disclosure, copying or
distribution is strictly prohibited by state and federal law. If you
received this message in error, please notify the sender as soon as
possible.
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of DLee@xxxxxxxx
Sent: Wednesday, March 25, 2009 11:18 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: RPG400-L Digest, Vol 8, Issue 228
Pat;
Thanks for pointing that out.
I made tha correction in my test, and reran it.
CALL CRPRDLIBTS.STR004SP('EL', '0131', '31478', '000', '100',
'20090319',
'000000000', ' ');
and got the below error.
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.
Not sure what all that means. Maybe a ptf problem with sql script.
Thanks for your help.
Darrell Lee
Information Technology
Extension 17127
rpg400-l-request@xxxxxxxxxxxx
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
03/25/2009 09:54 AM
Please respond to
rpg400-l@xxxxxxxxxxxx
To
rpg400-l@xxxxxxxxxxxx
cc
Subject
RPG400-L Digest, Vol 8, Issue 228
Send RPG400-L mailing list submissions to
rpg400-l@xxxxxxxxxxxx
To subscribe or unsubscribe via the World Wide Web, visit
http://lists.midrange.com/mailman/listinfo/rpg400-l
or, via email, send a message with subject or body 'help' to
rpg400-l-request@xxxxxxxxxxxx
You can reach the person managing the list at
rpg400-l-owner@xxxxxxxxxxxx
When replying, please edit your Subject line so it is more specific
than "Re: Contents of RPG400-L digest..."
*** NOTE: When replying to this digest message, PLEASE remove all text
unrelated to your reply and change the subject line so it is meaningful.
Today's Topics:
1. RE: Stored Procedures (Pat Landrum)
2. Re: OPM called by program running in a named activation group
(Buck)
3. Re: OPM called by program running in a named activation group
(Adam Glauser)
4. RE: OPM called by program running in a named activation group
(J.Beckeringh@xxxxxxxxxxxxxxxxxxxxxxxxxx)
5. Re: OPM called by program running in a named activation group
(Mark S. Waterbury)
6. Re:Stored Procedure (DLee@xxxxxxxx)
----------------------------------------------------------------------
message: 1
date: Wed, 25 Mar 2009 10:08:41 -0400
from: "Pat Landrum" <PLandrum@xxxxxxxxxxxxxxxxxxxxxxx>
subject: RE: Stored Procedures
Darrell,
In "CALL CRPRDLIBTS.STR004SP('EL', '0131', '31478', '000', '100',
'20090319', '0000000.00', ' ')" the 7th parameter is defined in your
procedure as char(9). The value you have entered is 10 characters in
length.
Regards,
Pat Landrum
Senior Programmer/Analyst
Hanover County Public Schools
200 Berkley Street
Ashland, VA 23005
Email: plandrum@xxxxxxx
Phone: 804-365-4658 Fax: 804-365-4628
Never trust a computer you can't throw out a window - Steve Wozniak
Notice: This message or any accompanying documents may contain
confidential or privileged information of Hanover County Public Schools.
If you are not the intended recipient, disclosure, copying or
distribution is strictly prohibited by state and federal law. If you
received this message in error, please notify the sender as soon as
possible.
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of DLee@xxxxxxxx
Sent: Wednesday, March 25, 2009 9:17 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: 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
midrange.com
