To update a particular element in a collection, declare
an update cursor for the collection host variable.
About this task
An update cursor for a collection variable is a
select cursor that was declared with the FOR UPDATE keywords. The
update cursor allows you to sequentially scroll through the elements
of the collection and update the current element with the UPDATE...WHERE
CURRENT OF statement.
To update elements, follow these steps:
Procedure
Create a client collection variable in your
Informix® ESQL/C program.
Close the update cursor with the CLOSE statement, and if
you no longer need the cursor, free it with the FREE statement.
Results
The application must position the update cursor on the
element to be updated and then use UPDATE...WHERE CURRENT OF to update
this value.
The Informix ESQL/C program
in the following figure uses an update cursor to update an element
in the collection variable, a_set, and then to update
the set_col column of the tab_set table (see Sample tables with collection columns). Figure 1: Updating one
element in a collection host variable
EXEC SQL BEGIN DECLARE SECTION;
int an_element;
client collection set(integer not null) a_set;
EXEC SQL END DECLARE SECTION;
EXEC SQL allocate collection :a_set;
EXEC SQL select set_col into :a_set from tab_set
where id_col = 6;
EXEC SQL declare set_curs cursor for
select * from table(:a_set)
for update;
EXEC SQL open set_curs;
while (SQLCODE != SQLNOTFOUND)
{
EXEC SQL fetch set_curs into :an_element;
if (an_element = 4)
{
EXEC SQL update table(:a_set)(x)
set x = 10
where current of set_curs;
break;
}
}
EXEC SQL close set_curs;
EXEC SQL update tab_set set set_col = :a_set
where id_col = 6;
EXEC SQL deallocate collection :a_set;
EXEC SQL free set_curs;