UPPER and LOWER on NON ASCII characters (ICU)

Discussion of open issues, suggestions and bugs regarding LiteDAC (SQLite Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Schlueter
Posts: 14
Joined: Thu 29 Aug 2013 06:57

UPPER and LOWER on NON ASCII characters (ICU)

Post by Schlueter » Wed 08 Oct 2014 08:21

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
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: UPPER and LOWER on NON ASCII characters (ICU)

Post by AlexP » Wed 08 Oct 2014 10:09

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
Posts: 14
Joined: Thu 29 Aug 2013 06:57

Re: UPPER and LOWER on NON ASCII characters (ICU)

Post by Schlueter » Wed 08 Oct 2014 12:54

Yes it works, great :)

Thanks
Rolf

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: UPPER and LOWER on NON ASCII characters (ICU)

Post by AlexP » Thu 09 Oct 2014 09:14

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

Schlueter
Posts: 14
Joined: Thu 29 Aug 2013 06:57

Re: UPPER and LOWER on NON ASCII characters (ICU)

Post by Schlueter » Mon 12 Jan 2015 09:52

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
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: UPPER and LOWER on NON ASCII characters (ICU)

Post by AlexP » Mon 12 Jan 2015 11:27

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
Posts: 14
Joined: Thu 29 Aug 2013 06:57

Re: UPPER and LOWER on NON ASCII characters (ICU)

Post by Schlueter » Mon 12 Jan 2015 12:24

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
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: UPPER and LOWER on NON ASCII characters (ICU)

Post by AlexP » Mon 12 Jan 2015 12:55

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
Posts: 14
Joined: Thu 29 Aug 2013 06:57

Re: UPPER and LOWER on NON ASCII characters (ICU)

Post by Schlueter » Sat 31 Jan 2015 06:34

With new version 2.4.13 it works.
Thanks

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: UPPER and LOWER on NON ASCII characters (ICU)

Post by AlexP » Tue 03 Feb 2015 07:42

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

Post Reply