OPEN
Valid in: ESQL
The OPEN statement opens a cursor for processing.
The OPEN statement has the following format:
Non-dynamic version:
EXEC SQL OPEN cursor_name [FOR READONLY];
Dynamic version:
EXEC SQL OPEN cursor_name [FOR READONLY]
[USING variable {, variable} |
USING DESCRIPTOR descriptor_name];
FOR READONLY
Opens the cursor for reading only, even though the cursor may have been declared for update. This clause improves the performance of data retrieval, and can be used whenever appropriate.
USING variable {,variable} | USING DESCRIPTOR descriptor_name
Provides values for the constants that are in the prepared SELECT statement.
Description
The OPEN statement executes the SELECT statement specified when the cursor was declared and positions the cursor immediately before the first row returned. (To actually retrieve the rows, use the FETCH statement.)
A cursor must be opened before it can be used in any data manipulation statements (such as FETCH, UPDATE, or DELETE) and a cursor must be declared before it can be opened.
When a cursor that was declared for a dynamically prepared SELECT statement is opened, use the USING clause if the prepared SELECT statement contains constants specified with question marks. For information about using question marks to specify constants in prepared statements, see
PREPARE.
The USING clause provides the values for these “unspecified” constants in the prepared SELECT so that the OPEN statement can execute the SELECT. For example, assume that your application contains the following dynamically prepared SELECT statement:
statement_buffer =
'select * from' + tablename + 'where low < ?
and high > ?';
exec sql prepare sel_stmt from :statement_buffer;
When opening the cursor for this prepared SELECT statement, values for the question marks must be provided in the WHERE clause. The USING clause performs this task. For example:
declare the cursor for sel_stmt;
assign values to variables named "low" and "high";
exec sql open cursor1
using :low, :high;
The values in the low and high variables replace the question marks in the WHERE clause and the DBMS Server evaluates the SELECT statement accordingly. If an SQLDA is used, the values that replace the question marks are taken from variables to which the sqlvar elements point. Before using the descriptor in an OPEN CURSOR statement, allocate the SQLDA and the variables to which the sqlvar elements point, and place values in the variables. For more information about the SQLDA and its sqlvar elements, see the chapter “Working with Embedded SQL.”
More than one cursor can be opened at the same time, but only one cursor that has been declared for update in deferred mode can be open at a time. If a cursor that has been declared for update in deferred mode is open, all other open cursors must have been declared for readonly or for update in direct mode.
The same cursor can be opened and closed (with the CLOSE statement) successive times in a single program. An open cursor must be closed before it can be reopened.
A string constant or a host language variable can be used to specify the cursor_name. The open statement must be terminated according to the rules of your host language.
Permissions
This statement is available to all users.
Locking
If FOR READONLY is not specified, the DBMS Server can take exclusive locks while the cursor is open.
Related Statements
CLOSE
DECLARE CURSOR
FETCH
UPDATE
Last modified date: 04/26/2024