Re: SQL stored procedures:

[afvaiv <afvaiv@xxxxxxxxxx>]

Matt, not sure if this is what you are loking after, but just in case:

Old book : Client Access for Windows 95 ODBC User's Guide V3
                 SC41-3535-00 , Book QBKADE00 in V4R1 (!!!)
includes some examples in its 2.5.2 chapter.
I'm including the following one, which is an example that fills in a "result
set" from an array, as mentioned
in the comments on top of the program

Just my 2 cents. Hope this helps, Antonio

2.5.2.3 Host RPG source

The RPG program called by the procedure should declare an array to hold the
data.   It can then open files and read the data in to the array and do
whatever other processing needs to be done.  and finally return the data in the
array using the SQL SET RESULT SETS function.

      *-------------------------------------------------------------*
      *                                                             *
      * RPG PROGRAM:  RPGARY                                        *
      *                                                             *
      * PURPOSE: THIS RPG PROGRAM IS CALLED BY AN ODBC STORED       *
      *          PROCEDURE.  IT READS DATA INTO AN ARRAY AND        *
      *          RETURNS THE RESULTS BY USING THE SET RESULT        *
      *          SETS SQL FUNCTION                                  *
      *                                                             *
      *-------------------------------------------------------------*
      * FILE DESCRIPTION SPECIFICATIONS                             *
      *-------------------------------------------------------------*
     FPERF    IF  E                    DISK
      *-------------------------------------------------------------*
      * INPUT SPECIFICATIONS                                        *
      *-------------------------------------------------------------*
     IPERFE     E DSPERF
     IARR         DS                         20
     I                                       01 105 VAR1
     ILCLVAR      DS
     I                                    B  01  040L#
      *-------------------------------------------------------------*
     C*      **  START OF PROGRAM  **                               *


      *-------------------------------------------------------------*
     C                     OPEN PERF
     C                     Z-ADD0         L#
     C           LOOP      TAG
     C                     READ PERF                   9999
     C                     ADD  1         L#
     C           L#        OCUR ARR
     C                     MOVE PERFE     ARR
     C           L#        COMP 20                   99  99
     C  N99                GOTO LOOP
      *-------------------------------------------------------------*
      *    *** EXECUTE SQL STATEMENT ***
     C/EXEC SQL SET RESULT SETS ARRAY :ARR FOR :L# ROWS
     C/END-EXEC
      *-------------------------------------------------------------*
     C                     CLOSEPERF
     C                     RETRN
     ================================================================


Matt Patee escribió:

> How do you define arrays within an SQL stored procedure? What is the syntax
> for populating and accessing data via subscripting within an array? Is
> there a good website that shows examples?
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Matthew Patee
> Midrange Systems Manager
> ABM Industries
> (415) 351-4309
> fax:(415) 351-4331
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.

--
-------------------------
Antonio Fernandez-Vicenti
afvaiv@wanadoo.es