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.<\/p>\n
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:<\/p>\n
\r\nselect rdb$relation_name as tablename from rdb$relations\r\nwhere rdb$view_blr is null and \r\n(rdb$system_flag is null or rdb$system_flag = 0)\r\n<\/pre>\nand to retrieve the actual structure of such a table I use the following statement:<\/p>\n
\r\nselect rf.rdb$field_position as fieldpos, \r\nTRIM(rf.rdb$field_name) as fieldname, \r\nf.rdb$field_type as fieldtype, \r\nf.rdb$field_length as fieldlen,\r\nf.rdb$field_scale as fielddec, \r\n(f.rdb$null_flag = 1) OR (rf.rdb$null_flag = 1) as canbenull \r\nfrom rdb$relation_fields rf \r\nJOIN rdb$fields f ON f.rdb$field_name = rf.rdb$field_source \r\nWHERE upper( rf.rdb$relation_name ) = upper( 'mytable' ) \r\norder by rf.rdb$field_position\r\n<\/pre>\nTo have no limitations with foreign languages, I have opted to use the UTF-8 character set for all varchar columns.<\/p>\n
And now I was very surprised: the select statement returned 4 times the actual field width<\/strong>: for a field defined as varchar(20) the query returned 80 as field length.<\/p>\n
My conclusion is very simple: since every UTF-8 character can take up to 4 bytes, Firebird reserves 4 bytes for every character.<\/p>\n