Array Types - Firebird

Firebird allows you to create homogeneous arrays of most data types. Using an array enables multiple data items to be stored as discrete, multi-dimensional elements in a single column. Firebird can perform operations on an entire array, effectively treating it as a single element, or it can operate on an array slice, a subset of array elements. An array slice can consist of a single element or a set of many contiguous elements.

ARRAY Types and SQL

Because Firebird does not implement any dynamic SQL syntax for operating on ARRAY types, performing DML and searches on them from a dynamic SQL (DSQL) interface is not simple. The Firebird API surfaces structures and functions to enable dynamic applications to work with them directly. Some RAD data access components—for example, IB Objects for use with Borland’s Delphi and Kylix products —provide classes encapsulating this API functionality as client-side properties and methods.

ESQL, which does not use the API structures and function calls, supports several static SQL syntaxes for operating on ARRAY types and integrating them with arrays declared in the host language.

For both dynamic and static applications, it is feasible, albeit not very practicable, to read array data into a stored procedure and return values that the client application can use. An example appears later in the section “Limited Dynamic SQL Access.”

When to Use an Array Type

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.
  • There is no requirement to access the values individually in triggers or stored procedures, or you have external functions that enable such access.

Eligible Element Types

An ARRAY can contain elements of any Firebird data type except BLOB. Arrays ofARRAY are not supported. All of the elements of a particular array are of the same data type.

Defining Arrays

An array can be defined as a domain (using CREATE DOMAIN) or as a column, in a CREATE TABLE or ALTER TABLE statement. Defining an array domain or column is similar to defining any other, with the additional inclusion of the array dimensions. Array dimensions are enclosed in square brackets following data type specification.

For example, the following statement defines both a regular character column and a one-dimensional character ARRAY column containing eight elements:

CREATE TABLE ATABLE (ID BIGINT, ARR_CHAR(14)[8] CHARACTER SET OCTETS); /* stores 1 row * 8 elements */

Multi-Dimensional Arrays

Firebird supports multi-dimensional arrays, which are arrays with 1 to 16 dimensions. For example, the following statement defines three INTEGER ARRAY columns with two, three, and four dimensions, respectively:

Firebird stores multi-dimensional arrays in row-major order. Some host languages, such as FORTRAN, expect arrays to be in column-major order. In these cases, care must be taken to translate element ordering correctly between Firebird and the host language.

Specifying Subscript Ranges for Dimensions

Firebird’s array dimensions have a specific range of upper and lower boundaries, called Subscripts. Dimensions are 1-based by default—that is, the first element of the dimension array of n elements has subscript 1, the second element has subscript 2, and the last element has subscript n. For example, the following statement creates a table with a column that is an array of four integers:

The subscripts for this array are 1, 2, 3, and 4.

Custom (Explicit) Subscript Boundaries

A custom set of upper and lower boundaries can be defined explicitly for each array dimension when an ARRAY column is created. For example, C and Pascal programmers, familiar with zero-based arrays, might want to create ARRAY columns with a lower boundary of zero to map transparently to array structures in application code.

Both the lower and upper boundaries of the dimension are required when defining custom boundaries, using the following syntax:

[lower:upper]

The following example creates a table with a single-dimension, zero-based ARRAY column:

Each dimension’s set of subscripts is separated from the next with commas. For example, the following statement creates a table with a two-dimensional ARRAY column where both dimensions are zero-based:

Storage of ARRAY Columns

As with other types implemented as BLOB, Firebird stores an array ID with the non- BLOB column data of the database table, that points to the page(s) containing the actual data.

Updates

As with other BLOB types, the Firebird engine cannot course through the data seeking successive individual target elements for conditional or selective update. However, in a single DML operation, it is possible to isolate one element or a set of contiguous elements, known as a slice, and target that slice for update.

Inserts

INSERT cannot operate on a slice. When a row is inserted in a table containing ARRAY columns, it is necessary to construct and populate the array entirely, before passing it in the INSERT statement.

Accessing Array Data

Some application interfaces do provide encapsulation of the API functions and descriptors, and limited read access is possible from stored procedures.

The Array Descriptor

The API exposes the array descriptor structure for describing to the server the array or array slice to be read from or written to the database. It is presented to programmers in ibase.h, as follows (comments added):

The InterBase 6 API Guide, published by Borland Software Corporation, provides detailed instructions for manipulating arrays through the API structures.

More information about using arrays in embedded applications can be obtained from the Embedded SQL Guide, a companion volume in the Borland set.

Limited Dynamic SQL Access

The following example is a simple demonstration of how a DSQL application can get limited read access to an array slice through a stored procedure:


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics