1.1 Jaybird 2.2.x End-Of-Life

Jaybird 2.2.15 is the last version of Jaybird 2.2.x, and we will not plan new changes or fixes for 2.2.x. This effectively means that Jaybird 2.2.x is End-Of-Life.

If you run into problems, we will recommend that you switch to Jaybird 3 (or 4 once it is released). Contact us on Firebird-Java for questions.

1.2 Supported Firebird versions

Jaybird 2.2.15 was tested against Firebird 2.5.8, and Firebird 3.0.4, but should also support other Firebird versions from 1.0 and up. The Type 2 and embedded server JDBC drivers require the appropriate JNI library. Precompiled JNI binaries for Windows and Linux platforms are shipped in the default installation, other platforms require porting/building the JNI library for that platform.

Connecting to Firebird 3 requires some additional configuration, see Jaybird and Firebird 3.0 for details.

Firebird 4 is not formally supported in Jaybird 2.2.x, although connecting and most functionality will work. We suggest that you use Jaybird 3.x or higher for Firebird 4. Support for newer data types like DECFLOAT and NUMERIC/DECIMAL with precision higher than 18 will be introduced in Jaybird 4.

This driver does not support InterBase servers due to Firebird-specific changes in the protocol and database attachment parameters.

1.3 Supported Java versions

Jaybird 2.2.15 supports Java 6 (JDBC 4.0), Java 7 (JDBC 4.1) and Java 8 (JDBC 4.2). Support for earlier Java versions has been dropped.

Jaybird 3 supports Java 7, 8, and 11.

1.4 Specification support

Jaybird 2.2.15 supports the following specifications:

2.1 Changelog

2.2 Support for getGeneratedKeys()

Support was added for the getGeneratedKeys() functionality for Statement and PreparedStatement.

There are four distinct use-cases:

1. Methods accepting an int parameter with values of Statement.NO_GENERATED_KEYS and Statement.RETURN_GENERATED_KEYS

   When NO_GENERATED_KEYS is passed, the query will be executed as a normal query.

   When RETURN_GENERATED_KEYS is passed, the driver will add all columns of the table in ordinal position order (as in the (JDBC) metadata of the table). It is advisable to retrieve the values from the getGeneratedKeys() result set by column name.

   We opted to include all columns as it is next to impossible to decide which columns are filled by a trigger or otherwise and only returning the primary key will be too limiting

2. Methods accepting an int[] parameter with column indexes.

   The values in the int[] parameter are the ordinal positions of the columns as specified in the (JDBC) metadata of the table. For a null or empty array the statement is processed as is. Invalid ordinal positions are ignored and silently dropped (be aware: the JDBC specification is not entirely clear if this is valid behavior, so this might change in the future)

3. Methods accepting a String[] parameter with column names.

   The values in the String[] are the column names to be returned. The column names provided are processed as is and are not checked for validity or the need of quoting. Providing non-existent or incorrectly (un)quoted columns will result in an exception when the statement is processed by Firebird.

   This method is the fastest as it does not retrieve metadata from the server.

4. Providing a query already containing a RETURNING clause. In this case all of the previous cases are ignored and the query is executed as is. It is possible to retrieve the result set using getGeneratedKeys().

This functionality will only be available if the ANTLR 3.4 runtime classes are on the classpath. Except for calling methods with NO_GENERATED_KEYS, absence of the ANTLR runtime will throw FBDriverNotCapableException.

This functionality should work for INSERT (from Firebird 2.0), and for UPDATE, UPDATE OR INSERT and DELETE (from Firebird 2.1).

2.3 Java 6 and JDBC 4.0 API support

Support was added for the following JDBC 4.0 features:

• Automatic driver loading: on Java 6 and later it is no longer necessary to use Class.forName("org.firebirdsql.jdbc.FBDriver") to load the driver
• Implementation of java.sql.Wrapper interface on various JDBC classes; in general it only unwraps to the specific implementation class (and superclasses) and implemented interfaces
• Support for chained exceptions (use getNextException() and iterator() to view other, related exceptions) and getCause() to retrieve the cause (deprecating similar getInternalException())
• Support for getClientInfo() and setClientInfo() on Connection

2.4 Java 7 and JDBC 4.1 API support

Support was added for the following JDBC 4.1 features:

• try-with-resources ²
• Statement closeOnCompletion

Other methods added by JDBC 4.1 will throw FBDriverNotCapableException (a subclass of SQLFeatureNotSupportedException).

