Introduction to Arrays
Go Up to Working with Array Data
Contents
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.
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;
Field | Description |
---|---|
array_desc_version |
Set to |
|
Data type (see below) |
|
Scale for numeric data types |
|
Length in bytes of each array element |
|
NULL-terminated column name |
|
NULL-terminated relation name |
|
Number of array dimensions |
|
Specifies whether array is to be accessed in row- major or column-major order
|
|
Lower and upper bounds for each dimension |
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 |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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 itsarray_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 toisc_array_lookup_desc2
(), except thatisc_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.