This manual covers Jaybird 5 and may use Java 11 and Firebird 4.0 specific features, but most examples and information also apply to previous versions of Jaybird, Firebird and Java. Features that were removed in Jaybird 3 or earlier are not covered.

New or removed features are tagged with the version that introduced a feature (e.g. Jaybird 5) or removed feature (e.g. Jaybird 5). This tagging is only done for features introduced (or removed) after Jaybird 4.0.0, or in Firebird 3.0 or later.

This manual may include documentation of features of — possibly unreleased — Jaybird versions later than 5 to simplify manual maintenance and versioning.

When Borland released an open-source version of the InterBase RDBMS, it included sources for a type 3 JDBC driver called InterClient.^[1] However, due to some inherent limitations of the InterBase (and later Firebird) client library, it was decided that the type 3 driver was a dead end. Instead, the Firebird team decided to develop a pure Java implementation of the wire protocol. This implementation became the basis for Jaybird, a pure Java driver for Firebird relational database.

The Jaybird driver consists of three layers, each of which is responsible for its part of the functionality.

In addition, the Services API allows you to manage the database and the server itself. The manager component represents a JMX compatible implementation that utilizes the Services API. Currently, only calls to create and drop database are available in the Manager component, other classes provide features for database backup/restore, user management, statistics gathering, etc.

Jaybird 5 supports Firebird 2.5 and higher. See Jaybird versions for detailed information on supported Java and Firebird versions per Jaybird version.

The latest version of Jaybird can be downloaded from https://firebirdsql.org/en/jdbc-driver/

The Jaybird team uses JUnit test cases to assure the quality of the released driver. During development unit tests are extensively used. Committing a code change to the source control is not allowed until it passes all existing unit tests. Each reproducible bug usually gets its own test case. This guarantees that a clean check out can be compiled and will not contain any previously discovered and fixed bugs.

Currently, there are more than 9000 test cases covering most of the driver code. A subset of the tests are automatically run — through a GitHub Action — for each push to the repository.

There are several ways you can contribute to Jaybird or Firebird in general:

java.sql.DriverManager is a database connection factory introduced with JDBC 1. It is based on the concept of the JDBC URL, a string that uniquely identifies the database to connect. The driver manager then checks which driver(s) — if any — can establish a connection.

There is also support to specify additional connection parameters, like username and password.

A JDBC URL consists of three parts:

The first part, jdbc:firebird: or jdbc:firebirdsql:, is required by JDBC and specifies the so-called protocol and subprotocol for the JDBC connection. In other words, it specifies the type of connection that the application wants to obtain, in this example, a connection to a Firebird database.

An example of obtaining a connection is shown below.

The first line of this code is important — it tells Java to load the Jaybird JDBC driver. As required by the JDBC specification, at this point driver registers itself with java.sql.DriverManager.

Since Java 6 (JDBC 4), explicitly loading the driver using Class.forName("org.firebirdsql.jdbc.FBDriver") is no longer necessary, except when the driver is not on the system class path. Examples where it may be necessary to explicitly load the driver are web applications that include the driver in the deployment (e.g. in WEB-INF/lib of the WAR). There, the driver is not on the system class path, so it will need to be loaded explicitly.

We will leave out usages of Class.forName in further examples, they will work because of automatic driver loading.

The second statement of the example tells the java.sql.DriverManager to open a database connection to the Firebird server running on localhost, and the path to the database is c:/database/employee.fdb.

The connection specification consists of the host name of the database server, optionally you can specify a port (by default port 3050 is used). The host name can be specified using either its DNS name (for example fb-server.mycompany.com or just fb-server), or its IP address (for example 192.168.0.5, or [1080::8:800:200C:417A] for an IPv6 address).

After the server name and port, the alias or path to the database is specified. We suggest to specify a database alias instead of the absolute database path. For more information about using aliases, see the documentation of Firebird server.

The format of the path depends on the platform of the Firebird server.

On Windows, the path must include the drive letter and path, for example c:/database/employee.fdb, which points to the employee database that can be found in the database directory of drive C:. Java (and Firebird) supports either / or \ (escaped as \\) as path separator on the Windows platform. On Unix and Linux platform, you can use only / as the path separator.

On Unix platforms the path must include the root, as the path is otherwise interpreted relative to a server-dependent folder. Having to include the root has the effect that a database in /var/firebird/employee.fdb needs to use a double // after the host name (and port) in the connection string: jdbc:firebird://localhost//var/firebird/employee.fdb.

It is possible to specify a relative path, but as this depends on the server configuration, this may be confusing or easily lead to errors. We suggest not to use relative paths, and instead use an alias.

As mentioned in the section Jaybird Architecture, Jaybird supports multiple implementations of the GDS API. The default Jaybird distribution contains two main categories of the implementations: the pure Java implementation of the Firebird wire protocol, and a JNA proxy that can use a Firebird fbclient library.

The next sections provide a description of these types and their configuration with the corresponding JDBC URLs that should be used to obtain the connection of desired type. The type of the JDBC driver for the javax.sql.DataSource is configured via a corresponding property.

Each time a connection is opened via DriverManager, a new physical connection to server is opened. It is closed when the connection is closed. To avoid the overhead of creating connections, you can use a connection pool implementation to maintain a cache of open physical connections that can be reused between user sessions.

Since Jaybird 3, Jaybird no longer provides its own connection pool implementation. If you need a javax.sql.DataSource implementation that provides a connection pool, either use the connection pool support of your application server, or consider using HikariCP, DBCP, or c3p0.

Connection pool implementations, whether provided by a Java EE/Jakarta EE application server or a third-party library, are exposed as an implementation of the javax.sql.DataSource interface.

The most important method exposed by this interface is the getConnection() method, which will return a connection based on the configuration of the data source. For a 'basic' (non-pooling) data source this will create a new, physical, connection. For a connection pool, this will create a logical connection that wraps a physical connection from the pool.

When the application is done with the connection, it should call close() on the connection. A connection from a non-pooling data source will be closed. For a logical connection from a connection pool, close() will invalidate the logical connection (which will make it behave like a closed connection), and return the underlying physical connection to the connection pool, where it will be either kept for re-use, or maybe closed.

