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

[Joost van der Sluis]

Op dinsdag 21-07-2009 om 16:41 uur [tijdzone +0200], schreef 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.

Yes, Firebird creates a char field with size 8 when the charset is set
to utf8, and you pass a width of 2. It's a documented feature, and could
look strange at first when using char-fields. But it's completely
normal.

And I guess that Flamerobin always trims char-fields. Which is ok when
you are showing data to the end-users, but not for the
connection-component.

Joost.