Example of Updating a Collection
1,8,4,5,2
}.
The following Informix® ESQL/C program
changes the element whose value is 4
to a value of 10
:
main
{
EXEC SQL BEGIN DECLARE SECTION;
int a;
collection b;
EXEC SQL END DECLARE SECTION;
EXEC SQL allocate collection :b;
EXEC SQL select set_col into :b from table1
where int_col = 6;
EXEC SQL declare set_curs cursor for
select * from table(:b) for update;
EXEC SQL open set_curs;
while (SQLCODE != SQLNOTFOUND)
{
EXEC SQL fetch set_curs into :a;
if (a = 4)
{
EXEC SQL update table(:b)(x)
set x = 10 where current of set_curs;
break;
}
}
EXEC SQL update table1 set set_col = :b
where int_col = 6;
EXEC SQL deallocate collection :b;
EXEC SQL close set_curs;
EXEC SQL free set_curs;
}
After you execute this Informix ESQL/C program,
the set_col column in table1 contains the values {1,8,10,5,2
}.
This Informix ESQL/C program
defines two collection variables, a and b, and
selects a SET from table1 into b. The WHERE clause ensures
that only one row is returned. Then the program defines a Collection
cursor, which selects elements one at a time from b into a.
When the program locates the element with the value 4
,
the first UPDATE statement changes that element value to 10
and
exits the loop.
In the first UPDATE statement, x is a derived-column name used to update the current element in the collection-derived table. The second UPDATE statement updates the base table table1 with the new collection.
For information on how to use collection host variables in Informix ESQL/C programs, see the discussion of complex data types in the Informix ESQL/C Programmer's Manual.