EGL Reference Guide for iSeries

Result-set processing

A common way to update a series of rows is as follows:

  1. Declare and open a cursor by running an EGL open statement with the option forUpdate; that option causes the selected rows to be locked for subsequent update or deletion
  2. Fetch a row by running an EGL get next statement
  3. Do the following in a loop:
    1. Change the data in the host variables into which you retrieved data
    2. Update the row by running an EGL replace statement
    3. Fetch another row by running an EGL get next statement
  4. Commit changes by running the EGL function commit.

The statements that open the cursor and that act on the rows of that cursor are related to each other by a result-set identifier, which must be unique across all result-set identifiers, program variables, and program parameters within the program. You specify that identifier in the open statement that opens the cursor, and you reference the same identifier in the get next, delete, and replace statements that affect an individual row, as well as on the close statement that closes the cursor. For additional details, see resultSetID.

The following code shows how to update a series of rows when you are coding the SQL yourself:

  handleHardIOErrors = 1;
 
  try
    open selectEmp forUpdate with
    #sql{  										// no space after "#sql"
      select empname
      from EMPLOYEE
      where empnum >= :myRecord.empnum
      for update of empname
    };
 
  onException
    myErrorHandler(8);    // exits program
  end
 
  try
    get next from selectEmp into :myRecord.empname;
  onException
    if (sysVar.sqlcode != 100)
      myErrorHandler(8);  // exit the program
    end
  end
 
  while (sysVar.sqlcode != 100) 
    myRecord.empname = myRecord.empname + " " + "III";
 
    try
      execute
      #sql{
        update EMPLOYEE
        set empname = :empname
        where current of selectEmp
      };
    onException
      myErrorHandler(10);   // exits program
    end
 
    try
      get next from selectEmp into :myRecord.empname;
    onException
      if (sysVar.sqlcode != 100)
        myErrorHandler(8);  // exits program
      end 
    end
  end  // end while; cursor is closed automatically
       // when the last row in the result set is read
 
  sysLib.commit;

If you wish to avoid some of the complexity in the previous example, consider SQL records. Their use allows you to streamline your code and to use I/O error values that do not vary across database management systems. The next example is equivalent to the previous one but uses an SQL record called emp:

  handleHardIOErrors = 1;
 
  try
    open selectEmp forUpdate for emp;
  onException
    myErrorHandler(8);    // exits program
  end
 
  try
    get next emp;
  onException
    if (sysVar.sqlcode not noRecordFound)
      myErrorHandler(8);  // exit the program
    end 
  end
 
  while (sysVar.sqlcode not noRecordFound) 
    myRecord.empname = myRecord.empname + " " + "III";
 
    try 
      replace emp;
    onException
      myErrorHandler(10);   // exits program
    end
 
    try
      get next emp;
    on exception
      if (sysVar.sqlcode not noRecordFound)
        myErrorHandler(8);  // exits program
      end
    end
  end  // end while; cursor is closed automatically
       // when the last row in the result set is read
 
  sysLib.commit;

Later sections describe SQL records.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]