Unicode and mySQL

[golife]

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]

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]

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

[Bellthorpe]

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]

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.

[Bellthorpe]

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]

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.

[Bellthorpe]

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]

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

[outstripp]

Does this help?

revExecuteSQL tconID, "SET NAMES 'utf8'"

[TS]

[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]

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]

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]

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]

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!!