Unicode and mySQL

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

Unicode and mySQL

Postby 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: 4
Joined: Fri Apr 02, 2010 12:10 pm

Re: Unicode and mySQL

Postby 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:
--ऱ慈獮㈊䌉㼿
--
golife
 
Posts: 4
Joined: Fri Apr 02, 2010 12:10 pm

Re: Unicode and mySQL

Postby 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 is here: http://qery.us/44g
The book "Programming LiveCode for the Real Beginner" is now available! Get it here! http://qery.us/3fi
Mark
Livecode Opensource Backer
Livecode Opensource Backer
 
Posts: 4949
Joined: Thu Feb 23, 2006 9:24 pm

Re: Unicode and mySQL

Postby 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.
Bellthorpe
 
Posts: 3
Joined: Thu Apr 02, 2009 12:26 pm
Location: Australia

Re: Unicode and mySQL

Postby 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
Janschenkel
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
 
Posts: 977
Joined: Sat Apr 08, 2006 7:47 am
Location: Aalst, Belgium

Re: Unicode and mySQL

Postby 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!
Bellthorpe
 
Posts: 3
Joined: Thu Apr 02, 2009 12:26 pm
Location: Australia

Re: Unicode and mySQL

Postby 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
Janschenkel
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
 
Posts: 977
Joined: Sat Apr 08, 2006 7:47 am
Location: Aalst, Belgium

Re: Unicode and mySQL

Postby 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.
Bellthorpe
 
Posts: 3
Joined: Thu Apr 02, 2009 12:26 pm
Location: Australia

Re: Unicode and mySQL

Postby 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
jcollett
 
Posts: 17
Joined: Sun Aug 01, 2010 8:28 pm

Re: Unicode and mySQL

Postby outstripp » Mon Oct 25, 2010 6:15 am

Does this help?

revExecuteSQL tconID, "SET NAMES 'utf8'"
outstripp
outstripp
 
Posts: 9
Joined: Fri Dec 05, 2008 4:32 am

Re: Unicode and mySQL

Postby 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!
TS
Livecode Opensource Backer
Livecode Opensource Backer
 
Posts: 5
Joined: Mon Sep 15, 2008 9:59 am

Re: Unicode and mySQL

Postby 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.
Soteris
 
Posts: 2
Joined: Mon Mar 12, 2012 10:51 am

Re: Unicode and mySQL

Postby 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
bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
 
Posts: 700
Joined: Fri Aug 15, 2008 7:15 am

Re: Unicode and mySQL

Postby 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!!!
Soteris
 
Posts: 2
Joined: Mon Mar 12, 2012 10:51 am

Re: Unicode and mySQL

Postby 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!!
TS
Livecode Opensource Backer
Livecode Opensource Backer
 
Posts: 5
Joined: Mon Sep 15, 2008 9:59 am


Return to Databases

Who is online

Users browsing this forum: No registered users and 1 guest