RE: Would like to know SQL/400 forum.

Here's an example:

Stored procedure definition:

CREATE PROCEDURE QGPL/PLODADRUDC(OUT CountriesWthStates CHAR (750
), OUT CountriesWithZips CHAR (750 )) LANGUAGE RPGLE NOT
DETERMINISTIC NO SQL CALLED ON NULL INPUT EXTERNAL NAME
ITEPOBJ62/PLODADRUDC PARAMETER STYLE GENERAL

Parameters defined as IN can only send data to the program I'm calling,
OUT can only receive data from the called program. If you need data to
go both ways, you use INOUT type parameters.

Here's the SQL statement in a program that calls it:

C/EXEC SQL
C+ CALL QGPL/PLODADRUDC (:StateParm, :ZipParm)
C/END-EXEC

The parameter names in the stored procedure don't need to match the
parameter names in the called program.

Matt

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of albartell
Sent: Friday, February 24, 2006 10:19 AM
To: 'RPG programming on the AS400 / iSeries'
Subject: RE: Would like to know SQL/400 forum.

>1 - If it does not use result sets then a simple exec sql call myproc;
should work.

That is what I thought when I initially tried it awhile back, but how do
you
go about passing parms in _and_ out of the store procedure?  If you have
an
example handy and have time to post it that would be great.

Thanks,
Aaron Bartell 

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of rob@xxxxxxxxx
Sent: Friday, February 24, 2006 8:49 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: Would like to know SQL/400 forum.

Yes, you can call a stored procedure from RPGLE.

1 - If it does not use result sets then a simple exec sql call myproc;
should work.

2 - If it returns result sets then you cannot use imbedded SQL.  You'll
have
to use SQL-CLI where the CLI stands for Call Level Interface.

Now, if you want to take an existing RPGLE program and make it a stored
procedure, and even have it return result sets to a different program,
that's no big deal.  In fact, an old S/36 programmer here who has long
moved
over to the dark side of PC development wrote his first RPG program in
years
to do a program that returned a result set.

DPARM1            DS 
DINPUT                           1A 
DPARM2            DS                  OCCURS(1) 
DOUTPUT                         11A 
DROW              S              1  0 
C     *ENTRY        PLIST 
C                   PARM                    PARM1 
C     INPUT         IFEQ      'A' 
C                   MOVE      '1'           OUTPUT 
C                   ELSE 
C                   MOVE      'TEST OUTPUT' OUTPUT 
C                   ENDIF 
C                   MOVE      1             ROW 
C/EXEC SQL SET RESULT SETS ARRAY :PARM2 FOR :ROW ROWS C/END-EXEC 
C                   RETURN 

Then there's a little work like
STRSQL
CREATE PROCEDURE ... to register this program as a stored procedure.

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"albartell" <albartell@xxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
02/24/2006 09:11 AM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
"'RPG programming on the AS400 / iSeries'" <rpg400-l@xxxxxxxxxxxx>
cc

Fax to

Subject
RE: Would like to know SQL/400 forum.






Anybody correct me if I am wrong, but I don't believe you can call a 
stored
procedure from within an RPG program using embedded SQL. I tried this 
about
a year ago without success. Note that I am talking about calling
something
created with the "CREATE PROCEDURE" SQL command.

>I would like to create my procedure with SQL/400 scripts and I would
like
to see the output in a PF.
Could you explain what you mean by you would like to see the output in a

PF?
Are you talking about the output from executing the SQL script? And by
output do you mean the resultant message like "query executed 
successfully"
or do you mean place the result set in the PF?

Aaron Bartell 

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of paramasivam.murugesan@xxxxxxxxx
Sent: Friday, February 24, 2006 4:17 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Would like to know SQL/400 forum.


I would want to create a SQL procedure, so that I can verify the output
of 
a
very complex business logic in one of RPGLE programs.

I would like to create my procedure with SQL/400 scripts and I would
like 
to
see the output in a PF.

Appreciate your help.

Thanks,
Murugesh


The information contained in this electronic message and any attachments

to
this message are intended for the exclusive use of the addressee(s) and 
may
contain proprietary, confidential or privileged information. If you are 
not
the intended recipient, you should not disseminate, distribute or copy 
this
e-mail. Please notify the sender immediately and destroy all copies of 
this
message and any attachments.

WARNING: Computer viruses can be transmitted via email. The recipient 
should
check this email and any attachments for the presence of viruses. The
company accepts no liability for any damage caused by any virus 
transmitted
by this email.

www.wipro.com

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