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
[CHARACTER SET[:]character-set-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
- When using the SQL descriptor area to assign a value to a ? parameter specified in a SELECT statement preprocessed by the PREPARE statement, specifies the name of the SQL descriptor area that specifies the value to be assigned to the input ? parameter.
- 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.
- [CHARACTER SET[:]character-set-descriptor-area-name]
- character-set-descriptor-area-name
- When using the SQL descriptor area to assign the value of a ? parameter specified in a SELECT statement preprocessed by the PREPARE statement, specifies the name of the character set descriptor area that specifies the character set name to be used for the value being assigned to the input ? parameter.
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 executing the OPEN statement, the UAP must set the required information in the SQL descriptor area and the character set descriptor area. For SQL descriptor areas and character set descriptor areas, see the HiRDB Version 9 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.
- 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