mORMot Open Source

tomek

Member

Registered: 2017-10-24

Posts: 46

Utf8 and diacritical characters issues

Hi
Despite using UTF8 I have problems with polish diacritical characters. Problem exists in Delphi, in Lazarus is OK (both, Win and Linux).
Below is very simple test program illustrating problem. Table TEST_TABLE have 1 row: ID = 1, STR_FIELD = 'żyto'.
1 query gets value of STR_FIELD ('żyto') and 2 query is using this value to get ID of the same row. Result obviously should return ID=1 but is empty. Debugger shows that 1 query fetches 'ĹĽyto' intead of 'żyto'.

program TestZyto;

{$APPTYPE CONSOLE}

{$IFDEF FPC_CROSSCOMPILING}
  {$IFDEF LINUX}
    {$linklib libc_nonshared.a}
  {$ENDIF}
{$ENDIF}

uses
  {$I SynDprUses.inc}  // use FastMM4 on older Delphi, or set FPC threads
  SynDB,
  SynDBZeos,
  SynCrossPlatformJSON,
  SynCommons,
  SysUtils,
  Classes,
  IniFiles;

var
  aProps: TSQLDBConnectionPropertiesThreadSafe;
  aStmt: TSQLDBStatement;
  sFBHost, sDBPath, sFBUser, sFBPass: string;
  sResultJSON, sResultField, sResultFinal: UTF8String;  //RawUTF8
  jsonTable: TJSONTable;
  //sl: TStringList;

begin

  // ... db params from ini

  aProps := TSQLDBZEOSConnectionProperties.Create(TSQLDBZEOSConnectionProperties.URI(dFIREBIRD, sFBHost),
                                                  sDBPath,
                                                  sFBUser,
                                                  sFBPass);
  try
    aProps.ThreadingMode := tmMainConnection;
    aProps.MainConnection.Connect;

    try
      aStmt := aProps.NewThreadSafeStatement;
      aStmt.Prepare('select STR_FIELD from TEST_TABLE where ID = 1', True);
      aStmt.ExecutePrepared;
      sResultJSON := aStmt.FetchAllAsJSON(True);
    finally
      aStmt.Free;
    end;

    jsonTable := TJSONTable.Create(sResultJSON);
    try
      if jsonTable.Step then
        sResultField := jsonTable.Value['STR_FIELD'];
    finally
      jsonTable.Free;
    end;

    try
      aStmt := aProps.NewThreadSafeStatement;
      aStmt.Prepare('select ID from TEST_TABLE where STR_FIELD = ?', True);
      aStmt.Bind([sResultField]);
      aStmt.ExecutePrepared;
      sResultFinal := aStmt.FetchAllAsJSON(True);
    finally
      aStmt.Free;
    end;

    writeln(sResultFinal);
    {$ifdef MSWINDOWS}
    readln;
    {$endif}

  finally
    aProps.Free;
  end;

end.

Regards, Tomek

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,261

Website

Re: Utf8 and diacritical characters issues

Perhaps a Zeos option to add to force UTF-8 work with Delphi?
(BTW which version of Delphi? Unicode?)

tomek

Member

Registered: 2017-10-24

Posts: 46

Re: Utf8 and diacritical characters issues

Perhaps a Zeos option to add to force UTF-8 work with Delphi?

How to do that?

(BTW which version of Delphi? Unicode?)

Delphi 10.1 Berlin

Regards, Tomek

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,261

Website

Re: Utf8 and diacritical characters issues

Try to ask support on the Zeos forum if there is an option to force UTF-8 for proper Delphi 10.1 support.

tomek

Member

Registered: 2017-10-24

Posts: 46

Re: Utf8 and diacritical characters issues

If there is a way to change Zeos behaviour with some options, how to set them through TSQLDBZEOSConnectionProperties?

Regards, Tomek

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: Utf8 and diacritical characters issues

What's the default characterset of the database?
I can't confirm your findings.

One szenario i could imagine is a FB-DB with characterset 'NONE' and undefined collations on the field.
A windows client would write raw data with windows encoded codepages and a *nix client expects utf8.
To be honest we are aware about the 'CS_NONE' issue and with 7.3 we already announced to cut the support for all db charactersets where we need a crystal ball for the encodings.
See: https://sourceforge.net/p/zeoslib/wiki/ … 20release/

If you db uses CS_NONE make a dump and recreate the DB again!

@AB there is no difference for the XE.1 compiler comparing to each other unicode ide.