Writing custom providers for Firebird to access external databases (ODBC, MySQL)

Author: Simonov Denis

Version: 1.0 by 2025-06-25

• Preface
• 1. How did it all start?
• 2. How can the functionality of EDS be extended?
• 3. Extension of the EDS mechanism
• 4. Plugin type Provider
• 5. Provider implementation
• 6. What provider implementations exist?
• 7. Examples of work
• 8. What are the problems with existing implementations?
• 9. Conclusion

Preface

Starting with Firebird 3.0, you can write various extensions (plugins) for Firebird SQL. The simplest extension is an external procedure/function (UDR) (see article Writing UDF in Pascal). UDR is not a Firebird plugin in the true sense of the word, but it allows you to significantly expand the capabilities of the DBMS. However, Firebird engine has more capabilities to extend the funcionality with various plugins.

Firebird has the following types of plugins (IPluginManager class constants):

• TYPE_PROVIDER — providers;
• TYPE_AUTH_SERVER — server-side authentication;
• TYPE_AUTH_CLIENT — client-side authentication;
• TYPE_AUTH_USER_MANAGEMENT — user management;
• TYPE_EXTERNAL_ENGINE — external engines. They are intermediaries between the code of external stored procedures/functions/triggers written in any programming language and Firebird. Examples of such plugins are udr_engine for interaction with UDRs written in C++/Delphi or libfbjava for UDRs in Java;
• TYPE_TRACE — tracing plugin;
• TYPE_WIRE_CRYPT — network traffic encryption;
• TYPE_DB_CRYPT — database encryption;
• TYPE_KEY_HOLDER — key holder for the database encryption plugin;
• TYPE_REPLICATOR — replicator plugin.

In this article we will look at perhaps the most complex and interesting type of plugin — providers.

1. How did it all start?

In 2023 I started to work on development of plugin for HQbird to access databases of other DBMSs (not Firebird) via EXECUTE STATEMENT ON EXTERNAL DATA SOURCE (hereinafter EDS). Vlad Khorsun, core developer of Firebird, was the consulant of this project.

Currently, in HQbird 2024, two plugins are available for Firebird 4.0 and 5.0: MySQLEngine and ODBCEngine. MySQLEngine was created first and was a test horse, the main efforts were focused on ODBCEngine.

2. How can the functionality of EDS be extended?

There are two options:

• Extend the EDS mechanism in the Firebird core;
• Implement one of the Firebird plugins.

I'll be straight with you - no matter which route you take, you're going to need to dig into the Firebird source code. There's just no way around it, because you won't find this stuff documented anywhere else.

First, let's consider the first option.

3. Extension of the EDS mechanism

The EDS implementation is located in the directory /src/jrd/extds/. The EXECUTE STATEMENT mechanism itself can be extended starting from Firebird 2.5. For this purpose, the so-called EDS Providers were invented. There are two EDS providers in Firebird:

• Provider Firebird. EXECUTE STATEMENT is used to access external Firebird databases.
• Provider Internal. EXECUTE STATEMENT is used to access the current database.

The code provides the ability to add new providers (see /src/jrd/extds/ExtDS.cpp).

void Manager::addProvider(Provider* provider)
{
    // TODO: if\when usage of user providers will be implemented,
    // need to check provider name for allowed chars (file system rules ?)
    // ...
}
    

Although these providers are hidden from you, you can explicitly specify the provider to use in the EXECUTE STATEMENT ON EXTERNAL connection string.

SQL = 'SELECT MON$ATTACHMENT_NAME FROM MON$ATTACHMENTS';

FOR
  EXECUTE STATEMENT :SQL
  ON EXTERNAL 'Firebird::inet://localhost/ext_db'
  AS USER 'SYSDBA' PASSWORD 'masterkey'
  INTO NAME
DO SUSPEND;
-- returns ext_db

FOR
  EXECUTE STATEMENT :SQL
  ON EXTERNAL 'Internal::inet://localhost/ext_db'
  AS USER 'SYSDBA' PASSWORD 'masterkey'
  INTO NAME
