sending a utf8 query to MySQL from LiveCode stack

[Devin Asay]

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