The javax.sql.ConnectionPoolDataSource interface represents a factory that creates PooledConnection objects for use by a connection pool. For example, application servers support the use of a ConnectionPoolDataSource to populate their connection pool.

A PooledConnection instance represents a physical connection to a database and is a source of logical connections that a connection pool can hand out to the application. Closing this logical connection returns the physical connection back into the pool.

Jaybird provides org.firebirdsql.ds.FBConnectionPoolDataSource as an implementation of the javax.sql.ConnectionPoolDataSource interface.

The JDBC 2.0 specification introduced the javax.sql.XADataSource interface that should be used to access connections that can participate in distributed transactions with JTA-compatible transaction coordinator. This gives applications possibility to use two-phase commit to synchronize multiple resource managers.

Just like javax.sql.ConnectionPoolDataSource, applications normally don’t access an XADataSource implementation directly, instead it is used as a factory of connections for an XA-enabled data source. To the application this is usually exposed as a javax.sql.DataSource.

Jaybird provides org.firebirdsql.ds.FBXADataSource as an implementation of the javax.sql.XADataSource interface.

Exception handling becomes even more important if we consider that this topic is either ignored or presented in incorrect form in most JDBC tutorials. The official JDBC tutorial from Oracle briefly mentions that exceptions should be handled by using try/catch blocks only at the end of the course, but neither reasons for doing this nor the best practices are presented.

There are good reasons to think about exception handling in your applications before you start coding. First of all, it is very hard to change the exception handling pattern in existing code. The changes will affect all layers above the place where the changes in exception handling are made and the new application must be thoroughly tested after the change.

Another reason was already mentioned on the beginning of this chapter: instances of java.sql.SQLException are the only way for the RDBMS server or JDBC driver to notify about the error condition that happened during request processing. By checking the error code which is sent with the exception, an application can try to recover from the error.

And last but not least, there is resource management. When an exception happens in a method, the execution flow of Java code differs from the normal flow, and only correctly coded application will ensure that all allocated resources will be released. The resources in our case are JDBC connections, statements, result sets, etc. All these objects not only take memory in the Java Virtual Machine of the application, but also consume memory and possibly hold locks on the server, which, worst case, can lead to an unintended Denial-of-Service attack, as the database server can no longer service requests.

A good exception handling strategy requires you do distinguish three kinds of error conditions:

The handling strategy then consists of

The problem of resource management can be solved if resource allocation and release happens in the same code block and is protected with a try-with-resources block. The code to recover from error conditions should use try/catch blocks. An example of such error and resource handling code is presented below.

The nested try/catch block shows you an example of handling a deadlock error if it happens (first scenario according to our classification), otherwise the exception is converted and passed to the upper layers (second scenario). As you see, there is no special treatment to the third scenario.

A possible bug in the JDBC driver could have generated runtime exception in the PreparedStatement.executeUpdate() method, which would lead to the statement handle leakage if the try-with-resource block had not been used to do the resource cleanup. As a rule of thumb, always declare and allocate resources in a try-with-resources block: the resource will be automatically closed/freed at the end of the block, even if exceptions occur.

Such coding practice might look weird, because on first sight the whole purpose of using the PreparedStatement is neglected: the statement is prepared, used only once and then deallocated. However, when this practice is combined with connection and statement pooling, it brings enormous advantage to the application code. The code becomes much more manageable — resource allocations and release happen in the same method and the software developer does not need to remember the places where the same prepared statement might be used. A statement pool will either reuse the statement or it will prepare a new one, if it detects that all pooled prepared statements are currently in use. As a side effect, the application will always use the minimum number of statements handles, which in turn reduces the used resources on the database side.

Some errors returned by Firebird are treated as warnings. They are converted into instances of java.sql.SQLWarning class in the JDBC layer. These exceptions are not thrown from the driver methods, but added to a connection instance.

Each next warning is appended to the tail of the warning chain. To read the warning chain, use the code presented below.

or

This second example will iterate over the first warning, all its causes (if any), and then on to other warnings (if any), and so on.

To clear existing warning, call Connection.clearWarnings() method.

TODO Information in section is possibly outdated or incomplete

An SQLException is a special exception that is thrown by the JDBC connectivity component in case of an error. Each instance of this exception is required to carry the vendor error code (if applicable) and a SQL state according to the X/Open SQLstate or SQL:2003 specifications. Firebird and Jaybird use SQL:2003 SQL state codes.^[2]

When multiple SQL errors happened, they are joined into a chain. Usually the most recent exception is thrown to the application, the exceptions that happened before can be obtained via SQLException.getNextException() method. Alternatively, SQLException.iterator() can be used to walk over all exceptions in the chain and their causes.

The JDBC specification provides an exception hierarchy that allows an application to react on the error situations using regular exception handling rather than checking the error code. Error codes may still be necessary for handling specific error cases.

The JDBC 4.3 specification defines the following exception hierarchy:^[3]

Each of three layers in Jaybird use exceptions most appropriate to the specific layer. TODO List needs revision

Jaybird supports the SQLstate values from the SQL:2003 standard,^[2] however only few states nicely map into the Firebird error codes.

Applications can use the SQLstate codes in the error handling routines which should handle errors that are returned from different databases. But since there is little agreement between RDBMS vendors, this method can be used only for very coarse error distinction.

Contrary to the SQLstates, the Firebird native error codes are extremely useful to determine the type of an error that happened.

Here you can find a short list of error codes, symbolic names of a corresponding constant in a org.firebirdsql.gds.ISCConstants class, the error message and short explanation of an error.

TODO Needs revising now Jaybird tries to pull the most important error code to the top

The java.sql.Statement interface is the simplest interface to execute SQL statements. It distinguishes three types:

Let’s check one of the typical usages shown below. In general the usage pattern of the statement consists of three steps.

As the connection object is the factory for the statement objects, this puts a constraint on the object lifetime: statements are bound to the connection; when the connection is closed, all statements that were created by that connection become invalid and the resources allocated by them are released. However, despite that these resources are released when the connection closes, it is strongly recommended to use the try-with-resources block, to guarantee that resources are released as soon as possible because of reasons that will be discussed later.

