DEALLOCATE PREPARE statement (Nullify the preprocessing of SQL)

Function

Nullifies the SQL statement preprocessed by the PREPARE statement, and releases the SQL statement identifier or the extended statement name from its allocated state.

Privileges

None.

Format

DEALLOCATE PREPARE {SQL-statement-identifier|extended-statement-name}

Operands

SQL-statement-identifier
Specifies the name that was assigned to identify the SQL statement preprocessed by the SQL-statement-identifier PREPARE statement.
For SQL statement identifiers, see 1.1.7 Specification of names.
HiRDB reserved words can also be used. If a HiRDB reserved word is used, the SQL statement identifier, even if it is identical to the reserved word, should not be enclosed in double quotation marks ("). However, the words SELECT and WITH cannot be used.
extended-statement-name
Specifies the extended statement name that was assigned to identify the SQL statement preprocessed by the PREPARE statement.
For extended statement names, see 2.26 Extended statement name.

Common rules

  1. If a cursor exists that was either declared by DECLARE CURSOR for the SQL statement identified by a specified SQL statement identifier or an extended statement name, or that was allocated by an ALLOCATE CURSOR statement, and if that cursor is open, the cursor cannot be nullified.
  2. All cursors that are declared for or allocated to a specified SQL statement or extended statement name and that are closed are also nullified. In addition, all preprocessed SQL statements that reference those cursors are also nullified.

Notes

  1. If the SQL statement for the nullified SQL statement identifier is a holdable cursor, the SQL statement identifier is not enabled, even if it is rolled back.
  2. The SQL statement identifier, similar to embedded variable names, is a name that is in effect within compile-by-compile modules; more than one SQL statement with respect to a given SQL statement identifier cannot be used across multiple modules.

Examples

Deallocate the results of preprocessing of the SQL statement identified by the SQL statement identifier (PRESQL) specified in a PREPARE statement.

DEALLOCATE PREPARE PRESQL