Unicode in Fields and SQLite

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller

Post Reply
Tukcedo
Posts: 65
Joined: Fri Jun 27, 2014 9:43 pm

Unicode in Fields and SQLite

Post by Tukcedo » Fri Jul 17, 2015 10:18 pm

Livecoders,

I'm at a loss with LCC 7.0.6 and Unicode under Android. I'm using a SQLite database which has placenames etc. with unicode characters, like e.g. in Portugal or France. I didn't actually define anything specific when creating the database, but it appears as if it's UTF-8.

The application works perfectly if I totally ignore the "funny" characters, apart of course from the display of those particular "special character" names. But I can select other data based on those names from the same database etc.

So the next step would be to improve the display of diacretic characters. However, despite the claim that 7.0.x would be unicode-transparent, I just can't get it to work. I tried putting text through uniencode/unidecode (deprecated) and textEncode/textDecode, stuck it in the unicodeText of a ScrolledList and all combinations, but the selectedText from the ScrolledList can never be converted back to what it was when it came from the database it seems.

The "best" results came from this lesson: http://lessons.runrev.com/m/4071/l/6930 ... ith-fields but I still can't select data from the database based on the selectedText no matter what I try.

Anyone any ideas? Thx!

Mich.
Michel J.L. van der Kleij
Coding to help stray animals in the Philippines
Albert Foundation - http://albert.tukcedo.nl
Aklan Animal Rescue & Rehabilitation Center - http://aarrc.tukcedo.nl

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: Unicode in Fields and SQLite

Post by phaworth » Sat Jul 18, 2015 8:34 pm

Use textEncode on any data in an INSERT/UPDATE statement, for example:

revExecuteSQL gDBID,”UPDATE Customers SET Custname=” & makeString(textEncode(field “Name”,”UTF8”)) -- makestring is just a handler that puts single quotes around a string

When you SELECT date, use textDecode, for example:

put textDecode(revDataFromQuery(,,gDBID,tSelect),”UTF8”) into tData

OR

put revQueryDatabase(gDBID,tSelect) into tCursor
put textDecode(revDatabaseColumnNamed(tCursor,”Name”),”UTF8”) into tName

If you have already inserted data into the database without encoding it as above, you can probably fix it by SELECTing it without decoding it, then UPDATE it with encoding, but I'm not sure if that will work.

Unicode only "just works" in LC7 within the Livecode environment and it does that very well but as soon as you write data out to any sort of file or read it back in from any sort of file, you have to do your own encoding/decoding.

Tukcedo
Posts: 65
Joined: Fri Jun 27, 2014 9:43 pm

Re: Unicode in Fields and SQLite

Post by Tukcedo » Sat Jul 18, 2015 8:59 pm

Great suggestion, I'll give that a whirl and report back.
Michel J.L. van der Kleij
Coding to help stray animals in the Philippines
Albert Foundation - http://albert.tukcedo.nl
Aklan Animal Rescue & Rehabilitation Center - http://aarrc.tukcedo.nl

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: Unicode in Fields and SQLite

Post by MaxV » Mon Jul 20, 2015 3:09 pm

I suggest always to use urlencode() / urldecode() fuctions with not ASCII chars.
This way all in converted in ASCII char inside the SQLite databse and you will never have troubles.
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: Unicode in Fields and SQLite

Post by phaworth » Mon Jul 20, 2015 6:01 pm

I believe he said he was using LC 7 so textEncode and textDecode are the recommended ways since the urlxxx equivalents have been deprecated. But you're right, no matter which ones you use, you have to use them for any db access.

Tukcedo
Posts: 65
Joined: Fri Jun 27, 2014 9:43 pm

Re: Unicode in Fields and SQLite

Post by Tukcedo » Tue Jul 21, 2015 9:17 pm

Indeed, this is 7.0.6 so I use text[En|De]code. I can report that it works!! So when I do a select from the database I first textEncode the name (or other diacretic-sensitive data) and pass the result to the query, then the result is textDecode'd and put in fields etc. Works like a charm. Thx for the tip phaworth!

Mich.
Michel J.L. van der Kleij
Coding to help stray animals in the Philippines
Albert Foundation - http://albert.tukcedo.nl
Aklan Animal Rescue & Rehabilitation Center - http://aarrc.tukcedo.nl

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: Unicode in Fields and SQLite

Post by phaworth » Tue Jul 21, 2015 10:16 pm

You're welcome!
Pete

rinzwind
Posts: 135
Joined: Tue May 01, 2012 10:44 am

Re: Unicode in Fields and SQLite

Post by rinzwind » Fri Aug 21, 2015 9:44 am

Why is textencode/decode necessary in LC7 if it states that unicode is 'transparant'

It makes working with sql and xml harder while I don't understand why I even need to do that. UTF-8 encoding should be the default. I had the same problem handling XML and sqlite data sources with completely 'normal' UTF-8 data in it. Why LC doesn't see this and handle it accordingly? It seems to be stuck to ASCII or something with these libraries?

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: Unicode in Fields and SQLite

Post by MaxV » Fri Aug 21, 2015 12:31 pm

I prefer use urlencode and urldecode.
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

rinzwind
Posts: 135
Joined: Tue May 01, 2012 10:44 am

Re: Unicode in Fields and SQLite

Post by rinzwind » Wed Aug 31, 2016 10:20 pm

Encoding should be a propery you set o a connection. revDataFromQuery outputs bad results too (and unfixable with textfecode) when there is non ascii text...

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: Unicode in Fields and SQLite

Post by MaxV » Thu Sep 01, 2016 8:46 am

These problems are related to the database internal encodings, so I created these functions those should resolve any issues about encodings:

StringtoNums transforms any text in a sequence of numbers
########CODE#######
function stringtonums tString
repeat for each char tChar in tString
put CodepointToNum(tChar) & comma after temp
end repeat
return temp
end stringtonums
#####END OF CODE#####

NumsToString reconverts the number sequence to the original string:
########CODE#######
function numsToString tString
repeat for each item tChar in tString
put numToCodepoint(tChar) after temp
end repeat
return temp
end numsTostring
#####END OF CODE#####

I attached also an example.
Attachments
Stringconverter.livecode.zip
Example
(1.23 KiB) Downloaded 226 times
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: Unicode in Fields and SQLite

Post by phaworth » Thu Sep 01, 2016 3:45 pm

If you are using lc7 or later, all you need to do is textencode your data on the way in to the database and textdecode when selecting it.

LC does "just work"with unicode within it's own environment but when you write out to or read in from an external source, the encoding/decoding is necessary. It is not possible to automatically identify the emcoding of a string of data so you must supply the encoding type to rextencode/decode.

You can specify the encoding of a database but sql does not do any automatic encoding/decoding, you have to do that in your code. Neither are there any checks that data put into the database is in the correct encoding so it is quite possible that data in the database is not encoded in the way specified in the database settings.

In short, it's the programmer's responsibility to take care of all this. LC makes it very simple - text encode any data for INSERT or UPDATE statements, textdecode any data returned from SELECT statements.

rinzwind
Posts: 135
Joined: Tue May 01, 2012 10:44 am

Re: Unicode in Fields and SQLite

Post by rinzwind » Thu Sep 01, 2016 5:50 pm

Would be welcome if the db library had a property that takes care of it for all text in the app. They have it for file handling... Like I said... even the buildin revDataFromQuery messes up and one cant fix that one with textdecode cause its already in ascii format.

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”