sending a utf8 query to MySQL from LiveCode stack, NOW with DG twist

[Tim Selander]

Hi again,

I have it working. Here is my working script:

on mouseUp
    global vDatabaseID --database ID

    --magic command for UTF8/mySQL, from forum user "bancock
    --without this I cannot send or receive UTF8 from mySQL
    revExecuteSQL vDatabaseID, "SET NAMES 'utf8'"

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

    put revDataFromQuery(, , vDatabaseID, locSQLParams) into myResult

    --fld "queryresults" holds data returned by mySQL
    --works great; get a field that shows the returned English 
and Japanese
    set the unicodetext of fld queryresults to 
uniEncode(myResult, "UTF8")

    --adding "unicodetext" to any part of "dgtext of group 
results causes error
    --following line does NOT work. Japanese text is broken in 
datagrid
    set the dgText of group "results" to uniEncode(myResult, 
"UTF8")
end mouseUp


My next problem, though, is that while I can get the returned 
Japanese into text or table fields, I cannot get it into a 
datagrid. I have googled, as well as read (again) all the unicode 
and datagrid lessons on the runrev.com site. Their lesson uses a 
  dummy button to set a custom property?? I did the steps without 
really understanding them... with predictable (non) results!

Can anyone tell me how you get unicode text into a datagrid?

Thanks,

Tim Selander
Tokyo, Japan

On 4/13/13 2:43 AM, Devin Asay wrote:
> Tim,
>
> I don't know the answers, but here are some thoughts based on my experience with unicode text in SQL db's.
>
> On Apr 12, 2013, at 8:55 AM, Tim Selander wrote:
>
>> Starting to work with LC with a MySQL database on my on-rev.com account. Got the database, user, table etc. made and imported all my data using phpMyAdmin and can use the database with phpMyAdmin.
>>
>> For testing purposes, I made some of the column names Japanese and some English. (Ideally, I would like all column names to be in Japanese utf8.)
>>
>> Everything works fine on phpMyAdmin, that is searching for Japanese data using the Japanese column names works fine.
>
> Wow, I didn't know you could even do that with MySQL!
>>
>> On my stack, I have a fld "query" that has
>> SELECT *
>> FROM  `tablename`
>> WHERE  `県` LIKE  '東京'
>> LIMIT 0 , 30
>>
>> when I put that in a variable to send to the server, the variable replaces the kanji characters with "?" -- whether the kanji is in the column name, or in the data to search for -- and of course the search fails.
>>
>> on mouseUp
>>    global vDatabaseID
>>    set useunicode to true
>>    revExecuteSQL vDatabaseID, "SET NAMES 'utf8'"
>
> I can't find anything on SET NAMES in my MySQL reference. What it is supposed to do? I suspect that each revExecuteSQL instance is treated as a single transaction. I wonder if you could put multiple SQL statements into a variable and execute them all at once?
>>
>>    put fld query into dbsql
>>    put revDataFromQuery(, , vDatabaseID, dbSQL) into myResult
>>
>>    put uniencode(myResult,"UTF8") into tData
>>    set the unicodetext of field "queryresults" to tData
>> end mouseUp
>>
>> <put uniencode(fld query, "UTF8") into dbsql> doesn't work either
>>
>> How do I get the kanji/utf8 into a variable correctly?
>>
>> Can anyone give me a pointer?
>
> This is just an educated guess, but I believe that the LiveCode database libraries can only handle ASCII characters when sending SQL statements to a database. The way I've always handled unicode data in MySQL is to unidecode everything to UTF-8, insert/update it to the database (so it essentially is stored as ascii), then reverse the process to retrieve it and display it in LiveCode fields. If I need to do text comparisons in WHERE clauses I do the comparisons using the "ascii-fied" text. Not the most convenient method maybe, but it has been reliable for me.
>
> HTH
>
> Devin
>
> Devin Asay
> Office of Digital Humanities
> Brigham Young University
>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>