Migrate HSQLDB to Firebird in LibreOffice Base

This guide provides a straightforward walkthrough for migrating legacy embedded HSQLDB databases to the Firebird engine within LibreOffice Base. As LibreOffice transitioned its default embedded relational database management system to Firebird, converting your older .odb files ensures long-term compatibility, improved performance, and access to modern SQL features.

Step 1: Create a Backup

Before initiating any database migration, create a secure copy of your original .odb file. The migration process alters the underlying database structure, and having a backup ensures no data is lost if conversion issues arise.

1. Close LibreOffice completely.
2. Locate your .odb file in your file manager.
3. Copy and paste the file into a separate backup folder.

Step 2: Enable Experimental Features

In some versions of LibreOffice, the Firebird migration assistant is located under experimental features.

1. Launch LibreOffice Base.
2. Navigate to Tools > Options (on macOS: LibreOffice > Preferences).
3. In the left panel, expand LibreOffice and select Advanced.
4. Check the box labeled Enable experimental features (may be unstable).
5. Click Apply and then OK.
6. Restart LibreOffice if prompted.

Step 3: Run the Migration Assistant

1. Open your legacy HSQLDB .odb file in LibreOffice Base.
2. LibreOffice will detect the legacy engine and display a prompt asking if you want to migrate to Firebird.
3. Click Yes to launch the Database Migration Wizard.
4. Review the migration summary screen detailing the tables, views, and data to be converted.
5. Click Next and allow the wizard to convert your data structures and records.
6. Once the conversion process completes, click Finish.

Step 4: Validate Data and Adjust Queries

Because HSQLDB and Firebird differ in SQL syntax, data typing, and function names, you must verify your database components:

• Field Types: Inspect your tables to ensure data types (such as VARCHAR, INTEGER, and BOOLEAN) converted accurately.
• SQL Queries: Open and run all saved queries. Firebird enforces stricter SQL standards than HSQLDB:
  • String literals must use single quotes ('text').
  • Table and column identifiers containing special characters or mixed casing must use double quotes ("FieldName").
  • Update any proprietary HSQLDB functions to their standard Firebird equivalents (e.g., date-formatting functions).
• Forms and Reports: Test all forms, subforms, and reports to confirm that control bindings and macro triggers function correctly with the new backend.

Step 5: Save the Converted Database

After verifying that all queries, forms, and data records operate without errors, click File > Save to finalize the migration. Your .odb container is now fully running on the embedded Firebird engine.