A statement can be executed using the following methods:

These execute methods have several variants for additional features covered in the reference section Generated keys retrieval.

A statement is closed by calling the Statement.close() method, or by using a try-with-resources which calls close() behind the scenes. After a close, the statement object is invalid and cannot be used anymore.

It is allowed to use the same statement object to execute different types of queries, one after another. The code below contains a short example which first performs a select to find the ID of the user 'Joe Doe', and if the record is found, it enables his account.

The way the code is constructed is quite tricky because of the result set lifetime constraints that are defined by the JDBC specification, please read the chapter Working with result sets for more details.

However, here it is done intentionally to emphasize that a single object is used to execute SELECT and UPDATE/INSERT statements. It also shows how to check whether the executed statement modified the expected number of rows — the application first tries to update the account and only if no rows were updated, it inserts new record into the accounts table.

When an application needs to execute DDL statements, it is recommended to use the Statement.execute(String) method, as in this case the amount of modified records makes little sense. The next example shows the creation of database tables using this method.

As mentioned earlier, the Statement.execute(String) method can also be used to execute statements of an unknown type.

According to the JDBC specification, the getResultSet() and getUpdateCount() methods can be only called once per result.

Jaybird 5.0.5 In Jaybird 5.0.4 and earlier, calling the getResultset() method a second time will throw an exception.
Jaybird 5.0.5 Since Jaybird 5.0.5, calling getResultSet() multiple times will return the same instance of ResultSet it returned from the first call to getResultSet() or from executeQuery().

The previous examples requires us to discuss the statement object dynamics, its lifecycle and how it affects other subsystems in details.

As we have seen, Jaybird already performs internal optimization when it comes to multiple statement execution — it can reuse the allocated statement handle in subsequent calls. However, this improvement is very small and sometimes can even be negligible compared to the time needed to compile the SQL statement into the BLR form.

The PreparedStatement interface addresses such inefficiencies. An object that implements this interface represents a precompiled statement that can be executed multiple times. If we use the execution flow described in the "Statement dynamics" section, it allows us to go directly to step 4 for repeated executions.

However, executing the same statement with the same values makes little sense, unless we want to fill the table with the same data, which usually is not the case. Therefore, JDBC provides support for parametrized statements — SQL statements where literals are replaced with question marks (‘?’), so-called positional parameters. The application then assigns values to the parameters before executing the statement.

Our first example in this chapter can be rewritten as shown below. At first glance the code becomes more complicated without any visible advantage.

So, where’s the advantage? First of all, prepared statements parameters protect against SQL injection as the values are sent separately from the statement itself. It is not possible to change the meaning of a statement due to incorrect string concatenation, so data leaks or other problems caused by SQL injection can be avoided. Second of all, the driver handles conversion of Java object types to the correct format for the target datatype in Firebird: you don’t need to convert a Java value to the correct string literal format for Firebird’s SQL dialect.

To address some identified problems, we can redesign our application to prepare those statements before calling that code (for example in a constructor) and close them when the application ends. In that case the code can be more compact (see the next example). Unfortunately, the application is now responsible for prepared statement management. When a connection is closed, the prepared statement object will be invalidated, but the application will not be notified. And when the application uses similar statements in different parts of the application, the refactoring might affect many classes, possibly destabilizing the code. So, the refactoring on this example is not something we want to do.

The answer to the advantage question is hidden in the prepareStatement(String) call. Since the same statement can be used for different parameter values, the connection object could have a possibility to perform prepared statement caching. A JDBC driver can ignore the request to close the prepared statement, save it internally and reuse it each time application asks to prepare an SQL statement that is known to the connection.

The CallableStatement interface extends PreparedStatement with methods for executing and retrieving results from stored procedures. It was introduced in the JDBC specification to unify access to the stored procedures across different database systems. The main difference to PreparedStatement is that the procedure call is specified using the portable escape syntax^[6]:

Each stored procedure is allowed to take zero or more input parameters, similar to the PreparedStatement interface. After being executed, a procedure can either return data in the output parameters or it can return a result set that can be traversed. Though the interface is generic enough to support database engines that can return both and have multiple result sets. These features are of no interest to Jaybird users, since Firebird does not support them.

The IN and OUT parameters are specified in one statement. The syntax above does not allow to specify the type of the parameter, therefore additional facilities are needed to tell the driver which parameter is will contain output values, the rest are considered to be IN parameters.

Batch updates are intended to group multiple update operations to be submitted to a database server to be processed at once. Firebird 3.0 and earlier did not provide support for such functionality, so Jaybird 4 and earlier emulate it by issuing separate update commands.

Jaybird 5 Firebird 4.0 added support for server-side batch updates, which is implemented in Jaybird 5 for prepared statements, see Server-side Batch Updates.

The escape syntax was introduced as a portable JDBC-specific syntax to represent parts of the SQL language that are (or were) usually implemented differently by database vendors. The escaped syntax is also used to define features that might not be implemented by the database server, but can have an appropriate implementation in the driver.

The JDBC specification defines escaped syntax for the following

ResultSet objects are created when either Statement.executeQuery(String) or Statement.getResultSet() methods are called, or when obtaining the generated keys from Statement.getGeneratedKeys(). Statement.getResultSet() is used in combination with Statement.execute(String) and can be called only once per result set (see the following two examples; Jaybird 5.0.5).

Firebird supports multiple concurrent transactions over the same database connection. This allows applications that work via the native Firebird API to save the number of network connections, which in turn saves the resources on the server^[7].

This model however cannot be applied to each database engine in the world and the designers of the JDBC API have chosen a model where each database connection has one and only one active transaction associated with it. Also, unlike the Firebird model, where transactions require explicit start, the JDBC specification requires the driver to start transaction automatically as soon as a transactional context is needed.

The following code shows a very simple example of using transactions in JDBC where a hypothetical intruder that increases salary of each employee twice and uses explicit transaction control in JDBC. They also try to hide their identity and if the operations succeed, they commit the transaction, otherwise the roll the changes back.

