Filling program arrays with rows

A program array is an ordered set of elements all of the same data type.The elements of the array can be simple types or they can be records. The FOREACH instruction can be used to fill program arrays with SQL rows.

Program Arrays

Arrays can be:
  • static - defined with an explicit size for all dimensions.
  • dynamic - has a variable size, with no theoretical size limit.

All elements of static arrays are initialized even if the array is not used. Therefore, defining huge static arrays may use a lot of memory. The elements of dynamic arrays are allocated automatically by the runtime system, as needed.

To hold a list of records, you typically define a dynamic array by using the LIKE clause to get the same structure than the database table:
 1 SCHEMA custdemo
 2
 3 DEFINE custarr DYNAMIC ARRAY OF RECORD LIKE customer.*
Note:
  • Line 1 declares the use of the custdemo schema, for LIKE clauses.
  • Line 3 defines a program variable as a dynamic array of records with the column names and types of the customer table.
  • A good practice is to define types and reuse the type definition when declaring variables, we will use user-defined types in the next pages. Go to Example: custlist.4gl types and variables for a detailed example.

Populating arrays

The FOREACH statement is equivalent to using the OPEN, FETCH and CLOSE statements to retrieve and process all the rows selected by a query, and is especially useful when loading arrays.

To load the program array in the example, you must retrieve the values from the result set of a query and load them into the elements of the array. You must DECLARE the cursor before the FOREACH statement can retrieve the rows:
 1 DEFINE x INTEGER,
 2        rec t_custrec,
 3        custarr t_custarr
 4 DECLARE ca_curs CURSOR FROM "SELECT * FROM customer"
 5 CALL custarr.clear()
 6 FOREACH ca_curs INTO rec.*
 7    LET x = x + 1
 8    LET custarr[x] = rec
 9 END FOREACH
Note:
  • Line 1 defines an integer variable to hold the array index.
  • Line 2 defines a record to hold a customer record.
  • Line 3 defines a dynamic array to hold a list of customer records.
  • Line 4 declares the SQL cursor for the SELECT statement.
  • Line 6 declares a FOREACH loop with an INTO clause to copy row data into the rec record. When this line is reached, the SQL statement is executed and produces a result set.
  • Lines 7 and 8 are executed for each row in the result of the cursor.
  • Line 7 increments the x counter (INTEGER variables are initialized to zero automatically)
  • Line 8 assigns the record values to a new element in the dynamic array. The array element at position x is automatically created when it is referenced. You could however use the custarr.appendElement() method before the LET, to make the code more readable.
  • Line 9 closes the loop block.