EGL creates an SQL SELECT statement in your generated code that is based on your get statement and the properties of the SQL record variable in that statement. For more information about this implicit SQL code, see SQL data access. Alternatively, you can use an #sql directive to write your own embedded SQL code (see sql directive). You can also assemble SQL code at run time using an EGL prepare statement, and then reference that code from your get statement. For more information about this use of dynamic SQL, see SQL data access.
If you specify the singleRow option, the SQL SELECT statement stands alone. If you do not specify singleRow, the SQL SELECT becomes a clause in a cursor declaration (an SQL OPEN statement). For more information about the SQL code that EGL generates, see SQL data access.
EGL provides a number of position options with the get statement (such as absolute and next) that allow you to retrieve a specific record relative to your current position in the result set. To do this, you must keep track of your current position by using a cursor. To be able to access the result set for your cursor, use the EGL open statement before you use the positional get. You can also use a get...forUpdate statement, but this is less common.
If the target of the get statement is a single SQL record variable, the statement returns the first record that matches the criteria (implicit or embedded) you provide. You can also specify a dynamic array of SQL record variables as the statement target. In this case, the get statement returns each matching row as an element in the array.

customer CustomerRecordPart{};
get customer with #sql{
select MySCHEMA.CUSTOMER.Column01
from MYSCHEMA.CUSTOMER
where MYSCHEMA.Column02 = "AZ"};
Record CustomerRecordPart type SQLRecord {tableNames = [["MYSCHEMA.CUSTOMER"]]}
customerNumber INT {column = "MYSCHEMA.CUSTOMER.Column01"};
...
end
In the get or open statement just described, the qualification used for a column name in the SQL SELECT clause must match the qualification used for the corresponding field in the Record part.
SELECT
CASE
WHEN address1 <> '' THEN address1
WHEN address2 <> '' THEN address2
END...
SELECT COLUMN01 || 10 + COLUMN02 * 5
Record MyRecordPart type SQLRecord
myField int { column = "COLUMN01 || 10 + COLUMN02 * 5" };
end
In that last case, consider writing your own INTO clause and not setting the column property. The reason is as follows: when you code an EGL add or replace statement that accesses a complex column property value, the SQL INSERT and UPDATE statements that are generated for you might not be valid.
Write the into clause in EGL, not in SQL. Do not begin the names of the variables in the clause with colons, as you can with host variables in an SQL statement. For more information, see Host variables.
get myCustomer usingKeys myCustomer.customerName;
Or,
you can search based on a field from a different record entirely:get myCustomer usingKeys myOrders.customerName;
The field or fields that you specify with usingKeys plug into the WHERE clause in the implicit SQL statement EGL generates from the get statement. If you follow that statement with embedded SQL code, the embedded code overrides the implicit code.
In the case of a dynamic array, the fields in the usingKeys clause (or the host variables in the SQL record) must not be in the SQL record variable that is the basis of the dynamic array.
If you specify the forUpdate keyword, the columns associated with the key items are excluded from the columns listed on the FOR UPDATE OF clause.
The next position option is available in other circumstances as well.
The initial position for the cursor (or position indicator) for a result set is before the first row of results. Programs typically use a forEach statement or use get next repeatedly to iterate over the results.
When you specify an SQL record variable in the get statement but do not specify an embedded SQL statement with the #sql directive, you create implicit SQL code. The implicit SQL SELECT has the following characteristics:
SELECT column01,
column02,
...
columnNN
FROM tableName
WHERE keyColumn01 = :keyField01
FOR UPDATE OF
column01,
column02,
...
columnNN
INTO :recordField01,
:recordField02,
...
:recordFieldNN
EGL derives the SQL INTO clause if the SQL record variable is accompanied by an embedded SQL SELECT statement when you have not specified an INTO clause. The fields in the derived INTO clause are those that are associated with the columns listed in the SELECT clause of the SQL statement. (The field-and-column association is in your custom SQL Record part; see SQLRecord stereotype.) An EGL INTO clause is required if a column is not associated with a field.
keyColumn01 >= :keyField01 &
keyColumn02 >= :keyField02 &
...
keyColumnNN >= :keyFieldNN
try
get myCustomer singleRow into customerName with
#sql{
SELECT customer_name
FROM Cusomter
WHERE customer_number = :myCustomer.customerNumber
};
onException(sqlEx SQLException)
myErrorHandler(8);
end
try
get myCustomer forUpdate into customerName with
#sql{
SELECT customer_name
FROM Cusomter
WHERE customer_number = :myCustomer.customerNumber
};
onException(sqlEx SQLException)
myErrorHandler(8); // exits the program
end
myCustomer.customerName = newName; // user entered name change
try
replace myCustomer;
onException(sqlEx SQLException)
myErrorHandler(12);
end
employees Employee[0]{rowsetsize=10};
Open resultset1 scroll with #sql{
select eID, uName, PASSWORD, fName, lName, office, sex, EMail
from EMPLOYEETEST
} for employees;
Get Next employees; //this empties the employees array and
// populates with up to the next 10 rows from the result set
or
eIDs INT[0]{};
uNames CHAR(20)[10]{};
Get Next employees into eIDs, uNames;
or
DynamicEmployee DynamicEmployee;
Get Next employees into DynamicEmployee;
Record DynamicEmployee
eIDs INT[10];
uNames CHAR(20)[10];
end
or
structuredEmployee StructuredEmployee;
Get Next employees into structuredEmployee.eIDs, structuredEmployee.uNames; //
Get Next employees into structuredEmployee;
Record StructuredEmployee
1 eIDs INT[10];
1 uNames CHAR(20)[10];
end
Each relational database management system (RDBMS) has its own version of SQL. Not all SQL statements are available in every implementation. See the documentation from your RDBMS before coding any embedded SQL.
| Platform | Issue |
|---|---|
| iSeries® COBOL | The absolute option is not supported. |