How to filter when using the TsWorksheetDataset and the field name is
not Eng
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
bills · 2023-06-10 09:05

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TMainForm.acFilterByCountryExecute(Sender: TObject);begin 
Dataset.Filtered := false;  if (cmbFilter.Text = '') or
(cmbFilter.Text = 'all countries') then    cmbFilter.ItemIndex := 0 
else  begin    //Dataset.Filter := 'Country = "' + cmbFilter.Text +
'"';    Dataset.Filter := ' البلد = "' + cmbFilter.Text + '"';   
Dataset.Filtered := true;  end;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 · 2023-06-10 10:01

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 · 2023-06-11 01:15

Quote from: wp on June 10, 2023, 12:01:15 pmCan 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Dataset.Filter := ' البلد = "' + cmbFilter.Text + '"';  
And one change to the original excel file Temperatures.xlsx：the first
field name 'country' to 'البلد '.

Thank you.

----------------------------------------------------------------------
wp · 2023-06-11 12:20

You could avoid the crash by quoting the fieldname in the filter
expression:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TMainForm.ToolButton9Click(Sender: TObject);var  L: TStrings;begin  L
:= TStringList.Create;  Dataset.GetfieldNames(L); 
ShowMessage(L.Text);  L.Free;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#msg
134481

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TMainForm.DatasetAfterOpen(ADataSet: TDataSet);begin 
Dataset.Fields[0].DisplayLabel := 'البلد'; 
Dataset.Fields[1].DisplayLabel := ...  [...]end;

----------------------------------------------------------------------
bills · 2023-06-14 05:27

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 · 2023-06-14 05:44

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 · 2023-06-17 07:06

Quote from: af0815 on June 14, 2023, 07:44:18 amA 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.