FirebirdSQL logo
 SQL Language StructureCommon Language Elements 

BLOB Specifics

Size

The maximum size of a BLOB field depends on the page size of the database, whether the blob value is created as a stream blob or a segmented blob, and if segmented, the actual segment sizes used when populating the blob.For most built-in functions, the maximum size of a BLOB field is 4 GB, or data beyond the 4 GB limit is not addressable.For a page size of 4 KB (4096 bytes) the maximum size is slightly less than 4 GB.

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 fully supported:

=

(assignment)

=, <>, <, <=, >, >=

(comparison)

||

(concatenation)

BETWEEN,

IS [NOT] DISTINCT FROM,

IN,

ANY | SOME,

ALL

 

As an efficient alternative to concatenation, you can also use BLOB_APPEND() or the functions and procedures of system package RDB$BLOB_UTIL.

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.

See also

FILTER, DECLARE FILTER, BLOB_APPEND(), RDB$BLOB_UTIL

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.