To declare an insert cursor for a collection variable, include the Collection- Derived Table segment in the INSERT statement associated with the collection cursor. An insert cursor can insert one or more elements in the collection. For a description of INSERT syntax and usage, see INSERT .
The insert cursor must be a sequential cursor. That is, the DECLARE statement cannot specify the SCROLL keyword.
When you declare an insert cursor for a collection variable, the Collection- Derived Table clause of the INSERT statement must contain the name of the collection variable. You cannot specify an input parameter (the question-mark ( ? ) symbol) for the collection variable. However, you can use an input parameter in the VALUES clause of the INSERT statement. This parameter indicates that the collection element is to be provided later by the FROM clause of the PUT statement.
A collection cursor that includes an INSERT statement with the Collection- Derived Table clause allows you to insert more than one element into a collection variable.
This example declares an insert cursor for the a_set collection variable:
EXEC SQL BEGIN DECLARE SECTION;
client collection multiset(smallint not null) a_mset;
int an_element;
EXEC SQL END DECLARE SECTION;
...
EXEC SQL declare mset_curs cursor for
insert into table(:a_mset) values (?);
EXEC SQL open mset_curs;
while (1)
{
...
EXEC SQL put mset_curs from :an_element;
...
}
To insert the elements into the collection variable, use the PUT statement with the FROM clause. For a code example that uses a collection cursor for an INSERT statement, see Inserting into a Collection Cursor (IDS).
To roll back a modification, you must perform the modification within a transaction. A transaction in a database that is not ANSI compliant begins only when the BEGIN WORK statement is executed.
In an ANSI-compliant database, transactions are always in effect.
The database server enforces these guidelines for select and update cursors to ensure that modifications can be committed or rolled back properly:
The database server lets you open and close a hold cursor for an update outside a transaction; however, you should fetch all the rows that pertain to a given modification and then perform the modification all within a single transaction. You cannot open and close a hold cursor or an update cursor outside a transaction.
The following example uses an update cursor within a transaction:
EXEC SQL declare q_curs cursor for select customer_num, fname, lname from customer where lname matches :last_name for update; EXEC SQL open q_curs; EXEC SQL begin work; EXEC SQL fetch q_curs into :cust_rec; /* fetch after begin */ EXEC SQL update customer set lname = 'Smith' where current of q_curs; /* no error */ EXEC SQL commit work;
When you update a row within a transaction, the row remains locked until the cursor is closed or the transaction is committed or rolled back. If you update a row when no transaction is in effect, the row lock is released when the modified row is written to disk. If you update or delete a row outside a transaction, you cannot roll back the operation.
In a database that uses transactions, you cannot open an insert cursor outside a transaction unless it was also declared with the WITH HOLD keywords.
Related statements: CLOSE , DELETE, EXECUTE PROCEDURE, FETCH , FREE , INSERT , OPEN , PREPARE, PUT , SELECT, and UPDATE
For discussions of cursors and data modification, see the IBM Informix Guide to SQL: Tutorial.
For more advanced issues related to cursors or using cursors with collection variables, see the IBM Informix ESQL/C Programmer's Manual.