DO SUSPEND;
-- returns self_db (connection string is ignored)
    

So, if we plan to extend the EDS ODBC provider mechanism, EXECUTE STATEMENT ON EXTERNAL would look like this:

FOR
  EXECUTE STATEMENT :SQL
  ON EXTERNAL 'ODBC::'
  AS USER 'user' PASSWORD 'secret'
  INTO NAME
DO SUSPEND;
    

This is possible, but there are significant drawbacks to this approach:

• EDS Provider data is not implemented as plugins (dynamic libraries), which means that modification of Firebird engine source codes is required.
• The solution is much more difficult to maintain. Manual porting of functionality to new versions is required, for example, when Firebird 6.0 is released and later.

However, having studied the features of EXECUTE STATEMENT ON EXTERNAL, one can note an important detail — the Firebird:: provider works through the usual Firebird API, which is used in applications, i.e. EDS acts as a regular client. And this leads to another thought: what if the API of an external data source is wrapped in the API used to access the Firebird DB?

4. Plugin type Provider

If you carefully study the existing Firebird documentation, you will find that for these purposes, starting with Firebird 3.0, a special type of plugins called Provider (not to be confused with EDS Provider) has been provided. A brief description of what it is can be found in doc/README.providers.html.

A Provider plugin provides a single API for interacting with Firebird. It does not matter how the physical interaction occurs over the network or directly with the Firebird engine.

Where do you encounter providers? Open firebird.conf and you will see the following line there:

Providers = Remote, Engine13, Loopback    

The Remote provider is responsible for interaction over the network, and Engine13 is the core for interaction with ODS13, used directly when working in embedded mode. Providers are tried sequentially, and the one that does not refuse to work is the active one.

Perhaps the most well-known example that demonstrates the work of providers is the configuration:

Providers = Remote,Engine13,Engine12,Loopback    

This configuration allows Firebird 5.0 to work with both native ODS 13.1 databases and ODS 12.0 (Firebird 3.0) databases.

If we provide Firebird API for working with MySQL or ODBC with the help of our provider, the configuration can be as follows:

Providers = Remote,Engine13,ODBCEngine,Loopback
Providers = ODBCEngine,Remote,Engine13,Loopback
Providers = MySQLEngine,Remote,Engine13,Loopback
    

5. Provider implementation

Now let's talk directly about the implementation of our own providers. What API interfaces need to be implemented?

• IProvider
• IAttachment
• ITransaction
• IStatement
• IResultSet
• IBlob

Please note that not all methods of these interfaces need to be implemented. We will implement only those that are required for EXECUTE STATEMENT ON EXTERNAL to work, and we will make stubs for the rest.

In addition to the provider itself, its factory must be implemented. You can take a look at the factory implementation and provider registration using the example of EngineXX providers in /src/jrd/jrd.cpp (.h).

namespace {

template 
class Factory : public IPluginFactoryImpl, CheckStatusWrapper>
{
public:
    // IPluginFactory implementation
    IPluginBase* createPlugin(CheckStatusWrapper* status, IPluginConfig* factoryParameter)
    {
        try {
            IPluginBase* p = new P(factoryParameter);
            p->addRef();
            return p;
        }
        catch (const std::exception& e) {
            Firebird::setStatusError(status, e.what());
        }
        return nullptr;
    }
};

static Factory engineFactory;

} // namespace

void registerEngine(IPluginManager* iPlugin)
{
    UnloadDetectorHelper* module = getUnloadDetector();
    module->setCleanup(shutdownBeforeUnload);
    module->setThreadDetach(threadDetach);
    iPlugin->registerPluginFactory(IPluginManager::TYPE_PROVIDER, ODBC_ENGINE_NAME, &engineFactory);
    module->registerMe();
}

extern "C" FB_DLL_EXPORT void FB_PLUGIN_ENTRY_POINT(IMaster * master)
{
    CachedMasterInterface::set(master);
    registerEngine(PluginManagerInterfacePtr());
}
    

5.1. Implementation of the IProvider interface

What needs to be implemented in the IProvider interface (ODBCProvider, MySQLProvider)?

