forEach considerations for SQL

The EGL forEach statement reads through an SQL result set, performing EGL statements on each row. You must have previously created the result set by using an EGL open or get statement. If the result set is not available, the statement cannot be processed and a hard I/O error occurs. The loop continues until one of these events occurs:

In most cases, the EGL runtime issues an implicit SQL CLOSE statement after the last iteration of the loop. Because that implicit statement modifies the SQL system variables, you must save the values of SQL-related system variables in the body of the forEach statement.

The EGL runtime does not perform the SQL CLOSE statement if the forEach statement ends because of an error other than noRecordFound.

Syntax

Syntax diagram for forEach statement
label
A label, followed by a colon, which a continue or exit statement can reference. For more information, see Conditional and loop statements.
sqlRecord
The SQL record variable that you referenced in the open or get statement that created the result set. You must specify either an SQL record or a result set ID.
resultSetID
An identifier of your choice that you provided in the open or get statement that created the result set you currently want to read.
target
The SQL INTO clause that EGL generates uses the information you specify here. These are the EGL host variables that receive values from the SQL FETCH statement that the forEach statement generates for each row of the result set. If you specify a record name, EGL uses all the fields in the record. Do not include a colon before the name of a host variable (see Host variables).

Creating an INTO clause in this context overrides any INTO clause from the related open statement.

statement
A statement in the EGL language to be performed for each row of the result set.

Example

The following example shows the process of changing a database record in a customer file:
vgVar.handleHardIOErrors  = 1;

try
  open myResults for myCustomer
    with #sql{
      SELECT customer_number, customer_name
      FROM Customer
      WHERE customer_number >= :myCustomer.customerNumber
      FOR UPDATE OF customer_name
    }
    into myCustomer.customerNumber, myCustomer.customerName;
onException(sqlEx SQLException)
  myErrorHandler(6);    // exits program
end

try 
  forEach (from myResults)
    // make last name first
    reverseName(myCustomer.customerName);

    try
      execute
        #sql{
          UPDATE Customer
          SET customer_name = :myCustomer.customerName
          WHERE CURRENT OF myResults
        };
    onException(sqlEx SQLException)
      myErrorHandler(10); // exits program
    end
  end  
  
onException(sqlEx SQLException)
  myErrorHandler(8);  // exits program
end

sysLib.commit();

Error conditions

A SQLCODE of 100 indicates that no matching data was found. This error terminates the forEach block normally; EGL does not run an onException block related to the forEach in this circumstance. You do not need to begin an onException block with a test like the one in the following example:
onException(sqlEx SQLException)
  if (sqlcode != 100)  // unnecessary test

Feedback