replace considerations for SQL

In the context of SQL, the EGL replace statement puts revised information from an SQL record variable back into a row of a relational database.

The statement produces an SQL UPDATE statement in the generated code. EGL can produce this statement implicitly, based on information and properties in your SQL record variable, or you can embed explicit SQL code in the replace statement using the #sql directive (see #sql directive).

Before you use the replace statement, you must retrieve a row for subsequent replacement. You can do this in either of two ways:

Syntax

Syntax diagram for the replace statement
SQLRecordVariable
Refers to an area of storage where you have placed the updated information. EGL uses this information to update a single database row.
sqlStatement
An embedded SQL UPDATE statement to replace the implicit SQL UPDATE statement that EGL otherwise generates. Do not leave a space between the #sql and the left brace.
replaceOptions
If you want to let EGL generate implicit SQL code for you, you have the following options to help shape that output:
cursor
This option tells EGL to create a WHERE CURRENT OF cursorID clause. For this to work, you must have created a result set using a get or open statement, as EGL does not give you direct access to the cursor these statements create. EGL uses the SQL record variable name to find this cursorID. This option is the default.
nocursor
This option generates a standard WHERE clause. You must also specify either an explicit SQL statement or a record that includes the keyItems property. Validation does not permit you to specify nocursor and usingKeys in the same statement.
usingKeys
You can specify field names from SQLRecordVariable here to override the key fields you specified in that record definition. This option provides the conditions for an SQL WHERE clause, specifying the fields from the above. Those fields must match the corresponding columns in the database rows to be replaced. Note that the key items are excluded from the list of columns updated on the SET clause.
from resultSetID
You must specify the result set ID from a previous EGL get or open statement if the following conditions are true:
  • You used embedded SQL statements (using the #sql directive) to create the result set.
  • That code retrieved different sets of columns for update than those in the implicit SQL that EGL generated.
In other words, if you are not working with the default result set that EGL normally creates for this SQL record variable, specify a result set ID.

Implicit SQL statement

By default, a replace statement that writes an SQL record has the following effects:
  • As a result of the association of record fields and SQL table columns in the Record part declaration, the generated code copies the data from each field into the related SQL table column.
  • If you defined a field to be a key item or to be read only, the value in the column that corresponds to that field is unaffected.
The SQL statement has the following characteristics by default:
  • The implicit SQL UPDATE statement does not include fields that are defined as key items or as read only.
  • The implicit SQL UPDATE statement for a particular record resembles the following:
    UPDATE tableName
    SET    column01 = :myField01,
           column02 = :myField02,
            ...
           columnNN = :myFieldNN    
    WHERE CURRENT OF cursor

Example

The following code includes the replace statement. The code comes from the complete SQL program that you can find in Sample EGL SQL program.

  try
    get dept forupdate;
    dept.description = "Test Engineers";
    replace dept;
    commit();
  onException(sqlEx SQLException)
    sqlFailure();     
  end 

Error conditions

The following conditions are among those that are not valid when you use a replace statement:
  • You embed an SQL statement of a type other than UPDATE using the #sql directive.
  • You specify some but not all clauses of an SQL UPDATE statement in your embedded code.
  • You do not specify a result set ID when you are not working with a default result set; for more information, see "Syntax" in this topic.
  • You specify an embedded (or accept an implicit) UPDATE statement that has one of these characteristics:
    • It updates multiple tables.
    • It is associated with a column that does not exist or is incompatible with the related host variable.
  • You specify an SQL record variable in which all the fields are read only.

Compatibility

Table 1. Compatibility considerations for replace
Platform Issue
COBOL generation For best performance in COBOL, always include the from resultSet clause in the replace statement.

Feedback