Putting Unicode text into MySQL and getting it back

[SimonV]

From a LiveCode field, want to put UTF-8 text into a field in a table in a MySQL database where it can be searchable, and get it back unchanged.

I have been trying for a week and just can't get it to work, unicode encoding and decoding etc.

I guess that this is a problem for many.

OK, the details:

In a field called "S4 Text" (it is text in S4 phonetics), I have the following text with weird but wanted characters:

ðə top twe‛ntii pəse‛nt
hæv oo‛lweiz bii‛n pritii we‛l edʒəkeitid

I want insert this into my database with something like this:

Code: Select all

on mouseUp
   global gConnectionID
   if gConnectionID is not a number then
      answer error "Please connect to the database first."
      exit to top
   end if
   --put unidecode(the unicodeText of field "S4 Text" ,"UTF8") into theS4Text
   get the unicodeText of fld "S4 Text"
   put uniDecode(it,"utf8") into theS4Text
   put "Update main set S4_Spelling = '" &theS4Text& "';" into tSQL
   put tSQL
   revExecuteSQL gConnectionID, tSQL
   put the result
end mouseUp

But all I get is garbage characters in the database.

I can take the text from field "S4 Text" field as unicode, convert it back and put it in another field (called "Data") with the following routine:

Code: Select all

on mouseUp
   put uniEncode(the unicodeText of field "S4 Text","UTF16") into theUnicode8text
   set the unicodeText of field "Data" to uniDecode(theUnicode8text,"English")
end mouseUp

This puts the following into field "Data"

ðə top twe‛ntii pəse‛nt
hæv oo‛lweiz bii‛n pritii we‛l edʒəkeitid

The boldness of the characters is lost, but this does not matter.

Why can't a MySQL database perform the same role as a variable, transparently?

I really need to get this sorted out, all suggestions gratefully received.

[dave_probertGA6e24]

Hi,

Just to check - is the mySQL table set for utf-8 characters or is it for the default Latin ?

Have you checked that you can add and retrieve utf-8 text from the database via a separate database tool - phpMyAdmin, Navicat, etc.

Try adding a bunch of "put xx into msg" lines into your code - so you can see what is being converted at each stage - it might help visualise the process.

Which version of Livecode are you using - I have seen a couple of discrepancies in the LC6 compared to LC5?

I hope something above helps.
Dave

[bangkok]

For getting back data, before you make your SELECT, try to do :

revExecuteSQL dbID, "SET NAMES 'utf8'"

[SimonV]

Thanks Bangkok, I tried the "SET NAMES 'utf8'" trick and all I got was garbage back again (sigh).

Thanks Dave, I am using phpMyAdmin to see what is going on in the database, set up tables etc.
The type of the field I am writing to is "varchar" and the collation sequence is "utf_general_ci"
The field can accept UTF-8 data, as I have pasted in and inserted some genuine text of this sort made with MS Word.
I can see this unicode data in the field with phpMyAdmin, it is

ðə top twe‛ntii pəse‛nt hæv oo‛lweiz bii‛n pritii...

I can also see the what I tried to insert as unicode text in another field using Livecode, it is

?? top twe?ntii p?se?nt
h¾v oo?lweiz bii?n pritii we?l ed??keitid

It is clear that Livecode can display unicode data properly in a field.

And so can MySQL.

But I still cannot figure out how to pass such data between them unchanged.

[malte]

Hi Simon,

how do you bring the data back into the liveCode field?

Cheers,

Malte

[FourthWorld]

The boldness of the characters is lost, but this does not matter.

If you want to preserve both the textual data with formatting info in a plain-text format, you could consider storing the htmlText of the field.

[malte]

Hi all,

took this off forum with Simon. As I thought the main problem was setting back the text, which of course was UTF8 encoded. So I proposed using a setProp and a getProp handler that retrieve and set UTF8 text directly, which turned out to work.

Code: Select all

-- Method sets UNICODE Text of the target field from an UTF8 encoded string --
--@pUTF8String: UTF8 encoded string--
-- note that font setting might not be necessary anymore with 5.5+ engines
setprop cUTF8Text pUTF8String
    -- patch MB 06.02.2012 : needs an explicit fontname here
    local tFont
    if word 1 of the name of the target<>"field" and word 1 of the name of the target<>"button" then
        if "dev" is in the environment then
            throw "cUTF8Text: Target is not a field or Button"
        end if
        exit cUTF8Text
    end if
    put the effective textFont of the target into tFont
    put item 1 of tFont into tFont
    if tFont is empty then put "Tahoma" into tFont 
    set the textFont of the target to (tFont&",UNICODE")
    set the unicodetext of the target to uniencode(pUTF8String,"UTF8")
end cUTF8Text

getprop cUTF8Text
    if word 1 of the name of the target<>"field" and not the allowsUnicodeTextRetrieval of the target then
        if "dev" is in the environment then
            throw "cUTF8Text: Target is not a field"
        end if
        exit cUTF8Text
    end if
    return unidecode(the unicodetext of the target,"UTF8")
end cUTF8Text

CHeers,

Malte

[SimonV]

Problem solved!

Malte is The Man