Updating Data in an Array Slice

Go Up to Accessing Arrays

A subset of elements in an array can be updated with a cursor. To perform an update, follow these steps:

1. Declare a host-language variable to hold the array slice data. For ­example,

   EXEC SQL
   BEGIN DECLARE SECTION;
     char char_slice[11]; /* 11-byte string for CHAR(10) data type */
     long int_slice[2][3];
   EXEC SQL
   END DECLARE SECTION;
   

   The first variable, char_slice, is intended to hold a single element of the CHAR_ARR array column defined in the programming example in the previous section. The second example, int_slice, is intended to hold a six-element slice of the INT_ARR integer array column.
2. Select the row that contains the array data to modify. For example, the following cursor declaration selects data from the INT_ARRAY and CHAR_ARRAY columns:

   EXEC SQL
   DECLARE TC1 CURSOR FOR
   SELECT CHAR_ARRAY[1], INT_ARRAY[1:2,1:3] FROM TABLE1;
   EXEC SQL
   OPEN TC1;
   EXEC SQL
   FETCH TC1 INTO :char_slice, :int_slice;
   

   This example fetches the data currently stored in the specified slices of CHAR_ARRAY and INT_ARRAY, and stores it into the char_slice and int_slice host-language variables, respectively.
3. Load the host-language variables with new or updated data.
4. Execute an UPDATE statement to insert data into the array slices. For example, the following statements put data into parts of CHAR_ARRAY and INT_ARRAY, assuming char_slice and int_slice contain information to insert into the table:

   EXEC SQL
   UPDATE TABLE1
   SET
   CHAR_ARR[1] = :char_slice,
   INT_ARR[1:2,1:3] = :int_slice
   WHERE CURRENT OF TC1;
   

5. Commit the changes:

   EXEC SQL
   COMMIT;
   

   The following fragment of the output from this example illustrates the contents of the columns, CHAR_ARR and INT_ARR after this operation.

Advance To:

• Testing a Value in a Search Condition