EGL Reference Guide for iSeries

get

The EGL get statement retrieves a single file record or database row and provides an option that lets you replace or delete the stored data later in your code. In addition, this statement allows you to retrieve a set of database rows and place each succeeding row into the next SQL record in a dynamic array.

The get statement is sometimes identified as get by key value and is distinct from get next and get previous.



Syntax diagram for the get statement

record name
Name of an I/O object: an indexed, relative, or SQL record. For SQL processing, the record name is required if the EGL INTO clause (described later) is not specified.
forUpdate
Option that lets you use a later EGL statement to replace or delete the data that was retrieved from the file or database.

If the resource is recoverable (as in the case of a VSAM file or SQL database), the forUpdate option locks the record so that it cannot be changed by other programs until a commit occurs. For details on commit processing, see Logical unit of work.

resultSetID
A result-set identifier for use in an EGL replace, delete, or execute statement, as well as in an EGL close statement. For details, see resultSetID.
singleRow
Option that causes generation of more efficient SQL, as is appropriate when you are sure that the search criterion in the get statement applies to only one row and when you do not intend to update or delete the row. A run-time I/O error results if you specify this option when the search criterion applies to multiple rows. For additional details, see SQL record.
#sql{ sqlStatement }
An explicit SQL SELECT statement, as described in SQL support. Leave no space between #sql and the left brace.
into ... item
An EGL INTO clause, which identifies the EGL host variables that receive values from a relational database. This clause is required when you are processing SQL, in either of these cases:

In a clause like this one (which is outside of an #sql{ } block), do not include a semicolon before the name of a host variable.

preparedStatementID
The identifier of an EGL prepare statement that prepares an SQL SELECT statement at run time. The get statement runs the SQL SELECT statement dynamically. For details, see prepare.
using ... item
A USING clause, which identifies the EGL host variables that are made available to the prepared SQL SELECT statement at run time. In a clause like this one (which is outside of an sql-and-end block), do not include a semicolon before the name of a host variable.
usingKeys ... item
Identifies a list of key items that are used to build the key-value component of the WHERE clause in an implicit SQL statement. The implicit SQL statement is used at run time if you do not specify an explicit SQL statement.

If you do not specify a usingKeys clause, the key-value component of the implicit statement is based on the SQL record part that is either referenced in the get statement or is the basis of the dynamic array referenced in the get statement.

In the case of a dynamic array, the items in the usingKeys clause (or the host variables in the SQL record) must not be in the SQL record that is the basis of the dynamic array.

The usingKeys information is ignored if you specify an explicit SQL statement.

SQL dynamic array
Name of a dynamic array that is composed of SQL records.

The following example shows how to read and replace a file record:

  emp.empnum = 1;         // sets the key in record emp
 
  try
    get emp forUpdate;
  onException
    myErrorHandler(8);  // exits the program
  end
 
  emp.empname = emp.empname + " Smith";
 
  try
    replace emp;
  onException
    myErrorHandler(12);
  end

The next get statement uses the SQL record emp when retrieving a database row, with no subsequent update or deletion possible:

  try
    get emp singleRow into empname with
      #sql{
        select empname
        from Employee
        where empnum = :empnum
      };
  onException
    myErrorHandler(8);
  end

The next example uses the same SQL record to replace an SQL row:

  try
    get emp forUpdate into empname with
      #sql{
        select empname
        from Employee
        where empnum = :empnum
      };
 
  onException
    myErrorHandler(8);  // exits the program
  end
 
  emp.empname = emp.empname + " Smith";
 
  try
    replace emp;
  onException
    myErrorHandler(12);
  end

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

Indexed record

When you issue a get statement against an indexed record, the key value in the record determines what record is retrieved from the file.

If you want to replace or delete an indexed (or relative) record, you must issue a get statement for the record and then issue the file-changing statement (replace or delete), with no intervening I/O operation against the same file. After you issue the get statement, the effect of the next I/O operation on the same file is as follows:

If the file is a VSAM file, the EGL get statement (with the forUpdate option) prevents the record from being changed by other programs. In iSeries COBOL programs, the lock remains until a commit occurs, which may not happen until the end of the run unit, as described in Run unit.

Relative record

When you issue a get statement against a relative record, the key item associated with the record determines what record is retrieved from the file. The key item must be available to any function that uses the record and can be any of these:

If you want to replace or delete an indexed (or relative) record, you must issue a get statement for the record and then issue the file-changing statement (replace or delete), with no intervening I/O operation against the same file. After you issue the get statement, the effect of the next I/O operation on the same file is as follows:

SQL record

The EGL get statement results in an SQL SELECT statement in the generated code. If you specify the singleRow option, the SQL SELECT statement is a stand-alone statement. Alternatively, the SQL SELECT statement is a clause in a cursor, as described in SQL support.

Error conditions

The following conditions are among those that are not valid when you use a get statement to read data from a relational database:

The following error conditions are among those that can occur when you use the forUpdate option:

Also, the following situation causes an error:

You can solve the previous mismatch in any of these ways:

Implicit SQL SELECT statement

When you specify an SQL record as an I/O object for the get statement but do not specify an explict SQL statement, the implicit SQL SELECT has the following characteristics:

When you specify a dynamic array of SQL records as an I/O object for the get statement but do not specify an explict SQL statement, the implicit SQL SELECT is similar to that described for a single SQL record, with these differences:


Related concepts
Logical unit of work
Record types and properties
References to parts
resultSetID
SQL support


Related tasks
Syntax diagram


Related reference
add
close


delete
EGL statements
Exception handling
execute
get next
get previous
I/O error values
open
prepare
replace
SQL item properties
sysVar.terminalID


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