Back to all posts

Firebird BLOB Guide in isql and DbSchema

For a developer who stores files or long text in Firebird and works in isql.

On this page

A Firebird VARCHAR stops at 32,765 bytes and a whole row at 64 KB, so the first PDF or long log file you try to store fails on the column width. A BLOB column sits outside both limits: the row keeps an 8-byte id, and the content lives on pages of its own. The subtype tells Firebird whether those bytes are text or something to leave alone:

DeclarationSubtypeUse it for
BLOB or BLOB SUB_TYPE BINARY0images, PDFs, archives, any bytes
BLOB SUB_TYPE TEXT1text longer than a VARCHAR
BLOB CHARACTER SET UTF81text, because a character set implies TEXT
BLOB SUB_TYPE -1 to -32768customformats of your own

The examples below ran in isql on Firebird 4.0.1, in a database created as in how to create a Firebird database. Firebird 5.0 takes the same syntax, and the one function that needs a later release says so.

Declaring a BLOB column

CREATE TABLE ATTACHMENTS (
  ID INTEGER NOT NULL,
  FILENAME VARCHAR(60) NOT NULL,
  BODY BLOB SUB_TYPE TEXT,
  SCAN BLOB SUB_TYPE BINARY,
  CONSTRAINT PK_ATTACHMENTS PRIMARY KEY (ID)
);

BODY holds text and SCAN holds files. A text BLOB also takes a CHARACTER SET and a COLLATE clause, as a VARCHAR does. The binary data types section of the Firebird 5.0 Language Reference lists these rules.

The syntax also accepts SEGMENT SIZE. That reference calls the clause "a throwback to times past": the program that writes the BLOB decides the segment size now, so leave it out.

How large one BLOB can grow depends on the database page size. The Firebird technical specifications give just under 4 GB for 4 KB pages and more than 128 GB for 16 KB pages, and the language reference adds that most built-in functions cannot address data past 4 GB.

How isql shows and saves a BLOB

INSERT INTO ATTACHMENTS (ID, FILENAME, BODY)
  VALUES (1, 'notes.txt', 'Ship the invoice on Monday.');
INSERT INTO ATTACHMENTS (ID, FILENAME, BODY)
  VALUES (2, 'empty.txt', '');
COMMIT;
SELECT ID, BODY FROM ATTACHMENTS WHERE ID = 1;

A string literal goes into a text BLOB as it would into a VARCHAR. Selecting the row prints the blob id in the column and the content in a block underneath:

          ID              BODY
============ =================
           1              80:0
==============================================================================
BODY:
Ship the invoice on Monday.
==============================================================================

The id is two hexadecimal numbers. The first is the table's relation id: 80 is 128, the RDB$RELATION_ID of ATTACHMENTS in this database. The second numbers the BLOB. Whether isql prints the content under the id depends on one setting:

  • SET BLOBDISPLAY 1, the default, prints the content of text BLOBs.
  • SET BLOBDISPLAY ALL prints every subtype, binary ones as raw bytes.
  • SET BLOBDISPLAY OFF prints the id alone.

Under the default, a binary BLOB shows "BLOB display set to subtype 1. This BLOB: subtype = 0" in place of its bytes.

Two isql commands take the id. BLOBDUMP 80:0 notes.txt copies the BLOB into a file, 27 bytes here, and BLOBVIEW 80:0 opens it in an external editor and blocks isql until you close the editor. BLOBDUMP checks nothing about the content: give a text BLOB a .jpg name and you get a file that no image viewer opens. The isql manual documents these commands for Firebird up to and including 5.0.

What a text BLOB can do in a query

A text BLOB works almost everywhere a VARCHAR does. The comparison operators, ||, BETWEEN, IN, IS [NOT] DISTINCT FROM, LIKE, CONTAINING and STARTING WITH all accept it, so filtering on the content is an ordinary WHERE clause:

SELECT ID, FILENAME
FROM ATTACHMENTS
WHERE BODY = 'Ship the invoice on Monday.';
IDFILENAME
1notes.txt

OCTET_LENGTH gives the size in bytes, as a BIGINT when the argument is a BLOB:

SELECT ID, FILENAME, OCTET_LENGTH(BODY) AS BYTES
FROM ATTACHMENTS
ORDER BY ID;
IDFILENAMEBYTES
1notes.txt27
2empty.txt0

To get a VARCHAR back, cast the BLOB. The cast fails when the text is longer than the target, so size the VARCHAR for the longest value or cut the text first with SUBSTRING:

SELECT CAST(BODY AS VARCHAR(10)) AS TXT FROM ATTACHMENTS WHERE ID = 1;
Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation
-expected length 10, actual 27

