Using a Collection Variable to Manipulate Collection Elements

About this task

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

To modify elements in a collection

Procedure

  1. Create a collection variable in your SPL routine or program.
    For information on how to declare a collection variable in , see the HCL OneDB ESQL/C Programmer's Manual. For information on how to define a COLLECTION variable in SPL, see DEFINE.
  2. In , allocate memory for the collection; see ALLOCATE COLLECTION statement.
  3. Optionally, use a SELECT statement to select a COLLECTION column into the collection variable.
    If the variable is an untyped COLLECTION variable, you must perform a SELECT from the COLLECTION column before you use the variable in the collection-derived table segment. The SELECT statement allows the database server to obtain the collection data type.
  4. Use the appropriate data manipulation statement with the collection-derived table segment to add, delete, or update elements in the collection variable.
    To insert more than one element or to update or delete a specific element of a collection, you must use a cursor for the collection variable.
    • For more information on how to use an update cursor with ESQL/C, see DECLARE statement.
    • For more information on how to use an update cursor with SPL, see FOREACH.
  5. After the collection variable contains the correct elements, use an INSERT or UPDATE statement on the table or view that holds the actual collection column to save the changes that the collection variable holds.
    • With UPDATE, specify the collection variable in the SET clause.
    • With INSERT, specify the collection variable in the VALUES clause.

Results

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.