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.
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.
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
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:
You need to end your statements with a semicolon
If you type enter without a semicolon it will show you:
To continue enter statements until you hit it with a
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.
cat to output the tables.txt to the stdout.
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;