Introduction to Arrays

InterBase supports arrays of most datatypes. 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 datatype.
• 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 datatype except Blob, and cannot be an array of arrays. All of the elements of a particular array are of the same datatype.

InterBase supports multi-dimensional arrays, arrays with 1 to 16 dimensions. Multidimensional 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.

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;

Table 8.2 Array Descriptor Fields

Field	Description
array_desc_version	Set to ARRAY_DESC_CURRENT_VERSION
array_desc_dtype	Datatype (see below)
array_desc_scale	Scale for numeric datatypes
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 datatypes in the following table:

Table 8.3 Data Types for Array Descriptors

array_desc_dtype value	Corresponding InterBase Datatype
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_varyingw	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 datatype, 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, /* datatype 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

• Accessing Array Data