SQL support

As shown in the next table, EGL-generated code can access a relational database on any of the target systems.

As you work on a program, you can code SQL statements as you would when coding programs in most other languages. To ease your way, EGL provides SQL statement templates for you to fill.

Alternatively, you can use an SQL record as the I/O object when you code an EGL statement. Using the record in this way means that you access a database either by customizing an SQL statement provided to you or by relying on a default that removes the need to code SQL.

Target System	Support for access of relational databases
AIX, iSeries, Linux, Windows 2000/NT/XP, UNIX System Services	JDBC provides access to DB2 UDB, Oracle, or Informix

EGL statements and SQL

The next table lists the EGL keywords that you can use to access a relational database. Included in this table is an outline of the SQL statements that correspond to each keyword. When you code an EGL add statement, for example, you generate an SQL INSERT statement.

In many business applications, you use the EGL open statement and various kinds of get by position statements. The code helps you to declare, open, and process a cursor, which is a run-time entity that acts as follows:

Returns a result set, which a list of rows that fulfill your search criteria
Points to a specific row in the result set

You can use the EGL open statement to call a stored procedure. That procedure is composed of logic that is written outside of EGL, is stored in the database management system, and also returns a result set. (In addition, you can use the EGL execute statement to call a stored procedure.)

Later sections give details on processing a result set.

If you intend to code SQL statements explicitly, you use the EGL execute statement and possibly the EGL prepare statement.

Keyword/Purpose	Outline of SQL statements	Can you modify the SQL?
add Places a row in a database; or (if you use a dynamic array of SQL records), places a set of rows based on the content of successive elements of the array.	INSERT row (as occurs repeatedly, if you specify a dynamic array).	Yes
close Releases unprocessed rows.	CLOSE cursor.	No
delete Deletes a row from a database.	DELETE row. The row was selected in either of two ways: When you invoked a get statement with the forUpdate option (as appropriate when you wish to select the first of several rows that have the same key value) When you invoked an open statement with the forUpdate option and then a get next statement (as appropriate when you wish to select a set of rows and to process the retrieved data in a loop)	No
forEach Marks the start of a set of statements that run in a loop. The first iteration occurs only if a specified result set is available and continues (in most cases) until the last row in that result set is processed.	EGL converts a forEach statement into an SQL FETCH statement that runs inside a loop.	No
freeSQL Frees any resources associated with a dynamically prepared SQL statement, closing any open cursor associated with that SQL statement.		No
get (also called get by key value) Reads a single row from a database; or (if you use a dynamic array of SQL records), reads successive rows into successive elements in the array.	SELECT row, but only if you set the option singleRow. Otherwise, the following rules apply: EGL converts a get statement to this: DECLARE cursor with SELECT or (if you set the forUpdate option) with SELECT FOR UPDATE. OPEN cursor. FETCH row. If you did not specify the option forUpdate, EGL also closes the cursor. The singleRow and forUpdate options are not supported with dynamic arrays; in that case, EGL run time declares and opens a cursor, fetches a series of rows, and closes the cursor.	Yes
get absolute Reads a numerically specified row in a result set that was selected by an open statement.	EGL converts a get absolute statement to an SQL FETCH statement.	No
get current Reads the row at which the cursor is already positioned in a result set that was selected by an open statement.	EGL converts a get current statement to an SQL FETCH statement.	No
get first Reads the first row in a result set that was selected by an open statement.	EGL converts a get first statement to an SQL FETCH statement.	No
get last Reads the last row in a result set that was selected by an open statement.	EGL converts a get last statement to an SQL FETCH statement.	No
get next Reads the next row in a result set that was selected by an open statement.	EGL converts a get next statement to an SQL FETCH statement.	No
get previous Reads the previous row in a result set that was selected by an open statement.	EGL converts a get previous statement to an SQL FETCH statement.	No
get relative Reads a numerically specified row in a result set that was selected by an open statement. The row is identified in relation to the cursor position in the result set.	EGL converts a get relative statement to an SQL FETCH statement.	No
execute Lets you run an SQL data-definition statement (of type CREATE TABLE, for example); or a data-manipulation statement (of type INSERT or UPDATE, for example); or a prepared SQL statement that does not begin with a SELECT clause.	The SQL statement you write is made available to the database management system. The primary use of execute is to code a single SQL statement that is fully formatted at generation time, as in this example-- try execute #sql{ // no space after "#sql" delete from EMPLOYEE where department = :myRecord.department }; onException myErrorHandler(10); end A fully formatted SQL statement may include host variables in the WHERE clause.	Yes
open Selects a set of rows from a relational database for later retrieval with get next statements.	EGL converts an open statement to a CALL statement (for accessing a stored procedure) or to these statements: DECLARE cursor with SELECT or with SELECT FOR UPDATE. OPEN cursor.	Yes
prepare Specifies an SQL PREPARE statement, which optionally includes details that are known only at run time; you run the prepared SQL statement by running an EGL execute statement or (if the SQL statement begins with SELECT) by running an EGL open or get statement.	EGL converts a prepare statement to an SQL PREPARE statement, which is always constructed at run time. In the following example of an EGL prepare statement, each parameter marker (?) is resolved by the USING clause in the subsequent execute statement: myString = "insert into myTable " + "(empnum, empname) " + "value ?, ?"; try prepare myStatement from myString; onException // exit the program myErrorHandler(12); end try execute myStatement using :myRecord.empnum, :myRecord.empname; onException myErrorHandler(15); end	Yes
replace Puts a changed row back into a database.	UPDATE row. The row was selected in either of two ways: When you invoked a get statement with the forUpdate option (as appropriate when you wish to select the first of several rows that have the same key value); or When you invoked an open statement with the forUpdate option and then a get next statement (as appropriate when you wish to select a set of rows and to process the retrieved data in a loop).	Yes

