mORMot TQuery and Unicode ( french accent characters)

Bsaidus · 2020-12-17 13:33:11

Hello, I have to use TQuery to execute SQL statement:

function fQueryComboBoxInit(var oCBox: TComboBox; sChField: string; sLocation: WideString): Boolean;
var
  fQuery: TQuery;
  fNc: TSQLDBConnection;
  fWstr, fWSQL: WideString;
begin
  REsult := False;
  fNc := gfP.NewConnection;
  fQuery := TQuery.Create(fNc);
  fWSQL := 'SELECT fz_desig FROM uc_fzchs WHERE fz_field = ' + QuotedStr(sChField) +
                '   AND fz_parent_id = (SELECT MIN(b.fz_seq) FROM uc_fzchs b ' +
                ' WHERE lower(b.fz_desig) LIKE lower(' + QuotedStr(  ' :text ' ) + '))';
                //' WHERE lower(b.fz_desig) LIKE lower(' + QuotedStr(sLocation) + ' ))' );
  with fQuery do
  begin
    try
      SQL.Clear;
      SQL.Add ( fWSQL );
      try
        fQuery.ParamByName('text').AsVariant := sLocation;    // not work for Algérie, Français ... .
        Open;
        oCBox.Clear;
        oCBox.Items.Add(NullStr^);
        while not EOF do
        begin
          fWstr := FieldByName('fz_desig').AsVariant;
          oCBox.Items.Append( fWstr );
          Next;
        end;
        oCBox.ItemIndex := 0;
      except
        raise;
      end;
    finally
      Free;
      fNc.Free;
    end;
  end;
  Result := True;

end;

But when executed, It return 0 records, Because it sends parameter in wrong character set.

?? Help please ??

ab · 2020-12-17 15:56:02

Why did you use asVariant?
IIRC AsVariant expects the string value to be a RawUTF8, which is probably not the case on your compiler - which compiler are you using?

You should use asString or asWideString.

Bsaidus · 2020-12-18 19:27:33

I use lazarus 2.0.10 with fpc 3.2.0 in {$mode delphi } mode.
I must informe you that this does not function correctelly when it return a string that have accents

function fQueryComboBoxInit      (var oCBox : TComboBox; sChField : string): Boolean;
var
  fQuery: TQuery;
  fNc: TSQLDBConnection;
  fWstr, WSQL: WideString;
begin
  Result := False;
  oCBox.Clear;
  fNc := gfP.NewConnection;
  fQuery := TQuery.Create(fNc);
  WSQL := 'SELECT fz_desig FROM uc_fzchs WHERE fz_field = :sChField';
  with fQuery do
  begin
    try

      SQL.Clear;
      SQL.Add ( WSQL );
      try
        ParamByName('sChField').AsWideString := sChField;
        Open;
        oCBox.Items.Add(NullStr^);
        while not EOF do
        begin
          // fWstr := FieldByName('fz_desig').AsWideString ; // Not work, ether AsString ( return Alg insteed of Algérie)
          fWstr := FieldByName('fz_desig').AsVariant;   // works well 
          oCBox.Items.Append( fWstr );
          Next;
        end;
        oCBox.ItemIndex := 0;
      except
        raise;
      end;
    finally
      Free;
      fNc.Free;
    end;
  end;
  Result := True;
end;

Just one question, is the native type "string" support caracters with accents ( french for exemple ) or I must use WideString ??

Thanks.

Last edited by Bsaidus (2020-12-18 20:33:34)

ab · 2020-12-19 09:17:34

Don't use TQuery, which are compatibility wrappers targetting Delphi.

Use directly the SynDB classses.

Bsaidus · 2020-12-19 09:34:28

Hello.
take a look at this:
https://pasteboard.co/JFCP9yI.jpg
OK ok, i will do.

Bsaidus · 2020-12-19 17:00:11

Re Hi,
I played with the procedure : GetAsWideString and added some code.
Now it works well:

function TQueryValue.GetAsWideString: SynUnicode;
begin
  CheckValue;
  with TVarData(fValue) do
  case VType of
    varNull:     result := '';
    varInt64:    result := UTF8ToSynUnicode(Int64ToUtf8(VInt64));
    {$ifdef fpc}  // +
    varString:   result := WideString(RawUTF8(VAny));//UTF8ToSynUnicode(RawUTF8(VAny));   // + 
    {$else}  // +
    varString:   result := UTF8ToSynUnicode(RawUTF8(VAny));
    {$endif}  // +
    {$ifdef HASVARUSTRING}
    varUString:  result := UnicodeString(VAny);
    {$endif}
  else Result := SynUnicode(fValue);
  end;
end;

If someone needs or if you want to fixe the compatibility with FPC.
Do the samething for String.
Thanks

Last edited by Bsaidus (2020-12-19 18:03:23)

ab · 2020-12-20 16:42:20

About all this, I guess that you should have tried to set VariantStringAsWideString in the properties.

Bsaidus · 2020-12-20 19:36:42

I will do & see.

Yessss, it works without modification of the Get Procedure.

Last edited by Bsaidus (2020-12-20 21:15:22)

mORMot Open Source

#1 2020-12-17 13:33:11

mORMot TQuery and Unicode ( french accent characters)

#2 2020-12-17 15:56:02

Re: mORMot TQuery and Unicode ( french accent characters)

#3 2020-12-18 19:27:33

Re: mORMot TQuery and Unicode ( french accent characters)

#4 2020-12-19 09:17:34

Re: mORMot TQuery and Unicode ( french accent characters)

#5 2020-12-19 09:34:28

Re: mORMot TQuery and Unicode ( french accent characters)

#6 2020-12-19 17:00:11

Re: mORMot TQuery and Unicode ( french accent characters)

#7 2020-12-20 16:42:20

Re: mORMot TQuery and Unicode ( french accent characters)

#8 2020-12-20 19:36:42

Re: mORMot TQuery and Unicode ( french accent characters)

Board footer