Firebird Interview Preparation Guide
Download PDF

Firebird Interview Questions and Answers will teach us now that Firebird is an open source relational database management system that runs on GNU/Linux, Windows, and a variety of Unix platforms. The database forked from Borlands open source edition of InterBase in 2000, so learn Firebird or get preparation for the job of Firebird with the help of this Firebird Interview Questions with Answers guide

44 Firebird Questions and Answers:

1 :: Can I concurrently access a Firebird database with embedded and with regular server?

If you mean it's one database and two applications then: NO
If you mean it's two databases and one application then: YES

2 :: How to activate all indexes in Firebird?

If you run Firebird 1.x which doesn't have EXECUTE BLOCK, you can run the following query:

select 'ALTER INDEX '||rdb$index_name ||' ACTIVE;'
from rdb$indices
where rdb$system_flag is not null and rdb$system_flag = 0

3 :: How to add, remove, modify users using SQL?

It is currently not possible. You need to use service API. Access to it is provided by most connectivity libraries (except ODBC).

4 :: How to change database dialect?

While you could simply change a flag in database file it isn't recommended as there's much more to it. Different dialects have different ways of handling numeric and date operations, which affects all object that are compiled into BLR (stored procedures, triggers, views, computed fields, etc.) Fixing all that on-the-fly would be very hard, so the recommended way is to create a new database and copy the data. You can easily extract the existing database structure using isql and then copy the data using some of the tools

5 :: How to configure events with firewall?

If firewall is on client, you don't have to do anything special. If firewall is on the server, you need to set RemoteAuxPort setting in Firebird.conf file and forward traffic from firewall to that port.

6 :: How do convert or display the date or time as string?

Simply use CAST to appropriate CHAR or VARCHAR data type (big enough). Example:

CREATE TABLE t1 ( t time, d date, ts timestamp );
INSERT INTO t1 (t,d,ts) VALUES ('14:59:23', '2007-12-31', '2007-12-31 14:59');

SELECT CAST(t as varchar(13)), CAST(d as varchar(10)), CAST(ts as varchar(24))
FROM t1;

Firebird would output times in HH:MM:SS.mmmm format (hours, minutes, seconds, milliseconds), and dates in YYYY-MM-DD (year, month, day) format.

If you wish a different formatting you can either use SUBSTRING to extract the info from char column, or use EXTRACT to buld a different string:

SELECT extract(day from d)||'.'||extract(month from d)||'.'||extract(year from d)
FROM t1;

7 :: How to create a database from my program?

Firebird doesn't provide a way to create database using SQL. You need to either use the Services API, or external tool. As API for database creation is often not available in libraries, you can call Firebird's isql tool to do it for you.

Let's first do it manually. Run the isql, and then type:

SQL>CREATE DATABASE 'C:dbasesdatabase.fdb' user 'SYSDBA' password 'masterkey';

That's it. Database is created. Type exit; to leave isql.

To do it from program, you can either feed the text to execute to isql via stdin, or create a small file (ex. create.sql) containing the CREATE DATABASE statement and then invoke isql with -i option:

isql -i create.sql

8 :: How to deactivate triggers?

You can use these SQL commands:


Most tools have options to activate and deactivate all triggers for a table. For example, in FlameRobin, open the properties screen for a table, click on Triggers at top and then Activate or Deactivate All Triggers options at the bottom of the page.

9 :: How to debug stored procedures?

Firebird still doesn't offer hooks for stored procedure debugging yet. Here are some common workarounds:

* You can log values of your variables and trace the execution via external tables. External tables are not a subject of transaction control, so the trace won't be lost if transaction is rolled back.

* You can turn your non-selectable stored procedure into selectable and run it with 'SELECT * FROM' instead of 'EXECUTE PROCEDURE' in order to trace the execution. Just make sure you fill in the variables and call SUSPEND often. It's a common practice to replace regular variables with output columns of the same name - so that less code needs to be changed.

* Some commercial tools like IBExpert or Database Workbench parse the stored procedure body and execute statements one by one giving you the emulation of stored procedure run. While it does work properly most of the time, please note that the behaviour you might see in those tools might not be exactly the same as one seen with actual Firebird stored procedure - especially if you have uninitialized variables or other events where behavior is undefined. Make sure you file the bug reports to tool makers and not to Firebird development team if you run such 'stored procedure debuggers'.

* Since Firebird 2.0 you can also use EXECUTE BLOCK to simulate stored procedures. EXECUTE BLOCK does not support input parameters, so you need to convert all of those to local variables (with DECLARE VARIABLE)

10 :: How to detect applications and users that hold transactions open too long?