Note: Under no circumstances can you update multiple database tables by coding a single EGL statement.

Result-set processing

A common way to update a series of rows is as follows:

Declare and open a cursor by running an EGL open statement with the option forUpdate; that option causes the selected rows to be locked for subsequent update or deletion
Fetch a row by running an EGL get next statement
Do the following in a loop:
1. Change the data in the host variables into which you retrieved data
2. Update the row by running an EGL replace statement
3. Fetch another row by running an EGL get next statement
Commit changes by running the EGL function commit.

The statements that open the cursor and that act on the rows of that cursor are related to each other by a result-set identifier, which must be unique across all result-set identifiers, program variables, and program parameters within the program. You specify that identifier in the open statement that opens the cursor, and you reference the same identifier in the get next, delete, and replace statements that affect an individual row, as well as on the close statement that closes the cursor. For additional details, see resultSetID.

The following code shows how to update a series of rows when you are coding the SQL yourself:

  handleHardIOErrors = 1;

  try
    open selectEmp forUpdate with
    #sql{                                          // no space after "#sql"
      select empname
      from EMPLOYEE
      where empnum >= :myRecord.empnum
      for update of empname
    };
      
  onException
    myErrorHandler(8);    // exits program
  end

  try
    get next from selectEmp into :myRecord.empname;
  onException
    if (sysVar.sqlcode != 100)
      myErrorHandler(8);  // exit the program
    end
  end

  while (sysVar.sqlcode != 100) 
    myRecord.empname = myRecord.empname + " " + "III";

    try
      execute
      #sql{
        update EMPLOYEE
        set empname = :empname
        where current of selectEmp
      };
    onException
      myErrorHandler(10);   // exits program
    end

    try
      get next from selectEmp into :myRecord.empname;
    onException
      if (sysVar.sqlcode != 100)
        myErrorHandler(8);  // exits program
      end 
    end
  end  // end while; cursor is closed automatically
       // when the last row in the result set is read

  sysLib.commit;

