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:
| Declaration | Subtype | Use it for |
|---|---|---|
BLOB or BLOB SUB_TYPE BINARY | 0 | images, PDFs, archives, any bytes |
BLOB SUB_TYPE TEXT | 1 | text longer than a VARCHAR |
BLOB CHARACTER SET UTF8 | 1 | text, because a character set implies TEXT |
BLOB SUB_TYPE -1 to -32768 | custom | formats 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 ALLprints every subtype, binary ones as raw bytes.SET BLOBDISPLAY OFFprints 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.';
| ID | FILENAME |
|---|---|
| 1 | notes.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;
| ID | FILENAME | BYTES |
|---|---|---|
| 1 | notes.txt | 27 |
| 2 | empty.txt | 0 |
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_ID | BY_TEXT |
|---|---|
| 3 | 2 |
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:
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.
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 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.

