Page 1 of 1
UPPER and LOWER on NON ASCII characters (ICU)
Posted: Wed 08 Oct 2014 08:21
by Schlueter
Hi,
on SQLite the UPPER and LOWER functions work only on the 26 ASCII-characters A..Z and a..z, but not on chararcters like äÄöÖüÜ. In the SQLite documentation I've read about the ICU extension, so that it would work.
Does the component support ICU and how can I link it?
Or do you suggest another idea?
Best regards
Rolf
Re: UPPER and LOWER on NON ASCII characters (ICU)
Posted: Wed 08 Oct 2014 10:09
by AlexP
Hello,
To use ICU in SQLite, the library must be built with the SQLITE_ENABLE_ICU key (disabled by default). Or you can implement your own user function that will perform this behavior. You can use the TLiteUserFunction component to implement the user function. For this, you should create the needed parameter in the component, specify the function name (for example MYUPPER), and implement the TLiteUserFunction.onExecute event. For example:
Code: Select all
procedure TForm14.LiteUserFunction1Execute(Sender: TObject;var ResultValue: Variant);
begin
ResultValue := AnsiUpperCase(TLiteUserFunction(Sender).Params.ParamByName('Value').AsString);
end;
And use your function name SELECT MYUPPER('a'); instead of UPPER in the query.
Re: UPPER and LOWER on NON ASCII characters (ICU)
Posted: Wed 08 Oct 2014 12:54
by Schlueter
Yes it works, great
Thanks
Rolf
Re: UPPER and LOWER on NON ASCII characters (ICU)
Posted: Thu 09 Oct 2014 09:14
by AlexP
If you have any further questions, feel free to contact us.
Re: UPPER and LOWER on NON ASCII characters (ICU)
Posted: Mon 12 Jan 2015 09:52
by Schlueter
Hi,
by updating from version 2.4.11 to 2.4.12 there must be a bug in your TLiteUserFunction handling:
I get an error exception (reading from address 00000000), if I try to select data with a TLiteUserFunction in WHERE clause. The exception will also raise in your TLiteDacDemo, if you try to run the TLiteUserFunction.
I've seen that you have changed its 'OnExecute' event by an additional param, so I've adjusted my event but the exception will still raise.
Can you fix it?
Best regards
Rolf
Re: UPPER and LOWER on NON ASCII characters (ICU)
Posted: Mon 12 Jan 2015 11:27
by AlexP
This event is already fixed in the LiteDAC demo. The fix will be included into the next version. If you add a parameter Params: TDAParams; to the declaration and implementation of the event in your code - there will be no error.
Re: UPPER and LOWER on NON ASCII characters (ICU)
Posted: Mon 12 Jan 2015 12:24
by Schlueter
Sorry, but I've added the additional param to the event in your demo and the error exception is still raising ...
Code: Select all
procedure TFunctionsFrame.fnABSExecute(Sender: TObject; Params: TDAParams;
var ResultValue: Variant);
begin
inherited;
ResultValue := Abs(Params[0].AsInteger);
end;
procedure TFunctionsFrame.fnPercentExecute(Sender: TObject; Params: TDAParams;
var ResultValue: Variant);
var
lComm, lSal: real;
begin
inherited;
lComm := Params[0].AsFloat;
lSal := Params[1].AsFloat;
if lSal = 0 then
lSal := 1;
ResultValue := lComm/lSal*100;
end;
procedure TFunctionsFrame.fnPercent_1Execute(Sender: TObject; Params: TDAParams;
var ResultValue: Variant);
begin
inherited;
ResultValue := VarToStrDef(Params[0].Value, '0') + ' %';
end;
Re: UPPER and LOWER on NON ASCII characters (ICU)
Posted: Mon 12 Jan 2015 12:55
by AlexP
You may have old DCU modules of the demo project remained. Try to completely rebuild the project:
1) Project PopUp Menu -> Clean
2) Delphi Main Menu -> Project - > Build All
And run the demo project once more.
Re: UPPER and LOWER on NON ASCII characters (ICU)
Posted: Sat 31 Jan 2015 06:34
by Schlueter
With new version 2.4.13 it works.
Thanks
Re: UPPER and LOWER on NON ASCII characters (ICU)
Posted: Tue 03 Feb 2015 07:42
by AlexP
Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.