Introduction to Arrays

From InterBase

Go Up to Working with Array Data


InterBase supports arrays of most data types. Using an array enables multiple data items to be stored in a single column. InterBase can treat an array as a single unit, or as a series of separate units, called slices. Using an array is appropriate when:

  • The data items naturally form a set of the same data type.
  • The entire set of data items in a single database column must be represented and controlled as a unit, as opposed to storing each item in a separate column.
  • Each item must also be identified and accessed individually.

The data items in an array are called array elements. An array can contain elements of any InterBase data type except Blob, and cannot be an array of arrays. All of the elements of a particular array are of the same data type.

InterBase supports multi-dimensional arrays, arrays with 1 to 16 dimensions. Multi-dimensional arrays are stored in row-major order.

Array dimensions have a specific range of upper and lower boundaries, called subscripts. The array subscripts are defined when an array column is created. For information about creating an array, see the Language Reference Guide.

Note:
Array field types only can be modified with isql. This applies until developer changes IBConsole to FireDAC

Array Database Storage

InterBase does not store array data directly in the array field of a table record. Instead, it stores an array ID there. The array ID is a unique numeric value that references the array data, which is stored elsewhere in the database.

Array Descriptors

An array descriptor describes an array or array subset to be retrieved or written to the ISC_ARRAY_DESC_V2 structure. ISC_ARRAY_DESC_V2 is defined in the InterBase header file as follows:

typedef struct {
short array_desc_version;
unsigned char array_desc_dtype;
char array_desc_scale;
unsigned short array_desc_length;
char array_desc_field_name [METADATALENGTH];
char array_desc_relation_name [METADATALENGTH];
short array_desc_dimensions;
short array_desc_flags;
ISC_ARRAY_BOUND array_desc_bounds [16];
} ISC_ARRAY_DESC_V2;

ISC_ARRAY_BOUND is defined as:

typedef struct {
short array_bound_lower; /* lower bound */
short array_bound_upper; /* upper bound */
} ISC_ARRAY_BOUND;
Array Descriptor Fields
Field Description

array_desc_version

Set to ARRAY_DESC_CURRENT_VERSION

array_desc_dtype

Data type (see below)

array_desc_scale

Scale for numeric data types

array_desc_length

Length in bytes of each array element

array_desc_field_name

NULL-terminated column name

array_desc_relation_name

NULL-terminated relation name

array_desc_dimensions

Number of array dimensions

array_desc_flags

Specifies whether array is to be accessed in row- major or column-major order

  • 0: row-major
  • 1: column-major

array_desc_bounds

Lower and upper bounds for each dimension

Note:
The ISC_ARRAY_DESC_V2 structure supports long metadata names of length METADATALENGTH. The older ISC_ARRAY_DESC structure supports only metadata names of 32 bytes or less.

The array_desc_version field is set to ARRAY_DESC_CURRENT_VERSION by isc_array_lookup_bounds2(), isc_array_lookup_desc2(), and isc_array_set_desc2().

The array_desc_dtype field of an array descriptor must be expressed as one of the data types in the following table:

array_desc_dtype value Corresponding InterBase data type

blr_boolean_dtype

BOOLEAN

blr_blob_id

ISC_QUAD structure

blr_cstring

NULL-terminated string

blr_cstring2

NULL-terminated string

blr_double

DOUBLE PRECISION

blr_float

FLOAT

blr_long

INTEGER

blr_quad

ISC_QUAD structure

blr_short

SMALLINT

blr_sql_date

DATE

blr_sql_time

TIME

blr_text

CHAR

blr_text2

CHAR

blr_timestamp

TIMESTAMP

blr_varying

VARCHAR

blr_varying2

VARCHAR

An array descriptor contains 16 ISC_ARRAY_BOUND structures, one for each possible dimension. An array with <n> dimensions has upper and lower bounds set for the first <n> ISC_ARRAY_BOUND structures. The number of actual array dimensions is specified in the array_desc_dimensions field of the array descriptor.

When you retrieve data from an array, you supply an array descriptor defining the array slice (entire array or subset of contiguous array elements) to be retrieved. Similarly, when you write data to an array, you supply an array descriptor defining the array slice to be written to.

Populating an Array Descriptor

There are four ways to populate an array descriptor:

  • Call isc_array_lookup_desc2(), which looks up (in the system metadata tables) and stores in an array descriptor the data type, length, scale, and dimensions for a specified array column in a specified table. This function also stores the table and column name in the descriptor, and initializes its array_desc_flags field to indicate that the array is to be accessed in row-major order. For example,
isc_array_lookup_desc2( status_vector,
&db_handle, /* Set by isc_attach_database() */
&tr_handle, /* Set by isc_start_transaction() */
"PROJ_DEPT_BUDGET", /* table name */
"QUART_HEAD_CNT", /* array column name */
&desc); /* descriptor to be filled in */
  • Call isc_array_lookup_bounds2(), which is like a call to ­isc_array_lookup_desc2(), except that ­isc_array_lookup_bounds2() also looks up and stores into the array descriptor the upper and lower bounds of each dimension.
  • Call isc_array_set_desc2(), which initializes the descriptor from parameters, rather than by accessing the database metadata. For example,
short dtype = SQL_TEXT;
short len = 8;
short numdims = 2;
isc_array_set_desc2( status_vector,
"TABLE1", /* table name */
"CHAR_ARRAY", /* array column name */
&dtype, /* data type of elements */
&len, /* length of each element */
&numdims, /* number of array dimensions */
&desc); /* descriptor to be filled in */
  • Set the descriptor fields directly. An example of setting the array_desc_dimensions field of the descriptor, desc, is:
desc.array_desc_dimensions = 2;

For complete syntax and information about isc_array_lookup_bounds2(), ­isc_array_lookup_desc2(), and isc_array_set_desc2(), see API Function Reference.


Advance To: