delete considerations for SQL

When you use the delete statement in the context of SQL, the statement removes a row from a database. You must have previously retrieved the record using a get or open statement with the forUpdate option.

EGL uses information in your SQL record variable to create an SQL DELETE statement in your code that references the current row (where your cursor points). The following SQL is equivalent to the EGL delete statement:

  DELETE FROM tableName
    WHERE CURRENT OF cursor

You do not have the option of rewriting this statement using the #sql directive. Use the EGL execute statement to write a custom SQL DELETE statement.

You cannot use a single EGL delete statement to remove rows from multiple SQL tables.

Syntax

SQLRecordVariable

Refers to an area of memory where EGL has stored retrieved information from a single database row.

SQLDynamicArray

This variable names a dynamic array that is composed of SQL record variables. EGL delete delete the whole dynamic array. The delete statement cannot 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 delete deletes the specified row in the dynamic array. The delete statement cannot use with #sql{ExplicitCode}. The cursor position must have been established through the use of an open or get position statement.

explicitCode

You can embed your own SQL DELETE statement to override the implicit code that EGL generates.

deleteOptions

Use the following options to control the implicit SQL code that EGL generates:

cursor: Tells EGL to create a WHERE CURRENT OF cursorID clause. You must have previously created a result set using get or open, 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 is the default option.
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: This option provides the conditions for an SQL WHERE clause, specifying the fields from SQLRecordVariable. Those fields should match the corresponding columns in the database rows to be deleted.
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.

If you are not working with the default result set that EGL normally creates for this SQL record variable, specify a result set ID.

Example

The following example shows the delete statement for SQL:

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

The following example shows the delete 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;
Delete employees[2]; //this deletes the second row in the row set using 
//WHERE CURRENT OF CS1
i int = 2;
Delete employees[i]; //the index could be a variable
Delete employees;    //this deletes the whole row set from current cursor

Compatibility

Table 1. Compatibility considerations for delete
Platform	Issue
COBOL generation	For best performance in COBOL, always include the from resultSet clause in the delete statement.
SQL Server	Multiple Row delete last and get last cannot be used together. The result set will be closed when both statements are executed once.
DB2® for i5/OS™	Journaling must be enabled or add `transation isolation=none` to the connection string.