FirebirdSQL logo
 SQL Language StructureCommon Language Elements 

Binary Data Types

Note

The types [fblangref50-datatypes-chartypes-binary] and [fblangref50-datatypes-chartypes-varbinary] are covered earlier in section [fblangref50-datatypes-chartypes].

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>)
Note

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

Specifying the BLOB segment size is a 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 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 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.Custom subtype aliases can be inserted into the RDB$TYPES table by users with the system privilege CREATE_USER_TYPES.