The basic idea of a relational database is that data is stored in persistent tables. Each table column represents a discrete unit of data such as an order ID or an order-status code, and each row represents a collection of such data. In short, a table row is equivalent to a file record.
To access data from a relational database, EGL-generated code includes SQL (pronounced as three separate letters). SQL statements allow for communication with a relational-database management system, which is the software that controls a relational database.
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 shows where to use each of the EGL SQL techniques:
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:defaultSelectCondition = #sqlCondition{ customer.customer_ID = orders.customer_ID } |
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.
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;