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

[Michael Van Canneyt]

On Tue, 21 Jul 2009, Graeme Geldenhuys wrote:

> Michael Van Canneyt wrote:
>> 
>> You may never trim these fields; The database contains always the
>> declared number of characters: the value is trimmed with a space till
>> the number of declared characters is reached. TField.AsString returns
>> what is in the database, which seems to be 8 characters in your case.
>
> Yes Firebird returns 8 characters for a Char(2) database field. Firebird is 
> very happy with inserting "en", but when read it back with FPC, it returns 
> "en      "
>
> If I run a select query using the FlameRobin database tool against that exact 
> same database and table, FlameRobin returns "en". FPC's SqlDB doesn't.
>
> ==============[  database DDL  ]====================
> CREATE DOMAIN D_OID AS Char(36)
> COLLATE UTF8;
>
> CREATE TABLE COUNTRY(
>  OID D_OID NOT NULL,
>  ID Char(2) NOT NULL,
>  NAME Varchar(50) NOT NULL,
>  CONSTRAINT PK_COUNTRY PRIMARY KEY (OID)
> );
> =================[  end  ]====================
>
> When I ask FlameRobin to export the Country table data as INSERT statements, 
> it does it correctly with the correct spacing as shown below.
>
> ==========================
> INSERT INTO COUNTRY (OID, ID, NAME) VALUES ('ZA          ', 'ZA', 'South 
> Africa');
> ==========================
>
> 36 characters for the OID field and 2 characters for the ID field. Just like 
> I defined it in the DDL.
>
> So why does FlameRobin handle Char() fields as I expected, but FPC's SqlDB 
> does something different. So who is right and who is wrong?
>
>
>> No, because it is fundamentally wrong.
>
> I did not mean with a simple Trim() function. I meant being more precise and 
> still return the correct amount of characters as defined in the DDL. Or maybe 
> do some UTF8ToString() conversion or something if field charset = UTF8.
>
>
>> The question should be: Why does Firebird report 8 characters to your
>> application ?
>
> Only via FPC's SqlDB - not via FlameRobin. See above.

That is why I wrote 'to your application', obviously.

This will require a serious amount of investigation and debugging of TDataset and
TSQLConnection. Because according to IBConnection.pp, the field size is determined
solely by what firebird reports in SQLLen:

   else case (SQLType and not 1) of
     SQL_VARYING,SQL_TEXT :
       begin
         TrType := ftString;
         if SQLLen > dsMaxStringSize then
           TrLen := dsMaxStringSize
         else
           TrLen := SQLLen;
       end;

Which means firebird reports 8 to your application.

Michael.