In an ANSI-compliant database, a cursor associated with a SELECT statement is an update cursor by default.
The following example declares an update cursor in an ANSI-compliant database:
EXEC SQL declare x_curs cursor for select * from customer_ansi;
To make it clear in the program documentation that this cursor is an update cursor, you can specify the FOR UPDATE option as in this example:
EXEC SQL declare x_curs cursor for select * from customer_ansi for update;
If you want an update cursor to be able to modify only some of the columns in a table, you must specify these columns in the FOR UPDATE option. The following example declares an update cursor and specifies that this cursor can update only the fname and lname columns in the customer_ansi table:
EXEC SQL declare y_curs cursor for select * from customer_ansi for update of fname, lname;
If you want a cursor to be a read-only cursor, you must override the default behavior of the DECLARE statement by specifying the FOR READ ONLY option in your DECLARE statement. The following example declares a read-only cursor:
EXEC SQL declare z_curs cursor for select * from customer_ansi for read only;
The PREPARE statement lets you assemble the text of an SQL statement at runtime and pass the statement text to the database server for execution. If you anticipate that a dynamically prepared SELECT, EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement that returns values could produce more than one row of data, the prepared statement must be associated with a cursor. (See PREPARE.)
The result of a PREPARE statement is a statement identifier (statement id or id variable), which is a data structure that represents the prepared statement text. To declare a cursor for the statement text, associate a cursor with the statement identifier.
You can associate a sequential cursor with any prepared SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement. You cannot associate a scroll cursor with a prepared INSERT statement or with a SELECT statement that was prepared to include a FOR UPDATE clause.
After a cursor is opened, used, and closed, a different statement can be prepared under the same statement identifier. In this way, it is possible to use a single cursor with different statements at different times. The cursor must be redeclared before you use it again.
The following example contains ESQL/C code that prepares a SELECT statement and declares a sequential cursor for the prepared statement text. The statement identifier st_1 is first prepared from a SELECT statement that returns values; then the cursor c_detail is declared for st_1.
EXEC SQL prepare st_1 from
'select order_date
from orders where customer_num = ?';
EXEC SQL declare c_detail cursor for st_1;
If you want to use a prepared SELECT statement to modify data, add a FOR UPDATE clause to the statement text that you want to prepare, as the following ESQL/C example shows:
EXEC SQL prepare sel_1 from 'select * from customer for update'; EXEC SQL declare sel_curs cursor for sel_1;
The DECLARE statement allows you to declare a cursor for an ESQL/C collection variable. Such a cursor is called a collection cursor. You use a collection variable to access the elements of a collection (SET, MULTISET, LIST) column. Use a cursor when you want to access one or more elements in a collection variable.
The Collection-Derived Table segment identifies the collection variable for which to declare the cursor. For more information, see Collection-Derived Table.
Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]