SQL data access

EGL supports relational database access through SQL.

EGL supports Structured Query Language (SQL) in the following ways:

EGL creates implicit SQL statements whenever you use an EGL data access statement (such as add or get) that specifies an SQL record variable as its target. Even if you do not know any SQL, you can write functions that access a relational database.
You can code embedded SQL statements through the #sql directive. Embedded SQL statements always override implicit ones. You can use embedded SQL statements to harness the full range of the SQL vocabulary. However, you must be proficient with SQL to use them.
When you use dynamic SQL, the actual SQL statements are not finalized until run time. You typically use a prepared statement when you use dynamic SQL. You assemble this SQL code at runtime using the EGL prepare statement, then pass it to an EGL open, get, or execute statement for processing.

Typical SQL processing

Typical SQL processing begins with an EGL open statement. The open statement sends a set of criteria to the database management software and gets a result set (rows of matching data) in return. The result set keeps track of the row to which it is pointing. You can use one of the variants of the get statement to read from the result set sequentially, or by relative or absolute position.

In the background, EGL behaves differently in COBOL than it does in Java™. COBOL code interprets the open statement as a series of SQL statements and uses the SQL cursor to maintain position in the result set. The following SQL statements are equivalent to an EGL open statement in COBOL:

A DECLARE statement to create a cursor
An OPEN statement to ready that cursor for use
A SELECT statement to create a result set

Java™ code uses a Statement object to create a ResultSet object.

A get statement with a position option (such as next or absolute) generates an SQL FETCH statement in COBOL (and an equivalent in Java™) to retrieve the data from a specific row.

You can also use the get statement without a position option and without previously having used the open statement. In this case, the get generates the following SQL in COBOL (and an equivalent in Java™):

a DECLARE statement to create a cursor
an OPEN statement to ready that cursor for use
a SELECT statement to create a result set
a FETCH statement to retrieve the first row
a CLOSE statement to delete the result set and cursor (unless the get statement includes a forUpdate clause, which is not typical)

When you create a result set with the get statement, EGL fetches the first row and then closes the result set. Because the get closes the result set, you cannot follow it with a positional get. To use repeated positional get statements, begin processing with an EGL open and do an EGL close when you finish.

Implicit SQL statements

EGL generates SQL statements based on the properties and values of your SQL record variable (see SQLRecord stereotype).

For example, consider the following EGL source code:

Record CustomerRecord type SQLRecord 
{ tableNames=[["Customer"]], keyItems=["customerNumber"] }
  customerNumber INT {column = "customer_number"};
  customerName STRING {column = "customer_name"};
  customerBalance DECIMAL(9,2) {column = "customer_balance"};
end

program CustomerTest type BasicProgram

myCustomer CustomerRecord;

  function main()
    myCustomer.customerNumber = 1001;
    get myCustomer;
    printCustomer(myCustomer);
  end
end

EGL translates the get myCustomer; statement into SQL DECLARE and OPEN statements, the following SQL SELECT code, and finally SQL FETCH and CLOSE statements:

select
	customer_number, customer_name, customer_balance
into :myCustomer.customerNumber, :myCustomer.customerName, 
	:myCustomer.customerBalance
from Customer
where
	customer_number = :myCustomer.customerNumber

This code is called implicit because everything in it is implied by elements in the myCustomer SQL record variable. EGL performs the following actions as a result of this code:

The SELECT clause uses the EGL field-level column property to locate all columns that correspond to fields in the record.
The INTO clause lists the variables that correspond to the columns in the SELECT clause. Variables that begin with a colon character (:) are called host variables; see Host variables.
The FROM clause takes the table name from the EGL record-level tableNames property.
The WHERE clause uses the value of the EGL record-level keyItems property as the key to the database table.

This SQL SELECT clause is visible through the editor. You can use the editor to make the code explicit by turning it into embedded SQL statements that you can modify. For more information, see Viewing implicit SQL statements in the EGL Programmer's Guide.

If you already know how to write SQL statements, creating embedded SQL is more flexible.

Embedded SQL statements

EGL performs very little syntax or other checking on the SQL statements you embed. Embedded SQL statements always override implicit ones, even when the embedded statements contain errors. Because of this, you must be careful when you use embedded SQL statements.

For syntax and examples of the #sql directive, see "#sql directive."

Dynamic SQL

Dynamic SQL code is assembled at run time, and is particularly useful in situations where users are generating queries themselves through your application. For example, a user might have a list of check boxes for potential report fields. Your application can build a SELECT statement based on the criteria that your user provides.

Dynamic SQL can negatively affect performance; you must weigh this disadvantage against the added flexibility it offers.

As a rule, use the EGL prepare statement to vary the selection criteria, the way data is aggregated, or the order in which rows are returned; those details are controlled by the WHERE, HAVING, GROUP BY, and ORDER BY clauses. For more information, see prepare.

As part of the compatibility between EGL and VisualAge® Generator, you can dynamically select a table for runtime processing through the use of table variables. For more information about this option, see SQLRecord stereotype. You typically perform dynamic table selection through the prepare statement.