Updating Data in an Array Slice
From InterBase
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:
- 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 theCHAR_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 theINT_ARR
integer array column. - Select the row that contains the array data to modify. For example, the following cursor declaration selects data from the
INT_ARRAY
andCHAR_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 ofCHAR_ARRAY
andINT_ARRAY
, and stores it into thechar_slice
andint_slice
host-language variables, respectively. - Load the host-language variables with new or updated data.
- Execute an
UPDATE
statement to insert data into the array slices. For example, the following statements put data into parts ofCHAR_ARRAY
andINT_ARRAY
, assumingchar_slice
andint_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;
- Commit the changes:
EXEC SQL COMMIT;
The following fragment of the output from this example illustrates the contents of the columns,CHAR_ARR
andINT_ARR
after this operation.