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.
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
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. |