SQLite / Encoding Problem

General
1

I have an SQLite database with foreign words. I am trying to run sql select statements entered into a textarea in XOJO. I can successfully run searches on numeric (integer) fields such as “select * from words where id=751”. Searches trying to find specific words such as “select * from words where word=‘oui’” fail.

I am able to successfully run such searches in another software: SQLite Manager. When I copy and paste searches from SQLite Manage into my XOJO textarea the searches work. However, if I edit even one character after pasting the sql into my XOJO textarea the search fails. I’m flummoxed as to why it works sometimes (when copy and pasted) but not others.

I suspect there may be a text encoding problem, but I haven’t been able to figure it out. The database was created by using XOJO to take the words from a web page and store them in SQLite. I believe that the web page was in UTF-8, although I am not sure. When I look at the ascii codes for some of the letters (vowels with diacritical marks), they are in the 200 range which corresponds to UTF-8 Latin Supplement as show at https://www.w3schools.com/charsets/ref_utf_latin1_supplement.asp). Any suggestions?

2

some answers here :
https://forum.xojo.com/36099-sqlitedatabase-accents-on-vowels
and here :

also https://xojo.com/issue/26859

3

Thanks for the links. I took a look at them, but I guess it is beyond my competence.

I still think it’s pretty strange that I got results when I copy/pasted a query into the textarea from SQLite Manager, but if I edited that query in the textarea, or tried to type in a query, it did not work.

How do french people use XOJO? Where do they store data that they need to search? MySQL, XOJO dictionaries???

4

most of the time, “collate nocase” at the end of the query works.
to be really working, you need to store an ascii version of your locale data and search on them