// blog

Reading an Old Firebird Database When Nothing Will Open It

An old Firebird database may use an on-disk structure that today’s Firebird can’t open: TurboCASH 4’s books are ODS 10, from Firebird 1.5. Check the version in the file’s header, then use a server old enough to read it. Old programs often ship one in their installer; TurboCASH’s brings Firebird 2.1, which ran under Wine on a Mac. From there, isql reads every table, and a short script exports what you need, so the new system doesn’t need Firebird at all. Work on a copy of the file.

Last updated 2 min read

An old Firebird database file's internals

Plenty of older Windows business software keeps its data in Firebird, or in InterBase before it. When you move off one of those programs, the first job is getting the data out, and an old database file doesn’t always open in a new server.

We hit this moving TurboCASH 4, open-source accounting software written in Delphi, to the web. Here’s what worked.

1. Find out which Firebird made the file

A Firebird database records its on-disk structure (ODS) version near the start of the file. Two bytes at offset 0x12 give the major version:

text
$ xxd -s 0x12 -l 2 books.FDB
00000012: 0a00

0a00 is 10, little-endian: ODS 10, Firebird 1.5. Firebird 3 and later don’t open a file that old; it would need a backup and restore made with an older Firebird first. Firebird 2.1, the version TurboCASH installs, opens it as it is.

2. Use the program’s own server

Old programs often install the database server they need. TurboCASH 4’s installer brings Firebird 2.1. We were on a Mac, so we ran the installer under Wine, the Windows compatibility layer. TurboCASH itself stopped at start-up there, but its Firebird server and command-line tools ran fine.

text
$ wine fbserver.exe -a &
$ wine isql.exe -u SYSDBA -p masterkey localhost:C:\work\books.fdb
SQL> SHOW TABLES;
SQL> SELECT COUNT(*) FROM ACCOUNT;

Work on a copy of the database, never the original file. Firebird writes to a database it opens, and an old file is worth more than the minutes a copy takes.

3. Read what’s there

SHOW TABLES and SHOW TABLE <name> describe every table and column. TurboCASH’s books hold about 120 tables. The ones a conversion needs first are few: the chart of accounts (ACCOUNT), VAT rates (TAX), journal types (BATTYPES), periods (PERIODS), customers and suppliers (DEBTOR, CREDITOR), stock (STOCK), documents (DOCHEAD, DOCLINE) and the posted ledger (TRANSACT).

Counting rows tells you what kind of file you have. The UK starter books had 74 accounts and no transactions: a set of books to begin with, not a year of trading.

4. Export once, then forget Firebird

We wrote a short script that runs isql for each table and writes the result as JSON. The new system loads the JSON, so its server needs no Firebird, no Wine and no 2008-era drivers. The script stays in the repository for the day someone has a real company’s books to bring over.

Two things to watch while exporting:

  • Fixed-width text. Old schemas use CHAR columns, padded with spaces, and some descriptions have doubled spaces inside them. Trim and tidy on the way out.
  • Broken references. Check that every ID points at something. In TurboCASH’s starter books, two stock items pointed their input VAT at an account that isn’t there; the import repairs it and says so.

If your data is somewhere else

The same approach works for Paradox, Access, dBase or an old SQL Server: find out exactly which version wrote the file, find a tool from that time that reads it, work on a copy, and export once into something modern. The case study shows the whole TurboCASH conversion, and the web version runs on the books we read out. Got data stuck in an old database? Tell us about it.