SQLite and Umlaut...again

Databases
1

Hello everyone,

i trying to find a solution since 1,5 days.

I have a database with 4 Tables (Givennames, Surnames, Names and Individuals).

Every Name-Record in Names has a SurnameID i[/i] and a Givennames Column i[/i]. You will see, why Givennames Type is String. I want to avoid duplicate entries in the tables.

If i add a Name-Record without a german Umlaut, my algorithms works fine. Also with Umlaute, but then, they add every time a new Record…a copy. I don’t want that. Why?

Here you can find my Source with the SQLite Databasefile.

Test Project

Hope you can help me.

Best regards

2

The problem isn’t encodings so most of the code you have for converting & defining encodings should be stripped out.
Looking it over you’re doing the wrong thing at the right time.
We can go over that later.

http://sqlite.org/lang_corefunc.html
Upper is only accurate for ASCII - which the isn’t
I’d suggest inserting 2 copies
One created from the original data as entered (or one that it titlecased or something like that for display)
One which is the result of Xojo’s UPPER function (which does a slightly better job) so you can do case insensitive comparison

Create the table with two columns
Surname
ciSurname // case insensitive surname

    Dim dbr As New DatabaseRecord
    
    dbr.Column("Surname") = Trim(Surname)
    dbr.Column("ciSurname") = UPPERCASE(Trim(Surname))

    DB.InsertRecord(tbl_SURN, dbr)
    
    ResultID = DB.LastRowID

Then you can query using

Dim SQL As String = "SELECT * FROM " + tbl_SURN + " WHERE siSurname='" + Trim(Surname.Uppercase) Dim rs As RecordSet = DB.SQLSelect(SQL) Dim SurnameID As Integer = rs.IdxField(1).IntegerValue

and you should get better results

3

Dear Norman Palardy,

thank you very much for the fast and working answer. It works…

The only thing is…do i ever need the insert an uppercased Copy of an String, if i know, that String could have some Umlaute?

Greetings

4

if the string can include non-ascii characters you should
if you always do and search that special column then you never have to worry - it will just work
Its a pain but thats the reality of the current state of things in sqlite

this isn’t even a bug in the plugin - its right in the sqlite engine