MySQL and character conversion

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
Torbjorn
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 2
Joined: Tue May 22, 2007 10:09 am
Contact:

MySQL and character conversion

Post by Torbjorn » Thu May 24, 2007 12:41 pm

All my MySQL databases and tables are in UTF-8 only. Still everything is delivered in ISO-8859-1 when I read data into Revolution (2.8.1).

Do I really have to take care of that myself?

Right now I have to do something like this:

if the platform contains "Mac" then
put ISOToMac(theText) into theText
end if

And the opposite before I want to write to the database.

The database driver should follow the character set as defined in MySQL field, table or database.

By the way, what character set is used internally in Revolution?

Torbjörn

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5150
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Post by Mark » Thu Jun 14, 2007 1:53 am

Dear Tjorborn,

Yes, you have to take care of the character set conversion yourself, when you import as Western (iso-8859-1) and export it us UTF8. The only conversion you don't need to do yourself is that of text fields when a stack is transferred from on platform to another. Revolution will apply the correct conversion by itself, e.g. MacRoman to Western if you move a stack from Mac to Win. Note that Revolution applies this conversion to text fields only. If you keep text in a custom property, you may have to take care of this yourself.

To convert Western to MacRoman, you can use macToIso. To save the data in your database, you need to use the uniEncode function, if you really need to save unicode data. Usually, you can simply store text and the MySQL driver knows in which format to store it. You don't need to convert to text to Western before sending it to the server, you can convert it directly to unicode (or leave it as plain text).

Best,

Mark
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

Torbjorn
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 2
Joined: Tue May 22, 2007 10:09 am
Contact:

More on MySQL and character conversion

Post by Torbjorn » Thu Jun 14, 2007 6:51 am

Thanks Mark,

I'm against converting data back and forth between different character encodings. The main reason is the risk of losing characters that are not represented in both encodings. I'm also opposed to unnecessary data manipulation.

I'm going to try getting the data from the database unmodified into the unicodetext property of the fields in Revolution. And the opposite when updating the db after text has been changed.

Has anyone tried this, and does it have any side effects?


When it comes to formatted text, I'm currently storing it as html-tagged unicode in the database. I haven't tried yet, but I guess some characters will come out wrong if I store it in the htmltext property.

And finally, how do I make the htmltext property use CSS?


I hope to see next Revolution version using unicode as it's native character set. I also hope to find the database drivers changed to folllow the encodings set in the databases and fields.

Torbjörn

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5150
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Post by Mark » Fri Jun 15, 2007 12:37 am

Hi Torbjörn,

Sure, you can store the unicodeText of a field in a database and also retrieve it. The database normally doesn't care about encodings, unless the variable in the database has been explcitly defined as plain ascii text.

However, if you use the same database on both Mac and Windows, you still may have to do some conversion before you can display the unicode text. I would just set up an experiment first and see if it works as expected.

You can't use css for the htmlText property. If feasible, you can use the revBrowser to apply css the same way you to in web pages.

Best,

Mark
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

Post Reply

Return to “Databases”