To do this, you need Firebird 2.1 or a higher version. First, run gstat tool (from your Firebird installation's bin directory), and you'll get an output like this:

gstat -h faqs.gdb

Database "faqs.gdb"
Database header page information:
Flags 0
Checksum 12345
Generation 919
Page size 4096
ODS version 11.1
Oldest transaction 812
Oldest active 813
Oldest snapshot 813
Next transaction 814

Now, connect to that database and query the MON$TRANSACTIONS table to get the MON$ATTACHMENT_ID for that transaction, and then query the MON$ATTACHMENTS table to get the user name, application name, IP address and even PID on the client machine. We are looking for the oldest active transaction, so in this case, a query would look like:

where mt.MON$TRANSACTION_ID = 813;

11 :: How to detect the server version?

You can get this via Firebird Service API. It does not work for Firebird Classic 1.0, so if you don't get an answer you'll know it's Firebird Classic 1.0 or InterBase Classic 6.0. Otherwise it returns a string like this:

LI-V2.0.0.12748 Firebird 2.0


LI-V1.5.3.4870 Firebird 1.5

The use of API depends on programming language and connectivity library you use. Some might even not provide it. Those that do, call the isc_info_svc_server_version API.

If you use Firebird 2.1, you can also retrieve the engine version from a global context variable, like this:

SELECT rdb$get_context('SYSTEM', 'ENGINE_VERSION')
from rdb$database;

12 :: How to determine who is and change the owner of database?

Use the following query:


Please note that in order to change the owner, it is not enough (or even advisable) to change this column only, as many other metadata fields are involved (there are multiple tables which have this field and SQL privileges need to be updated as well). There is a handy tool by Thomas Steinmaurer that can do this automatically, but you'll have to e-mail him directly to get it.

13 :: How to pipe multiline string to isql?

Using Bash shell you may use the following construct:

echo "DROP VIEW v1;"
echo "CREATE VIEW..."
} | isql -user SYSDBA -pass masterkey srv:db

each echo statement outputs newline at the end.

Because that's a lot of writing, use the so called 'document here' feature of the shell:

cat <<- _END_ | isql user SYSDBA -pass masterkey srv:db

14 :: How to open the database in exclusive mode?

You need to shutdown the database (using gfix or some other tool). Firebird 2.0 offers various shutdown modes (single-user, single-connection, multiple connection, etc.). Take a look at Firebird 2 release notes for details.

15 :: How to move a multi-file database?

You are probably used to having a single-file database which you can move around as much as you want. But, your database has grown too big and now you need a multi-file database. Paths to the secondary files are absolute and stored in the header page of the first database file. If you need to move those files, it is recommended that you backup the database and restore at new location. However, if you really, really need to copy them around, you can use freeware tool GLink by Ivan Prenosil:

16 :: How to monitor Firebird server activity?

Firebird 2.1 introduces server-side monitoring via special system tables. This way you can monitor your server directly from SQL. Those system tables all have prefix MON$ in their name. To use them, you need to make sure your database file is created with Firebird 2.1 or higher (ODS version 11.1). If you have a database that is created with earlier versions, you need to do backup and subsequent restore with Firebird 2.1 to have those tables.

For detailed information about each monitoring table, read the file README.monitoring_tables in 'doc' directory of your Firebird installation.

Please note that monitoring tables just offer the data, but you'll probably need some way to aggregate those and spot the problems. Most of these tools can also be used to monitor earlier Firebird versions as well.

17 :: How to migrate Paradox, dBase or FoxPro database to Firebird?

The easiest way is to download the freeware IBDataPump by CleverComponents. It will extract the metadata from Paradox/dBase/FoxPro database, create all the tables in a Firebird database and then copy all the data. You'll probably have a ready-to-go Firebird database in less than one hour.

18 :: How to lock records in a table?

While there shouldn't be many reasons to do this in MGA database system like Firebird, there are ways to do it.

One is to use a dummy update for all the records you wish to lock. Many developers do this by accident and get the deadlocks. Example that locks employee 8:

-- start transaction
update employee set emp_no = emp_no where emp_no = 8;
update employee set ... where emp_no = 8;
-- end transaction

A more elegant way is to use the SELECT ... WITH LOCK syntax.

-- start transaction
select * from employee where emp_no = 8 WITH LOCK;
update employee set ... where emp_no = 8;
-- end transaction

Please note that locking easily leads to deadlocks with NO WAIT and application hanging with WAIT transactions. Use it only if you're really sure you know what you are doing and why.

19 :: How to load a file into database column?

While some other database systems might have an SQL function for this, with Firebird you need an application. Datatype that holds binary files is called BLOB, and you should use sub_type zero, as sub_type one is for text-only data. Let's create a table to hold the file. We'll have a filename column and a blob column containing the file itself:

file_name VARCHAR(200),
file_data BLOB SUB_TYPE 0

The blobs are loaded via parametrized query:

INSERT INTO t1 (file_name, file_data) VALUES (?, ?);

20 :: How to get a list of tables, views and columns in Firebird database?

Tables and views are stored in RDB$RELATIONS system table. System tables and views have RDB$SYSTEM_FLAG set, while user defined ones have zero or NULL. You can distinguish views from tables as they have field RDB$VIEW_BLR set. Please note that there is also a field RDB$VIEW_SOURCE which stored human-readable view source and can be set to NULL - database would still be completely functional as it uses precompiled BLR. Here's query to list all user tables:

select rdb$relation_name
from rdb$relations
where rdb$view_blr is null
and (rdb$system_flag is null or rdb$system_flag = 0);

A query to list all views:

select rdb$relation_name
from rdb$relations
where rdb$view_blr is not null
and (rdb$system_flag is null or rdb$system_flag = 0);

Table and view columns are stored in RDB$RELATION_FIELDS. It stores the name, null flag, default value, and domain. In order to get the datatype you need to read domain info from rdb$fields. Here's a query that lists all tables with their columns:

select f.rdb$relation_name, f.rdb$field_name
from rdb$relation_fields f
join rdb$relations r on f.rdb$relation_name = r.rdb$relation_name
and r.rdb$view_blr is null
and (r.rdb$system_flag is null or r.rdb$system_flag = 0)
order by 1, f.rdb$field_position;