With EGL, you can even combine the two styles. You can access the SQL statements that EGL generates from your EGL code and modify them (see Viewing implicit SQL statements).
The following table outlines how to use EGL to interact with a relational database.
| SQL objective | EGL approach |
|---|---|
| Simple SQL data manipulation (SELECT, UPDATE, INSERT, DELETE). Primary key controls WHERE and ORDER BY. | Use EGL keywords (get, replace, add, delete) and let EGL generate implicit SQL. |
| Simple SQL data manipulation with reusable custom WHERE clause. | Place the custom WHERE clause in the defaultSelectCondition property. |
| SQL SELECT statements with custom WHERE clause. | Use explicit SQL through the #sql directive. |
| SQL table JOIN statement. | Use the Retrieve SQL feature in the workbench,
then create use the defaultSelectCondition property
to correctly join the tables on primary and foreign keys:
|
| Derived data in SELECT command (such as MAX() or AVG()) | Use explicit SQL through the #sql directive, placing the derived fields inside the braces. |
| Create a custom SQLRecord, where the column property for the individual fields specifies the derived or computed expression. | |
| Complex or custom SQL UPDATE, INSERT, or DELETE statement. | Use EGL replace, add, or delete statements with explicit SQL (#sql directive). |
| Use explicit SQL through the execute #sql statement. | |
| SQL statements other than simple data manipulation (such as CREATE TABLE). | Use explicit SQL through the execute #sql statement. |
| Dynamic SQL (prepared SQL statement). | Use explicit SQL through the execute #sql statement. |
| Stored procedure. | Use explicit SQL such as the following: |
| Processing of individual rows of the result set from a SQL SELECT statement. | Use the EGL open command
to open the result set, then initiate a loop with one of the following
statements:
|
| Programmatic paging for online searches. | Use the Data Access Application wizard. |
| Addition of data to SQL table. | Use the Table Editor in the workbench Data Perspective. |
| SQL statement validation | In the EGL editor, select Validate SQL from the context menu. |
| Run interactive SQL using the SQL Editor in the workbench Data Perspective. |
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 and program variables within the program. You specify that identifier in the open statement that opens the cursor, and you reference the same identifier in the forEach statement that creates the loop. You also reference the identifier in the get next, delete, and replace statements that affect an individual row and on the close statement that closes the cursor.
try
open selectEmp forUpdate for emp;
onException(sqlx SqlException)
myErrorHandler(sqlx); // exits program
end
foreach(emp)
emp.empname = emp.empname :: " " :: "III";
try
replace emp;
onException(sqlx SqlException)
myErrorHandler(sqlx); // exits program
end
end // end while; cursor is closed automatically
// when the last row in the result set is read
sysLib.commit();
To commit changes periodically as you process an EGL open statement (regardless of whether you use SQL records), use the hold statement option, which maintains cursor position after a commit. However, if a program that is targeted for CICS® is segmented, the hold option has no effect because a converse in a segmented program ends the CICS transaction and prevents the program from retaining any file or database position.
add myEmpRecord;
try
add myEmpRecord;
onException(sqlx SqlException)
if (myEmpRecord is unique) // if a table row had the same key
myErrorHandler(sqlx);
end
end
You define an SQLRecord part and associate each of the fields with a column in a relational table or view. EGL can do this for you automatically; see Retrieving SQL table data.
Only fields of a primitive type can represent a database column.
After you define an SQLRecord part, you declare a record variable that is based on that part.
You can create a set of EGL statements that each use the record variable 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 record variable and EGL statement. In the case of an EGL add statement, for example, an implicit SQL INSERT statement places the values of the fields in the given record into the associated table column. If your record variable includes a field for which no table column was assigned, EGL forms the implicit SQL statement on the assumption that the name of the field is identical to the name of the column.
The following EGL statements correspond to the SQL statements shown:
| EGL statement | SQL statement |
|---|---|
| add | INSERT |
| delete | DELETE |
| get, open | SELECT |
| replace | UPDATE |
Record Employee type sqlRecord
{ tableNames = [["EMPLOYEE"]],
keyItems = ["empnum"] }
empnum decimal(6,0);
empname char(40);
end
myEmpRecord Employee;
get myEmpRecord;
SELECT empnum, empname
FROM EMPLOYEE
WHERE empnum = :empnum
INTO :empnum, :empname
For details on the implicit SELECT statement, see individual keyword topics in the EGL Language Reference.
When you use SQL records, you can relate cursor-processing statements by using the same record variable in several EGL statements, much the same way 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 record variable; in some cases you must specify a resultSetID.
In addition, only one cursor can be open for a particular record variable. If an EGL statement opens a cursor when another cursor is open for the same record variable, 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(myDeptRecord Dept)
get myDeptRecord.employees usingKeys myDeptRecord.deptNo;
end
Record Employee type SQLRecord
employeeID CHAR(6);
empDeptNo INT?;
end
if (myEmpRecord.empDeptNo == null)
...
end
myEmpRecord.empDeptNo = null;
set myEmpRecord.empDeptNo empty;
| Platform | Issue |
|---|---|
| CICS for z/OS®, z/OS batch, iSeriesC | The generated code can access DB2® UDB directly |
| AIX®, HP-UX, iSeriesJ, Linux, Solaris, z/OS UNIX System Services, Windows 2000/NT/XP | JDBC provides access to DB2 UDB, Oracle, Informix®, or Microsoft SQL Server |