How can I filter a string field in a dataset with a like clause and an umlaut?

Question

Albeit there is some documentation about dataset filtering, the syntax details are only outlined. In my application I want to filter person names with a dataset filter. Normally this works really fast, but I've stumbled over a minor problem filtering for example a TClientDataset. How can I add a like filter for an umlaut? The expression

[X] LIKE 'Ö%'

(for a given field X) does not work (in contrast to the expression [X] LIKE 'A%'). Is this just a bug or do I need to set a charset / encoding somewhere?

Minimal example:

procedure TForm1.FormCreate(Sender: TObject);
var
  LField: TFieldDef;
  LCDs: TClientDataSet;
const
  SAMPLE_CHAR: string = 'Ö';
begin
  LCds := TClientDataSet.Create(Self);
  LField := LCds.FieldDefs.AddFieldDef();
  LField.DataType := ftString;
  LField.Size := 10;
  LField.Name := 'X';
  LCDs.CreateDataSet;
  LCDs.Append;
  LCDs.FieldByName('X').AsString := SAMPLE_CHAR;
  LCDs.Post;

  ShowMessage(LCds.FieldByName('X').AsString);
  LCds.Filter := '[X] LIKE ' + QuotedStr(SAMPLE_CHAR + '%');
  LCds.Filtered := true;
  ShowMessage(LCds.FieldByName('X').AsString);
end;

The first message box shows Ö, whereas the second message box is empty. If you change SAMPLE_CHAR from Ö to A, both message boxes show A.

Answer 1

Use ftWideString data type to create a TWideStringField field instead of ftString, which internally creates a TStringField field. TStringField is for ANSI strings whilst TWideStringField for Unicode ones. Do that, otherwise you lose data.

To access TWideStringField value use AsWideString property. I've made a quick test in D 2009, and when I tried to filter the dataset I got this:

First chance exception at $7594845D. Exception class EAccessViolation with message 'Access violation at address 4DB1E8D1 in module 'midas.dll'. Read of address 00FC0298'.

Tested code:

procedure TForm1.FormCreate(Sender: TObject);
var
  S: string;
  FieldDef: TFieldDef;
  MemTable: TClientDataSet;
begin
  S := 'Ŧĥε ｑùíçķ ƀřǭŵņ fôｘ ǰűmpεď ōvêŗ ţħě łáƶÿ ďơǥ';

  MemTable := TClientDataSet.Create(nil);
  try
    FieldDef := MemTable.FieldDefs.AddFieldDef;
    FieldDef.DataType := ftWideString;
    FieldDef.Size := 255;
    FieldDef.Name := 'MyField';

    MemTable.CreateDataSet;
    MemTable.Append;
    MemTable.FieldByName('MyField').AsWideString := S;
    MemTable.Post;

    ShowMessage(MemTable.FieldByName('MyField').AsWideString); { ← data lost }
    MemTable.Filter := '[MyField] LIKE ' + QuotedStr('%' + 'ǰűmpεď' + '%');
    MemTable.Filtered := True; { ← access violation }
    ShowMessage(MemTable.FieldByName('MyField').AsWideString);
  finally
    MemTable.Free;
  end;
end;

I hope it's not related to your Delphi version, but still, I would prefer using FireDAC if you can. There you would do the same for Unicode strings (your code would change by replacing TClientDataSet by TFDMemTable and adding FireDAC units).