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.
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 must be provided for the question marks 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 can evaluate the SELECT statement accordingly. If Descriptor Area (SQLDA) is used, then the values that replace the question marks are taken from variables pointed to by the sqlvar elements of the descriptor. Allocate the SQLDA and the variables to which the sqlvar elements point and place values in the variables before using the descriptor in an OPEN CURSOR statement.
The same cursor can be opened and closed (with the CLOSE statement) any number of times in a single program. It must be closed, however, before it can be reopened.
A string constant or a host language variable can be used to represent cursor_name. This statement must be terminated according to the rules of your host language.
Last modified date: 11/09/2022