Use host structure arrays in ILE RPG applications that use SQL

A host structure array is defined as an occurrence data structure or a data structure with the keyword DIM coded. Both types of data structures can be used on the SQL FETCH or INSERT statement when processing multiple rows.

The following list of items must be considered when using a data structure with multiple row blocking support.

All subfields must be valid host variables.
All subfields must be contiguous. The first FROM position must be 1 and there cannot be overlaps in the TO and FROM positions.
If the date and time format and separator of date and time subfields within the host structure are not the same as the DATFMT, DATSEP, TIMFMT, and TIMSEP parameters on the CRTSQLRPGI command (or in the SET OPTION statement), then the host structure array is not usable.

For all statements, other than the blocked FETCH and blocked INSERT, if an occurrence data structure is used, the current occurrence is used. For the blocked FETCH and blocked INSERT, the occurrence is set to 1.

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

DDEPARTMENT               DS                  OCCURS(10)
D DEPTNO                 01     03A
D DEPTNM                 04     32A
D MGRNO                  33     38A
D ADMRD                  39     41A
 
DIND_ARRAY        DS                  OCCURS(10)
D INDS                           4B 0 DIM(4)
…
C/EXEC SQL
C+ DECLARE C1 CURSOR FOR
C+    SELECT *
C+       FROM  CORPDATA.DEPARTMENT
C/END-EXEC
…
 
C/EXEC SQL
C+   FETCH C1 FOR 10 ROWS
C+     INTO :DEPARTMENT:IND_ARRAY
C/END-EXEC

Blocked FETCH and blocked INSERT are the only SQL statements that allow a data structure with the DIM keyword. A host variable reference with a subscript like MyStructure(index).Mysubfield is not supported by SQL.

Example

Dfststruct        DS                      DIM(10)  	QUALIFIED         
D sub1                           4B 0                
D sub2                           9B 0
D sub3                          20I 0                
D sub4                           9B 0   

C/EXEC SQL                                    
C+   FETCH C1 FOR 10 ROWS INTO :fststruct       
C/END-EXEC