2.5 Java 8 and JDBC 4.2 API support

Minimal support for JDBC 4.2 was added in Jaybird 2.2.4 and extended in 2.2.5:

• Large update counts: no actual support is provided, method call is forwarded to the normal update count method returning an int.
• Methods accepting java.sql.SQLType: no actual support is provided, method call is forwarded to equivalent method accepting a java.sql.Types integer value.

• PreparedStatement.setObject() and ResultSet.updateObject() now accept java.time objects:

  	DATE	TIME	TIMESTAMP	(VAR)CHAR	BLOB SUB_TYPE TEXT
  java.time.LocalTime		X		X	X
  java.time.LocalDate	X			X	X
  java.time.LocalDateTime	X	X	X	X	X
  java.time.OffsetTime				X	X
  java.time.OffsetDateTime				X	X

• Retrieval of java.time objects using getObject(int, Class<?>) and getObject(String, Class<?>) is not yet supported.

2.6 Jaybird on Maven

Jaybird 2.2.15 is available on maven, with a separate artifact for each supported Java version.

Groupid: org.firebirdsql.jdbc, artifactid: jaybird-jdkXX (where XX is 16, 17 or 18).
Version: 2.2.15

For example:

<dependency>
    <groupId>org.firebirdsql.jdbc</groupId>
    <artifactId>jaybird-jdk18</artifactId>
    <version>2.2.15</version>
</dependency>

When deploying to a JavaEE environment, exclude the javax.resource connector-api dependency as this will be provided by the application server.

2.7 Native and Embedded (JNI) 64-bit Windows and Linux support

The JNI libraries for native and embedded support now also have a 64 bit version.

2.8 Support for Firebird 2.5

Added support for Firebird 2.5 Services API enhancements:

• The security database can be set
• Support for SET/DROP AUTO ADMIN
• Mapping for new role RDB$ADMIN in security database
• Added new Firebird 2.1 shutdown/online modes available in Firebird 2.5 via the Services API
• Support for NBackup via Services API in Firebird 2.5
• Support for Trace/Audit via Services API in Firebird 2.5

Since Firebird 2.5, Firebird supports full UTF-8 database filenames and other connection properties (Database Parameter Buffer values). Jaybird does not yet support these changes, but a workaround is available:

This workaround consists of two steps:

1. Ensure your Java application is executed with the system property file.encoding=UTF-8 (either because that is the default encoding for your OS, or by explicitly specifying this property on the commandline of your application using -Dfile.encoding=UTF-8)
2. Include property utf8_filename=1 in the JDBC URL or (non-standard) properties of the datasource

This will only work if the Firebird server is version 2.5 or higher.

2.9 Support for Firebird 3.0

Jaybird 2.2.x only supports Firebird 3.0 using the legacy authentication. The new authentication model and wire protocol encryption is not yet supported. Technically these new protocol options will work when using the Type 2 driver, but this hasn’t been fully tested.

When using Jaybird with Firebird 3.0, make sure that

• Wire protocol encryption is not set to Required (set it to Enabled or Disabled)
• Legacy authentication is enabled on server
• The user has been created with the legacy usermanager

The new BOOLEAN data type is supported

See Jaybird and Firebird 3 for more details.

2.10 Improved support for OpenOffice / LibreOffice Base

The interpretation of the JDBC standard by Jaybird differs from the interpretation by OpenOffice / LibreOffice. To address some of the problems caused by these differences, Jaybird now provides a separate protocol for OpenOffice / LibreOffice.

When connecting from Base, use the protocol prefix jdbc:firebirdsql:oo:. Be aware that this is a variant of the pure Java wire protocol and not the native or embedded protocol.

Issues addressed by this protocol:

• Result sets are not closed when a statements is finished (eg fully read ResultSet or when creating a new Statement in auto-commit mode)
• DatabaseMetaData#getTablePrivileges(...) reports privileges granted to PUBLIC and to the current role (as reported by CURRENT_ROLE) as being granted to the user (after Jaybird 2.2.0 beta 1).

2.11 Use isc_tpb_autocommit in auto commit mode (experimental)

Added in Jaybird 2.2.9

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

With this option, Jaybird will configure the transaction to use isc_tpb_autocommit with autoCommit=true. This means that Firebird server will internally commit the transaction after each statement completion. Jaybird itself will not commit until connection close (or switching to autoCommit=false). 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 JDBC-401).

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 if autoCommit=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.

