3.7. Binary Data Types

ⓘ
Note

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>]
  |  [COLLATE <collation name>]

If the SUB_TYPE and CHARACTER SET clauses are absent, then subtype BINARY (or 0) is used. If the SUB_TYPE clause is absent and the CHARACTER SET clause is present, then subtype TEXT (or 1) is used.

Shortened syntax 

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

Formally, the COLLATE clause is not part of the data type declaration, and its position depends on the syntax of the statement.

3.7.1. 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 BINARY (or SUB_TYPE 0) is applied. 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 (or BLOB SUB_TYPE 1). 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. A COLLATE clause is also supported.

Specifying CHARACTER SET without specifying a SUB_TYPE implies SUB_TYPE TEXT.

Custom SubtypesIt 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. Custom subtype aliases can be inserted into the RDB$TYPES table by users with the system privilege CREATE_USER_TYPES.

3.7.2. BLOB Specifics

SizeThe 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 ExpressionsText 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:

  • 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 alsoFILTER, DECLARE FILTER, BLOB_APPEND(), RDB$BLOB_UTIL