EGL Reference Guide for iSeries

SQL records and their uses

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:

Declaring an SQL record part and the related record

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.

Defining the SQL-related EGL statements

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.

Using implicit SELECT statements

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:

For details on the implicit SELECT statement, which vary by keyword, see get and open.

Using SQL records with cursors

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.

Customizing the SQL statements

Given an EGL statement that uses an SQL record as the I/O object, you can progress in either of two ways:

Testing for and setting NULL

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.


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