Sorting and grouping are where a BLOB stops acting like a string. DISTINCT, GROUP BY and ORDER BY work on the blob id, not on the content. Add a second row with the same text and count it both ways:

INSERT INTO ATTACHMENTS (ID, FILENAME, BODY)
  VALUES (3, 'copy.txt', 'Ship the invoice on Monday.');
SELECT COUNT(DISTINCT BODY) AS BY_ID,
       COUNT(DISTINCT CAST(BODY AS VARCHAR(100))) AS BY_TEXT
FROM ATTACHMENTS;
BY_IDBY_TEXT
32

Three ids hold two different texts, and GROUP BY BODY returns three groups of one row each, because every row points to a BLOB of its own:

Rows 1 and 3 of ATTACHMENTS hold the blob ids 80:0 and 80:2, which point to two separate BLOBs with the same text

To compare the text itself, group or sort on CAST(BODY AS VARCHAR(100)), which puts rows 1 and 3 in one group.

Appending with || builds a new BLOB:

UPDATE ATTACHMENTS SET BODY = BODY || ' Confirmed.' WHERE ID = 1;
SELECT OCTET_LENGTH(BODY) AS BYTES FROM ATTACHMENTS WHERE ID = 1;
BYTES
38

Each || creates an intermediate BLOB, which costs memory and database space when a procedure builds a value piece by piece. BLOB_APPEND(BODY, ' Confirmed.') returns a BLOB left open for writing, so a chain of calls keeps adding to one object, and it treats a NULL argument as an empty string where || returns NULL. Firebird 4.0.2 added it; on 4.0.1 the same UPDATE fails with "Function unknown".

Loading a file into a binary BLOB

isql has no command that reads a file into a column, and a literal cannot carry a real file. An INSERT with a 70,000-character literal fails before it runs:

Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-String literal with 70000 bytes exceeds the maximum length of 65535 bytes

A hexadecimal literal such as x'CAFEBABE' puts a few bytes into SCAN, which is enough for a test row. For a real file, a client program reads the bytes and passes them as a statement parameter. With firebird-driver, the Python driver for Firebird 3.0 and later, the open file can be the parameter itself, and the driver streams it into the BLOB:

from firebird.driver import connect

with connect('localhost:/path/to/blobs.fdb', user='SYSDBA', password='your_password') as con:
    cur = con.cursor()
    with open('scan.jpg', 'rb') as f:
        cur.execute('INSERT INTO ATTACHMENTS (ID, FILENAME, SCAN) VALUES (?, ?, ?)',
                    (4, 'scan.jpg', f))
    con.commit()

Replace the database path, the user and the password with your own. The driver loads the Firebird client library, so the machine that runs the script needs the Firebird client installed. The older fdb package does not support Firebird 4.0 or later.

Who may read and write a BLOB column

A BLOB column needs no privilege of its own. Reading it takes SELECT on the table, and writing takes INSERT or UPDATE:

GRANT SELECT, INSERT ON ATTACHMENTS TO READER;
GRANT UPDATE (BODY) ON ATTACHMENTS TO READER;

UPDATE can name columns, SELECT cannot. GRANT SELECT (BODY) ON ATTACHMENTS TO READER fails with a syntax error at the parenthesis:

Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 14
-(

A user who may read the table therefore reads every BLOB in it. To hide a BLOB from some users, keep it in a table of its own, or grant them a view that leaves it out.

BLOB columns in DbSchema

Connect DbSchema to the Firebird database and it reverse-engineers the schema into a diagram, BLOB columns included. To add a BLOB column, double-click the table header, press Add in the Columns tab, and choose BLOB SUB_TYPE 0 or BLOB SUB_TYPE 1 as the data type. For a new table, right-click the diagram and choose Create Table. While DbSchema is connected, OK runs the change in the database at once; offline, it stays in the design model, and saving that model to a file is a Pro feature.

The DbSchema column dialog for a new column, with its name, comment, data type and Mandatory (Not Null) box

To see and change the rows, right-click the table on the diagram and choose Data Explorer. Press + at the bottom of the table to add a row, fill in the values, and press the save button next to +. Right-click a BLOB cell to view it: DbSchema shows an image or plain text itself, or opens the content in the application that your system uses for the extension you give. The same menu replaces the value with a file from disk. Changes in the Data Explorer run in the database at once. The Data Explorer is in DbSchema Pro.

A new row in the DbSchema Data Explorer, with the save, add and cancel buttons at the bottom of the table

A BLOB is easier to check when you see it in its row than as 80:0 in a terminal. Download DbSchema at https://dbschema.com/download.html, connect to your Firebird database, and open the table in the Data Explorer of DbSchema Pro to look at a stored file or replace it.

DbSchema ER diagram designer DbSchema ER diagram designer

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download