get next

The EGL get next statement reads the next record from a file or message queue, or the next row from a database.

Syntax diagram for the get next statement

record name: Name of the I/O object: an indexed, MQ, relative, serial, or SQL record.
from resultSetID: For SQL processing only, an ID that ties the get next statement to an open statement run earlier in the same program. For details, see resultSetID.
into: Begins an EGL into clause, which lists the items that receive values from a relational-database table.
item: An item that receives the value of a particular column. Do not precede the item name with a colon (:).

An example of file access is as follows:

  try
    open record1 forUpdate;
    onException
      myErrorHandler(8);
      return;
  end
  try
    get next record1; 
    onException
      myErrorHandler(12);
      return;
  end
  
  while (record1 not endOfFile)
    makeChanges(record1); // process the record

    try
      replace record1; 
    onException 
      myErrorHandler(16); 
      return;
    end
    
    try
      get next record1; 
    onException
      myErrorHandler(12);
      return;
    end
  end // end while
  
  sysLib.commit();

Details on the get next statement depends on the record type. For details on SQL processing, see SQL processing.

Indexed record

When a get next statement operates on an indexed record, the effect is based on the current file position, which is set by either of these operations:

A successful input or output (I/O) operation such as a get statement or another get next statement; or
A set statement of the form set record position.

Rules are as follows:

When the file is not open, the get next statement reads a record with the lowest key value in the file.
Each subsequent get next statement reads a record that has the next highest key value in relation to the current file position. An exception for duplicate keys is described later.
After a get next statement reads the record with the highest key value in the file, the next get next statement results in the I/O error value endOfFile.
The current file position is affected by any of these operations:
- An EGL set statement of the form set record position establishes a file position based on the set value, which is the key value in the indexed record that is referenced by the set statement. The subsequent get next statement against the same indexed record reads the file record that has a key value equal to or greater than the set value. If no such record exists, the result of the get next is endOfFile.
- A successful I/O statement other than a get next statement establishes a new file position, and the subsequent get next statement issued against the same EGL record reads the next file record. After a get previous statement reads a file record, for example, the get next statement either reads the file record with the next-highest key or returns endOfFile.
- If a get previous statement returns endOfFile, the subsequent get next statement retrieves the first record in the file.
- After an unsuccessful get, get next, or get previous statement, the file position is undefined and must be re-established by a set statement of the form set record position or by an I/O operation other than a get next or get previous statement.
When you are using an alternate index and duplicate keys are in the file, the following rules apply:
- Retrieval of a record with a higher-valued key occurs only after get next statements have read all the records that have the same key as the most recently retrieved record. The order in which duplicate-keyed records are retrieved is the order in which VSAM returns the records.
- If a get next follows a successful I/O operation other than a get next, the get next skips over any duplicate-keyed records and retrieves the record with the next higher key.
- The EGL error value duplicate is not set when your program retrieves the last record in a group of records containing the same key.

Consider a file in which the keys are as follows:

  1, 2, 2, 2, 3, 4

Each of the following tables illustrates the effect of running a sequence of EGL statements on the same indexed record.

EGL statement (in order)	Key in the indexed record	Key in the file record retrieved by the statement	EGL error value
get	2	2 (the first of three)	duplicate
get next	any	2 (the second)	duplicate
get next	any	2 (the third)	—
get next	any	3	—

EGL statement (in order)	Key in the indexed record	Key in the file record retrieved by the statement	EGL error value
set (of the form set record position)	2	no retrieval	duplicate
get next	any	2 (the first of three)	—
get next	any	2 (the second)	duplicate
get next	any	2 (the third)	—
get next	any	3	—

Message queue

When a get next operates on a MQ record, the first record in the queue is read into the MQ record. This placement occurs because the get next invokes one or more MQSeries calls:

MQCONN connects the generated code to the default queue manager and is invoked when no connection is active
MQOPEN establishes a connection to the queue and is invoked when a connection is active but the queue is not open
MQGET removes the record from the queue and is always invoked unless an error occurred in an earlier MQSeries call

Relative record

When a get next statement operates on a relative record, the effect is based on the current file position, which is set by a successful input or output (I/O) operation such as a get statement or another get next statement. Rules are as follows:

When the file is not open, the get next statement reads the first record in the file.
Each subsequent get next reads a record that has the next highest key value in relation to the current file position.
A get next does not return noRecordFound if the next record is deleted. Instead, the get next skips deleted records and retrieves the next record in the file.
After a get next statement reads the record with the highest key value in the file, the next get next statement results in the EGL error value endOfFile.
The current file position is affected by any of these operations:
- A successful I/O statement other than a get next establishes a new file position, and the subsequent get next against the same EGL record reads the next file record.
- After an unsuccessful get, get next, or get previous statement, the file position is undefined and must be re-established by a set statement of the form set record position or by an I/O operation other than a get next statement.
After a get next statement reads the last record in the file, the next get next statement results in the EGL error values endOfFile and noRecordFound.

Serial record

When a get next statement operates on a serial record, the effect is based on the current file position, which is set by another get next statement. Rules are as follows:

When the file is not open, the get next statement reads the first record in the file.
Each subsequent get next statement reads the next record.
After a get next statement reads the last record, the subsequent get next statement results in the EGL error value endOfFile.
If the generated code adds a serial record and then issues the equivalent of a get next statement on the same file, EGL closes and reopens the file before executing the get next statement. A get next statement that follows an add statement therefore reads the first record in the file. This behavior also occurs when the get next and add statements are in different programs, and one program calls another.

It is recommended that you avoid having the same file open in more than one program at the same time.

SQL processing

When a get next statement operates on an SQL record, your code reads the next row from those selected by an open statement. If you issue a get next statement to retrieve a row that was selected by an open statement that has the forUpdate option, you can do any of these:

Change the row with an EGL replace statement
Remove the row with an EGL delete statement
Change or remove the row with an EGL execute statement

An SQL FETCH statement represents the EGL get next statement in the generated code. The format of the generated SQL statement cannot be changed, except to set the INTO clause.

If you issue a get next statement that attempts to access a row that is beyond the last selected row, the following statements apply:

No data is copied from the result set
EGL sets the SQL record (if any) to noRecordFound
If the related open statement included the scroll option, the cursor remains open with the cursor position unchanged.
If you have not set the scroll option, the cursor is closed.

Finally, when you specify SQL COMMIT or sysLib.commit, your code retains position in the cursor that was declared in the open statement, but only if you use the hold option in the open statement.

Related tasks
Syntax diagram