Jaybird 4.0.0-beta-2 released

19 January 2020

We are happy to announce the second beta for Jaybird 4.

We’d really appreciate it if you take the time to test this version of Jaybird with your applications. Bug reports about undocumented changes in behavior are appreciated. Feedback can be sent to the Firebird-java mailing list or reported on the issue tracker http://tracker.firebirdsql.org/browse/JDBC.

Jaybird 4 is - compared to Jaybird 3 - an incremental release that builds on the foundations of Jaybird 3. The focus of this release has been on further improving JDBC support and adding support for the new data types and features of Firebird 4.

The main new features are:

  • Wire encryption support (backported to Jaybird 3.0.4)

  • Database encryption support (backported to Jaybird 3.0.4)

  • Wire compression support

  • Authentication plugin improvements

  • Firebird 4 data type bind configuration support (since Jaybird 4.0.0-beta-2)

  • Firebird 4 DECFLOAT support

  • Firebird 4 extended numeric precision support

  • Firebird 4 time zone support

  • Firebird 4 statement timeout support (since Jaybird 4.0.0-beta-2)

  • JDBC RowId support

  • DatabaseMetaData getPseudoColumns implemented

  • DatabaseMetaData getVersionColumns implemented

  • DatabaseMetaData getFunctions implemented (since Jaybird 4.0.0-beta-2)

  • DatabaseMetaData getFunctionColumns implemented (since Jaybird 4.0.0-beta-2)

  • Improved JDBC function escape support

  • New JDBC protocol prefix jdbc:firebird:

  • URL encoding in query part of JDBC URL (backported to Jaybird 3.0.9)

  • Generated keys support improvements

  • Operation monitoring

Jaybird 4.0.0-beta-2 is for testing purposes only, and not intended for production use.

See also:

Jaybird 3.0.8 released

25 November 2019

Jaybird 3.0.8 has been released. This release contains one bug fix:

  • Fixed: On Firebird 3 and 4 with WireCrypt = Enabled, the connection could hang or throw exceptions like "Unsupported or unexpected operation code". (JDBC-599)

Jaybird 3.0 supports Firebird 2.0 and higher, on Java 7, 8, 11 and 13. Basic Java 9 and higher compatibility is provided through the Java 8 version of the driver.

See also:

Jaybird 3.0.7 released

10 November 2019

Jaybird 3.0.7 has been released. This release contains a new feature and two bug fixes:

  • Fixed: attempts to use a blob after it was freed or after transaction end could throw a NullPointerException or just work depending on whether the connection had a new transaction. (JDBC-587)

  • Fixed: Instances of java.sql.Blob and java.sql.Clob obtained from a result set were freed after calls to ResultSet.next(). (JDBC-588)

  • New feature: added FBEventManager.createFor(Connection) to create an EventManager for an existing connection. Backported from Jaybird 4. (JDBC-594)

Jaybird 3.0 supports Firebird 2.0 and higher, on Java 7, 8, 11 and 13. Basic Java 9 and higher compatibility is provided through the Java 8 version of the driver.

See also:

Firebird support in Flyway

17 August 2019

Flyway is a database migration (versioning) tool that supports a wide variety of database systems. Recently, they accepted my pull request to add support for Firebird in Flyway 6.0. Although my pull request supported Firebird 2.1 and higher, the Flyway team has decided to only support Firebird 3 and higher. I assume this was decided as Firebird 2.5 and earlier are all end-of-life.

At this time, the release of Flyway 6.0 has not been announced yet, but it is already available from Maven central.

Flyway provides a number of ways to migrate database:

Personally, I prefer to use the Java API to migrate the database from within an application. However, that solution requires the application to have access to credentials with sufficient rights in the database, which isn’t alway an option. Depending on your deployment model, it might be better to provide a separate application or use one of the other three options to migrate a database.

I will show a small example of use of Flyway with Firebird 3 below. Be sure to read Flyway’s Get Started and Flyway’s documentation for more information and an overview of all features. This example doesn’t cover all options available.

In its current form, the example does what is better done using the command-line or the Maven or Gradle plugins. Consider it a stepping stone for a migration feature as part of a bigger application.

Using Flyway with a Firebird database

This example will use Gradle as its build and dependency tool, and for running the example.