To use transactions, the application first switches the auto-commit mode off (see below for more information), then creates a java.sql.Statement object, and executes an UPDATE statement. Please note, that there is no explicit transaction start, a new transaction will be started right before executing the statement (step 2).

If we work with a database where not only referential integrity is preserved, but also reasonable security rules are encoded in the triggers, it will raise an error preventing cleaning the audit trails information. In this case the intruder chooses to undo all the changes they made, so that nobody notices anything. But if no security rules are implemented, they commit the transaction.

When a connection uses explicit transaction control, each transaction must be finished by calling the commit() or rollback() methods of the Connection object before the connection is closed. If a transaction was not finished, but the close method is called, the active transaction is rolled back automatically. This also happens when you did not commit or roll back the transaction, and did not close the connection explicitly and that Connection object became eligible for garbage collection. Jaybird 5 In this case, the class finalizer implicitly invokes the close() method, which in turn rolls the transaction back.

Each newly created connection by default has the auto-commit property enabled. In other words, the duration of the transaction is limited by the duration of statement execution, or more formally — the transaction is ended when the statement is completed. The point when statement execution is considered complete, is defined in the specification as:

Jaybird 6 In Jaybird 5 and earlier, in auto-commit mode, a TYPE_FORWARD_ONLY result set was implicitly closed when next() returned false. This was to conform to the requirements of JDBC 3.0, but since this was relaxed in JDBC 4.0 (see quote above), this behaviour was changed in Jaybird 6.

Jaybird 6 In auto-commit mode, a result set will now remain open until explicitly closed using ResultSet.close(), when any statement is executed, when the auto-commit mode is disabled, or by the close of the Statement or Connection.

If there is an ongoing transaction and the value of the auto-commit property is changed, the current transaction is committed.

Note, when a connection is obtained via a javax.sql.DataSource object and container managed transactions are used (for example, the application is executing inside an EJB container), it is an error to call setAutoCommit method.

Special care should be taken when using multiple statements in auto-commit mode. The JDBC 2.0 specification did not fully define the rules for the statement completion as it did not define the behavior of multiple ResultSet objects created using the same connection object in auto-commit mode.

Since Firebird does not allow the result set to remain open after the transaction ends, Jaybird 1.5.x and below cached the complete result set in memory when SELECT statements were executed and the transaction was committed. This had an adverse effect on allocated memory when the result set is big, especially when it contains BLOB fields. The JDBC 3.0 specification addressed this unclear situation (see above) and Jaybird 2.1 was improved to correctly handle them. It also allowed to reduce the memory footprint — the driver no longer caches non-scrollable and non-holdable result sets in memory.

However, some Java applications that do not conform the current JDBC specification might no longer work with Jaybird 2.1 and above unless additional steps are taken.

The piece of code below works perfectly with explicit transaction control. However, it won’t work correctly with auto-commit with a driver — like Jaybird — that complies with the JDBC 3.0 specification, when the selectStmt and updateStmt object are created by the same connection object (step 1). When the UPDATE is executed in step 3, the result set produced by the SELECT statement must be closed before the execution. When the Java application tries to fetch the next record by calling the rs.next() method, it will receive an SQLException with a message "The result set is closed".

The only correct solution to this situation is to fix the application by either using explicit transaction control, or by using two connection objects, one for SELECT statement and one for UPDATE statement.

Unfortunately, not all applications can be changed either because there is no source code available or, simply, because any change in the code requires complete release testing of the software. To address this, Jaybird 2.1 introduced the connection parameter defaultHoldable which makes result sets holdable by default. The holdable result sets will be fully cached in memory, but won’t be closed automatically when transaction ends.^[8] This property also affects the default holdability of result sets when auto-commit is disabled.

See Default holdable result sets for more information.

A transaction can be declared read-only to reduce the possibility of lock conflicts. In general, this makes little sense for Firebird, because of its multi-generational architecture, where readers do not block writers and vice versa. However, in some cases it can be useful.

It is not allowed to connect with a read-write transaction to a database located on a read-only media, for example, a CD-ROM. The reason is that, to guarantee consistency of the read-write transactions, Firebird has to increase the transaction identifier when the transaction starts, and store the new value on the so-called Transaction Inventory Page even if no changes are made in that transaction. This requirement can be relaxed if a transaction is declared read-only and the engine ensures that no data can be modified.

Another reason is that long-running read-write transactions inhibit the process of collecting garbage, i.e. a process of identifying previous versions of the database records that are no longer needed and releasing the occupied space for the new versions. Without garbage collection the database size will grow very fast and the speed of the database operations will decrease, because the database engine will have to check all available record versions to determine the appropriate one.

Therefore, if you are sure that application won’t modify the database in the transaction, use the setReadOnly method of the java.sql.Connection object to tell the server that the transaction is read-only.

The ANSI/ISO SQL standard defines four such levels, each next one weaker than the previous. These isolation levels are also used in the JDBC specification:

Firebird, however, defines other isolation levels: read_committed, concurrency and consistency. Only the read_committed isolation level can be mapped to the same level defined by the ANSI/ISO SQL standard. Dirty reads are prevented, non-repeatable reads as well as phantom reads can occur.

The concurrency isolation level is stronger than repeatable read isolation defined in ANSI/SQL standard and satisfies the requirements of a serializable isolation level, however, unlike RDBMSes with locking concurrency control, it guarantees better performance.

And finally Firebird provides a consistency isolation level which in combination with table reservation feature guarantees the deadlock-free execution of transactions. A transaction will be prevented from starting if there is already another one with the overlapping sets of the reserved tables. This isolation level guarantees truly serial history of transaction execution.

To satisfy the JDBC specification Jaybird provides a following default mapping of the JDBC transaction isolation levels into Firebird isolation levels:

The default mapping is specified in the Jaybird code and can be overridden via the connection properties.

The property accepts the “short” names as shown, or the “long” names with the isc_dpb_ prefix, as defined in org.firebirdsql.jaybird.fb.constants.TpbItems (Jaybird 5 and higher) or org.firebirdsql.gds.ISCConstants (older versions). For readability, we recommend using the “short” names.

The overridden mapping is used for all transactions started within the database connection. If the default mapping is overridden via the data source configuration, it will be used for all connections created by the data source.

