RE: Stored Procedures

Darrell,

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?


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: Friday, March 20, 2009 12:10 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: Stored Procedures

Pat

Thanks for your examples:

In going thru them, and attempting to mimic some of your code.
I'm new at stored procedures, so attempting to keep it simple.

I can't get my program to compile when I use the SET PATH
"QSYS","QSYS2";

I create the proc in an sqlrpgle program like below.

C/EXEC SQL
C+ SET PATH "QSYS","QSYS2";
C+ CREATE PROCEDURE CRPRDLIBTS/STR004SP
C+ (COCD INOUT CHAR(2),
C+ AGNT INOUT CHAR(10),
C+ POLM INOUT CHAR(10),
C+ POLS INOUT DEC(3,0),
C+ COVC INOUT DEC(3,0),
C+ CANDTE INOUT DEC(8,0),
C+ REF INOUT DEC(9,2),
C+ RTN INOUT CHAR(02))
C+ (SPECIFIC CRPRDLIBTS/STR004SP NOT DETERMINISTIC NO SQL
C+ EXTERNAL NAME CRPRDLIBTS/STR004 LANGUAGE RPGLE SIMPLE CALL)
C/END-EXEC
I do put it above the create procedure, but it doesn't like the ; then
when I remove it, I still get an error about the end statement.
Also, how do I set my naming convention to sql or iseries.
The stored procedures manuals seems to talk all around it but not how to

set it.

What I want to be able to do is call a stored procedure from RPGLE,
In the rpg program I call the stored procedure like below:

CALLP GetPgm(PSCOCD:PSAGNT:PSPOLM:PSPOLS
:PSCOVC:$CANDT:PRMREF:RNCODE)

Would this be correct?

Any help appreciated.

Darrell Lee
Information Technology
Extension 17127