case sensitivity of characters outside of ASCII range... bug ?

Hey,

I was trying to get my app to do a case insensitive search of records containing ‘’ in the “name” field, but I stumbled upon what looks like to be a bug in sqlite…

is this true ? https://groups.google.com/forum/#!topic/android-developers/S1sOvaAthqA

Any ideas to overcome this situation ?

thanks!

As built for use in the sqlite plugin its not Unicode savvy so case insensitive searches outside the ASCII range do not work
see http://sqlite.org/datatype3.html#collation

I think Thomas Tempelmann posted some extensions for sqlite that let you make it use unicode which would alleviate this
see http://blog.tempel.org/2015/09/xojosqliteextensions.html

I’m on windows pc… What should I look for ? not sure I understand it clearly…

The was Xojo builds the sqlite plugin it is not not unicode savvy - so case sensitive comparisons outside the Ascii range wont work

I think Thomas Tempelmann posted some extensions for sqlite that let you make it use unicode which would alleviate this
see his blog at http://blog.tempel.org/2015/09/xojosqliteextensions.html
If you incorporate his code I think you should be able to do case sensitive comparisons as you expect

It states… “…Mind you, only for OS X, though, as I do not have the time to make and test versions for Windows and Linux”.

Can you not read the data in to a Xojo array and sort it yourself?

Unless the SQLite plugin is compiled with unicode support there’s not a lot you can do

  1. take thomas code & see if you can get it working on Windows
  2. beg thomas to try & make it work on Windows
  3. hope someone else might

or “do something else” like Peter suggested

And sign on to this feature request
<https://xojo.com/issue/26859>

The alternative is to do as thomas did - but perhaps officially supported
https://forum.xojo.com/17105-has-anyone-built-the-sqlite-icu-extension

Hey Peter… It’s a search (select where)… So if. I’d want to do it within xojo I guess I should first load all the database table records and then apply the filter… I don’t think it’s a good idea… I mean… can it be done? What if there are thousands of records?

There just isn’t a good way as things sit with sqlite the way it is built

You need unicode in sqlite but its not there

The “best” you could do, and something I have done, is flatten everything into ascii and store that data in another column
Then select from the db using that additional column to narrow things down a bit and then search in Xojo in the subset as it IS unicode aware

And, as you found, that is how you find sqlite built a lot of the time

Thousands would not scare me, tens of thousands maybe. Xojo is pretty efficient. Just don’t load them in a listbox. :wink:

I was thinking about forcing the ‘name’ input field to be uppercase… but that means messing up the code too much and it wouldn’t look nice… I was hoping there would be a simpler / better solution.

How you STORE things is up to you
Thats why I suggested a second column (or maybe many alternates) that are some “normalized” version of the data you want to search
So when you insert a name you insert both a mixed case AND uppercase only version
Then for a “case insensitive name search” you use the uppercased column

No one needs to be the wiser that you’ve had to work around it like this except you

Not sure how many records this table might have. It could eventually grow more than expected and then the app would get too slow retrieving all this records…

I think. It’s either forcing lower / uppercase… Or as Norman suggest, creating another column for this purpose.

Have a look in this thread

https://forum.xojo.com/25900-adding-icu-unicode-support-to-xojo-s-sqlite-database/0

So the MBS SQL plugin could help you out (and it might be a LOT faster too)

http://www.monkeybreadsoftware.de/xojo/plugin-sql.shtml