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.
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.
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.
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.
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.
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.
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:
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.
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:
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:
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:
keyColumn01 >= :keyItem01 & keyColumn02 >= :keyItem02 & . . . keyColumnN >= :keyItemN
Related concepts
Logical unit of work
Record types and properties
References to parts
resultSetID
SQL support
Related tasks
Syntax diagram
delete
EGL statements
Exception handling
execute
get next
get previous
I/O error values
open
prepare
replace
SQL item properties
sysVar.terminalID
(C) Copyright IBM Corporation 1992, 2005. All Rights Reserved.