Replace statement for a relational database

You can use the EGL replace statement to update a row in a relational database.

Syntax



Syntax diagram for the replace statement with SQL

entity
Name of the entity, which is a variable based on an External type, Handler type, or Record type. The values in that entity are used in the corresponding SQL UPDATE statement. If you specify the SQL data source but not an SQL statement, the following default is used:
UPDATE tableName 
   SET column_1 = ?,
       column_2 = ?,
       ... 
       column_n = ?
   WHERE
       ID_column_1 = ? AND
       ID_column_2 = ? AND
       ... 
       ID_column_n = ?
When an entity is used from a SQL data source, the basic idea is as follows:
  • A subset of entity fields are available. Those fields are updatable, with values that are neither generated nor transient.
  • The available entity fields are split into two categories. The non-key fields are used in the SET clause. The key fields are not used in the SET clause, but might be used in the WHERE clause.
  • If the using clause is present, the values in that clause are used in the WHERE clause. The using clause is an override.
field
A value of an EGL simple type that is compatible with the corresponding database column.

The field values are assigned to the question marks in the WHERE clause of the SQL UPDATE statement.

SQLDataSource
A variable of type SQLDataSource. The variable includes connection details or references an EGL deployment descriptor entry that provides access to the connection details.

If you issue a prepared statement, the variable is optional and is ignored if present.

SQLResultSet
An SQL result set that was established by an open statement. In this case, the Replace statement updates the current row. An attempt to update the row a second time results in an exception.
stmt
A single SQL UPDATE statement, which is not required to end with a semicolon but can. The statement format is as shown in the description of entity.

You can use a prepared statement in place of #sql{stmt}.

If you need to update an ID column, your replace statement must use an explicit SQL statement with a using clause that includes simple values. For example, consider the following Record type:
Record TwoKeys {@Table {name="MYTABLE"}}
   id01 int {@Id};
   id02 int {@Id};
   aString string;
end
Here is an example use, when you want to change the value of an ID column:
ds SQLDataSource?{@Resource{uri="binding:myEntry"}};
row TwoKeys;

replace row to ds with
			#sql{
					 update MYTABLE
					    set aString = ?, 	id02 = ?
        where
           id01 = ? and id02 = ?
      } 
      using
         row.aString,
         10,
         row.id01,
         row.id02;

Compatibility

Table 1. Compatibility
Target Issue
Java No issues
JavaScript Database access is not supported in JavaScript.