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.

Some of the benefits to using host arrays include:

You can perform multiple CALL, EXECUTE, INSERT or UPDATE operations by executing only one SQL statement, which can significantly improve performance, especially when the application and the database are on different systems.
You can fetch data in batches, which can be useful when creating a scrolling list of information.

As with simple host variables, you must declare host arrays in your program and then reference them in your SQL statements.

Declaring host arrays

Host arrays are declared in much 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.

Referencing host arrays

The following rules apply to coding host arrays into embedded SQL statements:

Just as with simple host variables, you must precede a host array name with a colon (;).
If the number of rows available is more than the number of rows defined in an array, a SELECT statement returns the number of rows defined in the array, and an SQLCODE message is issued to indicate that the additional rows 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.
If you use multiple host arrays in a single SQL statement, their dimensions must be the same.
OpenESQL does not support the mixing of host arrays and simple host variables within a single SQL statement. They must be all simple or all arrays.
For OpenESQL, you must define all host variables within a host array with the same number of occurrences. If one variable has 25 occurrences, all variables in that host array must have 25 occurrences.
If you are using COBSQL with Oracle, you can code a simple host variable in the WHERE clause of a SELECT statement that references a host array. This is the only scenario that allows the mixing of simple host variables and host arrays.
Optionally, use the FOR clause to limit the number of array elements processed to just those that you want. This is especially useful in UPDATE, INSERT and DELETE statements where you may not want to use the entire array. The following rules apply:
- If the value of the FOR clause variable is less than or equal to zero, no rows are processed.
- The number of array elements processed is determined by comparing the dimension of the host array with the FOR clause variable. The lesser value is used.

Examples

The following example shows typical host array declarations and references.

 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)

The following example demonstrates the use of the FOR clause, showing 10 rows (the value of :maxitems) modified by the UPDATE statement:

 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).
 01 maxitems         PIC S9(4) COMP-5 VALUE 10.
 EXEC SQL
     END DECLARE SECTION
 END-EXEC.
   . . .
     EXEC SQL
         CONNECT USERID 'user' IDENTIFIED BY 'pwd' 
                 USING 'db_alias'
     END-EXEC
     EXEC SQL
         FOR :maxitems
          UPDATE   authors
           SET   au_lname = :Auth_Lname
           WHERE au_id  = :Auth_id
     END-EXEC
     display sqlerrd(3)