Firebird

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;

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 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.

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.

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;