Savepoints provide finer-grained control over transactions by providing intermediate steps within a larger transaction. Once a savepoint has been set, a transaction can be rolled back to that point without affecting preceding work.

To set a savepoint, use the following code:

Note, rolling back to the savepoint automatically releases and invalidates any savepoints that were created after the released savepoint.

If the savepoint is no longer needed, you can use the Connection.releaseSavepoint method to release system resources. After releasing a savepoint, it is no longer possible to roll back the current transaction to that savepoint. Attempts to call the rollback(Savepoint) method will result in an SQLException. Savepoints that have been created within a transaction are automatically released when that transaction is committed or rolled back.

The behavior of Firebird transactions is internally controlled by the Transaction Parameter Buffer (TPB), which specifies different transaction properties:

The TPB is automatically generated depending on the transaction isolation level specified for the java.sql.Connection object. Additionally, if the connection is set to read-only mode, this is reflected in the TPB by appropriate constant.

Usually there is no need to manipulate the TPB directly. However, the lock resolution mode as well as table reservations cannot be specified by using the standard JDBC interfaces. For the cases where this is needed, Jaybird provides an extension of the JDBC standard.

The above presents an example of populating the TPB with custom parameters.

The constants used in the examples in this chapter were introduced in Jaybird 5. Earlier versions have constants of the same name defined in org.firebirdsql.gds.ISCConstants, and similarly named constants in org.firebirdsql.gds.TransactionParameterBuffer (i.e. without isc_tpb_ prefix and fully capitalized). Those older constants will be removed in Jaybird 6 in favour of org.firebirdsql.jaybird.fb.constants.TpbItems.

Table reservation allows you to specify the database tables and their corresponding access modes at the beginning of the transaction. When the transaction is started, the engine tries to obtain the requested locks for the specified tables and proceeds only when all of them were successfully obtained. Such behavior allows to create a deadlock-free execution history^[9].

The table reservation is specified via a TPB and includes the table to lock, the lock mode (read or write) and lock type (shared, protected and exclusive).

This shows an example of reserving the TEST_LOCK table for writing in a protected mode. The code does the following:

The lock mode to the table specified in the TPB can be either

The lock type can be either

The TpbItems.isc_tpb_exclusive mode was introduced in later versions of Firebird, however it behaves like TpbItems.isc_tpb_protected mode for all read-write transactions.

The lock conflict table depends on the isolation level of the transactions and has the following properties:

The Firebird syntax also defines a number of transaction management statements. In this section we discuss the COMMIT [WORK], ROLLBACK [WORK] and SET TRANSACTION statements, which have a hard transaction boundary (either ending or starting a transaction).

In general, you should not execute these methods with Jaybird. The JDBC specification formally discourages use of statements or functionality which is accessible through the JDBC API:

In Jaybird 5 and older, the COMMIT and ROLLBACK statements can — sometimes — be executed, but they bring the connection in an inconsistent state, which will prevent further use of the connection, and execution of the SET TRANSACTION statement will always fail as the statement is executed with an active transaction.

Since Jaybird 6, these statements are rejected by default, but support can be enabled with the connection property allowTxStmts.

Allowing execution of these statements can sometimes be helpful, for example for executing scripts, or simplifying access to more advanced features of transaction configuration like Table Reservation.

The ServiceManager interface and the FBServiceManager class are defined as the common superclasses providing setters and getters for common properties as well as some common routines. The following properties can be specified:

The last parameter requires some explanation. The calls to all Services API routines are asynchronous. The client application can start the call, but there are no other means to find out whether execution of the service call is finished or not except reading the output of the service call — EOF in this case means that execution is finished.

The FBServiceManager converts the asynchronous calls into a synchronous call by constantly polling the service output stream. If the logger property is specified the received data is copied into the specified OutputStream, otherwise it is simply ignored and the EOF-marker is being watched.

This behavior can be changed by overriding the appropriate method in the FBServiceManager class and/or subclasses. The only requirement is to detach from the service manager when it is no longer needed.

Jaybird supports gbak, gbak streaming, and nbackup backup and restore.

The next service available is the user management. The routines are defined in the UserManager interface and are implemented in the FBUserManager class. Additionally, there is an User interface providing getters and setters for properties of a user account on the server and corresponding implementation in the FBUser class.^[13] The available properties of the FBUser class are:

The management class, FBUserManager has the following methods to manipulate the user accounts on the server:

An example of using the FBUserManager class:

Database maintenance is something that everybody would prefer to avoid, and, contrary to the backup/restore and user management procedures, there is little automation that can be done here. Usually the maintenance tasks are performed on the server by the database administrator, but some routines are needed to perform the automated database upgrade or perform periodic checks of the database validity.

This chapter describes the methods declared in the MaintenanceManager interface and its implementation, the FBMaintenanceManager class.

Jaybird 5

A new class was added in Jaybird 5, org.firebirdsql.management.FBTableStatisticsManager, which can be used to retrieve the table statistics of a connection.

Create an instance with FBTableStatisticsManager#of(java.sql.Connection) — the connection must unwrap to a FirebirdConnection — and retrieve a snapshot of the statistics with FBTableStatisticsManager#getTableStatistics().

And last but not least is the StatisticsManager interface and corresponding implementation in the FBStatisticsManager class, which allow to obtain statistical information for the database, like page size, values of OIT and Next transactions, database dialect, database page allocation and its distribution.

The following methods provide the functionality equivalent to the gstat command line tool, the output of the commands is written to the output stream specified in the logger property. It is the responsibility of the application to correctly parse the text output if needed.

An event is a message generated in a trigger, stored procedure or execute block that is delivered to subscribed applications. The event is characterized only by a name which is used when the event is posted, therefore two different events must have two different names. The applications that subscribe for events are required to specify the event names of interest, no wildcards are allowed; and applications either provide a callback function that will be invoked in case of event or are required to poll for the posted events periodically.

Events are delivered to the application only on (after) commit of the transaction that generated the event. Firebird does not provide any guarantees about the time of event delivery, it depends on the load of the Firebird engine, application load, network delays between application and the database system. The database engine will continue operating even if no application subscribes to events or when the subscribed application crashed in the meantime.

