open considerations for SQL

resultSetID

A character string of your choice to identify the result set that the open statement creates. You can use the ID with a positional get statement, or with the replace, delete, or close statements.

rowsetsize

RowsetSize is an annotation that controls the number of rows retrieved in one time of multiple row fetch in one time of multiple row insert.

scroll

Option that enables a positional get statement to move through the result set. For more about positional get statements, see get considerations for SQL. This option is available only if you are generating output in Java™.

hold

Option that causes the program to retain position in a result set when a commit occurs. The hold option is appropriate when all of the following conditions are true:

• You use the EGL open statement to open a cursor rather than to call a stored procedure.
• You want to commit changes periodically without losing your position in the result set.
• Your database management system supports use of the WITH HOLD option in the SQL cursor declaration.

Cursors for which you specify the hold option are not closed on a commit; however, a rollback or database connect closes all cursors. If you do not need to retain cursor position across a commit, do not specify hold.

For more information, see "Compatibility" in this topic.

forUpdate

Option that enables later EGL statements to replace or delete the data that was retrieved from the database. Do not use this option when you call a stored procedure.

usingKeys ... field

You can specify field names from your SQL record here to override the key fields you specified in that record definition. The field or fields you specify with the usingKeys clause build the key-value component of the WHERE clause in an implicit SQL statement. The implicit SQL statement is used at run time if you do not specify an explicit SQL statement. If you include a usingKeys clause, you must also specify an SQL record variable.

If you do not specify a usingKeys clause, the key-value component of the implicit statement is based on the SQL Record part that is referenced in the open statement.

If you specify the forUpdate keyword, the columns associated with the key items are excluded from the columns listed on the SQL FOR UPDATE OF clause. If you do not specify the forUpdate keyword, only the columns associated with the key items are included in the list of columns on the SQL ORDER BY clause.

Any implicit code that EGL might otherwise generate is ignored if you specify an embedded SQL statement.

with #sql{ sqlStatement }

Specifies an embedded SQL SELECT statement, which is optional if you also specify an SQL record variable. Embedded code takes precedence over any implicit code EGL might otherwise generate. Do not leave space between the #sql directive 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.

into target

Corresponds to an SQL INTO clause, which identifies the EGL host variables (see Host variables) that receive values from the result set. You can alternately specify a record name, in which case EGL uses all fields from the record. In a clause like this one (which is outside of a #sql directive), do not include a colon before the name of a host variable.

preparedStatementID

Identifies code created with an EGL prepare statement, allowing dynamic processing. For details, see prepare considerations for SQL.

using field

Corresponds to an SQL USING clause, which identifies the EGL host variables that are made available to the prepared statement at run time. In a using field clause (which is outside of a #sql directive), do not include a colon before the name of a host variable.

SQLRecordVariable

The name of an SQL record variable. If you do not specify an SQL record variable, you must provide embedded SQL code (with the #sql directive). If you do provide an SQL record variable, and do not specify embedded SQL code, EGL uses information in the record properties to create an implicit SQL SELECT statement. You can query the variable after the open operation to detect error conditions; for more information, see "Error conditions" in this topic.

SQLDynamicArray

This variable names a dynamic array that is composed of SQL record variables. EGL supports dynamic array of SQLRecord as the target of the open statement. Usage of a dynamic array is only valid when rowset processing is being used for this statement.

You can override the default by specifying an embedded SQL statement in the EGL open statement (using the #sql directive).

The following examples assume an SQL record variable named myCustomer:

  open myCustomerResults forUpdate for myCustomer;

  open x1 with
    #sql{
      SELECT customer_number,
             customer_name, 
             customer_balance
      FROM   Customer 
      WHERE  customer_number >= :myCustomer.customerNumber
      FOR UPDATE OF 
             :myCustomer.customerNumber, 
             :myCustomer.customerName,
             :myCustomer.customerBalance
    }

  open x2 with 
    #sql{
      SELECT customer_name, customer_balance
      FROM Customer
      WHERE customer_number = :myCustomer.customerNumber
    }
  for myCustomer;

  open x3 with
    #sql{
      call aResultSetStoredProc(:parameter)
    }

If you do not specify hold, the first run of the fourth part of step three fails because the cursor is no longer open.

Each relational database management system (RDBMS) has its own version of SQL. Not all SQL commands are available in every implementation. Before coding any embedded SQL, check the documentation for your RDBMS.