Unicode in Fields and SQLite
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller
Unicode in Fields and SQLite
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.
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
Coding to help stray animals in the Philippines
Albert Foundation - http://albert.tukcedo.nl
Aklan Animal Rescue & Rehabilitation Center - http://aarrc.tukcedo.nl
Re: Unicode in Fields and SQLite
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.
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.
Re: Unicode in Fields and SQLite
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
Coding to help stray animals in the Philippines
Albert Foundation - http://albert.tukcedo.nl
Aklan Animal Rescue & Rehabilitation Center - http://aarrc.tukcedo.nl
Re: Unicode in Fields and SQLite
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.
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
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
Re: Unicode in Fields and SQLite
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.
Re: Unicode in Fields and SQLite
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.
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
Coding to help stray animals in the Philippines
Albert Foundation - http://albert.tukcedo.nl
Aklan Animal Rescue & Rehabilitation Center - http://aarrc.tukcedo.nl
Re: Unicode in Fields and SQLite
You're welcome!
Pete
Pete
Re: Unicode in Fields and SQLite
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?
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?
Re: Unicode in Fields and SQLite
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
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
Re: Unicode in Fields and SQLite
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...
Re: Unicode in Fields and SQLite
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.
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 253 times
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
Re: Unicode in Fields and SQLite
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.
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.
Re: Unicode in Fields and SQLite
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.