As shown in the next table, EGL-generated code can access a relational database on any of the target systems.
Target System | Support for access of relational databases |
---|---|
AIX®, HP-UX, iSeries™, Linux®, Solaris, UNIX® System Services, Windows® 2000/NT/XP | JDBC provides access to DB2® UDB, Oracle, Informix®, or Microsoft® SQL Server |
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.
select empnum, empname from employee where empnum >= :myRecord.empnum for update of empname
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 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:
|
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:
|
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:
|
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 |
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.
VGVar.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:
VGVar.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.
add EMP;
VGVar.handleHardIOErrors = 1; try add EMP; onException if (EMP is unique) // if a table row // had the same key myErrorHandler(8); end end
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.
Only fields of a primitive type can represent a database column.
After you declare an SQL record part, you declare an SQL record that is based on that part.
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.
Record Employee type sqlRecord { tableNames = [["EMPLOYEE"]], keyItems = ["empnum"] } empnum decimal(6,0); empname char(40); end
get EMP;
SELECT empnum, empname FROM EMPLOYEE WHERE empnum = :empnum
INTO :empnum, :empname
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.
If you remove an explicit SQL statement from the source, the implicit SQL statement (if any) is again available at generation time.
DataItem DeptNo { column = deptNo } end Record Dept type SQLRecord deptNo DeptNo; managerID CHAR(6); employees Employee[]; end Record Employee type SQLRecord employeeID CHAR(6); empDeptNo DeptNo; end Function getDeptEmployees(dept Dept) get dept.employees usingKeys dept.deptNo; end
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.
For additional details on null processing, see isNullable and itemsNullable.
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
Related tasks
Retrieving SQL table data
Setting preferences for SQL database connections
Setting preferences for SQL retrieve
Related reference
add
close
Database authorization and table names
Default database
delete
execute
get
get next
Informix and EGL
isNullable
itemsNullable
open
prepare
replace
SQL data codes and EGL host variables
SQL examples
SQL item properties
SQL record internals
SQL record part in EGL source format
Testing for and setting NULL