Wolfgang Riedmann Blog

In my Visual Objects applications based on DBFs I have a routine that checks the structure of the DBF tables, compares them with the structure that is required and adjusts the structure.

I’m now working on a similar functionality for my Firebird based .NET applications. To retrieve the list of the currently existing user tables, I’m using such a statement:

select rdb$relation_name as tablename from rdb$relations
where rdb$view_blr is null and 
(rdb$system_flag is null or rdb$system_flag = 0)

and to retrieve the actual structure of such a table I use the following statement:

select rf.rdb$field_position as fieldpos, 
TRIM(rf.rdb$field_name) as fieldname, 
f.rdb$field_type as fieldtype, 
f.rdb$field_length as fieldlen,
f.rdb$field_scale as fielddec, 
(f.rdb$null_flag = 1) OR (rf.rdb$null_flag = 1) as canbenull 
from rdb$relation_fields rf 
JOIN rdb$fields f ON f.rdb$field_name = rf.rdb$field_source 
WHERE upper( rf.rdb$relation_name ) = upper( 'mytable' ) 
order by rf.rdb$field_position

To have no limitations with foreign languages, I have opted to use the UTF-8 character set for all varchar columns.

And now I was very surprised: the select statement returned 4 times the actual field width: for a field defined as varchar(20) the query returned 80 as field length.

My conclusion is very simple: since every UTF-8 character can take up to 4 bytes, Firebird reserves 4 bytes for every character.

I have now found the following StackOwerflow article about this:
http://stackoverflow.com/questions/822837/firebird-utf8-varchar-size