The full code of the example is available from https://github.com/mrotteveel/firebird-flyway-example.

build.gradle
plugins {
    id 'java'
    id 'application'
}

group 'nl.lawinegevaar'
version '1.0-SNAPSHOT'

sourceCompatibility = 1.8

repositories {
    mavenCentral()
}

dependencies {
    implementation 'org.firebirdsql.jdbc:jaybird-jdk18:3.0.6'
    implementation 'org.flywaydb:flyway-core:6.0.0'
}

application {
    mainClassName = 'nl.lawinegevaar.firebird.flyway.example.Main'
}

To execute the migrations, we’ll use the following simple main class.

Main
package nl.lawinegevaar.firebird.flyway.example;

import org.flywaydb.core.Flyway;

import java.io.IOException;

public class Main {

    public static void main(String[] args) throws IOException {
        DatabaseProperties databaseProperties = DatabaseProperties.loadDefault();
        migrateDatabase(databaseProperties);
    }

    private static void migrateDatabase(DatabaseProperties databaseProperties) {
        Flyway flyway = Flyway.configure()
                .dataSource(
                        databaseProperties.getUrl(),
                        databaseProperties.getUser(),
                        databaseProperties.getPassword())
                .load();
        flyway.migrate();
    }
}

This example assumes an existing empty database is used. The database connection information is configured in a database.properties file in src/main/resources. This properties file is accessed through DatabaseProperties.

This simple application can be built and run using Gradle:

./gradlew run

When run without existing migrations, the following logging will be produced: [1]

Aug 17, 2019 10:14:15 AM org.flywaydb.core.internal.license.VersionPrinter printVersionOnly
INFO: Flyway Community Edition 6.0.0 by Boxfuse
Aug 17, 2019 10:14:16 AM org.flywaydb.core.internal.database.DatabaseFactory createDatabase
INFO: Database: jdbc:firebirdsql://localhost/flyway-example.fdb (Firebird 3.0 3.0)
Aug 17, 2019 10:14:16 AM org.flywaydb.core.internal.command.DbValidate validate
INFO: Successfully validated 0 migrations (execution time 00:00.064s)
Aug 17, 2019 10:14:16 AM org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory create
INFO: Creating Schema History table "flyway_schema_history" ...
Aug 17, 2019 10:14:16 AM org.flywaydb.core.internal.command.DbMigrate migrateGroup
INFO: Current version of schema "default": << Empty Schema >>
Aug 17, 2019 10:14:16 AM org.flywaydb.core.internal.command.DbMigrate logSummary
INFO: Schema "default" is up to date. No migration necessary.

Flyway has now created a flyway_schema_history table which is used to track the executed migrations.

First migration

Now, lets add a migration to add a simple person table. Flyway uses a naming format with scripts prefixed by a version, followed by a descriptive name. See SQL-based migrations for details.

The file we create is src/main/resources/db/migrations/V1__Create_person_table.sql

V1__Create_person_table.sql
create table PERSON (
    ID int generated by default as identity constraint PK_PERSON primary key,
    NAME varchar(100) not null
);

Executing our program, this now logs:

...
Aug 17, 2019 10:20:24 AM org.flywaydb.core.internal.command.DbValidate validate
INFO: Successfully validated 1 migration (execution time 00:00.083s)
Aug 17, 2019 10:20:24 AM org.flywaydb.core.internal.command.DbMigrate migrateGroup
INFO: Current version of schema "default": << Empty Schema >>
Aug 17, 2019 10:20:24 AM org.flywaydb.core.internal.command.DbMigrate doMigrateGroup
INFO: Migrating schema "default" to version 1 - Create person table
Aug 17, 2019 10:20:24 AM org.flywaydb.core.internal.command.DbMigrate logSummary
INFO: Successfully applied 1 migration to schema "default" (execution time 00:00.091s)

Second migration

Now, we want to add some data to the person table:

V2__Add_people.sql
insert into PERSON (NAME) values ('Mark');
insert into PERSON (NAME) values ('William');
insert into PERSON (NAME) values ('Andrew');

Running this will produce:

