Latest Firebird Interview Questions
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 rdbSsystem_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 isqi 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 VARCI-TAR 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-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;
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 DATAB ASE ‘C :\dbases\database. Rib’ 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:
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.
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 i 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 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:
SELECT ma.*
FROM MON$ATTACHMENTS ma
join MON$TRANSACTIONS mt
on ma.MON$ATTACHMENT ID – mt.MONSATTACHMENTID
where mt.MONSTRANSACTION_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
or…
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 ;
12. How to determine who is and change the owner of database?
Use the following query:
SELECT DISTINCT RDBSO WNER NAME AS DATABASE OWNER
FROM RDB$RELATIONS
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.
13. How to pipe multiline string to isqi?
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 < input my_script.sql;
SQL> commit;
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.
39. Is there a way to detect whether fbclient.dll or tbembed.dll is loaded?
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
40. Is there an example how to configure ExternalFileAccess setting in fi rebird.conf?
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
41. Is there an example how to configure UdfAccess setting in firebird.conf?
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 None
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.
42. Is there some bulk load or other way to import a lot of data fast?
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),
field2 smallint
);
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.
43. What is the best way to determine whether Firebird server is running?
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
Example:
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:
ping [hostname|IPaddress]
Example:
ping 192.168.0,22
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).
44. Why does reading require write privileges on database file?
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).
45. How to connect with Firebird database in Delphi using TSQLConnection?
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.
begin
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;
SQLConnection1.Open;
end;
46. How to tell Firebird to only accept conections from XYZ host or network?
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”