Host Arrays

An array is a collection of data items associated with a single variable name. You can define an array of host variables (called host arrays) and operate on them with a single SQL statement.

You can use host arrays as input variables in INSERT, UPDATE and DELETE statements and as output variables in the INTO clause of SELECT and FETCH statements. This means that you can use arrays with SELECT, FETCH, DELETE, INSERT and UPDATE statements to manipulate large volumes of data.

Host arrays are declared in the same way as simple host variables using BEGIN DECLARE SECTION and END DECLARE SECTION. With host arrays, however, you must use the OCCURS clause to dimension the array. For example:

 EXEC SQL
     BEGIN DECLARE SECTION
 END-EXEC
 01 AUTH-REC-TABLES
    05 Auth-id       OCCURS 25 TIMES PIC X(12).
    05 Auth-Lname    OCCURS 25 TIMES PIC X(40).
 EXEC SQL
    END DECLARE SECTION
 END-EXEC.
  . . .

     EXEC SQL
         CONNECT USERID 'user' IDENTIFIED BY 'pwd' 
                 USING 'db_alias'
     END-EXEC
     EXEC SQL
         SELECT au-id, au-lname
          INTO :Auth-id, :Auth-Lname FROM authors
     END-EXEC
     display sqlerrd(3)

In this example, up to 25 rows (the size of the array) can be returned by the SELECT statement. If the SELECT statement could return more than 25 rows, then 25 rows will be returned and SQLCODE will be set to indicate that more rows are available but could not be returned.

Use a SELECT statement only when you know the maximum number of rows to be selected. When the number of rows to be returned is unknown, use the FETCH statement. Using arrays, it is possible to fetch data in batches. This can be useful when creating a scrolling list of information.

If you use multiple host arrays in a single SQL statement, their dimensions must be the same.