The IProvider interface has the following functions:

• IAttachment* attachDatabase(...) (mandatory)
• IAttachment* createDatabase(...) (throw error isc_unavailable)
• IService* attachServiceManager(...) (throw error isc_unavailable)
• void shutdown(...) (not required)
• void setDbCryptCallback(...) (not required)

In this interface, the main thing is to implement the IProvider::attachDatabase method. It should do the following:

• Analyze the connection string and extract the prefix in it
• If the prefix matches our prefix, then create a connection to the DB, otherwise throw the isc_unavailable error.
• The prefix is necessary to quickly determine whether to make a connection attempt, which may not be cheap, or let the next provider try to connect to the DB.
• The following prefixes are provided:
  • For ODBC, this is: :odbc: or odbc://
  • For MySQL, this is: :mysql: or mysql://

Here is a small fragment of this function:

IAttachment* ODBCProvider::attachDatabase(CheckStatusWrapper* status, const char* fileName,
    unsigned dpbLength, const unsigned char* dpb)
{
    debug_print_call();

    status->clearException();

    std::string dbFileName(fileName);

    auto poviderPos = dbFileName.find(":odbc:");
    std::string connStr;
    if (poviderPos == 0) {
        connStr = dbFileName.substr(6);
    }
    else if (poviderPos = dbFileName.find("odbc://"); poviderPos == 0) {
        connStr = dbFileName.substr(7);
    }
    else {
        // It is important to set the error with the status isc_unavailable
        // to pass control to the next provider
        const ISC_STATUS statusVector[] = {
            isc_arg_gds, isc_unavailable,
            isc_arg_end
        };

        status->setErrors(statusVector);
        return nullptr;
    }

    ....
}
    

The functions IProvider::createDatabase and IProvider::attachServiceManager are not needed for EDS to function, but they still need to be implemented and the isc_unavailable error thrown in them. This is necessary so that the work of provider chains is not interrupted.

5.2. Implementation of the IAttachment interface

The following methods must be implemented in the IAttachment interface (MySQLAttachment, ODBCAttachment):

• void getInfo(status, ...)
• ITransaction* startTransaction(status, ...)
• IBlob* createBlob(status, ...)
• IBlob* openBlob(status, ...)
• IStatement* prepare(status, ...)
• ITransaction* execute(status, ...)
• IResultSet* openCursor(status, ...) — this method is never called in EDS as it always executes prepared statements only
• void detach(status)
• void dropDatabase(status) — in theory, this method is not needed, but sometimes the control flow gets into it, so we simply call IProvider::detach in it.

5.2.1. Implementation of IAttachment::getInfo

In this method, you need to return a response for requests with the isc_info_db_sql_dialect and fb_info_features tags. The fb_info_features tag is used to return the supported functionality of your provider. The possible values are described by the following enumeration:

enum info_features // response to fb_info_features
{
    fb_feature_multi_statements = 1, // Multiple prepared statements in single attachment
    fb_feature_multi_transactions= 2, // Multiple concurrent transaction in single attachment
    fb_feature_named_parameters= 3, // Query parameters can be named
    fb_feature_session_reset= 4, // ALTER SESSION RESET is supported
    fb_feature_read_consistency= 5, // Read consistency TIL is supported
    fb_feature_statement_timeout= 6, // Statement timeout is supported
    fb_feature_statement_long_life = 7, // Prepared statements are not dropped on transaction end
    fb_feature_max // Not really a feature. Keep this last.
};
    

5.2.2. Implementation of IAttachment::startTransaction

The IAttachment::startTransaction function should do the following:

• Should return an ITransaction instance, even if transactions are not supported.
• Increments the internal transaction counter.
• Should process the Transaction Parameter Buffer (tpb). Namely, set the transaction parameters in the target driver (isolation level, read/write mode, parameters for waiting for locks to be resolved, etc.) according to the Firebird parameters.

The following methods should be implemented in the ITransaction interface itself:

• void commit(status)
• void commitRetaining(status)
• void rollback(status)
• void rollbackRetaining(status)

