get considerations for SQL

SQLRecordVariable

The name of an SQL record variable. The default behavior is to locate database rows in accordance with the values in the following Record part properties: keyItems and defaultSelectCondition.

forUpdate

If you are creating a new result set with your get statement and expect to replace or delete any database rows later, include this keyword to lock the rows that correspond to your result set. The database manager does not allow other programs to change locked rows until a commit occurs. For details on commit processing, see Logical unit of work.

resultSetID

If you are creating a new result set with your get statement, you can specify an ID of your choice with the forUpdate keyword to use the same result set in a later EGL replace, delete, execute, or positional get statement, or in an EGL close statement.

singleRow

This option generates more efficient SQL, which you might use when you are sure that the key in the get statement applies to only one row and when you do not intend to update or delete the row. EGL does not create a cursor in this case. A runtime I/O error results if you specify this option when the key applies to multiple rows.

#sql{ sqlSelectStatement }

If you are an experienced SQL programmer, you can override the SQL code that EGL generates by embedding your own SQL SELECT statement, as described in sql directive. Do not leave space between #sql and the left brace.

Consider the following case: your code has a get or open statement that uses an SQL record, includes explicit SQL, and lacks an INTO clause. For example, here is EGL code that includes such a get statement:

customer CustomerRecordPart{};
get customer with #sql{
   select MySCHEMA.CUSTOMER.Column01
   from MYSCHEMA.CUSTOMER
   where MYSCHEMA.Column02 = "AZ"}; 

The Record part that was referenced in that code is as follows:

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.

Restriction:

When you code a relatively complex SQL SELECT statement, you might need to specify the INTO clause rather than relying on the EGL generator to create that clause. The situation arises (for example) when an SQL CASE expression is used in a SELECT statement, as shown here:

SELECT 
   CASE 
      WHEN  address1 <> '' THEN address1 
      WHEN  address2 <> '' THEN address2 
   END... 

The situation does not arise if every entry in the initial SELECT clause is taken from one of the following categories:

• A column name
• A literal
• A special register like CURRENT TIMESTAMP
• Column names, literals, and special registers that are joined by one of the following operators: ||, +, -, *, /

Nevertheless, complexity in the SELECT clause can lead to a problem when you are using an SQL record because, to generate a valid INTO clause, you must assign a value to the column property to match the content that you are selecting. For example, here is an initial SELECT clause that includes a combination of entries from the previous categories:

SELECT COLUMN01 || 10 + COLUMN02 * 5

Here is the related SQL Record part:

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.

into ... target

This clause corresponds to the INTO clause in the implicit SQL code that EGL creates. The INTO clause identifies the EGL variables that receive values from the result set. You can specify individual variables, or you can specify a record name, in which case, EGL uses all of the fields in the record. This clause is required when you are processing SQL in either of these cases:

• You did not specify an SQL record variable in the get statement.
• You specified both an SQL record variable and an embedded SQL SELECT statement, but you have one or more columns in the SQL SELECT clause that do not have corresponding fields in the target SQL record variable.

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.

When used with get positionOptionSQLDynamicArray, then the targets can be a dynamic array field within a record variable or structured records whose fields have an occurs specified. Using structured records can provide better performance in COBOL generation. If the associated rowSetSize is greater than the elements specified in the occurred field in the structured record, when the get positionOption statement is trying to fill value to an element which exceeds the maximum index of the occurred array, an IndexOutOfBoundException should be thrown.

preparedStatementID

This identifier points to an EGL prepare statement that prepares an SQL SELECT statement at run time. The get statement runs the SQL SELECT statement dynamically. For more information, see prepare considerations for SQL.

using ... field

This clause corresponds to the USING clause in the implicit SQL code that EGL creates. The USING clause identifies the EGL variables that you make available to the prepared SQL SELECT statement at run time. Write the using clause in EGL, not in SQL. Do not begin the names of the variables in the clause with colons, as you would with host variables in an SQL statement. For more information, see Host variables.

usingKeys ... field

You can specify field names from your SQL record here to override the key fields you specified in that record definition. For example, if customerNumber is the key field you defined in CustomerRecord, but you want to search for records by customerName, you can use the following code:

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.

positionOption

Use this option to retrieve data from an existing result set rather than creating a new one. For available options and details, see "Using position options" in this topic.

from resultSetID

When you use position options, you can use a resultSetID to specify the result set from which you want to retrieve record information by position. You assigned this ID in a previous open or get statement. If you do not specify a result set, EGL determines your current result set from context. See "Using position options" in this topic.

SQLDynamicArray

This variable names a dynamic array that is composed of SQL record variables. EGL retrieves all matching rows from the result set and inserts those values into the individual record variable elements of the array. Set the maxSize property of the array to avoid retrieving more data at one time than the program can handle. EGL stops retrieving results after the array has reached its maximum size. See Dynamic array properties.

When used with positionOption, the next rowset is returned into the dynamic array based on the rowSetSize property on the SQLDynamicArrayvariable declaration or on the open statement. If this is the first time the get positionOption is done, then the array elements are created. If it is the second or later time the get positionOption is done, then the elements are replaced in the array.

sqlCallStatement

A call to a stored procedure in the database management system. The procedure must return exactly one result set. For examples, see Calling a stored procedure.

The next position option is available in other circumstances as well.

absolute (position)

Retrieves a row that you specify with a number that either counts forward from the beginning of the result set (if you specify a positive value) or counts backward from the end of the result set (negative value).

current

Retrieves the row at your current cursor position.

first

Retrieves the first row in the result set.

last

Retrieves the last row in the result set.

next

Retrieves the first row following your current cursor position.

previous

Retrieves the last row before your current cursor position.

relative (position)

Retrieves a row that you specify with a number that either counts forward from the present cursor position (if you specify a positive value) or counts backward from the present cursor position (if you specify a negative value). A value of zero retrieves the record at the current cursor position and is equivalent to the current option.

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:

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.