LiveCode Forums.

Posted: **Mon Apr 14, 2014 11:46 am**

This is my first post, hello and thank you for any help

I have a mysql database from where I get the text, all works perfect on the mac and on the IOS emulator, but either on my Android device and on emulator the text encoding is incorrect (where it should be a "ó" the is a strange character), I been playing with encode/decode and searching for all possible solution... on internet I seen other people having similar problems with android devices, but no solutions...

I'm not a mysql guru, obviously

, my mysql is on utf8_unicode_ci... I will apreciate any help.

Thank so much

Posted: **Tue Apr 15, 2014 11:13 pm**

Hi,

Probably, you use MacRoman on Mac and iOS, while Android works with Latin-1. UTF8 is very similar to MacROman and Latin-1, but if you use these encodings as if they're UTF8, you'll be in trouble. Instead of using device-specific encodings, use unicode. To convert a text field to UTF8, before storing the text in the database, use

Code: Select all

put uniDecode(the unicodeText of fld 1,"UTF8") into myData

Now use myData in the statements that store the data in your database. If that doesn't work, post your script here.

Kind regards,

Mark

Posted: **Wed Apr 16, 2014 4:47 pm**

Thank you so much Mark!

I try, but it doesn't want to work

my code:

Code: Select all

--connect to database
   put revOpenDatabase ("mysql","www.xxx.com","tableName","userName","password") into  tDatabaseID


--create the table  
 put vBdRoom["table"] into tTableName
   put "CREATE TABLE" && tTableName && "(id int(11), type int(11), pos int(11), name char(50), xdays int(11), last  date, user1 int(11), user2 int(11), user3 int(11), user4 int(11), user5 int(11), user6 int(11), user7 int(11), user8 int(11), user9 int(11), user10 int(11))" into tSQL
   revExecuteSQL tDatabaseID, tSQL
   
--insert the records 
            put vId into tId
            put vBdRoomType into tType
            put vBdRoom["pos"] into tPos
            put vBdRoom["name"] into tName   
            put vBdRoom["xdays"] into tXdays
            put tMyFormattedDate into tLast
            put vBdRoom["user1"] into tUser1
            put vBdRoom["user2"] into tUser2
            put vBdRoom["user3"] into tUser3
            put vBdRoom["user4"] into tUser4
            put vBdRoom["user5"] into tUser5
            put vBdRoom["user6"] into tUser6
            put vBdRoom["user7"] into tUser7
            put vBdRoom["user8"] into tUser8
            put vBdRoom["user9"] into tUser9
            put vBdRoom["user10"] into tUser10
            
            put "id,type,pos,name,xdays,last,user1,user2,user3,user4,user5,user6,user7,user8,user9,user10" into tFields
            
            put "INSERT INTO" && tTableName && "(" & tFields & ") VALUES (" & vId & ", " & tType & ", " & tPos & ", :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16 );" into tSQL

            put tSQL

            revExecuteSQL  tDatabaseID, tSQL, "tId", "tType", "tPos", "tName", "tXdays", "tLast", "tUser1","tUser2", "tUser3", "tUser4", "tUser5", "tUser6", "tUser7", "tUser8", "tUser9", "tUser10"

Thank so much for any help, I'm getting mad

Posted: **Wed Apr 16, 2014 5:39 pm**

-Try to modify your table encoding : "utf8_general_ci"

By adding to your query to create the table :

Code: Select all

COLLATE='utf8_general_ci'

-And then in your script, before you do the SELECT, execute :

Code: Select all

revExecuteSQL dbID, "SET NAMES 'utf8'"

Last but not least :
-you should work on a database/table created by a SQL client like the excellent HeidiSQL, instead of creating the table by script. It would give your more control, and it's faster to make a change, to add data, to modify data and/or the table structure, and it would be faster to do tests , giving you the ability to focus on the SELECT script in LiveCode.

Posted: **Wed Apr 16, 2014 5:45 pm**

Thanks Bangkok

Sorry for my low skills, but where exactly I got to put the COLLATE='utf8_general_ci' when creating the table?

Thank so much!

Posted: **Thu Apr 17, 2014 12:02 pm**

Hi Bangkok, I been looking to HeidiSQL, as long I understand I cant install it directly on my Hostgator shared server account.
I'm developing a app for mobile that connect to the server and create the database and interact with it, every user can do it, don't see any help of using a program like HeidiSQL that must be installed in my computer... I understand it can be maravellous for other kind of uses.

I'm now on the 'collate' thing, will tell you if there's any progress

Thanks

Posted: **Thu Apr 17, 2014 12:17 pm**

Well finally I put the collate when creating the table:

put "CREATE TABLE" && tTableName && "(id int(11), type int(11), pos int(11), name char(50) COLLATE utf8_unicode_ci, xdays int(11), last date, user1 int(11), user2 int(11), user3 int(11), user4 int(11), user5 int(11), user6 int(11), user7 int(11), user8 int(11), user9 int(11), user10 int(11))" into tSQL

But still the same

Posted: **Fri Apr 18, 2014 1:55 pm**

Hi Asha,

sounds like you misunderstood bangkok's hint to HeidiSQL!?

Using an SQL editor like this will help you to set up a database and all neccessary tables and fields
(including this encoding stuff) from your machine ONCE! with a couple of mouse clicks!

Then you can concentrate on the rest of your app and do not mess around finding the correct syntax
for creating database fields etc. With an SQL edtior you would have set up already a lot ot tables
and databases in the time you were looking for the right snytax.

And this is really reasonable, unless you decide to let your users create DIFFERENT databases/tables each!

But if you really want to do everything from within Licecode, you should definitively check if a table
already exists, before you (try to) create it!

Best

Klaus

LiveCode Forums.

Mysql, encoding and Android

Mysql, encoding and Android

Re: Mysql, encoding and Android

Re: Mysql, encoding and Android

Re: Mysql, encoding and Android

Re: Mysql, encoding and Android

Re: Mysql, encoding and Android

Re: Mysql, encoding and Android

Re: Mysql, encoding and Android