Unicode and mySQL

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

Post Reply
golife
Posts: 6
Joined: Fri Apr 02, 2010 12:10 pm

Unicode and mySQL

Post by golife » Sat Apr 03, 2010 5:07 pm

I am using a remote mySQL 5.1 database where the collation is set to utf8_general_ci. Also the database itself has such value.

For example entered is a Russian name "Саша" (= Sasha) which is also visible in the using PHP commands.

Unfortunately, in runrev the SELECT * FROM TABLE does not retrieve the correctly entered name, but just a "C???". All fields used are set to Unicode.

Maybe this is just a beginners question, but I need to store and retrieve Unicode characters in mySQL and want to work with Revolution on the client side. I need to support a lot different languages such as Russian, Arabic, Chinese, Hindi, etc.

Somehow I am stuck. How do I INSERT Unicode characters into mySQL and how do I retrieve and display them again correctly in runrev?

golife
Posts: 6
Joined: Fri Apr 02, 2010 12:10 pm

Re: Unicode and mySQL

Post by golife » Sat Apr 03, 2010 9:21 pm

I would like to elaborate:

The simple select in "MySQL Query Browser" returns the correct results:

SELECT * FROM PARTY ->
1 Hans
2 Саша
------------------------------

So what is wrong retrieving Unicode with revStudio?

Code: Select all

on mouseUp
   global gConID
   local tSQL
   
   put revOpenDatabase("MySQL","www.babanin.com","babaninc_address","<user>","<password>",,,,) into gConID
   put "SELECT * FROM PARTY" into tSQL
   get revdb_querylist(,,gConID,tSQL)
   
   # get uniEncode(it,"utf8")   -- Trying all variants
   # get uniDecode(it,"utf8")
   # get uniEncode(it,"Unicode")
   
   set the unicodeText of field "Output" to "utf8"
   
   put it into fld "Output"
end mouseUp

# The expected result should be:
-- 1 Hans
-- 2 Саша

# Get uniEncode(it,"utf8") returns:
-- 1 Hans
-- 2 !0H0

# Without uniEncode() or uniDecode() the following is returned:
-- 1 Hans
-- 2 C???

# Get uniDecode(it,"utf8")  returns:
--ऱ慈獮㈊䌉㼿
--

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5118
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: Unicode and mySQL

Post by Mark » Sat Apr 24, 2010 3:49 pm

Golife (Sacha?),

RunRev can't retrieve unicode text using external libraries or drivers, but you could do this with PHP for example. RunRev can retrieve data from a server with PHP as an intermediary layer.

Best,

Mark
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/livecodebook

Bellthorpe
Posts: 3
Joined: Thu Apr 02, 2009 12:26 pm
Location: Australia
Contact:

Re: Unicode and mySQL

Post by Bellthorpe » Mon Aug 09, 2010 12:39 pm

I don't follow that. What's special about PHP? It still needs to use an ODBC driver, does it not?

I have the same issue as the OP. I'm trying to read Unicode data from an Access database. I've also tried from Excel ... but it appears that the same (Microsoft) drivers are used. I can't retrieve Unicode data.

Given that this is the reason I purchased Revolution, it's a problem for me.

Janschenkel
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 977
Joined: Sat Apr 08, 2006 7:47 am
Location: Aalst, Belgium
Contact:

Re: Unicode and mySQL

Post by Janschenkel » Mon Aug 09, 2010 1:20 pm

If you're in control of the modification of the data in the database, you can always load and save the data in UTF-8 encoding; then it's a matter of decoding when you read from and encoding when you write to the database. Another option is to work with the htmlText property of the rev field - this will convert special characters to numeric entities which fit in the ASCII range.

Jan Schenkel.
Quartam Reports & PDF Library for LiveCode
www.quartam.com

Bellthorpe
Posts: 3
Joined: Thu Apr 02, 2009 12:26 pm
Location: Australia
Contact:

Re: Unicode and mySQL

Post by Bellthorpe » Thu Aug 12, 2010 7:54 am

Thanks Jan,

As mentioned, I'm trying to read from Access or Excel databases. As far as I can determine, they do not support UTF8. Sure, you can save and export in UTF8, but the structure of the database itself, unlike (say) MySQL, can't be set to any other encoding than UTF16.

I don't believer htmlText can assist here ... as Unicode can't be retrieved, the function will not know what the character is, and therefore can't render it in HTML.

I am of course happy to be proven wrong with any of this!

Janschenkel
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 977
Joined: Sat Apr 08, 2006 7:47 am
Location: Aalst, Belgium
Contact:

Re: Unicode and mySQL

Post by Janschenkel » Fri Aug 13, 2010 5:54 am

If the data can also be written to the database by other processes, then the 'htmlText' approach goes out the door.

The idea was that your Rev app would write the 'htmlText' of a field to your database, and later populate that field via its 'htmlText' property with the data retrieved from the database. So instead of the original

Code: Select all

René Violà
the content of the database field would look like this

Code: Select all

<p>Ren&eacute; Viol&agrave;</p>
As you can see, this transforms the special diacritical characters into the HTML entity equivalents, and it can do the same for all other Unicode characters supported by the rev engine. This is a handy trick if the underlying database doesn't support Unicode, as all you store and retrieve is ASCII.

Jan Schenkel.
Quartam Reports & PDF Library for LiveCode
www.quartam.com

