Binary Data Types

Table of Contents

BLOB Subtypes
BLOB Specifics
ARRAY Type

BLOBs (Binary Large Objects) are complex structures used to store text and binary data of an undefined length, often very large.

Syntax:

     BLOB [SUB_TYPE <subtype>]
         [SEGMENT SIZE <segment size>]
         [CHARACTER SET <character set>]

Shortened syntax:

     BLOB (<segment size>)
     BLOB (<segment size>, <subtype>)
     BLOB (, <subtype>)

Segment Size: Specifying the BLOB segment is throwback to times past, when applications for working with BLOB data were written in C (Embedded SQL) with the help of the gpre pre-compiler. Nowadays, it is effectively irrelevant. The segment size for BLOB data is determined by the client side and is usually larger than the data page size, in any case.

BLOB Subtypes

The optional SUB_TYPE parameter specifies the nature of data written to the column. Firebird provides two pre-defined subtypes for storing user data:

Subtype 0: BINARY: If a subtype is not specified, the specification is assumed to be for untyped data and the default SUB_TYPE 0 is applied. The alias for subtype zero is BINARY. This is the subtype to specify when the data are any form of binary file or stream: images, audio, word-processor files, PDFs and so on..

Subtype 1: TEXT: Subtype 1 has an alias, TEXT, which can be used in declarations and definitions. For instance, BLOB SUB_TYPE TEXT. It is a specialized subtype used to store plain text data that is too large to fit into a string type. A CHARACTER SET may be specified, if the field is to store text with a different encoding to that specified for the database. From Firebird 2.0, a COLLATE clause is also supported.

Custom Subtypes: It is also possible to add custom data subtypes, for which the range of enumeration from -1 to -32,768 is reserved. Custom subtypes enumerated with positive numbers are not allowed, as the Firebird engine uses the numbers from 2-upward for some internal subtypes in metadata.

BLOB Specifics

Size: The maximum size of a BLOB field is limited to 4GB, regardless of whether the server is 32-bit or 64-bit. (The internal structures related to BLOBs maintain their own 4-byte counters.) For a page size of 4 KB (4096 bytes) the maximum size is lower—slightly less than 2GB.

Operations and Expressions: Text BLOBs of any length and any character set—including multi-byte—can be operands for practically any statement or internal functions. The following operators are supported completely:

=	(assignment)
=, <>, <, <=, >, >=	(comparison)
\|\|	(concatenation)
BETWEEN,	IS [NOT] DISTINCT FROM,
IN,	ANY\|SOME,
ALL

Partial support:

An error occurs with these if the search argument is larger than or equal to 32 KB:

STARTING [WITH],	LIKE,
CONTAINING

Aggregation clauses work not on the contents of the field itself, but on the BLOB ID. Aside from that, there are some quirks:

SELECT DISTINCT	returns several NULL values by mistake if they are present
ORDER BY	—
GROUP BY	concatenates the same strings if they are adjacent to each other, but does not do it if they are remote from each other

BLOB Storage:

By default, a regular record is created for each BLOB and it is stored on a data page that is allocated for it. If the entire BLOB fits onto this page, it is called a level 0 BLOB. The number of this special record is stored in the table record and occupies 8 bytes.
If a BLOB does not fit onto one data page, its contents are put onto separate pages allocated exclusively to it (blob pages), while the numbers of these pages are stored into the BLOB record. This is a level 1 BLOB.
If the array of page numbers containing the BLOB data does not fit onto a data page, the array is put on separate blob pages, while the numbers of these pages are put into the BLOB record. This is a level 2 BLOB.
Levels higher than 2 are not supported.

ARRAY Type

Table of Contents

Specifying Explicit Boundaries for Dimensions
Adding More Dimensions

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 multidimensional 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 after 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 DBMS does not offer much in the way of language or tools for working with the contents of arrays. 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.