EGL Reference Guide for iSeries

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.

You use the EGL open and get next statements in many business applications. Those statements help you to declare, open, and process a cursor, which is a run-time entity that acts as follows:

When your output is Java code, 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. (Regardless of your output language, 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
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 next

Reads the next row in a database that was selected by an open statement.

EGL converts a get next statement to an SQL FETCH statement. Yes, but only to set the INTO clause
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.


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