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:
| 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:
| 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:
| 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:
| Yes |
(C) Copyright IBM Corporation 1992, 2005. All Rights Reserved.