OPEN statement Format 2 (Open cursor)
Function
Opens a cursor. The OPEN statement opens a cursor either declared in the DECLARE CURSOR statement or allocated by the ALLOCATE CURSOR statement, and positions the cursor before the first row of retrieval results so that the retrieval results can be fetched.
In Format 2, the OPEN instruction opens a cursor by using the SQL descriptor area to assign a value to the ? parameter.
Privileges
- Users with the SELECT privilege
To open a cursor, the user must have the SELECT privilege for all tables that are included in the SELECT statement specified in the cursor declaration or in the ALLOCATE CURSOR statement.
Format 2: Opening a cursor (by assigning values to ? parameters using an SQL descriptor area)
OPEN cursor-name USING DESCRIPTOR [:]SQL-descriptor-area-name
Operands
- {cursor-name | extended-cursor-name}
- cursor-name
- Specifies the name of the cursor to be opened.
- extended-cursor-name
- Specifies the extended cursor name that identifies the cursor to be opened.
- For extended cursor names, see 2.27 Extended cursor name.
- [:]SQL-descriptor-area-name
- SQL-descriptor-area-name
- Specifies the name of the SQL descriptor area in which is stored the information needed in order to assign values to the ? parameters specified in the SELECT statement preprocessed by the PREPARE statement.
- The variables specified in the SQLVAR array of the SQL descriptor area assign values to the ? parameters that are specified in the prepared SELECT statement in the order in which they are specified.
Notes
- To reopen a cursor that is already open, it must be closed and then reopened.
- For details about the trigger for closing a cursor by issuing a CLOSE statement internally, see Common rule 1 in CLOSE statement (Close cursor) in this chapter.
- When the FETCH statement is to be executed, the OPEN statement must be used to open a cursor and then the FETCH statement relative to that cursor must be executed.
- Before the OPEN statement is executed, the UAP must set the needed information in the SQL descriptor area. For details about the SQL descriptor area, see the HiRDB Version 8 UAP Development Guide.
- A cursor name, similar to an embedded variable name, is effective only within a compile-unit module. Multiple SQLs relative to the same cursor cannot be used in multiple modules.
- If multiple cursor declarations are made in SQL statement identifiers that have the same name, a cursor that references other RD nodes cannot be opened.
- If more than one cursor is allocated to a given extended statement name, cursors that reference another RD node cannot be opened.
- Multiple holdable cursors cannot be opened for a single table.
Example
Open cursor CR2 in order to fetch retrieval results from a stock table; also specify information (SQLDA) for assigning values to the ? parameters specified in the SELECT statement preprocessed by the PREPARE statement:
OPEN CR2
USING DESCRIPTOR SQLDA