MBS; SQLite; Win10 64; lower upper Umlauts;

Add-Ons
1

In 07.2023 (07/dd/2023) I did a select like
select lower('ÄÖÜ')
to compare with ‘äöü’ which seemed to work.
In 01.2024 I did the same and it did not work.
Reason is that Umlauts where not converted at all by lower/upper.
Maybe in between version of MBS or Xojo where changed and I know I did some updates to the newest versions.
Anyway, SQLite documentation says that lower/upper do only work for ASCII - the “Ö” is C396 in unicode and is still C396 after lower(‘Ö’).

Any idea how to solve this and make “Ö” and “ö” equal in a comparison?
I tried with like but that didn’t work either.

Any help appreciated!
Thanks in advance!

2

Did you check ICU properties?

3

Thanks a lot for the info!
Ok, so ICU I found as solution but to get binaries is somewhat difficult.
I will try to find those.

I also have read that the upper/lower case handling for above ASCII UTF-8 is not so easy.

4

I see, that I need to update my plugin for Xojo 2024r1 there for Windows.
We’ll have a new SQL plugin soon to help here.

5

Please try a newer plugin here:

6

I tried the newer plugin but got not a ‘correct’ comparison for lower(‘Ö’).
ICUenabled = true; ICUloaded = false; ICUused = false;
Ok, ICUenabled defaults to true but can not load an ICU plugin.
Which one should I use for German umlauts? Any binary/compiled version for download available?

Thanks a lot!

7

Are you sure you have the new plugin active?

Then please run a query like select lower('ÄÖÜ') This should load the unicode DLLs on the first time it’s used. Does it give you äöü?

Or call the LoadICU method directly to try to load them.
Once loaded, SQLite should use them.

8

Yes, I have the new plugin from 21.02.2024 (58778KB).

This I try:

if InternalSQLiteLibraryMBS.LoadICU() then
  System.DebugLog("ICU not loaded")
  
else
  System.DebugLog("ICU loaded")
end if

System.DebugLog(str(InternalSQLiteLibraryMBS.ICUEnabled) + ";" + str(InternalSQLiteLibraryMBS.ICULoaded) + ";" + str(InternalSQLiteLibraryMBS.ICUUsed))

Which gives:

ICU loaded
True;False;False

var Cmd             as new SQLCommandMBS()
var SQL         as String

SQL = "select lower('ÄÖÜ')"

Cmd.Connection = me.Conn

Cmd.setCommandText(SQL)

try 
  Cmd.Execute()
  
catch re as RuntimeException
  System.DebugLog("SQLite3.selectSQL; Command.Execute; " + re.Message + ".")
end try


while Cmd.FetchNext()
  System.DebugLog(Cmd.Field(1).asStringValue)
wend

gives: ÄÖÜ

so, it does not work or is not loaded

And is it internal? I have not to use a seperate ICU.dll or something like that?
Ok, found that SQlite must be compiled with that…

9

Okay, on macOS this works:

Call InternalSQLiteLibraryMBS.use

If InternalSQLiteLibraryMBS.LoadICU() Then
  System.DebugLog("ICU not loaded")
  
Else
  System.DebugLog("ICU loaded")
End If

System.DebugLog(Str(InternalSQLiteLibraryMBS.ICUEnabled) + ";" + Str(InternalSQLiteLibraryMBS.ICULoaded) + ";" + Str(InternalSQLiteLibraryMBS.ICUUsed))

Var Cmd             As New SQLCommandMBS()
Var SQL         As String

SQL = "select lower('ÄÖÜ')"

Dim con As New SQLConnectionMBS

con.Connect(":memory:", "", "", con.kSQLiteClient)

Cmd.Connection = con

Cmd.setCommandText(SQL)

Cmd.Execute()


While Cmd.FetchNext()
  System.DebugLog(Cmd.Field(1).asStringValue)
Wend

Without the InternalSQLiteLibraryMBS.use call, you use the macOS SQLite library, not the one included in our plugin!

On Windows a console app doesn’t work. I’ve made some changes, so we have a new plugin for you:

Please copy the

icudt73.dll
icuin73.dll
icuuc73.dll

files from Xojo to the app folder if they are missing.

10

I use InternalSQLiteLibraryMBS.use() but get the same result even with new plugin.

ICU loaded
True;False;False
ÄÖÜ

All files are in Debug-Folder already.

11

Maybe you call me and we check via screen sharing?

12

I checked again on my windows server having the same configuration and I used given source code.
Same result, lower(‘ÄÖÜ’) does not work.

ICU loaded
True;False;False
ÄÖÜ

I checked ICU*.dll which are from 05.12.2023.

Could a “select load_extension(…)” help?

I also built the app and icu* files are in build folder.
Can You chek it on Windows 10/11?

Any combination of isEnabled = false and the LoadICU did not help.

Can anybody else confirm?

13

Added SQLiteFunctionMBS class to add custom SQLite functions for SQLite database connections.

Ok, those functions can not be used in an index, but it is a solution to the problem of lower/upper for unicode chars!
Thanks a lot!