SQLite column comes in with £

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: Klaus, FourthWorld, heatherlaine, kevinmiller

Post Reply
makeshyft
Posts: 187
Joined: Mon Apr 15, 2013 4:41 am
Contact:

SQLite column comes in with £

Post by makeshyft » Tue Jun 23, 2015 3:33 am

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
Tom @
http://www.makeshyft.com
---------------------------------
Changing the Future, Today!

zaxos
Posts: 222
Joined: Thu May 23, 2013 11:15 pm
Location: Greece

Re: SQLite column comes in with £

Post by zaxos » Tue Jun 23, 2015 6:36 pm

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.
Knowledge is meant to be shared.

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

Re: SQLite column comes in with £

Post by phaworth » Tue Jun 23, 2015 7:39 pm

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
Posts: 187
Joined: Mon Apr 15, 2013 4:41 am
Contact:

Re: SQLite column comes in with £

Post by makeshyft » Wed Jun 24, 2015 9:04 pm

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
Tom @
http://www.makeshyft.com
---------------------------------
Changing the Future, Today!

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

Re: SQLite column comes in with £

Post by phaworth » Wed Jun 24, 2015 10:08 pm

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
Posts: 187
Joined: Mon Apr 15, 2013 4:41 am
Contact:

Re: SQLite column comes in with £

Post by makeshyft » Thu Jun 25, 2015 1:34 am

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.
Tom @
http://www.makeshyft.com
---------------------------------
Changing the Future, Today!

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”