Problem replicating a MySQL on SQLite

[RazZziel]

Hi, I'm replicating a remote MySQL database onto a local SQLite database with QT4, SELECTing the remote data and INSERTing it into the local database.

On Windows it worked flawlessly, but on Linux I'm facing encoding problems, with stuff like Euro symbols (€) or Spanish accents (áéÃ*óú). The original MySQL database has the schema set to UTF8:

CREATE SCHEMA IF NOT EXISTS `4access` DEFAULT CHARACTER SET UTF8

CREATE SCHEMA IF NOT EXISTS `4access` DEFAULT CHARACTER SET UTF8

To copy to clipboard, switch view to plain text mode 

Accessing directly using the mysql client, everything is fine:

mysql> select * from currency;
+-------+---------+---------+-----------+---------+---------------------+
| cu_id | cu_name | cu_code | cu_symbol | cu_rate | version             |
+-------+---------+---------+-----------+---------+---------------------+
|     1 | Euro    | EU      | €       |       1 | 2010-12-15 20:28:04 |
|     2 | Dollar  | US      | $         |     1.5 | 2010-12-15 20:28:04 |
|     3 | Pound   | UK      | £        |     0.8 | 2010-12-15 20:28:04 |
+-------+---------+---------+-----------+---------+---------------------+

mysql> select * from currency;
+-------+---------+---------+-----------+---------+---------------------+
| cu_id | cu_name | cu_code | cu_symbol | cu_rate | version             |
+-------+---------+---------+-----------+---------+---------------------+
|     1 | Euro    | EU      | €       |       1 | 2010-12-15 20:28:04 |
|     2 | Dollar  | US      | $         |     1.5 | 2010-12-15 20:28:04 |
|     3 | Pound   | UK      | £        |     0.8 | 2010-12-15 20:28:04 |
+-------+---------+---------+-----------+---------+---------------------+

To copy to clipboard, switch view to plain text mode 

But after replicating the database with my application, I open the sqlite databse and some characters are screwed:

sqlite> select * from currency;
1|Euro|EU|€|1.0|2010-12-15T20:28:04
2|Dollar|US|$|1.5|2010-12-15T20:28:04
3|Pound|UK|£|0.8|2010-12-15T20:28:04

sqlite> select * from currency;
1|Euro|EU|€|1.0|2010-12-15T20:28:04
2|Dollar|US|$|1.5|2010-12-15T20:28:04
3|Pound|UK|£|0.8|2010-12-15T20:28:04

To copy to clipboard, switch view to plain text mode 

Does anyone know where the problem can be?

[ChrisW67]

You don't tell us exactly how you do the transfer. It seems likely that the character set on your connection (different from the database char set) to the Mysql database is Latin-1 rather than UTF-8. From the mysql man page:

· --default-character-set=charset_name

Use charset_name as the default character set for the client and
connection.

A common issue that can occur when the operating system uses utf8
or another multi-byte character set is that output from the mysql
client is formatted incorrectly, due to the fact that the MySQL
client uses the latin1 character set by default. You can usually
fix such issues by using this option to force the client to use the
system character set instead.

See Section 9.5, “Character Set Configuration”, for more
information.

[RazZziel]

Sorry, didn't see your reply. I fixed the problem issuing a SET NAMES UTF8 right after I open my MySQL connection