Bellthorpe
Posts: 3
Joined: Thu Apr 02, 2009 12:26 pm
Location: Australia
Contact:

Re: Unicode and mySQL

Post by Bellthorpe » Fri Aug 13, 2010 6:20 am

Understood Jan, very useful, and I appreciate it.

In this case, I want to use Revolution as a front end to an existing database. I've loaded it into MySql now, and am getting somewhere. I'll report back when I've concluded everything.

jcollett
Posts: 17
Joined: Sun Aug 01, 2010 8:28 pm

Re: Unicode and mySQL

Post by jcollett » Tue Aug 24, 2010 8:16 pm

Hi, Bellthorpe. You were going to "report back" re Unicode and mySQL. I'd be interested in your results. Thanks. JC

outstripp
Posts: 9
Joined: Fri Dec 05, 2008 4:32 am

Re: Unicode and mySQL

Post by outstripp » Mon Oct 25, 2010 6:15 am

Does this help?

revExecuteSQL tconID, "SET NAMES 'utf8'"
outstripp

TS
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5
Joined: Mon Sep 15, 2008 9:59 am

Re: Unicode and mySQL

Post by TS » Sun Nov 27, 2011 3:43 pm

[quote]Does this help?
revExecuteSQL tconID, "SET NAMES 'utf8'"[/quote]

Mr. outstripp!! Thank you -- had the same problem, Google brought me here and you fixed it for me.

Many, many thanks!

Soteris
Posts: 2
Joined: Mon Mar 12, 2012 10:51 am

Re: Unicode and mySQL

Post by Soteris » Fri Mar 30, 2012 3:25 pm

Hello all,

I have been trying to retrieve some data stored in a remote MySQL database in utf-8. Apparently I am doing something wrong as I can't see in my application what I see in the database.

Below is my code:

Code: Select all

    put 1 into tCounter
    put revQueryDatabase(sDatabaseID,tSQLQuery) into sRecordSetID
    if sRecordSetID is an integer then
       repeat until revQueryIsAtEnd(sRecordSetID)
          put  uniEncode(revDatabaseColumnNamed(sRecordSetID,"greek_name"),"UTF8") into tData[tCounter][1]
          put uniEncode(revDatabaseColumnNamed(sRecordSetID,"turkish_name"),"UTF8") into tData[tCounter][2]
          set the unicodeText of field "display_greek" of card "Commonalities" to tData[tCounter][1]
          set the unicodeText of field "display_turkish" of card "Commonalities" to tData[tCounter][2]
         revMoveToNextRecord sRecordSetID
         add one to tCounter
      end repeat
    else
      answer "Error connecting to the database:" && sDatabaseID & "."
       exit to top
    end if
I have spent the whole day searching for a possible solution with no luck.
Any help is much appreciated.

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 773
Joined: Fri Aug 15, 2008 7:15 am

Re: Unicode and mySQL

Post by bangkok » Fri Mar 30, 2012 3:40 pm

You might have forgotten the [famous]

Code: Select all

revExecuteSQL dbID, "SET NAMES 'utf8'"
or you got confused with your cursor.

Anyway. Here is a simple code. That works.

Code: Select all

revExecuteSQL dbID, "SET NAMES 'utf8'"

put "SELECT * FROM mytable" into dbsql
put revDataFromQuery(, , dbID, dbSQL) into myResult
revclosedatabase dbID

put uniencode(myResult,"UTF8") into tData
set the unicodetext of  field "myField" to tData

Soteris
Posts: 2
Joined: Mon Mar 12, 2012 10:51 am

Re: Unicode and mySQL

Post by Soteris » Fri Mar 30, 2012 4:39 pm

I used as you suggested

Code: Select all

revExecuteSQL dbID, "SET NAMES 'utf8'"
on top of my code.

Now I can see the names properly.

Thanks a million bangkok!!!

TS
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5
Joined: Mon Sep 15, 2008 9:59 am

Re: Unicode and mySQL

Post by TS » Thu Apr 18, 2013 3:55 pm

FINALLY it has all come together with the
[code]revExecuteSQL dbID, "SET NAMES 'utf8'"[/code]
command! Thank you! I have been trying to use LiveCode to retrieve Japanese/UTF8 from a mySQL database that has both Japanese data and Japanese field/column names, with very little success for several days. OK, I'm hardly a pro, but does it have to be this hard?? RunRev needs to update their UTF8 tutorial to add a section about databases.

For the next guy or gal trying to solve this problem via Google, here's my basic script:
[code]
on mouseUp
global gDictDBID --database ID
revExecuteSQL gDictDBID, "SET NAMES 'utf8'" --magic command for UTF8/mySQL

put the unicodeText of field "query" into locSQLParams --fld "query" contains SQL query, English & Japanese text
put uniDecode(locSQLParams, "UTF8") into locSQLParams

put revDataFromQuery(, , gDictDBID, locSQLParams) into myResult
set the unicodetext of fld queryresults to uniEncode(myResult, "UTF8") --fld "queryresults" is holds data returned by mySQL
end mouseUp
[/code]

And for the record, here is the basic SQL Query in fld "query":
SELECT *
FROM `test_table`
WHERE `県` Like '青森'

Haven't tested all possible SQL query commands, but have checked a number that use both Japanese data and Japanese column names. All works as it should!

So, again, many thanks to Mr. bangkok!!

Post Reply

Return to “Databases”