Updating Data in an Array Slice

From InterBase
Jump to: navigation, search

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: