SQLite und Umlaut-Sortierung

  1. last week

    Tim W

    Nov 3 Pre-Release Testers Solingen, Germany
    Edited last week

    Ich habe in einer SQLite Datenbank ein Problem mit der Sortierung von Umlauten, z. B. wird das Wort "Äpfel" nach dem letzten "Z"-Eintrag eingeordnet, trotz order by...

    Desweiteren kann ich nur casesensitive suchen, wenn ich in meinem sql-Statement z. B. "WHERE products_name LIKE '%äpfel%' verwende wird der Eintrag "Äpfel" nicht gefunden. Erst wenn ich nach "Äpfel" mit großem "Ä" suche, gibt es ein Ergebnis.

    Kann mir jemand sagen, was ich ergänzen muss? Ich habe es schon mit cSQLite.SQLExecute("SET COLLATE NOCASE;") versucht, aber ohne Erfolg.

    Danke schon mal für eure Antworten....

    Das habe ich aus dem SQLite-FAQ:

    Case-insensitive matching of Unicode characters does not work.

    The default configuration of SQLite only supports case-insensitive comparisons of ASCII characters. The reason for this is that doing full Unicode case-insensitive comparisons and case conversions requires tables and logic that would nearly double the size of the SQLite library. The SQLite developers reason that any application that needs full Unicode case support probably already has the necessary tables and functions and so SQLite should not take up space to duplicate this ability.
    Instead of providing full Unicode case support by default, SQLite provides the ability to link against external Unicode comparison and conversion routines. The application can overload the built-in NOCASE collating sequence (using sqlite3_create_collation()) and the built-in like(), upper(), and lower() functions (using sqlite3_create_function()). The SQLite source code includes an "ICU" extension that does these overloads. Or, developers can write their own overloads based on their own Unicode-aware comparison routines already contained within their project.

    https://www.sqlite.org/faq.html#q18

    Von der Webseite von Thomas Tempelmann kann man eine ICU-Library für SQLite herunterladen und in das Projekt laden. Das ist nicht wirklich schwierig. Ich brauchte das für mein Programm, weil Valentina kein FTS unterstützt. Das ist der Code für die MBS-Variante von SQLite:

    dim con as SQLConnectionMBS = SQLiteIndexDBMBS.Connection
    dim SQLiteAPI as SQLite3MBS = SQLiteIndexDBMBS.NativeAPI
    SQLiteAPI.EnableLoadExtension(con, true)
    dim errorMessage as string
    dim file as FolderItem = TPSF.Frameworks.Child("libicu.dylib") // an extension as FolderItem or path
    dim r as integer = SQLiteAPI.LoadExtension(con, file, errorMessage)
    if errorMessage <> "" then
      ConnectedToSQLite = False
    end if
  2. Beatrix W

    Nov 3 Pre-Release Testers, Third Party Store Answer Europe (Germany)

    Das habe ich aus dem SQLite-FAQ:

    Case-insensitive matching of Unicode characters does not work.

    The default configuration of SQLite only supports case-insensitive comparisons of ASCII characters. The reason for this is that doing full Unicode case-insensitive comparisons and case conversions requires tables and logic that would nearly double the size of the SQLite library. The SQLite developers reason that any application that needs full Unicode case support probably already has the necessary tables and functions and so SQLite should not take up space to duplicate this ability.
    Instead of providing full Unicode case support by default, SQLite provides the ability to link against external Unicode comparison and conversion routines. The application can overload the built-in NOCASE collating sequence (using sqlite3_create_collation()) and the built-in like(), upper(), and lower() functions (using sqlite3_create_function()). The SQLite source code includes an "ICU" extension that does these overloads. Or, developers can write their own overloads based on their own Unicode-aware comparison routines already contained within their project.

    https://www.sqlite.org/faq.html#q18

    Von der Webseite von Thomas Tempelmann kann man eine ICU-Library für SQLite herunterladen und in das Projekt laden. Das ist nicht wirklich schwierig. Ich brauchte das für mein Programm, weil Valentina kein FTS unterstützt. Das ist der Code für die MBS-Variante von SQLite:

    dim con as SQLConnectionMBS = SQLiteIndexDBMBS.Connection
    dim SQLiteAPI as SQLite3MBS = SQLiteIndexDBMBS.NativeAPI
    SQLiteAPI.EnableLoadExtension(con, true)
    dim errorMessage as string
    dim file as FolderItem = TPSF.Frameworks.Child("libicu.dylib") // an extension as FolderItem or path
    dim r as integer = SQLiteAPI.LoadExtension(con, file, errorMessage)
    if errorMessage <> "" then
      ConnectedToSQLite = False
    end if

or Sign Up to reply!