|
|
|
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];
The FOR READONLY clause indicates that, though the cursor can have been declared for update, the cursor is being opened for reading only. This clause improves the performance of data retrieval, and must be used whenever appropriate.
|
|
|
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' + table_name + '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.
|
|
|
All users are permitted to use this statement.
|
|
|
If for readonly is not specified, the DBMS Server can take exclusive locks while the cursor is open.
|
|
|
|
|
|
The following are Open statement examples:
exec sql declare cursor1 cursor for
select :one + 1, ename, age
from employee
where age :minage;
...
exec sql open cursor1;
When the open statement is encountered, the variables, one and minage, are evaluated. The first statement that follows the opening of a cursor must be a fetch statement to define the cursor position and retrieve data into the indicated variables:
exec sql fetch cursor1
into :two, :name, :age;
The value of the expression, :one + 1, is assigned to the variable, two, by the fetch.
select_buffer =
'select * from employee where eno = ?';
exec sql prepare select1 from :select_buffer;
exec sql declare cursor2 cursor for select1;
eno = 1234;
exec sql open cursor2 using :eno;