Reviving data from a legacy Firebird database

For a client we recently had to retrieve and manipulate data from a 20+ year old legacy application on which the original vendor no longer offered support.

Photo by Jan Antonin Kolar on Unsplash

After some digging, I figured out that the application was using an old InterBase/Firebird data file as a database backend.

Firebird is originally a fork of the open-source version of InterBase (the once popular database by Borland), but has been largely rewritten for some time.

Using the excellent universal database tool DBeaver, fed with the database credentials, I was able to connect directly to the database to get a better understanding of the messy database structure and its contents.

Once I knew from what tables I had to grab the data I needed and what joins to make, it was time to build a script to retrieve and manipulate the data.

For simplicity and portability, I chose PHP to complete this task.

As the Firebird extension is not bundled anymore in more recent (7.4.0+) PHP versions, it had to be installed manually. These are the steps I went through on MacOS to install the Firebird extension manually:

1. Download the latest PHP 7.4.x source from the official PHP website.
2. Uncompress the source (tar xvzf php-7.4.27.tar.gz)
3. Change working directory to ext/pdo_firebird
4. Run the phpize script the extension for compiling
5. Run ./configure
6. Run “make” to build the target (this initially resulted in error messages about missing header files, which was solved by copying the .h files from /Library/Frameworks/Firebird.framework/Headers/ to ext/pdo_firebird)
7. Run “sudo make install” to install the extension
8. Add the extension to php.ini (extension=pdo_firebird)

Using a few lines of code to retrieve data using PDO I was able to easily retrieve the data from the legacy Firebird database:

<?php
// Connect to database
$dsn = ‘firebird:dbname=127.0.0.1:/path/to/DATABASE.GDB’;
$username = ‘username’;
$password = ‘password’;
$pdo = new \PDO($dsn, $username, $password, [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
// Retrieve data
$stm = $pdo->query(‘SELECT FIELD1, FIELD2 FROM TABLE’);
while($rec = $stm->fetch(PDO::FETCH_ASSOC)) {
    echo $rec[‘FIELD1’] . ‘ -> ‘ . $rec[‘FIELD2’] . “\n”;
}

Once retrieved, it was straightforward to manipulate the data and export all results to an Excel spreadsheet, using the great PhpSpreadsheet library, but that’s maybe for another blog post.

If you’re in need of reviving data from an ancient application or database, don’t hesitate to contact us.