It can also happen that multiple transactions will be committed before the events are delivered to the client system. But even in such case the callback function will be invoked only once, and only the event name and the count of the events will be passed as parameters. The same applies to periodical polling, the application will receive event names and counts of the events since last polling.

Internally, Firebird can be thought to store the subscription information in a table where columns contain event names, rows correspond to the subscribed applications and the cells contain the count of the particular event for a particular application. When an event is posted in trigger or stored procedure, Firebird checks the subscription information and increases the event count for the subscribed applications. Another thread checks the table periodically and notifies the application about all new events relevant to the particular application. Such mechanism allows Firebird to keep the event notification table very small^[18] and to reduce the number of messages sent to the application.

It is not possible to pass parameters with the event, e.g. an ID of the modified records. It is also not possible to encode such information in the event names, wildcards are not supported. For such cases, applications should maintain a change tracking table where the IDs of the modified records are stored and the event mechanism is used to tell the application that new records were added to the table.

Events are posted from PSQL code (trigger, stored procedure, execute block, function) using the POST_EVENT command. It is possible to create a stored procedure with the sole purpose of posting events:

The EXECUTE BLOCK statement can be used to execute PSQL statements within DSQL code:

The design of the classes and interfaces in the org.firebirdsql.event package is similar to the Services API support; there is a central manager-class that establishes a database connection and provides service methods to work with the events, a callback interface that applications must implement to use the asynchronous event notification and an interface representing a database event with two properties, event name and occurrence count.

Applications have to configure the following properties before starting use of the implementation EventManager interface:

After configuring these properties, the application has to invoke the connect() method to establish a physical connection to the database. At this point the EventManager is ready to receive event notifications.

Now the application developer has two choices: use asynchronous event notification or use methods that will block until an event is delivered or a timeout occurs.

Firebird 3.0

Firebird 3.0 introduced authentication plugins together with a new authentication model. By default, Firebird 3.0 uses the authentication plugin Srp (Secure remote password). It also includes plugins Legacy_Auth that supports the pre-Firebird-3 authentication mechanism, and — Firebird 3.0.4 — Srp256. Firebird 4.0 introduced the plugins Srp224, Srp384 and Srp512.

The original Srp plugin uses SHA-1, the new Srp-variants use SHA-224, SHA-256, SHA-384 and SHA-512 respectively.^[19]

Firebird 3.0

Firebird 3.0 and higher have support for encrypting the data sent over the network. This wire encryption is configured using the connection property wireCrypt, with the following (case-insensitive) values:

The default value acts as ENABLED for pure Java connections, for native connections this wil use the fbclient default (either Enabled or the configured value of WireCrypt from a firebird.conf read by the native library).

Connection property wireCrypt=REQUIRED will not reject unencrypted connections when connecting to Firebird 2.5 or lower. This behavior matches the Firebird 3.0 client library behavior. The value will also be ignored when using native connections with a Firebird 2.5 client library.

Using wireCrypt=DISABLED when Firebird 3.0 or higher uses setting WireCrypt = Required (or vice versa) will yield error "Incompatible wire encryption levels requested on client and server" (error: isc_wirecrypt_incompatible / 335545064).

The same error is raised when connecting to Firebird 3.0 and higher with a legacy authentication user with connection property wireCrypt=REQUIRED, as the legacy authentication does not provide an encryption key to establish an encrypted connection.

Alternative wire encryption plugins are currently not supported, although we made some preparations to support this. If you want to develop such a plugin, contact us on the firebird-java Google Group, so we can work out the details of adding plugin support.

Firebird 3.0

Jaybird supports zlib wire compression in the pure Java wire protocol. Compression can be enabled using boolean connection property wireCompression.

The connection property only has effect for the pure Java wire protocol connections on Firebird 3.0 and higher, if the server has the zlib library. Native connections will follow the WireCompression configuration in the firebird.conf read by the client library, if the zlib library is on the search path.

Compression is currently disabled by default. This may change in future versions of Jaybird to be enabled by default.

The wireCompression property is also available on data sources and the management classes in org.firebirdsql.management.

Firebird 3.0

Jaybird 3.0.4 added support for Firebird 3.0 database encryption callbacks in the pure Java implementation of the version 13 protocol.

The current implementation is simple and only supports replying with a static value from a connection property. Be aware that a static value response for database encryption is not very secure as it can easily lead to replay attacks or unintended key exposure.

Future versions of Jaybird may introduce plugin support for database encryption plugins that require a more complex callback.

The static response value of the encryption callback can be set through the dbCryptConfig connection property. Data sources and ServiceManager implementations have an equivalent property with the same name. This property can be set as follows:

Because of the limitation of connection URL parsing, we strongly suggest to avoid plain string values with & or ;. Likewise, avoid : so that we can support other prefixes similar to base64: and base64url: in the future. If you need these characters, consider using a base64 encoded value instead.

Jaybird 3.0.9 and higher expects keys and values in the JDBC URL to be URL encoded. When the base64 encoded value contains +, it must be escaped as %2B in the JDBC URL, otherwise it is decoded to a space and decoding will fail. Jaybird 5 introduced base64url, an alternative to base64, which doesn’t use +, thus avoiding the need to escape.

For service operations, as implemented in the org.firebirdsql.management package, Firebird requires the KeyHolderPlugin configuration to be globally defined in firebird.conf. Database-specific configuration in databases.conf will be ignored for service operations. Be aware that some service operations on encrypted databases are not supported by Firebird 3.0 (e.g. gstat equivalents other than gstat -h or gstat -e).

This connection property enables a connection to create holdable result sets by default. This property can be used as a workaround for applications that expect a result set to remain open after commit, or have expectations regarding result sets in auto-commit mode that do not conform to the JDBC specification.

Specifically, such applications open a result set and, while traversing it, execute other statements using the same connection. According to the JDBC specification the result set has to be closed if another statement is executed using the same connection in auto-commit mode. With the default result set holdability, close on commit, doing this yields a SQLException with message "The result set is closed".

The property is accessible as:

This option is enabled by specifying the connection property useFirebirdAutocommit=true.

