Update the collection in the database

After you change the contents of a collection variable in an SPL routine (by deleting, updating, or inserting an element), you must update the database with the new collection.

To update a collection in the database, add an UPDATE statement that sets the collection column in the table to the contents of the updated collection variable. For example, the UPDATE statement in the following figure shows how to update the polygons table to set the definition column to the new collection stored in the collection variable vertexes.

Figure 1: Update a collection in the database.

CREATE PROCEDURE shapes()

   DEFINE vertexes SET(point NOT NULL);
   DEFINE pnt point;

   SELECT definition INTO vertexes FROM polygons
      WHERE id = 207;

   FOREACH cursor1 FOR
      SELECT * INTO pnt FROM TABLE(vertexes)
      IF pnt = '(3,4)' THEN
            -- calls the equals function that
            -- compares two values of point type
         DELETE FROM TABLE(vertexes) 
            WHERE CURRENT OF cursor1;
         EXIT FOREACH;
      ELSE
         CONTINUE FOREACH;
      END IF;
   END FOREACH

   UPDATE polygons SET definition = vertexes
      WHERE id = 207;

END PROCEDURE;

Now the shapes() routine is complete. After you run shapes(), the collection stored in the row whose ID column is 207 is updated so that it contains three values instead of four.

You can use the shapes() routine as a framework for writing other SPL routines that manipulate collections.

The elements of the collection now stored in the definition column of row 207 of the polygons table are listed as follows:

'(3,1)'
'(8,1)'
'(8,4)'