[fpc-devel] TField.AsString and Databases with UTF-8 charset

[Graeme Geldenhuys]

Michael Van Canneyt wrote:
>> 
>> 3) SqlDB does the TField.AsString trimming for any Char() fields.
>> 
>> Is option #3 viable?  This will resolve any similar issue to all
>> future developers using Firebird with Char() type fields and
>> Charset=UTF8.
> 
> You may never trim these fields; The database contains always the
> declared number of characters: the value is trimmed with a space till



OK Michael, I did some more research inside the system tables of a 
Firebird database. There is a field that stores the "length" in bytes 
and the "length" in characters. By default it seems that SqlDB only 
every looks at the "byte length"

See the select statement below which retrieves metadata for a know 
table. fields in question are "f.rdb$field_length" and 
"f.rdb$character_length"

What is SqlDB looked at the meta data of a string field and if it is set 
to UNICODE_FSS (pre v2 setting) or UTF8 (post v2 setting) and use the 
appropriate field to determine the "length".

So if the rdb$field_type is 14 (TEXT) or 37 (VARYING) and maybe 40 
(CSTRING) and rdb$character_set_id = 4 (UTF8), then it rather reports 
the rdb$character_length, rather than the rdb$field_length (bytes).

That way, SqlDB can return copy(fieldvaluestring, 0, character_len) as 
the actual field text value, which trims off the padding of spaces.


SELECT
   r.rdb$field_name     as field_name
  ,f.rdb$field_type      as field_type
  ,f.rdb$field_sub_type  as field_sub_type
  ,f.rdb$field_length    as field_length
  ,f.rdb$character_length as field_charlength
  ,f.RDB$CHARACTER_SET_ID as field_charset_id
,c.rdb$character_set_name  as field_charset
FROM
  rdb$fields f
INNER JOIN
   rdb$relation_fields r ON f.rdb$field_name = r.rdb$field_source
LEFT OUTER JOIN
   RDB$CHARACTER_SETS c ON f.rdb$character_set_id = c.rdb$character_set_id
WHERE
   r.rdb$relation_name = 'MODULE'



Regards,
   - Graeme -

-- 
fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal
http://opensoft.homeip.net/fpgui/