With this option, a connection in auto-commit mode will configure the transaction to use isc_tpb_autocommit. This means that Firebird server will internally commit the transaction after each statement completion. Jaybird itself will not commit until connection close (or disabling auto-commit). The exception is if the statement was of type isc_info_sql_stmt_ddl, in that case Jaybird will commit on statement success and rollback on statement failure (just like it does for all statements in normal auto-commit mode). The reason is that Firebird for some DDL commands only executes at a real commit boundary and relying on the Firebird auto-commit is insufficient.

On statement completion (as specified in JDBC), result sets will still close unless they are holdable over commit. The result set is only closed client-side, which means that the cursor remains open server-side to prevent roundtrips. This may lead to additional resource usage server-side unless explicitly closed in the code. Note that any open blobs will be closed client- and server-side (until this is improved with jaybird#442).

A connection can be interrogated using FirebirdConnection.isUseFirebirdAutocommit() if it uses isc_tpb_autocommit.

If you manually add isc_tpb_autocommit to the transaction parameter buffer, and you enable this option, the isc_tpb_autocommit will be removed from the TPB on setAutoCommit(false).

Artificial testing with repeated inserts (using a prepared statement) against a Firebird server on localhost shows that this leads to a reduction of execution time of +/- 7%.

Support for this option is experimental, and should only be enabled if you 1) know what you’re doing, and 2) really need this feature. Internally isc_tpb_autocommit uses commit_retaining, which means that using this feature may increase the transaction gap with associated sweep and garbage collection impact.

Introduced in Firebird 2.1, the MON$ATTACHMENTS monitoring table contains information about database attachments, including information about the client process.

The columns MON$REMOTE_PID and MON$REMOTE_PROCESS report the process id and process name of the connected process.

Jaybird 5 and earlier do not provide this information by default, except for the process ID of native connections, but they can be specified with system or connection properties (see below). This has two main reasons: until recently Java did not have a portable way of retrieving the process id, and in most cases the process name is just “java” (or similar), which is not very useful.

Jaybird 6 Starting with Jaybird 6, pure Java connections will by default report the actual process ID, just like native connections. When a SecurityManager is installed, the entire call-chain needs to have the RuntimePermission("manageProcess") to obtain the process ID in pure Java connections. If this permission check fails, Jaybird will silently ignore it and not set the isc_dpb_process_id based on the actual process ID.

Jaybird 6 The process name is returned from Connection.getClientInfo("ApplicationName"), if the client info property ApplicationName has not been set explicitly with Connection.setClientInfo, and/or if there is no property ApplicationName in the USER_SESSION context.

Native connections will always report the actual process ID, if a fbclient of Firebird 2.1 or higher is used. This cannot be overridden using the options below.

Since Firebird 3.0, the MON$ATTACHMENTS table also includes the column MON$CLIENT_VERSION. Jaybird will report its full version (e.g. Jaybird jaybird-5.0.1.java11).

It is possible to specify the process name and process id in two ways:

Firebird 4.0

Firebird 4.0 introduced the SET BIND statement and isc_dpb_set_bind DPB item. This allows you to define data type conversion rules for compatibility or ease of processing data.

This feature is specifically necessary for using the WITH TIME ZONE types under Jaybird 3, or Jaybird 4 on Java 7. See also Defining time zone data type bind.

In Jaybird this is exposed as connection property dataTypeBind (alias set_bind). The value of this connection property is a semicolon-separated list of data type bind definitions.

A data type bind definition is of the form <from-type> TO <to-type>. A definition is the same as the second half of a SET BIND statement after the OF. See the Firebird documentation of SET BIND for more information. Invalid values or impossible mappings will result in an error on connect.

When using the dataTypeBind connection property in a JDBC URL, the semicolons of the list need to be encoded as %3B, as unescaped semicolons in the JDBC URL are an alternative to & as the separator between properties.

For example:

When the property is set through a Properties object or a DataSource configuration, encoding the semicolon is not necessary and will result in errors.

For example:

Values set through this connection property will be the session default configuration, which means that they are retained (or reverted to) when executing ALTER SESSION RESET.

Jaybird 6

Since Jaybird 6, the pure Java protocol implementation will no longer attempt to connect with protocol versions of Firebird versions which are not supported. Since the minimum supported Firebird version in Jaybird 6 is Firebird 3.0, this means that protocol versions 10, 11 and 12 are no longer tried by default. Protocol versions higher than 18 (Firebird 5.0.0) will also not be attempted (assuming a protocol implementation with that version number exists on the classpath).

As a workaround, the connection property enableProtocol can enable unsupported protocols, assuming a suitable protocol implementation is available on the classpath.

This connection property can have the following values:

A different default value of enableProtocol can be set using the system property org.firebirdsql.jdbc.defaultEnableProtocol. This system property is checked each time a connection configuration is created, so it can be changed at runtime. If you use a Jaybird DataSource implementation, it uses the value at the time the DataSource is created; if you use DriverManager — this can include third-party data sources, it uses the value at the time the connection is created.

Jaybird 6
Firebird 3.0

Firebird 3.0 added packages, which can contain stored procedures and functions. The JDBC API does not provide a “standard” way of accessing information about packages, or the routines defined in packages. Instead of adding additional Jaybird-specific metadata methods, we’ve added an “opt-in” feature that provides access through normal metadata methods, using the “catalog” to report packages.

This feature can be enabled by setting the connection property useCatalogAsPackage to true. When this connection property is enabled, the DatabaseMetaData of that connection will have the following changes in behaviour:

The useCatalogAsPackage connection property does not result in any other behaviour.

Keep in mind, that this is non-standard behaviour, and standard JDBC tools or libraries may not work correctly when this property is enabled. This feature may be discontinued and removed in the future if Jaybird needs to implement “real” catalogs (e.g. because Firebird started supporting catalogs).

See also jdp-2023-08: Use Catalog as Package

Client info properties allow you to set properties on a connection for informational purposes, or as the JDBC API documentation says “used for accounting, diagnostics and debugging purposes only”​^[23].

Jaybird 6

