SQLite column comes in with £

[makeshyft]

Hi Livecoders,

I have the pound sign in a column in a sqlite table. when I query it and bring it into livecode engine memory it comes in preceeded by this "Â"character. SQLite db is utf-8 by default. how can I prevent this from happening?

Thank you,

Tom

[zaxos]

Hi makeshyft, i have experienced the same thing with Mysql,SQLite and even sockets. It only happends with Livecode7+ at least for me, try livecode 6.5 and see if it makes any difference.

[phaworth]

Do you mean the "#" character or the UK currency symbol for pound?

If the former, not sure why this would happen since "#" is a normal ASCII character.

If the latter then first question is what version of Livecode are you using? Hopefully v7 or higher since handling Unicode is much easier there.

I gave a webinar recently that included how to handle Unicode in databases. You can download the materials and sample scripts from the "Free Stuff" page on my web site, www.lcsql.com it's at the bottom of the page, labelled "Advanced Database Coding". The section on Unicode starts three slides from the end.

HTH,

Pete

[makeshyft]

In my SQLite DB Row, the value of the column is "£" .... when I query the db in Livecode 7 .... it comes in as "£". I am using 7.05, and have no interest in going to 6.5, since my app must have unicode support .... there is only one part of my app that reads columns directly from the database, which is where this particular problem arises. the db is utf-8 and the pound symbol is in a "text" column.

Pete, I will check out your link and materials.... thank you.

I was under the impression that unicode conversion functions do not work in v7 since its just supposed to work.

sigh.... was hoping there was a simple answer.

Thanks

Tom

[phaworth]

Things do "just work" as long as the data remains within Livecode. You have to do a little more work, but not much, when the data is read/written from/to any external file.

To save you the trouble of getting the presentation, the trick is to use textDecode when fetching data from the database and textEncode when inserting/updating it. 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

And for inserting/Updating:

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

or if using the varslist parameter

put textEncode(field “Name”,”UTF8”) into tName
revExecuteSQL gDBID,”UPDATE Customers SET CustName=:1”,”tName”

HTH,

Pete

[makeshyft]

Thanks Pete, TextDecode is what I needed. I have custom functions for querying the db, I just have to incorporate this in there .... Thank you Thank you. Have a wonderful day.