Using a Collection Variable to Manipulate Collection Elements

When you use data manipulation statements (SELECT, INSERT, UPDATE, or DELETE) of Dynamic Server in conjunction with a collection variable, you can modify one or more elements in a collection.

The collection variable stores the elements of the collection. It has no intrinsic connection, however, with a database column. Once the collection variable contains the correct elements, you must then save the variable into the actual collection column of the table with either an INSERT or an UPDATE statement.

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 ESQL/C 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;

After the DELETE statement executes, this collection variable contains the elements {1,8,5,2}. The UPDATE statement at the end of this code fragment saves the modified collection into the set_col column. Without this UPDATE statement, element 4 of the collection column is not deleted.

Example of Deleting from a Collection

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

CREATE_PROCEDURE test6()

   DEFINE a SMALLINT;
   DEFINE b SET(SMALLINT NOT NULL);
   SELECT set_col INTO b FROM table1
      WHERE id = 6;
      -- Select the set in one row from the table
      -- into a collection variable
   FOREACH cursor1 FOR
      SELECT * INTO a FROM TABLE(b);
         -- Select each element one at a time from
         -- the collection derived table b into a
      IF a = 4 THEN
         DELETE FROM TABLE(b) 
            WHERE CURRENT OF cursor1;
            -- Delete the element if it has the value 4
         EXIT FOREACH;
      END IF;
   END FOREACH;

   UPDATE table1 SET set_col = b
      WHERE id = 6;
      -- Update the base table with the new collection

END PROCEDURE;

This SPL routine declares two SET variables, a and b, each to hold a set of SMALLINT values. The first SELECT statement copies a SET column from one row of table1 into variable b. The routine then declares a cursor called cursor1 that copies one element at a time from b into SET variable a. When the cursor is positioned on the element whose value is 4, the DELETE statement removes that element from SET variable b. Finally, the UPDATE statement replaces the row of table1 with the new collection that is stored in variable b.

For information on how to use collection variables in an SPL routine, see the IBM Informix Guide to SQL: Tutorial.

Example of Updating a Collection

Suppose that the set_col column of a table called table1 is defined as a SET and that it contains the values {1,8,4,5,2}. The following 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 ESQL/C program, the set_col column in table1 contains the values {1,8,10,5,2}.

This 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 an ESQL/C program, see the discussion of complex data types in the IBM Informix ESQL/C Programmer's Manual.

Example of Inserting a Value into a Multiset Collection

Suppose the ESQL/C host variable a_multiset has the following declaration:

EXEC SQL BEGIN DECLARE SECTION;
   client collection multiset(integer not null) a_multiset;
EXEC SQL END DECLARE SECTION;

The following INSERT statement adds a new MULTISET element of 142,323 to a_multiset:

EXEC SQL allocate collection :a_multiset;
EXEC SQL select multiset_col into :a_multiset from table1
   where id = 107;
EXEC SQL insert into table(:a_multiset) values (142323);
EXEC SQL update table1 set multiset_col = :a_multiset
   where id = 107;

EXEC SQL deallocate collection :a_multiset;

When you insert elements into a client-collection variable, you cannot specify a SELECT statement or an EXECUTE FUNCTION statement in the VALUES clause of the INSERT. When you insert elements into a server-collection variable, however, the SELECT and EXECUTE FUNCTION statements are valid in the VALUES clause. For more information on client- and server-collection variables, see the IBM Informix ESQL/C Programmer's Manual.