FirebirdSQL logo
 SQL Language StructureCommon Language Elements 

Array Types

Note

Firebird does not offer much in the way of language or tools for working with the contents of arrays, and there are no plans to improve this.This limits the usefulness and accessibility of array types.Therefore, the general advice is: do not use arrays.

The support of arrays in the Firebird DBMS is a departure from the traditional relational model.Supporting arrays in the DBMS could make it easier to solve some data-processing tasks involving large sets of similar data.

Arrays in Firebird are stored in BLOB of a specialized type.Arrays can be one-dimensional and multi-dimensional and of any data type except BLOB and ARRAY.

Example
CREATE TABLE SAMPLE_ARR (
  ID INTEGER NOT NULL PRIMARY KEY,
  ARR_INT INTEGER [4]
);

This example will create a table with a field of the array type consisting of four integers.The subscripts of this array are from 1 to 4.

Specifying Explicit Boundaries for Dimensions

By default, dimensions are 1-based — subscripts are numbered from 1.To specify explicit upper and lower bounds of the subscript values, use the following syntax:

'[' <lower>:<upper> ']'

Adding More Dimensions

A new dimension is added using a comma in the syntax.In this example we create a table with a two-dimensional array, with the lower bound of subscripts in both dimensions starting from zero:

CREATE TABLE SAMPLE_ARR2 (
  ID INTEGER NOT NULL PRIMARY KEY,
  ARR_INT INTEGER [0:3, 0:3]
);

The database employee.fdb, found in the ../examples/empbuild directory of any Firebird distribution package, contains a sample stored procedure showing some simple work with arrays:

PSQL Source for SHOW_LANGS, a procedure involving an array

CREATE OR ALTER PROCEDURE SHOW_LANGS (
  CODE VARCHAR(5),
  GRADE SMALLINT,
  CTY VARCHAR(15))
RETURNS (LANGUAGES VARCHAR(15))
AS
  DECLARE VARIABLE I INTEGER;
BEGIN
  I = 1;
  WHILE (I <= 5) DO
  BEGIN
    SELECT LANGUAGE_REQ[:I]
    FROM JOB
    WHERE (JOB_CODE = :CODE)
      AND (JOB_GRADE = :GRADE)
      AND (JOB_COUNTRY = :CTY)
      AND (LANGUAGE_REQ IS NOT NULL))
    INTO :LANGUAGES;

    IF (LANGUAGES = '') THEN
    /* PRINTS 'NULL' INSTEAD OF BLANKS */
      LANGUAGES = 'NULL';
    I = I +1;
    SUSPEND;
  END
END

If the features described are enough for your tasks, you might consider using arrays in your projects.Currently, no improvements are planned to enhance support for arrays in Firebird.

Special Data Types

“Special” data types …​

SQL_NULL Data Type

The SQL_NULL type holds no data, but only a state: NULL or NOT NULL.It is not available as a data type for declaring table fields, PSQL variables or parameter descriptions.This data type exists to support the use of untyped parameters in expressions involving the IS NULL predicate.

An evaluation problem occurs when optional filters are used to write queries of the following type:

WHERE col1 = :param1 OR :param1 IS NULL

After processing, at the API level, the query will look like this:

WHERE col1 = ? OR ? IS NULL

This is a case where the developer writes an SQL query and considers :param1 as though it were a variable that they can refer to twice.However, at the API level, the query contains two separate and independent parameters.The server cannot determine the type of the second parameter since it comes in association with IS NULL.

The SQL_NULL data type solves this problem.Whenever the engine encounters an “? IS NULL” predicate in a query, it assigns the SQL_NULL type to the parameter, which will indicate that parameter is only about “nullness” and the data type or the value need not be addressed.

The following example demonstrates its use in practice.It assumes two named parameters — say, :size and :colour — which might, for example, get values from on-screen text fields or drop-down lists.Each named parameter corresponds with two positional parameters in the query.

SELECT
  SH.SIZE, SH.COLOUR, SH.PRICE
FROM SHIRTS SH
WHERE (SH.SIZE = ? OR ? IS NULL)
  AND (SH.COLOUR = ? OR ? IS NULL)

Explaining what happens here assumes the reader is familiar with the Firebird API and the passing of parameters in XSQLVAR structures — what happens under the surface will not be of interest to those who are not writing drivers or applications that communicate using the “naked” API.

The application passes the parameterized query to the server in the usual positional ?-form.Pairs of “identical” parameters cannot be merged into one, so for the two optional filters in the example, four positional parameters are needed: one for each ? in our example.

After the call to isc_dsql_describe_bind(), the SQLTYPE of the second and fourth parameters will be set to SQL_NULL.Firebird has no knowledge of their special relation with the first and third parameters: that responsibility lies entirely on the application side.

Once the values for size and colour have been set (or left unset) by the user, and the query is about to be executed, each pair of XSQLVARs must be filled as follows:

User has supplied a value

First parameter (value compare): set *sqldata to the supplied value and *sqlind to 0 (for NOT NULL)

Second parameter (NULL test): set sqldata to null (null pointer, not SQL NULL) and *sqlind to 0 (for NOT NULL)

User has left the field blank

Both parameters: set sqldata to null (null pointer, not SQL NULL) and *sqlind to -1 (indicating NULL)

In other words: The value compare parameter is always set as usual.The SQL_NULL parameter is set the same, except that sqldata remains null at all times.