Export Data From Firebird Database to CSV File

Background

Some software applications use a database called Firebird. This is an open source database. If the software application is compiled you cannot (easily) see the source code and try to “hack” it to make customizations.

These applications are usually connected to a database.

If you want to export data from your application for analytics, data mining, reports, excel, etc. Most times you can do it from the User Interface. If there is no such feature and the software manufacturer doesn’t want to build such feature. Then you could get into the database and extract the data.

Warning

Don’t touch the database if you are not technical. You might break it and break your software installation.

Common sense to see if this works for you is to make a copy of the database into a different location and experiment with the copy.

Requirements

This is the process to export data from Firebird:

  • Copy the database from a Windows Server to a Mac
  • Install Firebird on the Mac
  • Open the database
  • Extract the data

Export Data From Firebird

Ideally your firebird installation version is the same as the firebird database you want to open. Otherwise you will have issues connecting to it.

In this case I have a database that was created in version 1.5 so I installed that version of the database.

Choose file FirebirdCS-1.5.6-5026-i386.pkg.zip “Classic for MacOSX 10.4 + (x86)”

In Firebird you use 2 tools gsec and isql. You need to add path to bash_profile as shown here.

There is also a bash script if you want to remove a newer version of firebird. See here

Now go to where the database file is located using your Terminal. To connect to it use:

isql YourDatabaseName.fdb -user yourusername -password yourpassword

Once you connect it will show:

SQL>

You need to end your statements with a semicolon ;.

If you type enter without a semicolon it will show you:

CON>

To continue enter statements until you hit it with a ;.

SQL commands

To show all tables use:

SQL> SHOW TABLES;

Find Primary Key

Let’s say your table name is called ‘COMPANIES’.

select RDB$FIELD_POSITION,RDB$FIELD_NAME
from rdb$index_segments
where RDB$INDEX_NAME = (
select RDB$INDEX_NAME
from RDB$RELATION_CONSTRAINTS
where rdb$relation_name = 'COMPANIES'
and RDB$CONSTRAINT_TYPE = 'PRIMARY KEY'
)
order by RDB$FIELD_POSITION;

Find field names

Same example as ‘COMPANIES’

SELECT RDB$FIELD_NAME
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='COMPANIES';

Output to a file

SQL> OUTPUT tables.txt;
SQL> SHOW TABLES;
SQL> shell;
$ cat tables.txt
$ exit
SQL>

The first line OUTPUT everything you give to SQL to tables.txt instead of the stdout.

Then you open the TABLES of the database you connected to.

You can go into the shell without exiting SQL.

Use cat to output the tables.txt to the stdout.

Then exit to go back to SQL.

You can also do any SQL statement after the OUTPUT statement such as:

SQL> OUTPUT emails.csv;
SQL> SELECT EMAILS FROM COMPANIES;

etc…

Please Post a Comment or Connect with me on Twitter and send me a Tweet


Related Post

Display Post Excerpts in WordPress WordPress is the simplest way to create a website or a blog.I have used Squarespace, Drupal, Octopress and built from scratch. These have their pr...
Go to the Previous Directory in Vim I use Vim.Vim is the most productive text editor if you know some vim commandsWhen I open a file and I am done with it. I often want to go...
Installing Ruby on Ubuntu Installing Ruby on UbuntuAs seen on RVM...Go to the terminal in Ubuntu.Add the public key from RVM$ gpg --keyserver hkp://keys.gnupg.n...
Fix Blank Screen in WordPress Admin One day.You login to your wp-admin in your website.And…Blank page.If you can see the navigation bar on the left but you see a blank pa...

2 thoughts on “Export Data From Firebird Database to CSV File

Leave a Reply