French char with SQLite database

[davidchang]

Hi,
I've read the forums but still can't seem to make this work. I have a CSV file with french char and want to import that to a SQLite db then have an in app query to display the french char in a field. The first problem is when importing to the SQLite db the accents change to other weird char. I have managed to find a way to correct that but I don't know if my method is right because of the second problem. The second problem is when performing an in app SQL query I can not seem to get the accents to display correct, regardless of whether they appeared correctly in the db or not. I can't seem to find the right encoding to make this work.

Thanks

[LCNeil]

Hi David,

You will need to textEncode your data when writing to the DB and then textDecode again when reading data back to your app. textEncoding converts your text data into binary.

A more visual way to see this encoding working is by textEncoding some data, base64Encoding it and then decoding it on the following site-

http://www.percederberg.net/tools/text_converter.html

You should notice that any encoded text, decodes as expected.


Kind Regards,

Neil Roger
--
LiveCode Support Team ~ http://www.livecode.com
-

[Dixie]

You have probably been bitten by bug 15336...

[davidchang]

I've made some progress... So as mentioned, I seem to be able to store the french char correctly in the SQLite db. I achieved this by opening the csv in notepad and saving it was an encoded ISO8859-1 .txt file and then I import with SQLite. At this point the french char are correct in the db. Now I apply this code to perform the query in livecode...

Code: Select all

put "SELECT column2  from French_Verb_db WHERE rowid = 5" into tSQLQuery
put revDataFromQuery (tab, return, sDatabaseID, tSQLQuery) into tSQL_Output   
put uniEncode(tSQL_Output, "UTF8") into field "Field1"  

the output will contain the french char, however, there now appears to be a spacing issue as if each char is separated by a space (ie. "P r é s e n t"). Note that I am using uniEncode (UTF8) in livecode because I tried ISO8859-1 without success. This is the closest I've come but something is still a bit off. Any ideas?

Thanks

[MaxV]

SQLite doesn't support unicode, so I suggest you to use urlencode() and urldecode() function.

[Klaus]

Hi all,

recently there was this posting on the use-livecode mailing list!
No idea if and how that will work, but sound interesting

For those of you wanting to use Unicode with an SQLite database, I came
across this on the SQLite list:

https://bitbucket.org/alekseyt/nunicode ... -extension

Livecode 7 makes it very easy to insert and retrieve Unicode data to/from
an SQlite database but there are some functions within SQlite that don't
work correctly with Unicode, specifically upper(), lower(), COLLATE and X
LIKE Y.

The above sqlite extension makes those function work. It's a library that
is loadable at runtime.

To load a runtime extension from Livecode:

- use the "extensions" keyword of the revOpendatabase sqliteoptions
parameter

- use the SQLite load_extension() function to load the library.

Best

Klaus

[phaworth]

SQLite does support unicode and you can set the encoding when the database is created. However, it doesn't do anything automatic to encode data when you insert data or decode it when you select it, that's your application's responsibility.

If the OP is using LC 7 or later, it's really easy to encode the data when inserting/updating and decode it when selecting it. Check out my webinar presentation on the Free Stuff page of www.lcsql.com for details. Using those techniques will solve the problem.

If using LC6 or earlier, check out Devin Assay's articles on how to do it. More complicated but still doable.

Klaus' reference is to a post I made recently. SQLite's support of unicode isn't 100% complete since things like upper() and lower() don;t work correctly and the external library I mentioned corrects that.

Pete
www.lcsql.com