Firebird Interview Preparation Guide

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;'
from rdb$indices
where rdb$system_flag is not null and rdb$system_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 isql 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 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;

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

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 "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:

SELECT ma.*
FROM MON$ATTACHMENTS ma
join MON$TRANSACTIONS mt
on ma.MON$ATTACHMENT_ID = mt.MON$ATTACHMENT_ID
where mt.MON$TRANSACTION_ID = 813;