DECLARE statement
Use the DECLARE statement of dynamic SQL to declare a cursor and to associate it with an SQL statement that returns a set of rows to an SPL or Informix® ESQL/C or routine.
Syntax
FOR UPDATE or FOR READ ONLY Select Options
[] <Subset of SELECT Statement>[] { FOR READ ONLY | []FOR UPDATE [ OF column ] }
Other Select or Function Options
FOR { [] <SELECT Statement>[] | statement_id | [] { [] | statement_id_var | <EXECUTE PROCEDURE Statement>[] } | [] <EXECUTE FUNCTION Statement>[] }
| Element | Description | Restrictions | Syntax |
|---|---|---|---|
| column | Column to update with cursor | Must exist, but need not be listed in Select list of the Projection clause | Identifier |
| cursor_id | Name declared here for cursor | Must be unique in the routine among names of cursors and prepared objects (and in SPL, among variables) | Identifier |
| cursor_id_var | Variable holding cursor_id | Must have a character data type | Language-specific |
| statement_id | Name of prepared statement | Must have been declared by a PREPARE statement | Identifier |
| statement_id_var | Variable holding statement_id | Must have a character data type | Language-specific |
Usage
Except as noted, sections that follow describe how to use the DECLARE statement in Informix® ESQL/C routines. For information about the more restricted syntax and semantics of the DECLARE statement in SPL routines, see Declaring a Dynamic Cursor in an SPL Routine.
- With an SQL statement, such as SELECT, EXECUTE FUNCTION (or EXECUTE
PROCEDURE), or INSERT.
Each of these SQL statements creates a different type of cursor. For more information, see Overview of Cursor Types.
- With the statement identifier (statement id or statement id
variable) of a prepared statement
You can prepare one of the previous SQL statements and associate the prepared statement with a cursor. For more information, see Associating a Cursor with a Prepared Statement.
- With a collection variable in Informix®
ESQL/C
programs
The name of the collection variable appears in the FROM clause of a SELECT or the INTO clause of an INSERT. For more information, see Associating a Cursor with a Prepared Statement.
DECLARE assigns an identifier to the cursor, specifies its uses, and directs the Informix® ESQL/C preprocessor to allocate storage for it. DECLARE must precede any other statement that references the cursor during program execution.
The cursors and prepared objects that can exist concurrently in a single program are limited by available memory. To avoid exceeding the limit, use the FREE statement to release the resources of prepared statements or cursors that are no longer needed.
An ESQL/C program can consist of one or more source-code files. By default, the scope of reference of a cursor is global to a program, so a cursor that was declared in one source file can be referenced from a statement in another file. If you want to limit the scope of each cursor name to the file where it was declared, you must preprocess each file with the -local command-line option.
EXEC SQL prepare id1 from 'select * from customer';
EXEC SQL declare x cursor for id1;
EXEC SQL declare y scroll cursor for id1;
EXEC SQL declare z cursor with hold for id1;- Invalid use of sequential cursors as scroll cursors
- Use of undeclared cursors
- Invalid cursor names or statement names (empty)
EXEC SQL declare x cursor for select * from customer;
. . .
stcopy("x", s);
EXEC SQL declare :s cursor for select * from customer;A cursor uses the collating order that was in effect when the cursor was declared, even if this is different from the collation of the session at run time.