2.12 Other fixes and changes

• Replaced mini-j2ee.jar with connector-api-1.5.jar: make sure to remove the old mini-j2ee.jar from the classpath of your application.
• Dropped jaybird-pool jar from the distribution (all classes are included in the jaybird jar and the jaybird-full jar).
• FBResultSetMetaData#getcolumnName(int) will now return the original column name (if available) for compliance with the JDBC specification, getColumnLabel(int) will still return the alias (or the column name if no alias is defined). See Compatibility with com.sun.rowset.* for potential problems when using the reference implementation of CachedRowSet.
  Jaybird 2.2.1 introduced the connection property columnLabelForName which will revert to the old behavior when set to true. Be aware that the old behavior is not JDBC-compliant.
• FBDatabaseMetaData has been updated to include metadata columns defined by JDBC 3.0, 4.0 and 4.1. This also changes the position of OWNER_NAME column in the result set of getTables(..)as this column is Jaybird-specific and not defined in JDBC.
• FBDatabaseMetaData#getIndexInfo(..) now also returns expression indexes. The COLUMN_NAME column will contain the expression (if available).
• FBDatabaseMetaData#getIndexInfo(..) now correctly limits the returned indexes to unique indexes when parameter unique is set to true.
• The connection property octetsAsBytes can be used to identify fields with CHARACTER SET OCTETS as being (VAR)BINARY (in ResultSetMetaData only).
• The getTime(), getDate(), getTimestamp() methods which take a Calendar object now correctly handle conversions around Daylight Savings Time (DST) changes. Before, the time was first converted to the local JVM timezone, and then to the timezone of the provided Calendar, this could lose up to an hour in time. Now the time is converted directly to the timezone of the provided Calendar. (JDBC-154)

A full list of changes is also available at:

• Jaybird 2.2.12
• Jaybird 2.2.11
• Jaybird 2.2.10
• Jaybird 2.2.9
• Jaybird 2.2.8
• Jaybird 2.2.7
• Jaybird 2.2.6
• Jaybird 2.2.5
• Jaybird 2.2.4
• Jaybird 2.2.3
• Jaybird 2.2.2
• Jaybird 2.2.1
• Jaybird 2.2.0

3.1 Java support

Java 5 support has been dropped starting with Jaybird 2.2.8 as Java 5 has been on End-Of-Public-Updates³status since October 2009.

Java 6 support will be dropped for Jaybird 3.0 as Java 6 has been on End-Of-Public-Updates status since February 2013. This is a change from previous announcements that support would be dropped with Jaybird 3.1.

Java 7 support will be dropped for Jaybird 3.1 as Java 7 has been on End-Of-Public-Updates status since April 2015.

Java 9 support is expected to be introduced with Jaybird 3.0. There are no plans to add Java 9 support to Jaybird 2.2.x, although the driver is expected to function if JDBC 4.3 specific features are avoided.

For compatibility with Java 9 modules, versions 2.2.14 and 3.0.3 introduced the automatic module name org.firebirdsql.jaybird. This guarantees a stable module name for Jaybird, and allows for future modularization of Jaybird.

3.2 Firebird support

Jaybird 2.2 supports Firebird 1.0 and higher, but is only tested with Firebird 2.1, 2.5 and 3.0.

Firebird 4 is not formally supported in Jaybird 2.2.x, although connecting and most functionality will work.

Firebird 1.0 and 1.5 support will be dropped with Jaybird 3.0. In general this should not impact the use of the driver, but might have impact on the availability and use of metadata information. This also means that starting with Jaybird 3.0, bugs that only occur with Firebird 1.0 and 1.5 will not be fixed.

Firebird 2.0 support will be dropped with Jaybird 3.1.

3.3 Important changes to Datasources

The ConnectionPoolDataSource and XADataSource implementations in org.firebirdsql.pool and org.firebirdsql.pool.sun contain several bugs with regard to pool and connection management when used by a JavaEE application server. The decision was made to write new implementations in the package org.firebirdsql.ds.

The following implementation classes have been deprecated and will be removed in Jaybird 3.0:

• org.firebirdsql.pool.DriverConnectionPoolDataSource
• org.firebirdsql.pool.FBConnectionPoolDataSource
• org.firebirdsql.pool.sun.AppServerDataSource
• org.firebirdsql.pool.sun.AppServerXADataSource
• org.firebirdsql.jca.FBXADataSource
• org.firebirdsql.pool.SimpleDataSource

