OPEN statement

Use the OPEN statement to activate a cursor.

Syntax

(1)
Notes:
  • 1 HCL OneDB™ extension
  • 2 ESQL/C only

Element Description Restrictions Syntax
cursor_id Name of a cursor Must have been declared by the DECLARE statement Identifier
cursor_id_var Host variable = cursor_id Must be a character data type Language specific
descriptor Name of a system-descriptor area Must have been allocated Quoted String
descriptor_var Host variable that identifies the system-descriptor area System-descriptor area must have been allocated Quoted String
parameter_var Host variable whose contents replace a question ( ? ) mark placeholder in a prepared SQL statement Must be a character or collection data type Language specific
sqlda_pointer Pointer to sqlda structure defining data type and memory location of values to replace question ( ? ) marks in a prepared statement Cannot begin with a dollar ( $ ) sign nor with a colon ( : ). You must use an sqlda structure with dynamic SQL statements. DESCRIBE statement

Usage

Use this statement with or with SPL.

A cursor is an identifier associated with an SQL statement that returns an ordered set of values. The OPEN statement activates a cursor that the DECLARE statement defined.

Cursor can be classified by their associated SQL statements:
  • A Select cursor: a cursor that is associated with a SELECT statement
  • A Function cursor: a cursor that is associated with the EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement
  • An Insert cursor: a cursor that is associated with the INSERT statement
  • A Collection cursor: a Select or Insert cursor that operates on a collection variable.

In UDRs written in the SPL language, the OPEN statement can reference only Select or Function cursors, and these must specify the identifier of the cursor, rather than a variable that stores the cursor_id. The OPEN statement cannot reference a direct cursor that the FOREACH statement of SPL has declared.

The specific actions that the database server takes differ, depending on the statement with which the cursor is associated. In ESQL/C, when you associate one of the previous statements with a cursor directly (that is, you do not prepare the statement and associate the statement identifier with the cursor), the OPEN statement implicitly prepares the statement. (This is not a feature of OPEN in SPL routines, where the DECLARE statement associates a cursor with the identifier of an existing prepared statement, rather than directly with SQL statement text.)

In an ANSI-compliant database, you receive an error code if you try to open a cursor that is already open.