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.
Hope you can help me.
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.
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
ciSurname // case insensitive surname
Dim dbr As New DatabaseRecord
dbr.Column("Surname") = Trim(Surname)
dbr.Column("ciSurname") = UPPERCASE(Trim(Surname))
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
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?
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