Their replacement classes are:

• org.firebirdsql.ds.FBConnectionPoolDataSource
• org.firebirdsql.ds.FBXADataSource
• org.firebirdsql.ds.FBSimpleDataSource (a normal DataSource)

We strongly urge you to switch to these new implementations if you are using these classes in an application server. The bugs are described in JDBC-86, JDBC-93, JDBC-131 and JDBC-144.

The deprecated classes can still be used with the DataSource implementations WrappingDataSource as the identified bugs do not occur with this implementation, but we advise you to switch to FBSimpleDataSource. If you require a standalone connection pool (outside an application server) or statement pooling, please consider using a third-party connection pool like C3P0, DBCP or HikariCP.

The new ConnectionPoolDataSource and XADataSource implementations only provide the basic functionality specified in the JDBC specifications and do not provide any pooling itself. The ConnectionPoolDataSource and XADataSource are intended to be used by connection pools (as provided by application servers) and should not be connection pools themselves.

3.4 Future changes to Jaybird

The next versions of Jaybird will include some – potentially – breaking changes. We advise to check your code if you will be affected by these changes and prepare for these changes if possible.

CREATE TABLE tablename (
   column1 INTEGER,
   "column2" INTEGER
);

4.1 License

Jaybird JCA/JDBC driver is distributed under the GNU Lesser General Public License (LGPL). Text of the license can be obtained from http://www.gnu.org/copyleft/lesser.html.

Using Jaybird (by importing Jaybird’s public interfaces in your Java code), and extending Jaybird by subclassing or implementation of an extension interface (but not abstract or concrete class) is considered by the authors of Jaybird to be dynamic linking. Hence our interpretation of the LGPL is that the use of the unmodified Jaybird source does not affect the license of your application code.

Even more, all extension interfaces to which application might want to link are released under dual LGPL/modified BSD license. Latter is basically “AS IS” license that allows any kind of use of that source code. Jaybird should be viewed as an implementation of that interfaces and LGPL section for dynamic linking is applicable in this case.

4.2 Source Code

The distribution package contains the normal sources in jaybird-2.2.15-sources.jar; this file does not include the sources of the tests, nor the sourcecode for other JDK-versions.

Full source code, including tests and build files, can be obtained from the GitHub repository. The repository URL is https://github.com/FirebirdSQL/jaybird

5.1 Where to get more information on Jaybird

The most detailed information can be found in the Jaybird Frequently Asked Questions (FAQ). The FAQ is included in the distribution, and is available on-line in several places.

JaybirdWiki is available at https://github.com/FirebirdSQL/jaybird/wiki

Jaybird 2.1 Programmers Manual: http://www.firebirdsql.org/file/documentation/drivers_documentation/Jaybird_2_1_JDBC_driver_manual.pdf

5.2 Where to get help

The best place to start is the FAQ. Many details for using Jaybird with various programs are located there. Below are some links to useful web sites.

• The http://groups.yahoo.com/group/Firebird-Java and corresponding mailing-list [email protected]
• The code for Firebird and this driver are on https://github.com/FirebirdSQL/jaybird
• The Firebird project home page http://www.firebirdsql.org/

5.3 Contributing

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

