× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. February 2006

AW: Would like to know SQL/400 forum.

Hi Mike,

what do you want to do?
Returning a result set from SQLRPGLE or receiving a result set?

Returning a  result set from SQLRPGLE is easy:
Before Release V5R2M0 you have to use the SQL Statement SET RESULT SET to
return a result set.
With Release V5R2M0 and later, you'll return a result set simply by opnening
a cursor.
You only have to register these programs as stored procedures.
After registering these programs can be called like any other stored
procedure.

If you want to receive a result set, you have to use CLI-APIs.
An alternative for stored procedures returning a result set are User Defined
Table Functions (UDTF)
UDTFs return a table (with a single row or with several rows).
UDTFs can be used in a SELECT statement:

Example: Creating an UDTF
CREATE FUNCTION MySchema/GetAddress
       (ParCity Character(15))
       RETURNS TABLE
               (FirstName CHARACTER( 15 ) ,
                Name CHARACTER( 15 ) ,
                ZipCode DECIMAL (5, 0),
                City CHARACTER( 15 ))
       LANGUAGE SQL
       NOT DETERMINISTIC
       CONTAINS SQL DATA
       CALLED ON NULL INPUT
       NO EXTERNAL ACTION
       DISALLOW PARALLEL
       RETURN Select   *
                 from  MyFile
                 Where MyCity = ParCity;

Example: Using an UDTF
C/Exec SQL
C+ Declare .....
C+ Select  FirstName, Upper(LastName)
C+   from  Table(GetAddress(:HostCity)) as MyUDTF
C+   Where Upper(LastName) = :HostName
C+   order by Upper(LastName), FirstName;
C/End-Exec

Birgitta

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown)

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Mike Eovino
Gesendet: Freitag, 24. Februar 2006 22:50
An: RPG programming on the AS400 / iSeries
Betreff: Re: Would like to know SQL/400 forum.


Too bad it's not easier to return a result set.  It would be nice to
be able to create SQLRPGLE programs that could return result sets to
both web front ends (Net.Data, .NET, Java Servlets) and other RPG
programs.

How much of a PITA is it to use the CLI?

Mike E.

On 2/24/06, albartell <albartell@xxxxxxxxx> wrote:
> Thanks for the responses!
>
> I guess I had dismissed them because I was implementing one that returned
a
> result set. Thanks for setting me straight.
>
> Aaron Bartell
>
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
> On Behalf Of Matt.Haas@xxxxxxxxxxx
> Sent: Friday, February 24, 2006 9:48 AM
> To: rpg400-l@xxxxxxxxxxxx
> Subject: 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.
>
> --
> 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.
>
>
> --
> 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.
>
> --
> 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.
>
>
> --
> 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.
>
> --
> 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.
>
>

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

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.