Dear Readers, Welcome to Firebird Interview Questions have been designed specially to get you acquainted with the nature of questions you may encounter during your Job interview for the subject of Firebird. These Firebird Questions are very important for campus placement test and job interviews. As per my experience good interviewers hardly plan to ask any particular questions during your Job interview and these model questions are asked in the online technical test and interview of many IT companies.
If you mean it’s one database and two applications then: NO
If you mean it’s two databases and one application then: YES
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:’
where rdb$system_flag is not null and rdbSsystem_flag 0
It is currently not possible. You need to use service API. Access to it is provided by most connectivity libraries (except ODBC).
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 isqi and then copy the data using some of the tools
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.
Simply use CAST to appropriate CHAR or VARCI-TAR data type (big enough).
CREATE TABLE t1 (t time, d date. ts timestamp);
INSERT INTO t1 (t,d,ts) VALUES (‘14:59:23’, ‘2007-12-3 1’, ‘2007-12-31 14:59’);
SELECT CAST(t as varchar(1 3)), 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) FROMt1;
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 DATAB ASE ‘C :\dbases\database. Rib’ user ‘SYSDBA’ password
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
You can use these SQL commands:
ALTER TRIGGER trigger_name INACTIVE;
ALTER TRIGGER trigger_name ACTIVE;
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.
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)
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 header page information:
Page size 4096
ODS version Ii .1
Oldest transaction 812
Oldest active 813
Oldest snapshot 813
Next transaction 814
Now, connect to that database and query the MON$TRANSACTTONS table to get the
MON$ATTACHMENT_ID for that transaction, and then query the
MONSATTACHMENTS table to get the user name, application name, 1P 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:
FROM MON$ATTACHMENTS ma
join MON$TRANSACTIONS mt
on ma.MON$ATTACHMENT ID - mt.MONSATTACHMENTID
where mt.MONSTRANSACTION_ID = 813,
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
U-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 rdbSget_context(’SYSTEM’, ‘ENGINE VERSION’)
from rdbSdatabase ;
Use the following query:
SELECT DISTINCT RDBSO WNER NAME AS DATABASE OWNER
WHERE (RDB$SYSTEM FLAG = 1 )
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 email him directly to get it.
Using Bash shell you may use the following construct:
echo “DROP VIEW vi;”
echo “CREATE VIEW..”
} isqi -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_ I isql user SYSDBA -pass masterkey srv:db
DROP VIEW v1;
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
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 G1.ink by Ivan Prenosil:
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.
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.
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.
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:
CREATE TABLE t1
file_data BLOB SUB_TYPE 0
The blobs are loaded via parametrized query:
INSERT INTO t1 (file_name, file_data) VALUES (?,? );
Tables and views are stored in RDBSRELATIONS system table. System tables and views have RDB$SYSTEM_FLAG set, while user defined ones hake zero or NULL. You can distinguish views from tables as they have field RDB$ VIE W_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:
where rdb$view_blr is null
and (rdb$system_flag is null or rdb$system_flag = 0):
A query to list all views:
where rdb$view bir is not null
and (rdb$system_flag is null or rdb$system_flag 0);
Table and view columns are stored in RDBSRELATION_FIELDS. It stores the name, null flag, default value, and domain, in order to get the datatype you need to read domain info from rdbSfields. Here’s a query that lists all tables with their columns:
select frdb$relation_name, PrdbSfield_name
from rdb$relation_fields f
join rdb$relations ron f. rdb$relation name r. rdb$relation_narne
and r. rdb$view_ blr is null
and (r.rdb$system_flag is null or r.rdb$system_fiag = 0)
order by 1, frdb$field_position;
It’s quite simple, use isql with -x or -a options. Please be careful and test if it works. Some commercial administration tools like to play with system tables directly, and isql isn’t always able to understand their hacks
You can also extract DDL with FlameRobin Open the properties page for the database and select DDL option at the top.
If your databases are on-line, i.e. visible to each other via network, then you can use some data pump tool like freeware lB Pump or some of advanced commercial administration tools which have this option integrated.
If your databases are offline, you should first export the data and then import it on the other end. Most admin. tools can do export to CVS,XML or INSERT statements. If efficiency is important, or your have data with BLOB column, you can use the open source FBExport tool.
If you are looking for a way to easily import CS\’ or XML data into Firebird, take a look at XMLWizard tool. You can also use it to import data from Microsoft Excel or OpenOffice by saving the sheet to .csv format and then importing via XMLWizard
Deleting all foreign keys can be done by querying the system tables and droping them one by one. If you use Firebird 2 or higher, it can be done with a single SQL statement:
EXECUTE BLOCK RETURNS (stmt VARCHAR(1 000)) AS
select ‘alter table ‘||r.rdb$relation_name ||’ drop constraint’|| ‘ r. rdb$constraint_name||’;’ from rdb$relation _constraints r
where (r. rdb$constraint_type=’FORETGN KEY’)
DO begin suspend; execute statement :stmt; end
set term; !!
If you use Firebird i .x, you can run the following query to get statements to execute and then copy/paste the result and execute:
select ‘ALTER TABLE’ ||r. rdb$relat ion_name
||’DROP CONSTRAINT’|| r. rdb$constraint_name||’:’
from rdb$relation_constraints r
where (r.rdb$constraint_type=’FORETGN KEY’)
Firebird does not offer replication out-of-the-box, you need to use some 3rd party tools. Those external tools add specific triggers that log all the changes in database and
replicate to other databases.
Currently there is no easy way of doing it. You can bring database to some of shutdown modes, but it affects all users. If you use Classic you can (with some effort) find the users process by detecting the IP address and open database files of that process and simply kill that process. With Super Server it is not possible as the connection is run in a thread of’
multithreaded SuperServer process.
There are plans for future versions of Firebird to address this. For example. version 2.1 introduces ability to cancel running queries (by deleting the relevant records from
Here are some tips:
a) create a scheduled task or cron job that will truncate or rotate the log file. By rotation, we mean renaming the flies in such way that you always have a number of previous logs available. Example.
rename firebird. iog.4 firebird. log. 5
rename flrebird.iog.3 firebird.log.4
rename flrebird.log.2 firebird.log.3
rename firebird.log.l f’irebird.iog,2
rename firebird.iog firebird.log. I
This way you’ll always have last five logs available, and those too old get deleted. You
can also use zip, rar, bzip2 or some other packer to compress the old log files. Since they are plain text, they compress very well.
b) redirect logging to void. For example, on Linux, you can do it by creating a symlink to /dev/null instead of the regular log file:
# cd /opt/firebird
# rm -f firebird log
in -s /dev/null firebird.log
Many recommend IB Pump or IB Data Pump, but the problem is when you have complex relations between tables. In such cases, it is better to use tool like FB Copy which sorts the tables by dependencies (foreign keys, check constraints) into correct order
Recreating the index:
ALTER INDEX indexName INACTIVE.
ALTER INDEX indexName ACT1VE
Please note that it does not work for indices automatically created by the system like the ones for primary key or foreign key. You might want to drop and recreate the constraints in such case.
To recalculate the index statistics (which is enough for most cases), use:
SET STATISTICS INDEX indexName;
While the order should not matter to applications (you should always use explicit column names in queries), perhaps it’s easier for you when you work with tables in database administration tools. You can move a column to different location using the following SQL statement:
ALTER TABLE table_name ALTER field name POSITION new_position:
Positions are numbered from one. If you wish to exchange two fields, make sure you run the statement for both of them. It’s easy to run tools like FlameRobin to do this (Reorder Fields option at table’s properties screen).
Here’s a short step-by-step walkthrough.
* disconnect users and disable incoming connections to the database
* make a copy of database file (or two copies) and work on that
* use GFIX with -v option to validate the database file
* use GF1X with -v and -f to do full validation
If problem is not too serious, you can try to backup the broken db and restore under a new name:
* use GF1X -mend to prepare corrupt database for backup
* use GBAK -b -g to backup the database. -g disables garbage collection (FAQ #41)
* use GBAK -c to restore backup to a new database.
If you succeed, you have fixed the problem and have a functional database. If not, you can try to create an empty database with the same structure and pump the data to it.
One of the reasons why backup or restore can fail is if some broken database triggers exist, and prevent connection to the database. For example, a database trigger might use some table which has a broken index, etc. To work around this, connect to database with isql tool using -nodbtriggers option and then disable those triggers. You can enable them later when you fix other problems and get a working database again.
There is no such feature in Firebird, but you can use some tricks. The following example requires that you have a unique integer column (primary key is usually used):
FROM table t1
ORDER BY (t1 .int_col + seed)*4294967291 -((tl .int_col +
seed)*429496729 1/491 57)*491 57
If you just need one random record, limit the result set using FIRST or ROWS clause. This query will give consistent records for the same seed. If you wish to be completely random, you need to change the seed. You could use the value of int_col from previous run, or simply fetch a new value from a generator (just make sure the same value for seed is used in both places in expression).
In order to keep the server low reasonable, you might want to limit the time a single query can consume. Firebird does not support this directly yet (there are plans for Firebird 3.0).
However, you could periodically query the monitoring tables (Firebird 2.1 and above) to detect and cancel long running queries. You can do:
SELECT * FROM MON$STATEMENTS:
Look for those having MON$STATE set to 1.
Please note that your database needs to be at least ODS 11.1, i.e. created with Firebird 2.1 or above. Older databases wont show you these tables even if you use Firebird 2.1 to access them. To learn more about ODS and how to retrieve it.
The server is started and stopped by ‘fbmgr’ executable from ‘bin’ directory of your
Firebird installation. It is called ‘ibmgr’ in Firebird 1.0. To start the serer type:
To start the server with Guardian (Guardian watches the server and restarts it if it crashes) type:
/opt/firebird/bin/fbmgr -start -forever
To stop a running server, type:
/opt/firebird/bin/fbmgr -shut -user SYSDBA -pass ****
To force a shutdown, type:
/opt/firebird/bin/fbmgr -shut -force -user SYSDBA -pass ****
If you use Firebird 2 or higher, you can also use the regular ‘kill’ command to shutdown the server, as it handles the signals properly. Make sure you first kill the guardian and then the server (otherwise guardian would restart the server).
This isn’t really a thing you should be configuring in Firebird. There is a
RemoteBind Address setting in firebird.conf which configures on which
interfaces/addresses the Firebird listens but that’s all. You should really use your system’s firewall to set this up.
Beside firewall, if you use Classic on Linux, you can use xinetd or meld access control files /etc/hosts.allow and /etc/hosts.deny. With xinetd you can also edit die xinetd configuration file for Firebird service, which is in /etc/xinetd.d/firebird and add a line like this:
“only _from 192.168.0.0/24”
You have to use SuperServer, set up RemoteAuxPort setting in firebird.conf and create two tunnels (one for data, other for events).
It’s quite simple. the only thing you need to remember is that you must always use ib_util_mallocOto allocate memory if your UDF returns string result. The UDF must be declared as FREE IT. so that Firebird releases the memory after it reads the string.
To use ib_util_mallocO, you need to import it from ib_utildil into your program - and make sure you use it instead of regular memory alocating functions. Here’s a simple example of Delphi UDF:
function ib uti l_rnalloc(l: integer): pointer; cdecl: external ‘ib_util.dll;
function ChangeMyString(const p: PChar). PChar; cdecl:
s := DoSomething(string(p));
Result := ib_util_malloc(Length(s) + 1);
Declaration in Firebird:
DECLARE EXTERNAL FUNCTION ChangeMyString
RETURNS CString(255) FREE_IT
ENTRY_POINT ‘ChangeMyString’ MODULE_NAME ‘……’‘
To get it from SQL. you need to use Firebird 2.0 (own address), or Firebird 2.1 (anyone’s):
If you use Firebird 2.0 or higher, use the GET_RDB$Context function with (‘SYSTEM’, ‘CLIENT_ADDRESS’) parameters.
If you use Firebird 2.1 or higher, you can get address of any client by selecting from the monitoring tables.
With Firebird I .x you can try to get the information from TCP/IP stack, using netstat or
Yes. You can use isql for this. It is located in the ‘bin’ directory of your Firebird installation. If you wish to try it interactively, run isql and then type:
isql localhost:my_database -user sysdba -pass “‘
SQL> input my_script.sql;
To run it from a batch (.bat) file or a shell script, use -i switch:
isql -i my_script.sql localhost:my_database -user sysdba -pass ******
If you have some DM1. statements in your script, make sure you put the COMMIT command at the end of the file. Also, make sure the file ends with a newline, as isql executes the commands on the line only after it gets the newline character.
There are some ways to detect it:
- check the size of DLL file
- if you are using different versions of Firebird (for example 1.5.4 and 2.0.1, you can query the server version via Services API)
You should understand that fbembed can be used as a regular Firebird client. Checking whether embedded or fbclient is loaded for licensing or similar needs is really not useful. You could use the connection string as guide, but super server can establish direct local connections without localhost prefix.
If you combine all this information, you could get some conclusions:
- if DLL size matches fbembed and connection string doesn’t have hostname, you are using embedded
- if DLL size matches fbembed and connection string does have hosmame. you are using either super server or classic
- if DDL size matches fbclient and connection string doesn’t have hostname, you are using super server via local connection (IPC. XNET)
- if DLL size matches fbclient and connection string does have hostname, you are using either super server or classic
Firebird’s config file (firebird.conf) does have descriptions inside that explain everything, but sometimes they are confusing and hard to understand what should you do exactly if you don’t have examples. One of such settings is ExternalFileAccess. Some people are even tempted to put Full as it is much easier than trying to guess what’s the correct format Here are the basic settings (‘None’ to disallow external tables and ‘Full’ to allow them anywhere) which you probably understood yourself
ExtemalFileAccess = None
ExtemalFileAccess = Full
And here are those tricky Restrict settings:
ExtemalFileAccess Restrict C :\some\directory
For multiple directories, use something like this:
ExtemalFileAccess = Restrict C :\some\directory,C :some\other\directory
For Linux users:
ExtemalFileAccess= Restrict /some/directory
Well, there’s one right there in the firebird.conf, but perhaps it isn’t obvious enough. Here are the basic settings (‘None’ to disallow UDFs completely and ‘Full’ to allow them anywhere) which you probably understood yourself:
UdfAccess = Full
And here is that tricky Restrict setting:
UdfAccess = Restrict C:\some\directory
For multiple directories, use something like this:
UdfAccess = Restrict C:\some\directoryC:’sorne\other\directory
For Linux users:
UdfAccess = Restrict !some/directory
In the default setting ‘Restrict UDF, ‘UDF is a directory relative to root directory of Firebird installation.
Currently there is only one way to quickly load a lot of data into database. That is by using external tables. You should read the manual for details, but here’s a short explanation. You create a binary or textual file using the external table format and then hook it up in the database using a statement like this:
CREATE TABLE extl EXTERNAL ‘c:\rnyfile.txt’
field I char(20),
To do quick import into regular table, do something like this:
INSERT INTO realtablel (field1, field2)
SELECT field1, field2 FROM ext1;
This insert would still check constraints, foreign keys, fire triggers and build indexes. If you can, it is wise to deactivate indexes and triggers while loading and activate them when done.
Make sure you drop the external table when done, in order to release the lock on the file.
The main problem with external tables is handling of NULLs and BLOBs. If you need to deal with those, you’re better off using some tool like FBExport. However, please note that external tables are much faster.
If you want to do it from an application, a simple try to connect should suffice. Otherwise you have various options:
a) check if firebird server is in the list of running programs (use task manager on Windows, or ‘ps ax’ command on Linux). Please note that Classic won’t be running until there is a connection established.
b) check whether the port 3050 is open on the machine, First. you can check with netstat command, and if it is open, you can test whether it accepts connections by telnet-ing to the port. Just type:
telnet [hostname|IPaddress] 3050
telnet localhost 3050
If you use Linux, you can also check the open port with ‘Iso? command. It outputs a lot, so you might want to ‘grep’ for 3050 or gds_db strings:
# lsof |grep gds_db
#Isof grep 3050
c) if all of this fails, perhaps you should check whether the remote server is reachable at all You can use ‘ping’ command:
Please note that ping can still give you ‘host unreachable’ message even if host is up. This is because the firewall software can drop the ICMP (ping) packets (it’s done to prevent some viruses from spreading, or network scans).
In order to run the SELECT statement, it still needs to start a transaction.
if you wish to build a read-only database to place on some read-only media like CD or
DVD ROM. you can do it with:
gfix -mode read _only database. fdb
or within your favorite administration tool. ft is also available via ServicesAPl, so you may do it from your application as well. Please note that you can only make this change while preparing the database, because the read-only flag needs to be written in the database file.
When the database becomes read-only, the only thing you can write to is the read_only flag (to reset it back to read-write).
This question is related to Delphi developers. TSQLConnection component is used to connect with firebird in Delphi. Below is code snippet for making firebird database connection in Delphi.
SQLConnection1.ConnectionName := 'Devart InterBase';
SQLConnection1.DriverName := 'DevartInterBase';
SQLConnection1.GetDriverFunc := 'getSQLDriverInterBase';
SQLConnection1.Params.Values['LibraryName'] := 'dbexpida40.dll';
SQLConnection1.Params.Values['VendorLib'] := 'fbclient.dll';
SQLConnection1.Params.Values['HostName'] := 'hostname';
SQLConnection1.Params.Values['Database'] := 'databasename';
SQLConnection1.Params.Values['User_Name'] := 'username';
SQLConnection1.Params.Values['Password'] := 'password';
SQLConnection1.LoginPrompt := False;
This isn't really a thing you should be configuring in Firebird. There is a RemoteBindAddress setting in firebird.conf which configures on which interfaces/addresses the Firebird listens but that's all. You should really use your system's firewall to set this up.
Beside firewall, if you use Classic on Linux, you can use xinetd or inetd access control files /etc/hosts.allow and /etc/hosts.deny. With xinetd you can also edit the xinetd configuration file for Firebird service, which is in /etc/xinetd.d/firebird and add a line like this:
"only_from = 192.168.0.0/24"