How to filter when using the TsWorksheetDataset and the field name is not Eng

[bills]

I test the filter function in the demo: sort_filter_search_bookmarks, the function works fine.
But when I change the first field name 'country' to 'البلد' in file:Temperatures.xlsx,
and changed the pas source code to:

Code: Pascal  [Select][+][-]

1. procedure TMainForm.acFilterByCountryExecute(Sender: TObject);
2. begin
3.   Dataset.Filtered := false;
4.   if (cmbFilter.Text = '') or (cmbFilter.Text = 'all countries') then
5.     cmbFilter.ItemIndex := 0
6.   else
7.   begin
8.     //Dataset.Filter := 'Country = "' + cmbFilter.Text + '"';
9.     Dataset.Filter := ' البلد = "' + cmbFilter.Text + '"';
10.     Dataset.Filtered := true;
11.   end;
12. end;

Then I compile and run the filter function,get error message:

lndex based on unknown field "??".

How can I use un-English field name to use the filter function of TsWorksheetDataset ?
Thanks!

[wp]

Can you prepare for me a small spreadsheet file (a few columns and rows only) which shows the issue in the "sort_filter_search_bookmarks" demo project? If the forum software does not allow to upload it you should pack it into a zip or 7z file.

[bills]

Can you prepare for me a small spreadsheet file (a few columns and rows only) which shows the issue in the "sort_filter_search_bookmarks" demo project? If the forum software does not allow to upload it you should pack it into a zip or 7z file.

I made one change to the original demo code：

Code: Pascal  [Select][+][-]

1. Dataset.Filter := ' البلد = "' + cmbFilter.Text + '"';  

And one change to the original excel file Temperatures.xlsx：the first field name 'country' to 'البلد '.

Thank you.

[wp]

You could avoid the crash by quoting the fieldname in the filter expression:

Code: Pascal  [Select][+][-]

1. Dataset.Filter := ' "البلد" = "' + cmbFilter.Text + '"';  

but now the result of the filtering action is an empty dataset. Moreover, listing the fields of the dataset (after replacing "Country" by "بلد") shows an empty fieldname:

Code: Pascal  [Select][+][-]

1. procedure TMainForm.ToolButton9Click(Sender: TObject);
2. var
3.   L: TStrings;
4. begin
5.   L := TStringList.Create;
6.   Dataset.GetfieldNames(L);
7.   ShowMessage(L.Text);
8.   L.Free;
9. end;

So, something is very wrong...

I am afraid this issue is deeply woven into the LCL database infrastructure. The debugger led me to an expressionparser which inherits from a dbase parser, and this really smells a lot like "non-unicode". There is also an older post stating that field names must be valid Pascal identifiers, and for this unicode is not allowed: https://forum.lazarus.freepascal.org/index.php/topic,22716.msg134481.html#msg134481

Nevertheless, there is an easy solution: Keep the fieldnames unchanged, but introduce values for the DisplayLabel property of the fields. This way, the dataset works internally always with the ASCII fieldnames but your users see the unicode names in their language.

Code: Pascal  [Select][+][-]

1. procedure TMainForm.DatasetAfterOpen(ADataSet: TDataSet);
2. begin
3.   Dataset.Fields[0].DisplayLabel := 'البلد';
4.   Dataset.Fields[1].DisplayLabel := ...
5.   [...]
6. end;  

[bills]

Thank you，wp.
I found that the locate function is not affected by non English fields.
You gave me some ideas, thank you again.

[af0815]

A good design approach for DB Systems is, use only ASCII 7 for SQL, Fieldnames, Parameternames. If you use local chars, you newer know, where a error comes and your system must not be the system of the target. So you some times unwanted errors and did not know, if it is LCL, DB-driver or a mistake in code.

[bills]

A good design approach for DB Systems is, use only ASCII 7 for SQL, Fieldnames, Parameternames. If you use local chars, you newer know, where a error comes and your system must not be the system of the target. So you some times unwanted errors and did not know, if it is LCL, DB-driver or a mistake in code.

But it is really convenient to use spreadsheets as database files directly, because there is no need to design fields in a real database.
One consequence of pursuing extreme convenience is a reluctance to control the character set used for field names.