FAQ for Firebird Users Migrating From MySQL
FAQ maintained by Milan Babuskov
- How do I Install Firebird?
- How to connect to Firebird database?
- Is there a PHP web tool like phpMyAdmin for Firebird?
- How to create auto increment values?
- Is there something like limit x, y in select query?
- How to list all tables in a database?
- How to count the number of records retrieved?
- Is there something else I should know?
If you don't find something here, you can always get some help, at this mailing list: http://groups.yahoo.com/group/ib-support/
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
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:
SERVER_HOST=localhost DATABASE_NAME=testdb USERNAME=milanb PASSWORD=*****
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.
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', '');
This tool is still beta, but it's quite useful for everyday work. However, you should look into great number of excellent GUI tools (opensource, freeware and commercial) for easier manipulation with Firebird database.
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:
CREATE TABLE test ( field1 integer not null auto_increment, field2 char(10), PRIMARY KEY (field1) );
INSERT INTO test (field2) VALUES ('testme');
CREATE TABLE test ( field1 integer not null, field2 char(10), PRIMARY KEY (field1) ); CREATE GENERATOR gen_test_id;
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.
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).
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;
For advanced users: The above query will select both user tables and views. To select tables only use this:
SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB$SYSTEM_FLAG = 0 AND RDB$VIEW_BLR IS NULL;
Firebird doesn't have this feature. You can count records by fetching all rows, or by doing SELECT COUNT(*) ... using the same query.
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
- Stored Procedures
- User defined functions