UPPER and LOWER on NON ASCII characters (ICU)

[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

[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.

[Schlueter]

Yes it works, great

Thanks
Rolf

[AlexP]

If you have any further questions, feel free to contact us.

[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

[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.

[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;

[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.

[Schlueter]

With new version 2.4.13 it works.
Thanks

[AlexP]

Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.