Selecting Data from an Array

Go Up to Accessing Arrays

To select data from an array, perform the following steps:

1. Declare a host-language array variable of the correct size to hold the array data. For example, the following statements create three such variables:

   EXEC SQL
   BEGIN DECLARE SECTION;
   BASED ON TABLE1.CHAR_ARR char_arr;
   BASED ON TABLE1.INT_ARR int_arr;
   BASED ON TABLE1.FLOAT_ARR float_arr;
   EXEC SQL
   END DECLARE SECTION;
   

2. Declare a cursor that specifies the array columns to select. For example,

   EXEC SQL
   DECLARE TC1 CURSOR FOR
   SELECT NAME, CHAR_ARR[], INT_ARR[]
   FROM TABLE1;
   

   Be sure to include brackets ([]) after the array column name to select the array data. If the brackets are left out, InterBase reads the array ID for the column, instead of the array data.

   The ability to read the array ID, which is actually a Blob ID, is included only to support applications that access array data using InterBase API calls.
3. Open the cursor, and fetch data:

   EXEC SQL
   OPEN TC1;
   EXEC SQL
   FETCH TC1 INTO :name, :char_arr, :int_arr;
   

   Note:
   It is not necessary to use a cursor to select array data. For example, a singleton SELECT might be appropriate, too.
   When selecting array data, keep in mind that InterBase stores elements in row-major order. For example, in a 2-dimensional array, with 2 rows and 3 columns, all 3 elements in row 1 are returned, then all three elements in row two.

Advance To:

• Inserting Data into an Array