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 other statements that begin with the word get.

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:

An SQL record is not specified; or
Both an SQL record and an explicit SQL SELECT statement are specified, but a column in the SQL SELECT clause is not associated with a record item. (The association is in the SQL record part, as noted in SQL item properties.)

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, 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 next I/O operation is a replace statement on the same EGL record, the record is changed in the file
If the next I/O operation is a delete statement on the same EGL record, the record in the file is marked for deletion
If the next I/O operation is a get statement on a record in the same file and includes the forUpdate option, a subsequent replace or delete statement is valid on the newly read file record
If the next I/O operation is a get statement on the same EGL record (with no forUpdate opton) or is a close statement on the same file, the file record is released without change

If the file is a VSAM file, the EGL get statement (with the forUpdate option) prevents the record from being changed by other programs.

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:

An item in the same record
An item in a record that is global to the program or is local to the function that is running the get statement
A data item that is global to the program or is local to the function that is running the get statement

If the next I/O operation is a replace statement on the same EGL record, the record is changed in the file
If the next I/O operation is a delete statement on the same EGL record, the record in the file is marked for deletion
If the next I/O operation is a get on the same file (with the forUpdate option), a subsequent replace or delete is valid on the newly read file record
If the next I/O operation is a get on the same EGL record (with no forUpdate option) or is a close on the same file, the file record is released without change

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:

You specify an SQL statement of a type other than SELECT
You specify an SQL INTO clause directly in an SQL SELECT statement
Aside from an SQL INTO clause, you specify some but not all of the clauses of an SQL SELECT statement
You specify (or accept) an SQL SELECT statement that is associated with a column that either does not exist or is incompatible with the related host variable

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

You specify (or accept) an SQL statement that shows an intent to update multiple tables; or
You use an SQL record as an I/O object, and all the record items are read only.

Also, the following situation causes an error:

You customize an EGL get statement with the forUpdate option, but fail to indicate that a particular SQL table column is available for update; and
The replace statement that is related to that get statement tries to revise the column.

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

When you customize the EGL get statement, include the column name in the SQL SELECT statement, FOR UPDATE OF clause; or
When you customize the EGL replace statement, eliminate reference to the column in the SQL UPDATE statement, SET clause; or
Accept the defaults for both the get and replace statements.

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:

The record-specific property called defaultSelectCondition determines what table row is selected, so long as the value in each SQL table key column is equal to the value in the corresponding key item of the SQL record. If you specify neither a record key nor a default selection condition, all table rows are selected. If multiple table rows are selected for any reason, the first retrieved row is placed in the record.
As a result of the association of record items and SQL table columns in the record definition, a given item receives the content of the related SQL table column.
If you specify the forUpdate option, the SQL SELECT FOR UPDATE statement does not include record items that are read only.
The SQL SELECT statement for a particular record is similar to the following statement, except that the FOR UPDATE OF clause is present only if the get statement includes the forUpdate option :
```
  SELECT column01, 
         column02, ... 
         columnNN
  FROM   tableName 
  WHERE  keyColumn01 = :keyItem01
  FOR UPDATE OF
         column01, 
         column02, ... 
         columnNN
```
The SQL INTO clause on the standalone SQL SELECT or on the cursor-related FETCH statement is similar to this clause:
```
  INTO   :recordItem01,
         :recordItem02, ...
         :recordItemNN
```
EGL derives the SQL INTO clause if the SQL record is accompanied by an explicit SQL SELECT statement when you have not specified an INTO clause. The items in the derived INTO clause are those that are associated with the columns listed in the SELECT clause of the SQL statement. (The item-and-column association is in the SQL record part, as noted in SQL item properties.) An EGL INTO clause is required if a column is not associated with an item.

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:

The key-value component of the query is a set of relationships that is based on a greater-than-or-equal-to condition:

  keyColumn01 >= :keyItem01 &
  keyColumn02 >= :keyItem02 &
         .
         .
         .
  keyColumnN  >= :keyItemN

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.

Related tasks
Syntax diagram