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

[Michael Van Canneyt]

On Fri, 24 Jul 2009, Graeme Geldenhuys wrote:

> 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).

This is not feasable, because it works only for actual fields. Expressions
for instance will not work, and secondly, it requires a second query per
executed query, which is simply unacceptable from a performance point of view.
So this proposal is not an option.

Firebird reports the theoretical length of each field in the result set, 
we should use that in all cases.

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

If you look carefully, you'll see that the padding of spaces happens in code 
in the case of a CHAR field. Maybe we should do something about that.

Michael.