The Boolean connection property createDatabaseIfNotExist instructs Jaybird to attempt to create the database if it does not exist yet. This property can be used in the JDBC URL, in the Properties object passed to DriverManager, and can be set on data sources.

Jaybird also allows you to create databases using org.firebirdsql.management.FBManager, but this is not always accessible, for example in tools or libraries that only expose or use the JDBC API. This property provides an alternative to create databases in those cases.

Jaybird also allows you to set or override connection properties specifically for creating the database by suffixing the property name with @create (case-sensitive). This can be used to set configuration properties that are only relevant for creating the database, or — for example — to use a different user or role for creating the database than used for normal connections.

As an example:

If the database already exists, the connection will be made with user normaluser, but if the database does not exist, the database will be created with user sysdba.

For more information, see also jdp-2024-02: Create database through JDBC URL.

Jaybird provides support for the getGeneratedKeys() JDBC feature for Statement and PreparedStatement. This feature can be used to retrieve the generated ids (and other columns) from DML statements.

This feature is available for Connection.prepareStatement, and Statement.execute, Statement.executeUpdate and Statement.executeLargeUpdate.

There are four distinct use-cases:

This functionality is available for INSERT, UPDATE, UPDATE OR INSERT and DELETE, and for MERGE (Firebird 3.0).

Generated keys retrieval modifies the statement to add a RETURNING-clause. Firebird 4.0 and earlier only support RETURNING for DML operations that affect a single row. Attempting to use generated keys retrieval with a statement that affects multiple rows will yield the error "multiple rows in singleton select".

Jaybird 5/Firebird 5.0 Support for multi-row RETURNING is introduced in Firebird 5.0. Support for multi-row RETURNING is available in Jaybird 5.

The examples in this section use the following (Firebird 3.0) table:

On Firebird 2.1 and higher, Jaybird will use the procedure type information from the database metadata to decide how to execute CallableStatement. When a procedure is selectable, Jaybird will automatically transform a call-escape or EXECUTE PROCEDURE statement to a SELECT.

In some cases this automatic transformation to use a SELECT leads to problems. You can explicitly set FirebirdCallableStatement.setSelectableProcedure(false) to fix most of these issues, but this is not always an option. For example spring-data-jpa’s @Procedure will not work correctly with selectable procedures, but you can’t call setSelectableProcedure.

To disable this automatic usage of procedure type information, set connection property ignoreProcedureType=true. When necessary you can use FirebirdCallableStatement.setSelectableProcedure(true) to execute a procedure using SELECT.

Be aware though, when EXECUTE PROCEDURE is used with a selectable procedure, it is executed only up to the first SUSPEND, and the rest of the stored procedure is not executed.

For Firebird 2.0 and lower this property has no effect, as there the procedure type information is not available.

Jaybird 5
Firebird 5.0

On Firebird 5.0 and higher, Jaybird 5 and higher support server-side scrollable cursors..

This feature is only available on pure Java connections, and only when explicitly enabled with connection property scrollableCursor. Support is only available in pure Java, as the bindings for native and embedded use the legacy fbclient API, and scrollable cursors are not exposed in the legacy fbclient API.

The connection property scrollableCursor has the following values (case-insensitive):

If a connection does not support scrollable cursors, or if holdable cursors are requested, the behaviour will silently fall back to emulated scrollable cursors.

Compared to emulated scrollable cursor, server-side scrollable cursors exhibit slightly different behaviour in Jaybird 5:

In Jaybird 6 and higher, emulated has been changed to have the same behaviour as server.

See also jdp-2021-04 and jdp-2024-05.

Jaybird 5
Firebird 4.0

A Boolean property to configure use of server-side batch updates. The default value is true, to use server-side batch updates when possible. Set to false to disable server-side batch updates and always use emulated batch updates.

See Server-side Batch Updates for more information.

Jaybird 5
Firebird 4.0

Configures the server-side batch buffer size in bytes.

Too small buffer sizes will use 128KB or the size necessary for two rows (whichever is bigger). Too large buffer sizes will use the server-side maximum (256MB as of Firebird 4.0).

See Server-side Batch Updates for more information.

Jaybird 6

Enables support for the transaction management statements COMMIT [WORK], ROLLBACK [WORK] (without retain or savepoint) and SET TRANSACTION.

When disabled (false, the default), Jaybird will reject attempts to execute or prepare these statements with one of the following errors:

When enabled (true), Jaybird will support these statements under the following conditions:

These statements will throw an SQLException under the following conditions:

The allowTxStmts connection property has no effect on the other transaction management statements, i.e. COMMIT [WORK] RETAIN, ROLLBACK [WORK] RETAIN, ROLLBACK [WORK] TO [SAVEPOINT] name, SAVEPOINT name and RELEASE SAVEPOINT name [ONLY]. These statements can always be executed. However, for the savepoint related methods it is advisable to use the relevant JDBC API methods instead.

See also Transaction management statements, and jdp-2024-01: Explicit support for transaction statements.

Jaybird 5.0.5

By default, Jaybird queries extended metadata information. This can be disabled by setting the connection property extendedMetadata to false.

This setting only affects the following methods:

Invocation of either of these methods will query the extended metadata needed for both methods.

Setting extendedMetadata to false may improve performance of these methods in exchange for an estimated precision, and not being able to determine the auto-increment state of a column.

Jaybird logs a variety of information during its work.

For logging, jaybird uses the following log levels:

The JDBC (and SQL standard) types BINARY and VARBINARY are called CHAR(n) CHARACTER SET OCTETS and VARCHAR(n) CHARACTER SET OCTETS in Firebird.

(Firebird 4.0) Firebird 4.0 introduces the names BINARY and VARBINARY/BINARY VARYING as aliases for (VAR)CHAR(n) CHARACTER SET OCTETS.

In Java, binary and varbinary are usually handled with byte arrays and InputStream/OutputStream.

Firebird 3.0

Firebird 3.0 introduced the SQL standard type BOOLEAN.

Firebird 4.0

Firebird 4.0 introduces the SQL:2016 DECFLOAT datatype, a decimal floating point with a precision of 16 or 34 digits (backed by an IEEE-754 Decimal64 or Decimal128). See the Firebird 4.0 documentation for details on this datatype.