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.
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.
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.
EGL generates SQL statements based on the properties and values of your SQL record variable (see SQLRecord stereotype).
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
select customer_number, customer_name, customer_balance into :myCustomer.customerNumber, :myCustomer.customerName, :myCustomer.customerBalance from Customer where customer_number = :myCustomer.customerNumber
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.
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 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.