forEach

The EGL keyword forEach marks the start of a set of statements that run in a loop. The first iteration occurs only if a specified result set is available. (If the result set is not available, the statement fails with a hard error.) The loop reads each row in the result set, continuing until one of these events occurs:

• All rows are retrieved;

• An exit statement runs; or

• A hard or soft error occurs.

sqlRecord

Name of an SQL record that is used in a previously run open statement. You must specify either an SQL record or a result set ID, and it is recommended that you specify the result set ID.

from resultSetID

The result-set identifier that is used in a previously run open statement. For details, see resultSetID.

into ... item

An INTO clause, which identifies the EGL host variables that receive values from the cursor or stored procedure. In a clause like this one (which is outside of a #sql{ } block), do not include a semicolon before the name of a host variable.

A specification of an INTO clause in this context overrides any INTO clause identified in the related open statement.

statement

A statement in the EGL language

In most cases, the EGL run time issues an implicit close statement occurs after the last iteration of the forEach statement. That implicit statement modifies the SQL system variables, for which reason you may want to save the values of SQL-related system variables in the body of the forEach statement.

The EGL run time does not issue an implicit close statement if the forEach statement ends because of an error other than noRecordFound.

An example is as follows, as further described in SQL examples:

  handleHardIOErrors = 1;

  try
    open selectEmp
      with #sql{
        select empnum, empname
        from employee
        where empnum >= :empnum
        for update of empname
      }
      into empnum, empname;
  onException
    myErrorHandler(6);    // exits program
  end

  try 
    forEach (from selectEmp)
      empname = empname + " " + "III";
    
      try
        execute
          #sql{
            update employee
            set empname = :empname
          where current of selectEmp
          };
      onException
        myErrorHandler(10); // exits program
      end
    end  // end forEach; cursor is closed automatically
         // when the last row in the result set is read

  onException
    // the exception block related to forEach is not run if the condition 
    // is "sqlcode = 100", so avoid the test "if (sqlcode != 100)"
    myErrorHandler(8);  // exits program
  end

  sysLib.commit();

Related concepts
resultSetID
SQL support

Related tasks
Syntax diagram

Related reference
EGL statements
exit
open
SQL examples