FAQ for Firebird Users Migrating From MySQL

How do I Install Firebird?

Check this page for downloading Firebird server

On windows, just run the setup executable. Also, installing with linux .rpm package is very easy. For installation details on linux platforms, follow this link: http://firebird.sourceforge.net/index.php?op=doc&id=install

How to connect to Firebird database?

Im MySQL, all database aliases are registered with server. Firebird 1.0 doesn't have that feature, so you have to point to the server and database with full path to database file. Examples of connection strings:

MySQL:

SERVER_HOST=localhost
DATABASE_NAME=testdb
USERNAME=milanb
PASSWORD=*****

Firebird:

DATABASE_PATH=localhost:/usr/local/db/testdb.gdb
USERNAME=milanb
PASSWORD=*****

As you can see, you connect to the server and select a database at the same time. Firebird since v1.5 supports server side aliases. Unlike MySQL, which stores the each table and index in separate files, Firebird stores the entire database in a single file, which has default extension .fdb.

Is there a PHP web tool like phpMyAdmin for Firebird?

Yes, actually, there is. It's called ibWebAdmin, and you can download it here: http://sourceforge.net/projects/ibwebadmin/

Download the package, unpack in some directory readable by web server, and edit the configuration.inc.php file in inc directory.

Installation note for Windows users:

If you used default directories during Firebird instalation, these are the settings you need to change:

define('BINPATH', 'c:/progra~1/firebird/bin/');
define('SECURITY_DB', 'c:/progra~1/firebird/isc4.gdb');
define('PATH_SEPARATOR', '');

How to create auto increment values?

For this task, Firebird uses generators (like Oracle). Each generator has a value. Here's an example how to use generators in comparison with MySQL autoinc values:

MySQL:

CREATE TABLE test
(
field1 integer not null auto_increment,
field2 char(10),
PRIMARY KEY (field1)
);

inserting values:

INSERT INTO test (field2) VALUES ('testme');

Firebird:

CREATE TABLE test
(
field1 integer not null,
field2 char(10),
PRIMARY KEY (field1)
);

CREATE GENERATOR gen_test_id;

inserting values:

INSERT INTO test (field1, field2) VALUES (gen_id(gen_test_id, 1), 'testme');

This may seem little too complicated, but generators give you much more power than autoinc values, since you can always read generator value without increasing it:

SELECT gen_id(gen_test_id, 0) FROM ...

and you can change the current value with:

SET GENERATOR gen_test_id TO [some_value];

To make usage of generators easier, you can define trigger for your table. The trigger will automatically insert new generator value each time the row is inserted. Here's an example:

CREATE TRIGGER test_bi FOR test
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.field1 IS NULL) THEN
NEW.field1 = GEN_ID(gen_test_id,1);
END

This is even better than autoinc values since you can insert any value into autoinc column if you want to. The generator value will be inserted only if no value is supplied. Many GUI tools for Firebird management have options to automatically create such triggers when you select that you want an autoincrement column.

To learn more about generators look at the InterBase Data Definition Guide

In case you didn't know, Firebird is an InterBase 6 fork and almost all InterBase 6 documents apply to it. Alternatively you can get Firebird sepecific documentation by buying a copy of the IBPhoenix CD.

Is there something like limit x, y in select query?

Yes, there is, it's named FIRST x SKIP y, and it used like this:

SELECT FIRST x SKIP y FROM ... [rest of query]

This will select total of x rows, skipping first y rows (i.e. it starts from row y+1).

How to list all tables in a database?

In MySQL, you can do SHOW TABLES. You can use the same in Firebird's isql command-line tool, but nowhere else. However, this can be done by querying Firebird's system tables:

SELECT RDB$RELATION_NAME FROM RDB$RELATIONS;

This query will show you both system and user tables. To select user tables only, use this:

SELECT RDB$RELATION_NAME FROM RDB$RELATIONS
WHERE RDB$SYSTEM_FLAG = 0;

SELECT RDB$RELATION_NAME FROM RDB$RELATIONS
WHERE RDB$SYSTEM_FLAG = 0 AND RDB$VIEW_BLR IS NULL;

How to count the number of records retrieved?

Firebird doesn't have this feature. You can count records by fetching all rows, or by doing SELECT COUNT(*) ... using the same query.

Is there something else I should know?

Yes, Firebird is a mature database server, and has some features you may find very useful, but you don't know about it. These are:

• Referential Integrity
• Sub-selects
• Unions
• Views
• Triggers
• Stored Procedures
• User defined functions