Page 1 of 1
Unicode and mySQL
Posted: Sat Apr 03, 2010 5:07 pm
by 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?
Re: Unicode and mySQL
Posted: Sat Apr 03, 2010 9:21 pm
by 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:
--ऱ慈ç®ãˆŠäŒ‰ã¼¿
--
Re: Unicode and mySQL
Posted: Sat Apr 24, 2010 3:49 pm
by 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
Re: Unicode and mySQL
Posted: Mon Aug 09, 2010 12:39 pm
by 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.
Re: Unicode and mySQL
Posted: Mon Aug 09, 2010 1:20 pm
by 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.
Re: Unicode and mySQL
Posted: Thu Aug 12, 2010 7:54 am
by 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!
Re: Unicode and mySQL
Posted: Fri Aug 13, 2010 5:54 am
by 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
the content of the database field would look like this
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.
Re: Unicode and mySQL
Posted: Fri Aug 13, 2010 6:20 am
by 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.
Re: Unicode and mySQL
Posted: Tue Aug 24, 2010 8:16 pm
by jcollett
Hi, Bellthorpe. You were going to "report back" re Unicode and mySQL. I'd be interested in your results. Thanks. JC
Re: Unicode and mySQL
Posted: Mon Oct 25, 2010 6:15 am
by outstripp
Does this help?
revExecuteSQL tconID, "SET NAMES 'utf8'"
Re: Unicode and mySQL
Posted: Sun Nov 27, 2011 3:43 pm
by 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!
Re: Unicode and mySQL
Posted: Fri Mar 30, 2012 3:25 pm
by 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.
Re: Unicode and mySQL
Posted: Fri Mar 30, 2012 3:40 pm
by 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
Re: Unicode and mySQL
Posted: Fri Mar 30, 2012 4:39 pm
by 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!!!
Re: Unicode and mySQL
Posted: Thu Apr 18, 2013 3:55 pm
by 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!!