There is one peculiarity here. When a transaction is completed, it is necessary to free the resources associated with it, for example, BLOBs. We will return to this peculiarity later.

5.2.3. Implementation of IAttachment::prepare

The IAttachment::prepare function should do the following:

• Create and return an instance of the IStatement interface;
• Prepare the query
• Determine what type the query is: isc_info_sql_stmt_select, isc_info_sql_stmt_ddl, isc_info_sql_stmt_exec_procedure, isc_info_sql_stmt_insert.
• Prepare the query flags:
  • IStatement::FLAG_HAS_CURSOR — if it is a cursor
  • IStatement::FLAG_REPEAT_EXECUTE — if there are input parameters
• Prepare input and output messages

When preparing input and output messages, you should determine what Firebird data type corresponds to the type from your provider and vice versa.

5.3. Implementation of the IStatement interface

The following methods must be implemented in the IStatement (MySQLStatement, ODBCStatement) interface:

• void getInfo(status, ...)
• void free(status)
• ISC_UINT64 getAffectedRecords(status)
• IMessageMetadata* getOutputMetadata(status)
• IMessageMetadata* getInputMetadata(status)
• unsigned getType(status)
• ITransaction* execute(status, ...)
• IResultSet* openCursor(status, ...)
• unsigned getFlags(status)
• unsigned int getTimeout(status)
• void setTimeout(status, timeout)

The IStatement::getOutputMetadata, IStatement::getInputMetadata, IStatement::getType and IStatement::getFlags methods simply return the prepared values of the class fields, since the flags, type and input and output metadata were prepared during the execution of IAttachment::prepare.

5.3.1. Implementation of IStatement::execute

The IStatement::execute method does the following:

• Convert input parameters to the required types
• Copy input parameters of type BLOB
• Execute the query
• Convert output parameters to the required types
• Copy output parameters of some types to BLOB

5.3.2. Implementation of IStatement::openCursor

The IStatement::openCursor method does the following:

• Convert input parameters to the required types
• Copy input parameters of type BLOB
• Execute the query
• Create and return an instance of IResultSet

5.4. Implementation of the IResultSet interface

The following methods must be implemented in the IResultSet (MySQLResultSet, ODBCResultSet) interface:

• int fetchNext(Status* status, void* message)
• IMessageMetadata* getMetadata(Status* status)
• void close(Status* status)
• void setDelayedOutputFormat(Status* status, IMessageMetadata* format)

The methods IResultSet::fetchFirst, IResultSet::fetchPrior, IResultSet::fetchLast, IResultSet::fetchAbsolute and IResultSet::fetchRelative are never called inside the EDS subsystem, so we simply create stubs for them.

5.4.1. Implementation of IStatement::fetchNext

The IStatement::fetchNext method should do the following:

• Move to the next record in the cursor
• Convert output parameters to the required types
• Copy output parameters of some types to BLOB
• Return IStatus::RESULT_OK if the cursor record was successfully fetched and IStatus::RESULT_NO_DATA if the cursor has run out of records.

5.4.2. Implementation of IStatement::setDelayedOutputFormat

The IStatement::setDelayedOutputFormat method is intended to set the output message for the cursor. In principle, the cursor output message could be created in the constructor, but the EDS implementation still calls IStatement::setDelayedOutputFormat and could potentially overwrite our message.

5.5. Implementation of the IBlob interface

The following methods must be implemented in the IBlob (MySQLBlob, ODBCBlob) interface:

• int getSegment(Status* status, ...)
• void putSegment(Status* status, ...)
• void close(Status* status)
• void cancel(Status* status)
• void seek(Status* status, int mode, int offset)

Features of the IBlob implementation:

• The contents of the BLOB type are stored in memory at the IAttachment level
• BLOBs have an identifier of the ISC_QUAD type. It is formed as follows:
  • gds_quad_high — transaction number (incremented in IAttachment::startTransaction)
  • gds_quad_low — BLOB number inside the transaction (increment)
