DbSchema | Firebird - Binary Large Objects (BLOBs)?

Firebird- How to Apply Binary Large Objects (BLOBs) in isql and DbSchema

Introduction
Prerequisites
Understanding Binary Large Objects (BLOBs)
Purpose of Using Binary Large Objects
Restrictions and Permissions for Using BLOBs
Advantages and Limitations of Using BLOBs
BLOBs Datatypes
Applying Binary Large Objects in isql
Applying Binary Large Objects in DbSchema
Conclusion
References

Introduction

Binary Large Objects (BLOBs) are a datatype available in many relational databases including Firebird, which is used for storing large binary data such as images, documents, audio, and more. In this article, we will explore the application of BLOBs in isql and DbSchema, two commonly used tools for interacting with Firebird databases.

Prerequisites

To follow along, it is essential to have the following:

Firebird database server installed and running.
Basic understanding of SQL and how to use isql and DbSchema tools.
Basic knowledge about BLOBs and binary data.

For installation and establishing connection you can read our article Firebird-How to create a database?

Understanding Binary Large Objects (BLOBs)

BLOB stands for Binary Large Object. It’s a collection of binary data stored as a single entity in a database. BLOBs are typically images, audio files, video files, or other multimedia objects; they can also be executable code or entire software applications.

Purpose of Using Binary Large Objects

The primary purpose of BLOBs is to store large binary data that doesn’t fit conveniently into traditional database field types such as integer, string, or date. BLOBs are especially useful when large amounts of raw data need to be stored and retrieved without any modification.

Restrictions and Permissions for Using BLOBs

To use BLOBs in Firebird, you must have the necessary permissions set in the database. The user should have the INSERT, SELECT, and UPDATE permissions on the BLOB columns.

Advantages and Limitations of Using BLOBs

Advantages:

BLOBs provide a convenient way to store and retrieve large binary data.
They are flexible and can hold data of different types and sizes.

Limitations:

BLOBs can consume a lot of memory and slow down database operations.
Not all database operations are applicable to BLOBs (e.g., you can’t use a BLOB field in a WHERE clause).

7. BLOBs Datatypes

BLOB Datatype	Description
BLOB	Stores binary data for input or output in byte format.
BLOB TEXT	Stores unformatted characters as string data.

Applying Binary Language Objects in isql

Step 1: Create a Table with a BLOB Column

We start by creating a table with a BLOB column. Assume you’re logged into isql and connected to your Firebird database. Run the following SQL command to create a table named Files with an ID and FileData column:

CREATE TABLE Files (
  ID INT NOT NULL PRIMARY KEY,
  FileData BLOB
);

Step 2: Insert BLOB Data

Now we will insert some BLOB data. Firebird doesn’t have a LOAD_FILE() function like some other DBMS, so we need to handle this differently. You need to read the file data using a programming language of your choice (like Python, PHP, etc.), convert the data to binary, and then insert it into the database. Below is a Python example:

import fdb

def write_file(data, file):
    with open(file, 'wb') as f:
        f.write(data)

def read_file(file):
    with open(file, 'rb') as f:
        photo = f.read()
    return photo

def insert_blob(file):
    try:
        # Connect to the Firebird database
        firebird_connection = fdb.connect(dsn='localhost:/path/to/your/firebird.fdb',
                                          user='your_username', password='your_password')
        cursor = firebird_connection.cursor()
        print("Connected to Firebird")

        firebird_insert_blob_query = """ INSERT INTO Files
                                      (ID, FileData) VALUES (?, ?)"""

        fileData = read_file(file)
        # Convert data into binary format
        converted_picture = fdb.Binary(fileData)
        data_tuple = (1, converted_picture)
        cursor.execute(firebird_insert_blob_query, data_tuple)
        firebird_connection.commit()
        print("Image and file inserted successfully as a BLOB into a table")
        cursor.close()

    except fdb.Error as error:
        print("Failed to insert blob data into Firebird table", error)
    finally:
        if (firebird_connection):
            firebird_connection.close()
            print("the Firebird connection is closed")

insert_blob("/path/to/your/file")

Please make sure to replace ‘localhost:/path/to/your/firebird.fdb’, your_username, and your_password with the appropriate values for your Firebird database connection. Additionally, ensure that the table structure and column names (Files, ID, FileData) match your Firebird database schema.

Step 3: Retrieve BLOB Data

Retrieving the BLOB data is more straightforward and can be done directly in isql. Run the following SQL command:

SELECT ID, FileData FROM Files WHERE ID = 1;

The above command will return the binary data stored in the FileData column for the row with ID = 1. To use this data, you would need to convert it back to its original format using a suitable application or programming language.

Applying Binary Language Objects in DbSchema

Working with BLOBs in DbSchema involves a slightly different process, since it offers a more graphical interface.

Step 1: Create a Table with a BLOB Column

In DbSchema, navigate to Layouts and click on Create New Layout. On the new layout, click Create Table. Name the table Files. Add two columns: ID (set as primary key) and FileData (set type as BLOB). Save the table.

Step 2: Insert BLOB Data

To insert BLOB data in DbSchema, you need to use the data editor. Right-click on the Files table in the layout and select Open in Data Editor. In the opened Data Editor window, click on Insert Row. Fill the ID field with a suitable value, click on the FileData field and select Import BLOB. Navigate to the file you want to import and select Open.

Step 3: Retrieve BLOB Data

Retrieving BLOB data is also done in the Data Editor. Right-click on the desired cell in the FileData column and select Export BLOB. Choose a location on your file system where you want to export the BLOB, give it a name, and click Save.

Sample Result

Here is a sample result of what you might see in DbSchema’s Data Editor after inserting a BLOB:

ID	FileData
1	(BLOB)

In isql, a similar SELECT query might output something like this:

ID	FileData
1	BINARY DATA

In both cases, the actual binary data isn’t displayed directly, but you can be sure it’s there and has been correctly stored and retrieved.

Visually Manage Firebird using DbSchema

DbSchema is a Firebird client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.

Key Features of DbSchema:

Following are the key features of DbSchema which distinguish it from other database GUI tools.

Conclusion

BLOBs are extremely useful for storing large binary data in Firebird databases. Both isql and DbSchema provide effective ways to handle BLOB data, each with its own strengths. While isql provides a quick and scriptable way to work with BLOBs, DbSchema offers a more visual, user-friendly experience.