Xojo SQLite and NOCASE Collate

I had what I thought was an epiphany but is now looking like a brain-fart. I’ve been doing work with a genealogy program called RootsMagic which is cross-platform and uses a SQLite database. There is a site with a lot of tools (https://sqlitetoolsforrootsmagic.wikispaces.com) that has many scripts to do useful things to clean up genealogy data in the RootsMagic database.

I was looking at this along with some other things possible with good ol’ fashioned SQL and thought “I could assemble those tools together into a cool Xojo program since it has SQLite built right in!”.

Alas, the SQLite database uses a COLLATE sequence named RMNOCASE to support full UTF-8 to make the program internationally comparable for sorting and searching. Any select statement against a table with text values returns a NIL recordset. This is happening even with a simple SELECT COUNT(*).

Searching the forum it looks like what I want to do is impossible with Xojo without sticking to one platform, or maybe buying Monkeybread although it’s unclear if that will work either.

Since all the code I’m seeing to support UTF seems to be C++ that may be the only practical way to go, but since many of the threads I see are a few years old I’m holding out hope Xojo may have some international chops by now. Anyone have good news for me?

there was an article by thomas tempelmann about it
http://blog.tempel.org/2015/09/xojosqliteextensions.html

he explains how to make your own sqlite plugin to have the encoding you need
I did not try this myself, althought I would need it, as do almost all non english users …

Yea, I read that, and from the looks of it it can be made portable, but isn’t necessarily portable. I’d was hoping I wasn’t having to do that, but maybe I’ll give it a try. SQLite is such a nice option with Xojo, but I keep hitting roadblocks trying to use it.

SQLite has a lot of power. Only so much of it is automagically builtin to Xojo. Some of it needs to be expanded by either plugins or code. Same with all the other databases too.