• Creation of a new BLOB instance is done by the IBlob* IAttachment::createBlob(status, ...) method. This method is called both to return fields of some types mapped to BLOB inside the provider, and on the Firebird side in the EDS implementation when passing input parameters of the BLOB type.
• Opening an existing BLOB instance by its identifier is done by the IBlob* IAttachment::openBlob(status, ...) method
• When a transaction is completed (ITransaction::commit, ITransaction::rollback), the contents of BLOBs that belong to this transaction must be cleared.

5.5.1. Optimizing BLOB lifetime

When investigating the operation of EXECUTE STATEMENT ON EXTERNAL, the following was discovered:

• BLOB contents are always completely copied between EDS and the provider (no segment forwarding);
• EDS implements only one-way cursors

This led me to the idea that the lifetime of BLOBs in memory can be reduced. That is, the contents of BLOB fields of cursors only need to be stored until the next call to the IResultSet::fetchNext method, after which old BLOBs can be cleared. This allowed us to reduce the memory consumption of the provider when fetching cursors with BLOB fields.

That's all about the implementation.

6. What provider implementations exist?

There are the following implementations of access to external databases via EDS:

• ODBCEngine, MySQLEngine (HQbird)
• jdbc_provider (Red Database)
• Magpie (ODBC Dmitry Sibiryakov)

7. Examples

Now let's look at examples of accessing the database via MySQL and ODBC.

You can download the trial version of HQbird for Windows at https://firebirdsql.org/hqbird 

Install HQbird by checking the ODBCEngine and MySQLEngine boxes in the installer.

ODBCEngine and MySQLEngine plugins starting with Firebird 4.0 and higher.

Edit the firebird.conf file by changing the Providers configuration parameter as follows

Providers = MySQLEngine,Remote,Engine13,Loopback
    

Now you can execute SQL query against your MariaDB or MySQL database:

dsn_mysql = 'mysql://host=localhost;port=3306;database=employees';

for
  execute statement (q'{
select
  emp_no, birth_date,
  first_name, last_name,
  gender, hire_date
from employees
where emp_no = ?
}') (10020)
  on external dsn_mysql
  as user 'root' password 'sa'
  into
    emp_no, birth_date, first_name, last_name, gender, hire_date
do
  suspend;
    

The same, but using named parameters:

dsn_mysql = 'mysql://host=localhost;port=3306;database=employees';

for
  execute statement (q'{
select
  emp_no, birth_date,
  first_name, last_name,
  gender, hire_date
from employees
where emp_no = :emp_no
}') (emp_no := 10020)
  on external dsn_mysql
  as user 'root' password 'sa'
  into
  emp_no, birth_date, first_name, last_name, gender, hire_date
do
  suspend;
    

If you need access via ODBC, edit the firebird.conf file and change the Providers configuration parameter as follows:

Providers = ODBCEngine,Remote,Engine13,Loopback
    

Now you can execute SQL query against MariaDB or MySQL database via ODBC interface:

conn_str = 'odbc://DRIVER={MariaDB ODBC 3.1 Driver};SERVER=server;PORT=3306;DATABASE=test;CHARSET=utf8mb4';

sql = Q'{
SELECT
  id, title,
  body, bydate
FROM article
}';

for execute statement (:sql)
    on external :conn_str
    as user 'root' password 'play'
    into id, title, body, bydate
do
  suspend;
    

ODBC with named parameters:

xConnStr = 'odbc://DRIVER={MariaDB ODBC 3.1 Driver};SERVER=server;PORT=3306;DATABASE=test;TCPIP=1;CHARSET=utf8mb4';

xSQL = '
SELECT
  CODE_SEX, NAME, NAME_EN
FROM sex
WHERE CODE_SEX = :A_CODE_SEX
';

for
  execute statement (:xSQL) (A_CODE_SEX := xCODE_SEX)
  on external xConnStr
  as user 'test' password '12345'
  into CODE_SEX, NAME, NAME_EN
do
  suspend;
    

Using the ODBC interface you can execute SQL queries to any database for which an ODBC driver exists.

8. What are the problems with existing implementations?

