Using host structure arrays in RPG/400 applications that use SQL

A host structure array is defined as an occurrence data structure. An occurrence data structure can be used on the SQL FETCH statement when fetching multiple rows.

In these examples, the following are true:

All items in BARRAY must be valid host variables.
All items in BARRAY must be contiguous. The first FROM position must be 1 and there cannot be overlaps in the TO and FROM positions.
For all statements other than the multiple-row FETCH and blocked INSERT, if an occurrence data structure is used, the current occurrence is used. For the multiple-row FETCH and blocked INSERT, the occurrence is set to 1.

*...1....+....2....+....3....+....4....+....5....+....6....+....7. ..*
IBARRAY      DS                         10
I                                       01 20 C1VAR
I                                    B  21 220C2VAR

The following example uses a host structure array called DEPT and a multiple-row FETCH statement to retrieve 10 rows from the DEPARTMENT table.

    *...1....+....2....+....3....+....4....+....5....+....6....+....7...*
    E                             INDS        4  4 0
    IDEPT        DS                         10
    I                                       01  03 DEPTNO
    I                                       04  32 DEPTNM
    I                                       33  38 MGRNO
    I                                       39  41 ADMRD
    IINDARR      DS                         10
    I                                    B   1   80INDS
…
    C/EXEC SQL
    C+ DECLARE C1 CURSOR FOR
    C+    SELECT *
    C+       FROM  CORPDATA.DEPARTMENT
    C/END-EXEC
    C/EXEC SQL
    C+ OPEN C1
    C/END-EXEC
    C/EXEC SQL
    C+   FETCH C1 FOR 10 ROWS INTO :DEPT:INDARR
    C/END-EXEC