Omnis character sets and MySQL

Aus MHC-Wiki

This is based on the NON unicode version of Omnis. By now I do not use the unicode version as it's still not available for Linux. The main goal of my work was to get the same data in MySQL when German special characters are inserted in the database. My tests have showen that

Do SessionObj.$charmap.$assign(kSessionCharMapNative)

has no effect at all. This technote says about this:

When MySQL is using the default character set ISO-8859-1 you must set the property

  Do MySQL_SessionObj.$charmap.$assign(kSessionCharMapNative)

in the Omnis session object. 

I've not tested this as UTF8 is the default characterset in MySQL 5. So using this on all platforms after $logon is no harm and ensures data consistecy even if the MySQL character set is set to ISO-8859-1.

My test data in the database looks like this in phpMyAdmin:

My insert string                The last 14 characters in HEX

Linux-Omnis: öäüÖÄÜasdfASDF     C3B6C3A4C3BCC396C384C39C6173646641534446
Linux-Native: öäüÖÄÜasdfASDF    C3B6C3A4C3BCC396C384C39C6173646641534446
Mac-Omnis: öäüÖÄÜasdfASDF       C3B6C3A4C3BCC396C384C39C6173646641534446
Mac-Native: öäüÖÄÜasdfASDF      C3B6C3A4C3BCC396C384C39C6173646641534446
Windows-Omnis: öäüÖÄÜasdfASDF   C3B6C3A4C3BCC396C384C39C6173646641534446
Windows-Native: öäüÖÄÜasdfASDF  C3B6C3A4C3BCC396C384C39C6173646641534446

Data is stored in UTF8 in the MySQL table. To proofe this I've done the following test with the same database, table, data:

mysql> select * from test;
+--------------------------------+
| ctest                          |
+--------------------------------+
| Windows-Omnis: ������asdfASDF  |
| Windows-Native: ������asdfASDF |
| Mac-Native: ������asdfASDF     |
| Mac-Native: ������asdfASDF     |
| Linux-Omnis: ������asdfASDF    |
| Linux-Native: ������asdfASDF   |
+--------------------------------+
6 rows in set (0.00 sec)

mysql> SET character_set_client=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> SET character_set_results=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+--------------------------------+
| ctest                          |
+--------------------------------+
| Windows-Omnis: öäüÖÄÜasdfASDF  |
| Windows-Native: öäüÖÄÜasdfASDF |
| Mac-Native: öäüÖÄÜasdfASDF     |
| Mac-Native: öäüÖÄÜasdfASDF     |
| Linux-Omnis: öäüÖÄÜasdfASDF    |
| Linux-Native: öäüÖÄÜasdfASDF   |
+--------------------------------+
6 rows in set (0.00 sec)

Linux & Windows

On Linux and Windows you do not need to take care about any thing but about $charmap, see above.

Mac

On a Mac OS X with German localisation you need the following code after the $logon:

Do SessionObj.$getdatatypemapping(mappingList)
Calculate mappingList.4.MySqlType as 'TEXT'
Do SessionObj.$setdatatypemapping(mappingList)
Do SessionObj.$query('SET character_set_client=macce')
Do SessionObj.$query('SET character_set_results=macce')

I've not tested it but this should work for every language where Mac OS X uses "macce" as characterset.

If you have existing data in a MySQL database written without this code and you add this code to you lib characters beyond $7E (see character map) will be remapped and more or less useless. But it should be too complicated to write a generic dataconverter.