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



Hi,

if you are already on release V5R3 or higher, you can define and use an array data structure:
D Array1          DS                  DIM(999) Qualified
D  A1Code                        3A

When using an array data structure you can get direct access to the elements:
For example:
C/Exec SQL  Fetch next from C1 For 10 rows into :Array1
C/End-Exec
/Free
  If SQLER3 <> *Zeros // At least 1 row returned
     For Index = 1 to SQLER3;
         Fld2 = Array1(Index).A1Code;
         Dsply Fld2;
     EndFor;
  EndIf;
/End-Free

If you are not yet on release V5R3, you have to define and use a multi occurence data structure.
D Array1          DS                  OCCURS(999)
D  A1Code                        3A

To get access to the elements, you first have to read the appropriate data structure by either using the opcode occur or the built-in-function %OCCUR.
For example:
C/Exec SQL  Fetch next from C1 For 10 rows into :Array1
C/End-Exec
/Free
  If SQLER3 <> *Zeros // At least 1 row returned
     For Index = 1 to SQLER3;
         %Occur(Array1) = Index;
         Fld2 = A1Code;
         Dsply Fld2;
     EndFor;
  EndIf;
/End-Free

Just some more tips:
1. Never use Host variables that start with 'SQL', 'SQ', 'RDI' or 'DSN'. These variables are reserved for the database manager. Even if these variables can be used today, they can cause problems with one of the next releases.

2. Replace dynamic SQL through static SQL whenever possible. In your example you do not need to built the SQL-Statement dynamically. The variable where conditions can be replaced by using a Host-Variable.
C/EXEC SQL
C+ Declare ... For
c+     Select max(SVCD26) From Carrier
C+        Where SCVD26 <> :HstVar
C+        Group By SCVD26
C/END-EXEC
(Even though I don't understand what you want to archieve with this statement.)

DB2 UDB for iSeries is optimized for static SQL. That means access plans will be stored in the programm objects, so they have only to be validated and not to be rebuilt each time the SQL-Statement gets called. Access plans are necessary to built or better to open the data path (ODP) to get access to the data.

3. And avoid special signs, such as @, #, $, §. They are not international. I added the §-sign willfully, because we must use it instead of the @-sign. If we get sources from an american machine, we always have to replace @ through §.

Mit freunlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)




----- Original Message ----- From: <fkany@xxxxxxxxxxxxxxxxxx>
To: "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
Sent: Friday, October 20, 2006 22:51
Subject: Re: fill Array using SQL


Hi Birgitta,

