RelationalDbBlobStorage Extension

Introduction

The RelationalDBBlobStorage extension contains the Blob Storage Provider to store Blobs using a relational database.

Note: Check the Database Compatibility of the RelationalDBBlobStorage extension in the Technical Requirements: FNZ Studio 2025 article.

Configuration

Silos

You can configure the required silos by updating the following property:

com.nm.extensions.relationaldbblobstorage.silos

The value of this property represents a comma-separated list of key-value pairs. The first part of the pair represents the name of the silo (e.g. tmp-archive, archive), while the second part is included in the table name in the database.

The tables are automatically created when the extension is started, if the configured user has the correct permissions. Otherwise, the database tables should be manually created before the extension is started.

For example, the following configuration would result in four database tables:

Silo definitions:

com.nm.extensions.relationaldbblobstorage.silos = temp=Temp,archive=Archive 

Database tables:

blobsDataArchive
blobsMetaArchive
blobsDataTemp
blobsMetaTemp

Database Configuration

Before using the RelationalDbBlobStorage extension, you need to set up a database and configure the connection credentials in the extension configuration.

The database schema can be specified either in the connection properties described below or using the following property: com.nm.extensions.relationaldbblobstorage.schema

The default value is empty.

JDBC

If you choose JDBC to connect to the database you have to configure the following properties:

• com.nm.extensions.relationaldbblobstorage.driver.name — driver name to connect to the database
• com.nm.extensions.relationaldbblobstorage.state.script — simple sql script to check that a connection is valid and can be used to execute queries
• com.nm.extensions.relationaldbblobstorage.connection.url — URL to connect to the database
• com.nm.extensions.relationaldbblobstorage.username — database username
• com.nm.extensions.relationaldbblobstorage.password — database password

When the extension starts, it automatically creates the required tables if the database user has access to create tables. If the regular user does not have access to create the table, you can use a special user that has elevated privileges. The connection to the database for this admin user can be configured using the following properties:

com.nm.extensions.relationaldbblobstorage.admin.connection.url
com.nm.extensions.relationaldbblobstorage.admin.username
com.nm.extensions.relationaldbblobstorage.admin.password

The JDBC pool of database connections can be customized using the following properties:

• com.nm.extensions.relationaldbblobstorage.jdbc.connectionpool.maxActive — the maximum number of active database connections. Default is 40.
• com.nm.extensions.relationaldbblobstorage.jdbc.connectionpool.maxIdle — the maximum number of idle database connections. Default is 40.
• com.nm.extensions.relationaldbblobstorage.jdbc.connectionpool.maxWait — the maximum time to wait for a database connection to become available in ms. Default is 10000.
• com.nm.extensions.relationaldbblobstorage.jdbc.connectionpool.timeBetweenEvictionRuns — the number of milliseconds to sleep between the runs of the thread that cleans the idle connections. Default is 30000.
• com.nm.extensions.relationaldbblobstorage.jdbc.connectionpool.minEvictableIdleTime — the minimum amount of time in milliseconds for a connection to stay idle before being clean up. Default is 600000.

JNDI

To enable JNDI to connect to the database you have to set the following property to true (the default value is false): com.nm.extensions.relationaldbblobstorage.jndi.enabled

You can configure the JNDI path using the following property: com.nm.extensions.relationaldbblobstorage.jndi.path.

All additional properties starting with "java." or "javax." will be used as JNDI environment properties (e.g., java.naming.factory.initial). Empty values are also supported.

The JNDI property used for creating tables if needed is the following: com.nm.extensions.relationaldbblobstorage.admin.jndi.path

All additional properties starting with com.nm.extensions.relationaldbblobstorage.admin.jndi. will be used as JNDI environment properties for the admin connection. When the property is empty, the default jndi property is used.

Tuning the Databases for Streaming Large Objects

MySQL

If the ER_NET_PACKET_TOO_LARGE MySql error is received, then the max_allowed_packet configuration property must be increased. The largest possible packet that can be transmitted to or from a MySQL 8.0 server or client is 1GB. Because of this, the maximum size of an uploaded Blob to a MySql database is limited to 1GB.

SET GLOBAL max_allowed_packet=1073741824;
SHOW VARIABLES LIKE 'max_allowed_packet';

To be able to stream blobs to a MySql database, you have to enable server-side prepared statements, by setting the useServerPrepStmts property to true.

To be able to download blobs from a MySql database, you have to emulate BLOBs with locators by adding the property emulateLocators=true to your JDBC URL.

Refer to: https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-implementation-notes.html

PostgreSQL

To store large objects in PostgreSQL, you need to use the lo module that provides support for managing Large Objects (also called LOs or BLOBs). This includes a data type lo and a trigger lo_manage.

If the Blob tables need to be created manually before the extension is started, the following queries need to be executed to store large objects:

CREATE EXTENSION IF NOT EXISTS lo;
CREATE TABLE ##table_name_data## (blobKey VARCHAR(100) NOT NULL, data lo NOT NULL, CONSTRAINT ##table_name_data##_blobKey PRIMARY KEY (blobKey))
CREATE TRIGGER trigger_##table_name_data## BEFORE UPDATE OR DELETE ON ##table_name_data## FOR EACH ROW EXECUTE FUNCTION lo_manage(data);

To list large objects use the following command:

\lo_list

See more details: https://www.postgresql.org/docs/current/lo.html