• Prefix in connection string depends on configuration
• Passing authentication parameters depending on configuration
• Handling connection errors
• Not all DBMS can return input parameter types
• Named parameters are processed only for SQL operators whose syntax is similar to Firebird
• CALL operator does not work with stored procedures that return a data set
• It is necessary to ensure that the encoding of string parameters matches

Let's take a closer look at why this happens.

8.1. Prefixes in the connection string

What are prefixes in the connection string for?

• Providers try to open a connection in the order specified in the Provider parameter (firebird.conf)
• If the connection attempt fails, the next provider is tried
• Trying to establish a connection may not be cheap
• The prefix in the connection string allows you to quickly determine whether the provider is suitable or not

We have two types of prefixes in the current implementation:

• For ODBC it is: :odbc: or odbc://
• For MySQL it is: :mysql: or mysql://

Each prefix has its advantages and disadvantages.

A more familiar URL like prefix requires that our provider be specified before the Remote provider, i.e.

Providers = ODBCEngine,Remote,Engine13,Loopback
    

A provider whose connection string prefix begins with a colon can be specified anywhere before the Loopback provider. A colon before and after the prefix prevents this prefix from being interpreted as a host name.

In addition, the order of providers affects how authentication parameters are passed. If a connection string prefix beginning with a colon is used, the user name must be passed in the connection string, and not using the USER keyword (EDS). In this case, NULL is passed in USER.

The reason: if a user name is passed and the host is not specified in the connection string, EDS considers this to be a Firebird database located on the current server, and therefore tries to apply Firebird authentication to it. That is, the user is looked up in security.db (see /src/jrd/extds/ValidatePassword.cpp).

8.2. Handling connection errors

Another problem is getting a connection error. The algorithm for enumerating providers works in such a way that if the connection attempt is unsuccessful, then we move on to the next provider. In this case, the real error that occurs during the connection attempt is lost. Instead, an error from the last provider in the list is returned.

The following solution (a hack) was invented:

• The connection string prefix is checked, and if it does not match, then we throw the error isc_unavailable;
• If the connection string prefix matches, then we try to connect to the database;
• If an error occurs during the connection to the database, then an instance of a pseudo-connection is created, called ErrorAttachment (inherited from IAttachment);
• When an instance of this connection is created, the original error is saved in it;
• The error is thrown when calling any method of this connection.

8.3. Not all DBMS can return input parameter types

The next big problem is that not all DBMSs can return the types of input parameters when preparing a SQL query. For example:

• MySQL API has the function mysql_stmt_param_count to return the number of input parameters;
• The function for describing input parameters mysql_stmt_param_metadata is just a stub without implementation.

A hack: use the largest capacity type for the parameter — VARCHAR(32765).

Another example from ODBCEngine:

• ODBC has the function SQLDescribeParam
• Not all drivers return a real description of the parameter. For example, myodbc returns VARCHAR(255) for all parameters

8.4. Named parameters cannot be used with all queries

Named parameters can only be used with SQL queries whose syntax is similar to Firebird's.

Take a look on the following example:

dsn_mysql = 'mysql://host=localhost;port=3306;database=employees';

execute statement
  ('CALL sp_conn_audit(:A_CONN_ID, :A_USER, :A_DT)')
  (
    A_CONN_ID := current_connection,
    A_USER := current_user,
    A_DT := localtimestamp
  )
on external dsn_mysql
as user 'root' password 'sa';
    

Running this example will produce the following error:

Statement failed, SQLSTATE = 42000
Execute statement error at isc_dsql_prepare :
335544382 : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server
version for the right syntax to use near ':A_CONN_ID, :A_USER, :A_DT)' at line 1
Statement : CALL sp_conn_audit(:A_CONN_ID, :A_USER, :A_DT)
Data source : Firebird::mysql://host=localhost;port=3306;database=employees
-At block line: 7, col: 3
    

The reason is that the SQL query pre-parser for extracting named parameters in EXECUTE STATEMENT does not know the syntax of calling a stored procedure via the CALL statement. This will be fixed in Firebird 6.0, which supports such syntax.

9. Conclusion

Firebird's functionality can be significantly extended through the implementation of custom plugins, enabling the addition of numerous useful features.

Please send feedback and questions to [email protected].