REAL Software Forums

Hope someone can help. I've written a converter program whereby I transfer data from one MySql database (Wordpress) to another MySql database (Drupal). Both are blogging type packages (Drupal more a CMS).

My problem is with funny accented or weird characters. The characters of concern are curly quotes and unusual single quotes which can't be typed on my Windows keyboard.

The following piece of text:-


turns into this at the destination database:-
[code]
“Mares eat oats–and does eat oats—and…little lambs eat ‘ivy’.â€

that's an Encoding issue

Seems to me that strings coming back from mySql (at least using the old plugin, I haven't checked this on the new one) have undefined encoding. I had to explicitly tell RB that they were utf8.

Tim

I'm going around in circles now. When I enter the text string containing curly quotes via a Wordpress blog, it's saved and the viewing of the text (using Wordpress) is as per initial input - correctly showing the curly quotes. When checking the MySql database, for that particular field, the text has changed to show weird characters in place of the curly quotes. So obviously Wordpress is doing something in order to correctly display the text.

Now my program, for all intents and purposes is merely copying that particular field and inserting it into another MySql database.

I've noticed that the Wordpress MySql database, for the particular table and field is marked as "Latin1_sweedish_ci". My target database (Drupal MySql) is "utf8_general_ci".

I am retrieving the particular field like so:-

So the variable "strBody" now contains the contents of the source field, which contains the altered characters (characters in place of curly quotes).

I've tried various attempts at using 'Encodings' but nothing seems to work. Can someone simply list the steps required.

Any advice greatly appreciated.
My Realbasic Program

The answer:

After making a connection, send the following command to MySql:

db.SqlExecute("SET NAMES 'utf8';") // if unicode

or

db.SqlExecute("SET NAMES 'latin1';") // if Latin1.

For a full understanding of character codes the reference manual over at the MySql is a good source to start with.

Even though RB, by default, works with utf8, still send the command to MySql, if MySql is expecting utf8.