...
Aug 17, 2019 10:26:48 AM org.flywaydb.core.internal.command.DbValidate validate
INFO: Successfully validated 2 migrations (execution time 00:00.110s)
Aug 17, 2019 10:26:48 AM org.flywaydb.core.internal.command.DbMigrate migrateGroup
INFO: Current version of schema "default": 1
Aug 17, 2019 10:26:48 AM org.flywaydb.core.internal.command.DbMigrate doMigrateGroup
INFO: Migrating schema "default" to version 2 - Add people
Aug 17, 2019 10:26:48 AM org.flywaydb.core.internal.command.DbMigrate logSummary
INFO: Successfully applied 1 migration to schema "default" (execution time 00:00.047s)

Procedural SQL in a migration

A migration script is a collection of statement separated by a statement terminator, by default this is a semi-colon (;). To be able to execute procedural SQL statements (eg create procedure or execute block) in a migration, the statement terminator must be changed. To change the statement terminator, the Firebird support in Flyway supports the SET TERM statement that is also used by ISQL and other Firebird tools.[2]

As an example of creating a stored procedure:

V3__Factorial_procedure.sql
set term #;
create procedure factorial(max_value integer)
  returns (factorial bigint)
as
  declare variable counter integer;
begin
  factorial = 1;
  counter = 0;
  while (counter <= max_value) do
  begin
    if (counter > 0) then
      factorial = factorial * counter;
    counter = counter + 1;
  end
end#
set term ;#

Caveats

Mixing DDL and DML in a single migration

Although Firebird has transactional DDL, it is not possible to use objects created in a transaction in DML in that same transaction. Phrased differently, DML in Firebird can only use objects (tables, columns, etc) as they existed at the start of the transaction. As a result, it is not possible to create a table and in the same migration populate it with data.[3] You can logically group related DDL and DML together by using minor version numbers in your migration, for example V4_1__Create_department_table.sql and V4_2__Add_departments.sql.

Unsupported statement types

The following statements are not supported as they will invalidate the transaction handle used by Jaybird:

  • SET TRANSACTION

  • COMMIT [WORK]

  • ROLLBACK [WORK]

Dialect 3 only

Flyway uses quoted identifiers for its version table and assumes single quote string literals when parsing scripts. This means that Flyway will only work on dialect 3 databases.

Conclusion

Flyway is a powerful tool to manage changes to your database, and I think it is good news that Firebird is now supported. This example only shows the most basic usage of Flyway, so make sure to read the full documentation so you don’t miss out on other useful features.

If you need help, or more information, ask a question on the Firebird-Java mailinglist. You can subscribe by sending an email to firebird-java-subscribe@yahoogroups.com


1. some warnings produced by Jaybird have been removed
2. SET TERM is not part of the Firebird server statement syntax
3. You can add a COMMIT RETAIN; statement to your script to commit in the middle of a script. This is not recommend as this will break the transactionality of your script, which can result in maintenance and recovery headaches if a script partially succeeds and partially fails

Introducing firebird-testcontainers-java

02 June 2019

I have just released org.firebirdsql:firebird-testcontainers-java:1.0.0, based on Testcontainers.

This library allows you to start a Firebird docker container for a JUnit 4 or 5 test or manually from your code. This can be useful for JUnit-based integration tests.

For a very simple example, add to your pom.xml:

<dependency>
    <groupId>org.firebirdsql</groupId>
    <artifactId>firebird-testcontainers-java</artifactId>
    <version>1.0.0</version>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.firebirdsql.jdbc</groupId>
    <artifactId>jaybird-jdk18</artifactId>
    <version>3.0.6</version>
    <scope>test</scope>
</dependency>

And create a test:

/**
  * Simple test demonstrating use of {@code @Rule}.
  */
public class ContainerExampleTest {

     @Rule
     public final FirebirdContainer container = new FirebirdContainer();

     @Test
     public void canConnectToContainer() throws Exception {
         try (Connection connection = DriverManager
                 .getConnection(container.getJdbcUrl(), container.getUsername(),
                     container.getPassword());
              Statement stmt = connection.createStatement();
              ResultSet rs = stmt.executeQuery("select CURRENT_USER from RDB$DATABASE")) {
             assertTrue("has row", rs.next());
             assertEquals("user name",
                 container.getUsername().toUpperCase(), rs.getString(1));
         }
     }
}


Older posts are available in the archive.