Firebird specials in table structure for varchar and UTF-8
2016
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