An SQL record is a variable that is based on an SQL record part. This type of record allows you to interact with a relational database as though you were accessing a file. If the variable EMP is based on an SQL record part that references the database table EMPLOYEE, for example, you can use EMP in an EGL add statement:
add EMP;
In this case, EGL inserts the data from EMP into EMPLOYEE. The SQL record also includes state information so that after the EGL statement runs, you can test the SQL record to perform tasks conditionally, in accordance with the I/O error value that resulted from database access:
handleHardIOErrors = 1; try add EMP; onException if (EMP is unique) // if a table row // had the same key myErrorHandler(8); end end
An SQL record like EMP allows you to interact with a relational database as follows:
You declare an SQL record part and associate each of the record items with a column in a relational table or view. You can let EGL make this association automatically by way of the EGL editor's retrieve feature, as described later in Database access at declaration time.
The structure in each SQL record part must be flat (without hierarchy), and none of the record items can be an array.
After you declare an SQL record part, you declare an SQL record that is based on that part. The SQL record must be declared as a program variable (global to the program), not as a program parameter or function variable.
You can define a set of EGL statements that each use the SQL record as the I/O object in the statement. For each statement, EGL provides an implicit SQL statement, which is not in the source but is implied by the combination of SQL record and EGL statement. In the case of an EGL add statement, for example, an implicit SQL INSERT statement places the value of a given record item into the associated table column. If your SQL record includes a record item for which no table column was assigned, EGL forms the implicit SQL statement on the assumption that the name of the record item is identical to the name of the column.
When you define an EGL statement that uses an SQL record and that generates either an SQL SELECT statement or a cursor declaration, EGL provides an implicit SQL SELECT statement. (That statement is embedded in the cursor declaration, if any.) For example, you might declare a variable that is named EMP and is based on the following record part:
Record Employee sqlRecord tableNames = EMPLOYEE keyItems = empnum empnum decimal(6,0) isReadOnly=yes; empname char(40); end
Then, you might code a get statement:
get EMP;
The implicit SQL SELECT statement is as follows:
SELECT empnum, empname FROM EMPLOYEE WHERE empnum = :empnum
EGL also places an INTO clause into the standalone SELECT statement (if no cursor declaration is involved) or into the FETCH statement associated with the cursor. The INTO clause lists the host variables that receive values from the columns listed in the first clause of the SELECT statement:
INTO :empnum, :empname
The implicit SELECT statement reads each column value into the corresponding host variable; references the tables specified in the SQL record; and has a search criterion (a WHERE clause) that depends on a combination of two factors:
A special situation is in effect if you read data into a dynamic array of SQL records, as is possible with the get statement:
Any host variables specified in the property defaultSelectCondition must be outside the SQL record that is the basis of the dynamic array.
For details on the implicit SELECT statement, which vary by keyword, see get and open.
When you are using SQL records, you can relate cursor-processing statements by using the same SQL record in several EGL statements, as you can by using a result-set identifier. However, any cross-statement relationship that is indicated by a result-set identifier takes precedence over a relationship indicated by the SQL record; and in some cases you must specify a resultSetID.
In addition, only one cursor can be open for a particular SQL record. If an EGL statement opens a cursor when another cursor is open for the same SQL record, the generated code automatically closes the first cursor.
Given an EGL statement that uses an SQL record as the I/O object, you can progress in either of two ways:
If you remove an explicit SQL statement from the source, the implicit SQL statement (if any) is again available at generation time.
EGL internally maintains a null indicator for each host variable that has the following characteristics:
Do not code host variables for null indicators in your SQL statements, as you might in some languages. To test for null in a nullable host variable, use an EGL if statement. You also can test for retrieval of a truncated value, but only when a null indicator is available.
You can null an SQL table column in either of two ways:
For additional details on null processing, see SQL item properties.
(C) Copyright IBM Corporation 1992, 2005. All Rights Reserved.