Firebird

Supported Versions

• 4.0
• 3.0

Driver

SQL Script Syntax

• Standard SQL syntax
• Terminator changes supported using `SET TERM <new terminator><old terminator>
• Firebird dialect 3 only

Compatibility

• DDL exported by Firebird isql needs to be split into separate files on each COMMIT WORK, removing the COMMIT WORK statement from the script.
• Any Firebird SQL script executed by MigrateDB, can be executed by the Firebird tools (after the placeholders have been replaced).

Example

/* Single line comment */
CREATE TABLE test_data (
  name VARCHAR(25) NOT NULL PRIMARY KEY
);
 /*
Multi-line
comment
*/
 -- Sql-style comment
 -- Placeholder
ALTER TABLE ${tableName} ADD id INT NOT NULL;
 -- Terminator changes
SET TERM #;
CREATE OR ALTER PROCEDURE SHIP_ORDER (
    PO_NUM CHAR(8))
AS
BEGIN
  /* Stored procedure body */
END#
SET TERM ;#

Limitations

• Mixing DDL and DML involving the same tables in a single migration is not supported. Firebird disallows DDL changes to be used by DML in the same transaction, so one of the following is necessary:
  • separate migrations for DML / DDL, or
  • a per-script override of executeInTransaction
• SET TRANSACTION and COMMIT [WORK] are not supported in migrations. COMMIT RETAIN can be used, but this will only allow partial rollback of a migration; only statements after the last COMMIT RETAIN can be rolled back.
• Migrations modifying or dropping objects while the database is in use can result in long delays or “object X is in use” errors as Firebird uses existence locks on metadata objects when they are in use. It may be necessary to shut down the database and run the migration with exclusive access.

Existing database setup ➡️