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));
         }
     }
}

Jaybird 3.0.6 released

25 May 2019

We are happy to announce the release of Jaybird 3.0.6.

This release is largely a bugfix release, but contains additional support for Firebird 4.

Jaybird 3.0.6 contains the following fixes and changes:

  • Fixed: Exceptions during fetch of cached result sets (holdable over commit, scrollable and metadata) prevented prepared statement reuse/re-execute with error "Statement state CURSOR_OPEN only allows next states [CLOSING, PREPARED, ERROR], received EXECUTING" (JDBC-531)

  • Improvement: Added FBManager.setDefaultCharacterSet to set default database character set during database creation (JDBC-541)

  • New feature: Support for Firebird 3 case sensitive user names (JDBC-549)

  • Fixed: Savepoints did not work in connection dialect 1 as savepoint names were always quoted (JDBC-556)

  • Changed: The DatabaseMetaData statement cache introduced in Jaybird 3 was unlimited, it is now limited to 12 prepared statements; the least recently used statement will be closed and removed when a new statement is added (JDBC-557)

  • Fixed: UPDATE OR INSERT with existing RETURNING clause handled incorrectly for generated keys (JDBC-566)

  • Fixed: Exceptions during initialization of result sets would not properly close the database cursor leading to error "Current statement state (CURSOR_OPEN) does not allow call to prepare" on reuse of the statement (or errors similar to described for JDBC-531 above). (JDBC-571)

  • New feature: boolean connection property ignoreProcedureType to disable usage of metadata for stored procedure types in CallableStatement. When set to true, call escapes and EXECUTE PROCEDURE will default to use EXECUTE PROCEDURE and not switch to SELECT for selectable stored procedures. (JDBC-576)

  • New feature: connection properties timeZoneBind and sessionTimeZone for limited support for Firebird 4 TIME(STAMP) WITH TIME ZONE types, and decfloatBind for limited support for Firebird 4 DECFLOAT types. (JDBC-538)

  • Fixed: Connection property defaultIsolation/isolation did not work through DriverManager, but only on DataSource implementations. (JDBC-584)

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

See also:


Older posts are available in the archive.