Example of Deleting from a Collection in ESQL/C

Suppose that the set_col column of a row in the table1 table is defined as a SET and for one row contains the values {1,8,4,5,2}. The following code fragment uses an update cursor and a DELETE statement with a WHERE CURRENT OF clause to delete the element whose value is 4:

EXEC SQL BEGIN DECLARE SECTION;
   client collection set(smallint not null) a_set;
   int an_int;
EXEC SQL END DECLARE SECTION;
...
EXEC SQL allocate collection :a_set; 
EXEC SQL select set_col into :a_set from table1 where int_col = 6;
EXEC SQL declare set_curs cursor for
   select * from table(:a_set) for update;

EXEC SQL open set_curs;
while (i<coll_size)
{
   EXEC SQL fetch set_curs into :an_int;
   if (an_int = 4)
   {
      EXEC SQL delete from table(:a_set) where current of set_curs;
      break;
   }
   i++;
}

EXEC SQL update table1 set set_col = :a_set 
   where int_col = 6;
EXEC SQL deallocate collection :a_set;
EXEC SQL close set_curs;
EXEC SQL free set_curs;