• Participate on the mailinglists (see http://www.firebirdsql.org/en/mailing-lists/)
• Report bugs or submit patches on the tracker (see below)
• Create pull requests on GitHub (https://github.com/FirebirdSQL/jaybird)
  
• Become a developer (for Jaybird contact us on Firebird-Java, for Firebird in general, use the Firebird-devel mailing-list)
• Become a paying member or sponsor of the Firebird Foundation (see http://www.firebirdsql.org/en/firebird-foundation/)

See also http://www.firebirdsql.org/en/consider-your-contribution/

5.4 Reporting Bugs

The developers follow the [email protected] list. Join the list and post information about suspected bugs. List members may be able to help out to determine if it is an actual bug, provide a workaround and get you going again, whereas bug fixes might take awhile.

If you are sure that this is a bug you can report it in the Firebird bug tracker, project “Java Client (Jaybird)” at http://tracker.firebirdsql.org/browse/JDBC

When reporting bugs, please provide a minimal, but complete reproduction, including databases and sourcecode to reproduce the problem. Patches to fix bugs are also appreciated. Make sure the patch is against a recent master version of the code. You can also fork the jaybird repository and create pull requests.

5.5 Corrections/Additions to Release Notes

Please send corrections, suggestions, or additions to these Release Notes to the mailing list at [email protected].

6.1 Pure Java

jdbc:firebirdsql://host[:port]/<database>

Default URL, will connect to the database using the Type 4 JDBC driver using the Java implementation of the Firebird wire-protocol. Best suited for client-server applications with dedicated database server. Port can be omitted (default value is 3050), host name must be present.

The <database> part should be replaced with the database alias or the path to the database. In general it is advisable to uses database aliases instead of the path the file.

On Linux the root / should be included in the path. A database located on /opt/firebird/db.fdb should use the URL below (note the double slash after port!).

jdbc:firebirdsql://host:port//opt/firebird/db.fdb

Deprecated but still available alternative URL:

jdbc:firebirdsql:host[/port]:<database>

6.2 Using Firebird client library

jdbc:firebirdsql:native:host[/port]:<database>

Type 2 driver, will connect to the database using client library (fbclient.dll on Windows, and libfbclient.so on Linux). Requires correct installation of the client library.

jdbc:firebirdsql:local:<database>

Type 2 driver in local mode. Uses client library as in previous case, however will not use socket communication, but rather access database directly. Requires correct installation of the client library.

6.3 Embedded Server

jdbc:firebirdsql:embedded:<database>

Similar to the Firebird client library, however fbembed.dll on Windows and libfbembed.so on Linux are used. Requires correctly installed and configured Firebird embedded library.

7.1 Configuring Type 2 JDBC driver

The Type 2 JDBC driver requires the Jaybird JNI library to be installed and available to the Java Virtual Machine. Precompiled binaries for Windows and Linux platforms are distributed with Jaybird.

Please note that Jaybird 2.2 provides an update to the JNI libraries to support new features. It is not compatible with the JNI library for Jaybird 2.1 or earlier.

• jaybird22.dll / jaybird22_x64.dll is a precompiled binary for the Windows platform. It was successfully tested with Windows XP and Windows 7, 8, 8.1 and 10, but there should be no issues in other Windows OS versions (as long as the MS Visual C++ 2010 SP1 distributable is available). The library should be copied into a directory in the PATH environment variable, or be made available to the JVM using the java.library.path system property.
• libjaybird22.so / libjaybird22_x64.so is a precompiled binary for the Linux platform (AMD/Intel). It must be available via the LD_LIBRARY_PATH environment variable, or be made available to the JVM using the java.library.path system property. Dependent libraries (libfbclient.so or libfbembed.so) need to be on the LD_LIBRARY_PATH. The java.library.path is ignored for these libraries as they are loaded from the JNI library, and not from Java. Some Firebird distributions will not create libfbclient.so (but only libfbclient.so.2 and .so.2.5), you will need to add a symlink with the name as expected by Jaybird.
• Other platforms can compile the JNI library by checking out the Jaybird sources from GitHub and using ./build.sh compile-native command in the directory with checked out sources.

After making Jaybird JNI library available to the JVM, the application has to tell driver to start using this by either specifying TYPE2 or LOCAL type in the connection pool or data source properties or using appropriate JDBC URL when connecting via java.sql.DriverManager.

7.2 Configuring Embedded Server JDBC driver

The Embedded Server JDBC driver uses the same JNI library and configuration steps for the Type 2 JDBC driver.

There is however one issue related to the algorithm of Firebird Embedded Server installation directory resolution. Firebird server uses a pluggable architecture for internationalization. By default server loads fbintl.dll or libfbintl.so library that contains various character encodings and collation orders. This library is expected to be installed in the intl/ subdirectory of the server installation. The algorithm of directory resolution is the following:

1. FIREBIRD environment variable.
2. RootDirectory parameter in the firebird.conf file.
3. The directory where server binary is located.

When Embedded Server is used from Java and no FIREBIRD environment variable is specified, it tries to find firebird.conf in the directory where the application binary is located. In our case the application binary is the JVM and therefore Embedded Server tries to find its configuration file in the bin/ directory of the JDK or JRE installation. The same happens to the last item of the list. In most cases this is not desired behavior.

Therefore, if the application uses character encodings, UDFs or wants to fine-tune server behavior through the configuration file, the FIREBIRD environment variable must be specified and point to the installation directory of the Embedded Server, e.g. current working directory.

7.3 Support for multiple JNI libraries

Up to Jaybird 2.0 only one client library could be loaded in a single JVM. That could be either an embedded Firebird library (fbembed.dll/libfbembed.so), or Firebird client library (fbclient.dll/libfbclient.so). This could lead to problems, For example, if embedded Firebird was used first, the JDBC driver would access the database file directly instead of using the local IPC protocol if only the path to the database was specified. It was not possible to change this without restarting the JVM.

Since Jaybird 2.1, Jaybird is able to correctly load arbitrary number of shared libraries that implement the ISC API and forward the requests correctly depending on the type of the driver being used.

8.1 Events

Events is one of the unique features in the Firebird RDBMS and allows asynchronous notification of the applications about named events that happen in the database. Information on this feature can found in the free IB 6.0 documentation set as well as in The Firebird Book by Helen Borrie.

The interfaces and classes for the event support can be found in org.firebirdsql.event package, which includes:

• EventManager interface to register for the synchronous and asynchronous notification about the events in the database;
• EventListener interface which has to be implemented by the application that wants to participate in the asynchronous notification;
• DatabaseEvent interface which represents the object that will be passed to the EventListener notification method;
• Implementation of the above interfaces: FBEventManager and FBDatabaseEvent.

Please note, that each instance of FBEventManager will open a new socket connection to the Firebird server on the port specified by Firebird.

Similar to other JDBC extensions in Jaybird, the interfaces are released under the modified BSD license, the implementation of the code is released under LGPL license.

8.2 Default holdable result sets (closed ResultSet in auto-commit mode)

This connection property allows to create holdable result sets by default. This is needed as a workaround for the applications that do not follow JDBC specification in regard to the auto-commit mode.

Specifically, such applications open a result set and, while traversing it, execute other statements using the same connection. According to JDBC specification the result set has to be closed if another statement is executed using the same connection in auto-commit mode. Among others the OpenOffice/LibreOffice Base users have problems with the JDBC compatibility in Jaybird.

The property is called:

• defaultResultSetHoldable as connection property for JDBC URL or for java.sql.DriverManager class and no or empty value should be assigned to it; it has an alias defaultHoldable to simplify the typing;
• isc_dpb_result_set_holdable as a DPB member;
• FirebirdConnectionProperties interface methods isDefaultResultSetHoldable() and setDefaultResultSetHoldable(boolean)

Note, the price for using this feature is that each holdable result set will be fully cached in memory. The memory occupied by it will be released when the statement that produced the result set is either closed or re-executed.

8.3 Updatable result sets

Jaybird provides support for updatable result sets. This feature allows a Java application to update the current record using the updateXXX methods of java.sql.ResultSet interface. Updates are performed within the current transaction using a best row identifier in the WHERE-clause. This sets the following limitation on the result set “updatability”:

• the SELECT references a single table;
• all columns not referenced in SELECT permit NULLs (otherwise INSERTs will fail);
• the SELECT statement does not contain a DISTINCT predicate, aggregate functions, joined tables or stored procedures;
• the SELECT statement references all columns from the table primary key definition or a RDB$DB_KEY column.

8.4 Firebird management interfaces

Jaybird provides full support of the Firebird Services API that allows Java applications to perform various server management tasks:

• database backup/restore on remote server; it is possible to performs metadata-only backups, switch the garbage collection during backup off, restore databases with no validity constraints or active indices, etc.
• database maintenance, e.g. database shutdown, sweep, changing the forced writes settings, changing SQL dialect of the database, shadow management, etc.
• retrieving database statistics including header page statistics, system table statistics, data page statistics and index statistics.
• user management, including adding, modifying, and deleting user accounts.

8.5 Jaybird JDBC extensions

Jaybird provides extensions to some JDBC interfaces. JDBC extension interface classes are released under modified BSD license, on “AS IS” and “do what you want” basis, this should make linking to these classes safe from the legal point of view. All classes belong to org.firebirdsql.jdbc.* package.

The table below shows all JDBC extensions present in Jaybird with a driver version in which the extension was introduced.

8.6 JDBC connection properties

The table below lists the properties for the connections that are obtained from this data source. Commonly used parameters have the corresponding getter and setter methods, the rest of the Database Parameters Block parameters can be set using setNonStandardProperty setter method.

9.1 JDBC deviations and unimplemented features

The following optional features and the methods for their support are not implemented:

• java.sql.Array data type is not (yet) supported
• java.sql.Blob does not implement following methods:
  • position(Blob, long) and position(byte[], long); Firebird does not provide any server-side optimization for these calls, client application must fetch complete blob content from the server to do pattern search.
  • truncate(long); Firebird does not provide such functionality on the server side, application must fetch old BLOB from the server and pump old content into a newly created blob.
• java.sql.Connection
  • getCatalog() and setCatalog(String) are not supported by Firebird server
  • getTypeMap() and setTypeMap(Map) are not supported
• java.sql.Ref data type is not supported by Firebird server
• java.sql.SQLData data type is not supported by Firebird server
• java.sql.SQLInput is not supported
• java.sql.SQLOutput is not supported
• java.sql.SQLXML is not supported
• java.sql.RowId is not supported
• java.sql.NClob is not supported
• java.sql.Statement
  • cancel() is implemented, but not fully supported by Jaybird
• java.sql.Struct data type is not supported by server.

The following methods are implemented, but deviate from the specification:

• java.sql.Statement
  • get/setMaxFieldSize does nothing, Firebird server does not support this feature.
  • get/setQueryTimeout does nothing, Firebird server does not support this feature.
• java.sql.PreparedStatement
  • setObject(int index, Object object, int type) Target SQL type is determined from the class of the passed object and corresponding parameter is ignored.
  • setObject(int index, Object object, int type, int scale) Same as above, type and scale are ignored.
• java.sql.ResultSetMetaData
  • isReadOnly() always returns false
  • isWritable() always returns true
  • isDefinitivelyWritable() always returns true

10.1 Result sets

Jaybird behaves differently not only when different result set types are used but also whether the connection is in auto-commit mode or not.

• ResultSet.TYPE_FORWARD_ONLY result sets when used in auto-commit mode are completely cached on the client before the execution of the query is finished. This leads to the increased time needed to execute statement, however the result set navigation happens almost instantly. When auto-commit mode is switched off, only part of the result set specified by the fetch size is cached on the client.
• ResultSet.TYPE_SCROLL_INSENSITIVE result sets are always cached on the client. The reason is quite simple – the Firebird API does not (yet) provide scrollable cursor support, navigation is possible only in one direction.
• ResultSet.HOLD_CURSORS_OVER_COMMIT holdability is supported in Jaybird only for result sets of type ResultSet.TYPE_SCROLL_INSENSITIVE. For other result set types driver will throw an exception.

10.2 Using java.sql.ParameterMetaData with callable Statements

This interface can be used only to obtain information about the IN parameters. Also it is not allowed to call the PreparedStatement.getParameterMetaData method before all of the OUT parameters are registered. Otherwise the corresponding method of CallableStatement throws an SQLException, because the driver tries to prepare the procedure call with incorrect number of parameters.

10.3 Using ResultSet.getCharacterStream with blob fields

Jaybird JDBC driver always uses connection encoding when converting array of bytes into character stream. The BLOB SUB_TYPE 1 fields allow setting the character encoding for the field. However when the contents of the field is sent to the client, it is not converted according to the character set translation rules in Firebird, but is sent “as is”. When such fields are accessed from a Java application via Jaybird and character set of the connection does not match the character encoding of the field, conversion errors might happen. Therefore it is recommended to convert such fields in the application using the appropriate encoding.

10.4 Heuristic transaction completion support

Current JCA implementation does not support XAResource.forget(Xid). It might be important in cases where a distributed transaction - that was at some time in-limbo - was either committed or rolled back by the database administrator. Such transactions appear to Jaybird as successfully completed, however XA specification requires resource manager to “remember” such transaction until the XAResource.forget(Xid) is called.

10.5 Compatibility with com.sun.rowset.*

The reference implementation of javax.sql.rowset included with Java in package com.sun.rowset does not correctly look up columns by name as it ignores column aliases and only allows look up by the original column name⁵ (this specifically applies to com.sun.rowset.CachedRowSetImpl).

We advise you to either only access columns by their index or use an implementation which correctly uses the column label for column lookup (which is either the alias or the original column name if no alias was defined).

Jaybird 2.2.1 introduced the connection property columnLabelForName for backwards compatible behavior of ResultSetMetaData#getColumnName(int). Set property to true for backwards compatible behavior (getColumnName() returns the column label); don’t set the property or set it to false for JDBC-compliant behavior (recommended).

10.6 Support for Firebird 3 BOOLEAN type

Jaybird 2.2.4 introduces support for the Firebird 3 BOOLEAN type. A boolean field can also be set with all numeric setters and the string setter (as implied by JDBC 4.1 appendix B).

For numeric types, currently only 0 will set to false and all other values will set to true. This is something that might change in the future. Only 0 for false and 1 for true are guaranteed, in the future we might decide to throw a conversion exception for other values!

For string types we currently set true for "true", "T", "Y" and "1" (case insensitive, ignoring whitespace), all other values will set false; this is for compatibility with the current getBoolean behaviour of FBStringField. This is something that might change in the future. Only "true" and "1" for true and "false" and "0" for false are guaranteed (case insensitive, ignoring whitespace), in the future we might decide to throw a conversion exception for other values!

When using string or numeric setters for a boolean field we strongly recommend to only use the guaranteed values (0, 1, "0", "1", "true" and "false"). Better yet: use the boolean setter instead.

11.1 Description of deprecated org.firebirdsql.pool classes

WARNING: This section provides information on deprecated classes

See Important changes to Datasources

Connection pooling provides effective way to handle physical database connections. It is believed that establishing new connection to the database takes some noticeable amount or time and in order to speed things up one has to reuse connections as much as possible. While this is true for some software and for old versions of Firebird database engine, establishing connection is hardly noticeable with Firebird 1.0.3 and Firebird 1.5. So why is connection pooling needed?

There are few reasons for this. Each good connection pool provides a possibility to limit number of physical connections established with the database server. This is an effective measure to localize connection leaks. Any application cannot open more physical connections to the database than allowed by connection pool. Good pools also provide some hints where connection leak occurred. Another big advantage of connection pool is that it becomes a central place where connections are obtained, thus simplifying system configuration. However, main advantage of good connection pool comes from the fact that in addition to connection pooling, it can pool also prepared statement. Tests executed using AS3AP benchmark suite show that prepared statement pooling might increase speed of the application by 100% keeping source code clean and understandable.

11.2 Usage scenario

When some statement is used more than one time, it makes sense to use prepared statement. It will be compiled by the server only once, but reused many times. It provides significant speedup when some statement is executed in a loop. But what if some prepared statement will be used during lifetime of some object? Should we prepare it in object’s constructor and link object lifetime to JDBC connection lifetime or should we prepare statement each time it is needed? All such cases make handling of the prepared statements hard, they pollute application’s code with irrelevant details.

Connection and statement pooling remove such details from application’s code. How would the code in this case look like? Here’s the example

Connection connection = dataSource.getConnection();
try {
    PreparedStatement ps = connection.prepareStatement(
        “SELECT * FROM test_table WHERE id = ?”);
    try {
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            // do something here
        }
    } finally {
        ps.close();
    }
} finally {
    connection.close();
}

Lines 1-16 show typical code when prepared statement pooling is used. The application obtains a JDBC connection from the data source (instance of javax.sql.DataSource interface), prepares some SQL statement as if it is used for the first time, sets parameters, and executes the query. Lines 12 and 15 ensure that statement and connection will be released under any circumstances. Where do we benefit from the statement pooling? The call to prepare a statement in lines 3-4 is intercepted by the pool, which checks if there’s a free prepared statement for the specified SQL query. If no such statement is found it prepares a new one. In line 12 the prepared statement is not closed, but returned to the pool, where it waits for the next call. Same happens to the connection object that is returned to the pool in line 15.

11.3 Connection Pool Classes (deprecated)

Jaybird connection pooling classes belong to the org.firebirdsql.pool.* package. Description of some connection pool classes:

11.4 org.firebirdsql.pool.FBConnectionPoolDataSource (deprecated)

This class is a corner stone of connection and statement pooling in Jaybird. It can be instantiated within the application as well as it can be made accessible to other applications via JNDI. Class implements both java.io.Serializable and javax.naming.Referenceable interfaces, which allows using it in a wide range of web and application servers.

Class implements both javax.sql.ConnectionPoolDataSource and javax.sql.XADataSource interfaces. Pooled connections returned by this class implement javax.sql.PooledConnection and javax.sql.XAConnection interfaces and can participate in distributed JTA transactions.

Class provides following configuration properties:

11.5 org.firebirdsql.pool.FBWrappingDataSource

This class is a wrapper for FBConnectionPoolDataSource converting the interface from javax.sql.ConnectionPoolDataSource to javax.sql.DataSource. It defines same properties as FBConnectionPoolDataSource does.

11.6 Runtime object allocation and deallocation hints

The Ppool implementation shipped with Jaybird can provide hints for the application where the connection was obtained from the pool, when it was released back to the pool, when the statement was prepared. Such information is written into the log when appropriate system properties are set to true.