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 Borland’s open source edition of InterBase in 2000, so learn Firebird or get prepared for the job of Firebird with the help of this Firebird Interview Questions with Answers guide by Pritish Kumar Halder.
1. 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;’
where rdb$system_flag is not null and rdb$system_flag = 0
2. 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).
3. 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.
4. How to configure events with firewall?
5. 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))
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)
6. 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
7. How to deactivate triggers?
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.
8. 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’.
9. 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 header page information:
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:
FROM MON$ATTACHMENTS ma
join MON$TRANSACTIONS mt
on ma.MON$ATTACHMENT_ID = mt.MON$ATTACHMENT_ID
where mt.MON$TRANSACTION_ID = 813;
10. 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-V18.104.22.16848 Firebird 2.0
LI-V22.214.171.12470 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’)