I would like to return multiple rows with a single fetch.  I setup an array
data structure, but I get the following error when trying to compile the
new program:
"Position 36 Host structure array A1CODE not defined or not usable."
    D @SQLCMD         S             70A
    D Q               C                   ''''
     *
     * Vendor Service Code Array
    D Array1          DS
    D  A1Code                        3A   DIM(999)
     *
     * Embedded SQL Initializations
    C/EXEC SQL
    C+ Set Option
    C+     Naming    = *Sys,
    C+     Commit    = *None,
    C+     UsrPrf    = *User,
    C+     DynUsrPrf = *User,
    C+     Datfmt    = *Iso,
    C+     CloSqlCsr = *EndMod,
    C+     Commit    = *NONE
    C/END-EXEC
     *
     * Build Vendor Service Code Array
    C                   EVAL      @SQLCMD = 'select max(SVCD26) ' +
    C                                       'from CARRIER '       +
    C                                       'where SVCD26 <> '    +
    C                                       Q + '   ' + Q         +
    C                                       ' group by SVCD26'
    C/EXEC SQL
    C+ PREPARE SQL1 FROM :@SQLCMD
    C/END-EXEC
    C/EXEC SQL
    C+ DECLARE C1 CURSOR FOR SQL1
    C/END-EXEC
    C/EXEC SQL
    C+ OPEN C1
    C/END-EXEC
    C                   IF        SQLCOD >= 0
    C/EXEC SQL
    C+ FETCH C1 FOR 10 ROWS INTO :A1Code
    C/END-EXEC
    C                   ENDIF
    C/EXEC SQL
    C+ CLOSE C1
    C/END-EXEC
     *
    C                   EVAL      *INLR = *ON
    C                   RETURN




            "HauserBirgitta"
            <Hauser@SSS-Softw
            are.de>                                                    To
            Sent by:                  "RPG programming on the AS400 /
            rpg400-l-bounces@         iSeries" <rpg400-l@xxxxxxxxxxxx>
            midrange.com                                               cc

                                                                  Subject
            10/20/2006 03:30          Re: fill Array using SQL
            PM


            Please respond to
             RPG programming
             on the AS400 /
                 iSeries
            <rpg400-l@midrang
                 e.com>






Hi,

you have to fetch the single value per row into a workfield and after move
this workfield into the appropriate array element.
In your example you always fill A1CODE (or the first array element because
it's overlayed by the array).

C/EXEC SQL
C+ FETCH C1 INTO :WrkField
C/END-EXEC
C                   EVAL      Array(i) = WrkField
C                   EVAL      i = i + 1

If you want to return multiple rows with a single fetch, you either have to

define a multi occurrence data structure or an array data structure, but
not
an array.

Mit freunlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)




----- Original Message -----
From: <fkany@xxxxxxxxxxxxxxxxxx>
To: <rpg400-l@xxxxxxxxxxxx>
Sent: Friday, October 20, 2006 21:32
Subject: fill Array using SQL



Happy Friday everbody!

I'd appreciate any suggestions on why my program isn't filling an array
using SQL.

I've created the test program below to see how to populate an Array with
SQL.  At first I set my break point at the end of the program so that I
could go check the values in the array.  I found that the array was
blank.

I reset my break point at the FETCH(C                   Z-ADD     -4
SQLER6) statement and re-ran the program.
The following line of code populates the array element:
C                   EVAL      A1CODE = SQL_00011

The next line of code clears it out:
C                   EVAL      VNDSVCCDE = SQL_00012

Debug View:
======================================
C*EXEC SQL
C* FETCH C1 INTO :Array1:i
C*END-EXEC
C                   Z-ADD     -4            SQLER6
C                   CALL      SQLROUTE
C                   PARM                    SQLCA
C                   PARM                    SQL_00006
C     SQL_00009     IFEQ      '1'
C                   EVAL      A1CODE = SQL_00011
C                   EVAL      VNDSVCCDE = SQL_00012
C                   EVAL      I = SQL_00013
C                   END
C                   EVAL      i = i + 1
C                   IF        i = 999
C                   LEAVE
C                   ENDIF
C                   ENDDO
C                   ENDIF


Source Code:
=================================
    D i               S              5i 0
    D @SQLCMD         S             47A
     *
     * Vendor Service Code Array
    D Array1          DS
    D  A1Code                        3A
    D VndSvcCde                           LIKE(A1Code) OVERLAY(Array1)
DIM(999)
     *
     * Embedded SQL Initializations
    C/EXEC SQL
    C+ Set Option
    C+     Naming    = *Sys,
    C+     Commit    = *None,
    C+     UsrPrf    = *User,
    C+     DynUsrPrf = *User,
    C+     Datfmt    = *Iso,
    C+     CloSqlCsr = *EndMod,
    C+     Commit    = *NONE
    C/END-EXEC
     *
     * Build Vendor Service Code Array
    C                   EVAL      @SQLCMD = 'select max(SVCD26) ' +
    C                                       'from CARRIER '       +
    C                                       'group by SVCD26'
    C/EXEC SQL
    C+ PREPARE SQL1 FROM :@SQLCMD
    C/END-EXEC
    C/EXEC SQL
    C+ DECLARE C1 CURSOR FOR SQL1
    C/END-EXEC
    C/EXEC SQL
    C+ OPEN C1
    C/END-EXEC
    C                   IF        SQLCOD >= 0
    C                   EVAL      i = 1
    C                   DOU       SQLCOD < 0
    C/EXEC SQL
    C+ FETCH C1 INTO :Array1:i
    C/END-EXEC
    C                   EVAL      i = i + 1
    C                   IF        i = 999
    C                   LEAVE
    C                   ENDIF
    C                   ENDDO
    C                   ENDIF
    C/EXEC SQL
    C+ CLOSE C1
    C/END-EXEC
     *
    C                   EVAL      *INLR = *ON
    C                   RETURN

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