replace considerations for SQL

SQLRecordVariable

Refers to an area of storage where you have placed the updated information. EGL uses this information to update a single database row.

SQLDynamicArray

This variable names a dynamic array that is composed of SQL record variables. EGL replace updates the whole DymicArray. The replace statement must use with #sql{ExplicitCode}. The cursor position must have been established through the use of an open or get position statement.

SQLDynamicArrayElement

You can specify an element of a dynamic array. EGL replace updates the specified row in the dynamic array. The cursor position must have been established through the use of an open or get position statement.

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.

Note:

For Multiple Row operations, EGL does not support usingKeys.

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.

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 

The following example shows the replace statement for Multiple Row:

employees Employee[0]{rowsetsize=10};
Open resultset1 forUpdate with #sql{ 
    select eID, uName, PASSWORD, fName, lName, office, sex, EMail
    from EMPLOYEETEST
} for employees;
Get Next employees;

//this updates the second row in the row set with current contents
employees[2].uName = test;
Replace employees[2]; 

// this updates the second row in the row set with explicit variable
newName CHAR(20) = test1;
Replace employees[2]
         #with SQL{ update EMPLOYEETEST 
                    set uName = :newName }; 

// the index can be a variable
i int = 2;
Replace employees[i]; 

//this updates all rows in the row set with uName = "test"
//Explicit SQL must be used, otherwise there should be a 
//validation error. 
uName CHAR(10) = test;
Replace employees 
        #with SQL{ update EMPLOYEETEST  
                   set uName = :uName };