Using 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.
No subfields using the POS or OVERLAY keywords can be included in the data structure.
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.

DCL-DS DEPARTMENT OCCURS(10);
  DEPTNO CHAR(3);
  DEPTNM CHAR(29);
  MGRNO CHAR(6);
  ADMRD CHAR(3);
END-DS;

DCL-DS IND_ARRAY OCCURS(10);
  INDS INT(5) DIM(4);
END-DS;
...
EXEC SQL DECLARE C1 CURSOR FOR
  SELECT * FROM CORPDATA.DEPARTMENT;
...
EXEC SQL FETCH C1 FOR 10 ROWS
  INTO :DEPARTMENT:IND_ARRAY;

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

DCL-DS fststruct DIM(10) QUALIFIED;
  sub1 INT(5);
  sub2 INT(10);
  sub3 INT(20);
  sub4 INT(10);
END-DS;

EXEC SQL FETCH C1 FOR 10 ROWS INTO :fststruct;