Firebird 2.0.3 is released

On 26 September 2007, Firebird version 2.0.3 is released, you can download in here. This version is only bug fixed version from FB 2.0

Installing ZeosDBO in Delphi (rev version)

I already make some revision on my previous post about how to install ZeosDBO components in Delphi. The revision link is here. ZeosDBO is one of the direct connection components to many RDBMS including Firebird. The idea of ZeosDBO is like JDBC, and to switch from one database to another database is really easy, find it by yourself after trying this amazing components. One thing, this component is FREE and OPEN SOURCE

Firebird System Tables (Part III rev 1)

Here is some revision (from Romkin idea) to the query of Firebird System Tables part III.
In this query, it doesn't need anymore to has "case" statement to get the convertion from numeric type_name to more readable name. And I want to say thank you for the supporting comment on this little articles. Once correction from Milan Babuskov that told me "CONTAINING" is in Firebird since the first versions 1.0. Hope all articles in this blog can be usefull for FB community. Wait for the next Firebird System Tables again, stay tune on the same channel he..he..he.. :)

CREATE VIEW "vSYS_FindDependencies"(
"dependentName",
"dependentType",
"dependedOnName",
"dependedOnFieldName",
"dependedOnType")
AS
select d.RDB$DEPENDENT_NAME DEPENDENT_NAME,
(select t.RDB$TYPE_NAME from RDB$TYPES t
where d.RDB$DEPENDENT_TYPE=t.RDB$TYPE
and t.RDB$FIELD_NAME = 'RDB$OBJECT_TYPE'
) DEPENDENT_TYPE,
d.RDB$DEPENDED_ON_NAME DEPENDED_ON_NAME, d.RDB$FIELD_NAME DEPENDED_ON_FIELD_NAME,
(select t.RDB$TYPE_NAME from RDB$TYPES t
where d.RDB$DEPENDED_ON_TYPE=t.RDB$TYPE
and t.RDB$FIELD_NAME = 'RDB$OBJECT_TYPE'
) DEPENDED_ON_TYPE
from RDB$DEPENDENCIES d

Firebird System Tables (Part III)

This article idea (Part III) is belong to the someone that put comment on my previous article, so I just accomodate his/her idea. And I already arrange to become a view like this:

CREATE VIEW "vSYS_FindDependencies"(
"dependentName",
"dependentType",
"dependedOnName",
"dependedOnFieldName",
"dependedOnType")
AS
select RDB$DEPENDENT_NAME,
case RDB$DEPENDENT_TYPE
when 0 then 'Table'
when 1 then 'View'
when 2 then 'Trigger'
when 3 then 'Computed field'
when 4 then 'Validation'
when 5 then 'Procedure'
when 6 then 'Expression index'
when 7 then 'Exception'
when 8 then 'User'
when 9 then 'Field'
when 10 then 'Index'
when 14 then 'Generator'
else RDB$DEPENDENT_TYPE
end DEPENDENT_TYPE,
RDB$DEPENDED_ON_NAME, RDB$FIELD_NAME,
case RDB$DEPENDED_ON_TYPE
when 0 then 'Table'
when 1 then 'View'
when 2 then 'Trigger'
when 3 then 'Computed field'
when 4 then 'Validation'
when 5 then 'Procedure'
when 6 then 'Expression index'
when 7 then 'Exception'
when 8 then 'User'
when 9 then 'Field'
when 10 then 'Index'
when 14 then 'Generator'
else RDB$DEPENDED_ON_TYPE
end DEPENDED_ON_TYPE
from RDB$DEPENDENCIES;

The idea is to know the dependency on some entity in the database. Perhaps it is also usefull for everyone.

Firebird System Table (Part II)

Another FB system table is system table for trigger. The reason I create this view is to scan for some code inside the source of the trigger. For example, I want to change the field type of table A, then I want to know, this field is being used by which trigger, so I can search thorough this view:

CREATE VIEW "vSYS_FindTriggerSource"(
"triggerName",
"tableName",
"triggerInactive",
"triggerSource")
AS
select r.rdb$trigger_name, r.rdb$relation_name, r.rdb$trigger_inactive, r.rdb$trigger_source
from rdb$triggers r
where r.rdb$system_flag = 0

If you want to seach some text in the trigger source then you can use this simple query:

select *
from "vSYS_FindTriggerSource"
where "triggerSource" containing 'some_field'

The keyword "containing" here (if I'm not forget) is exists from FB 2. The difference between "containing" and "like" is the "like" is case sensitive where "containing" is case insensitive, and also you not need the "%" sign like in "like" keyword.

That view also display the status of the trigger, if the "triggerInactive" field value is zero, then it means that trigger is active and vice versa.

Firebird System Tables (Part I)

Hi, whew it's has been long time since my last time post, yes I a little bit busy now, with some of my own project and some of my family work. Ok, now I want to share about Firebird System Tables, they are tables that side on every FB database that consist of metadata of user tables (table that you created), many question about this and also many purpose to know about this.

I often use this technique when I develop a database that consist of many tables and where there is an error on one of the field and then I must know this field is belong on which table, to solve that problem, I create a view like this:

CREATE VIEW "vSYS_FindField"(
"tableName",
"fieldName",
"default_val")
AS
select r.rdb$relation_name, f.rdb$field_name, f.rdb$default_source
from RDB$RELATIONS r, RDB$RELATION_FIELDS f
where r.rdb$system_flag = 0
and r.rdb$relation_name=f.rdb$relation_name;

The FB System Tables can be known from "RDB$RELATIONS.RDB$SYSTEM_FLAG" greater than 0 (zero). The "RDB$RELATIONS" is a system table for user table and user view (off course it also contain of many things that you can look by yourself ;P) And "RDB$RELATION_FIELDS" is as you may guess is a system table for user field. It's now up to you to improve that query, maybe you want to include some more information, then go ahead :)

NB: Thank's Romkin for the correction, now I already applied your correction in this short article :)