If you wish to avoid some of the complexity in the previous example, consider SQL records. Their use allows you to streamline your code and to use I/O error values that do not vary across database management systems. The next example is equivalent to the previous one but uses an SQL record called emp:

  handleHardIOErrors = 1;

  try
    open selectEmp forUpdate for emp;
  onException
    myErrorHandler(8);    // exits program
  end

  try
    get next emp;
  onException
    if (sysVar.sqlcode not noRecordFound)
      myErrorHandler(8);  // exit the program
    end 
  end

  while (sysVar.sqlcode not noRecordFound) 
    myRecord.empname = myRecord.empname + " " + "III";

    try 
      replace emp;
    onException
      myErrorHandler(10);   // exits program
    end

    try
      get next emp;
    on exception
      if (sysVar.sqlcode not noRecordFound)
        myErrorHandler(8);  // exits program
      end
    end
  end  // end while; cursor is closed automatically
       // when the last row in the result set is read

  sysLib.commit;

Later sections describe SQL records.

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:

Declare an SQL record part and the related SQL record
Define a set of EGL statements that each use the SQL record as an I/O object
Accept the default behavior of the EGL statements or make the SQL changes that are appropriate for your business logic

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 type sqlRecord
    { tableNames = EMPLOYEE,
      keyItems = empnum }
    empnum decimal(6,0);
    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:

The value you specified for the record property defaultSelectCondition; and
A relationship (such as an equality) between two sets of values:
- Names of the columns that constitute the table key
- Values of the host variables that constitute the record key

A special situation is in effect if you read data into a dynamic array of SQL records, as is possible with the get statement:

A cursor is open, successive rows from the database are read into successive elements of the array, the result set is freed, and the cursor is closed.
If you do not specify an SQL statement, the search criterion depends on the record property defaultSelectCondition, but also depends on a relationship (specifically, a greater-than-or-equal-to relationship) between the following sets of values:
- Names of columns, as specified indirectly when you specify items in the EGL statement
- Values of those items
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.

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 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:

You can accept the implicit SQL statement. In this case, changes made to the SQL record part affect the SQL statements used at run time. If you later indicate that a different record item is to be used as the key of the SQL record, for example, EGL changes the implicit SELECT statement used in any cursor declaration that is based on that SQL record part.
You can choose instead to make the SQL statement explicit. In this case, the details of that SQL statement are isolated from the SQL record part, and any subsequent changes made to the SQL record part have no effect on the SQL statement that is used at run time.
If you remove an explicit SQL statement from the source, the implicit SQL statement (if any) is again available at generation time.

Testing for and setting NULL

EGL internally maintains a null indicator for each host variable that has the following characteristics:

Is in an SQL record
Is declared to be nullable

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:

Use an EGL set statement to null a nullable host variable, then write the related SQL record to the database; or
Use the appropriate SQL syntax, either by writing an SQL statement from scratch or by customizing an SQL statement that is associated with the EGL add or replace statement

For additional details on null processing, see SQL item properties.

Database access at declaration time

You receive the following benefits from accessing (at declaration time) a database that has similar characteristics to the database that your code will access at run time:

If you access a database table or view that is equivalent to a table or view associated with an SQL record, you can use the retrieve feature of the EGL part editor to create or overwrite the record items. The retrieve feature accesses information stored in the database management system so that the number and data characteristics of the created items reflect the number and characteristics of the table columns. After you invoke the retrieve feature, you can rename record items, delete record items, and make other changes to the SQL record.
Your access of an appropriately structured database at declaration time helps to ensure that your SQL statements will be valid in relation to an equivalent database at run time.

The retrieve feature creates record items that each have the same name (or almost the same name) as the related table column.

You cannot retrieve a view that is defined with the DB2 condition WITH CHECK OPTIONS.

For further details on using the retrieve feature, see Retrieving SQL table data. For details on naming, see Setting preferences for SQL retrieve.

To access a database at declaration time, specify connection information in a preferences page, as described in Setting preferences for SQL database connections.

Related concepts

Dynamic SQL
Logical unit of work
resultSetID