#3 Access from Oracle to MySql not working

[Manfred]

I spent several days about trying to solve a character conversion problem when accessing a mysql database from oracle.
The connection is done by unixOdbc on a linux 64 os.

There seems to be a problem when changing the oracle-gateway character set and oracle states that it is a problem of the unixOdbc implementation. I doubt that, could you please clarify that? Thank's a lot!

*****************************************************************************************************************************
Oracle Note:

Symptoms
using DG4ODBC and MySQL ODBC driver with UnixODBC Driver Manager on Linux corupts multibyte characters stored in a MySQL database using UTF8 charset.

Cause
Root cause of the corrupted characters is the unixODBC Driver Manager which forces the gateway to specify an 8bit language like HS_LANGUAGE=american_america.we8iso8859p1

Commonly when DG4ODBC uses HS_LANGUAGE=american_america.utf8 it uses the unicode ODBC functions like SQLDriverConnectW. This works well when using DataDirect ODBC driver. When using unixODBC Driver manager it expects a double byte ODBC connect string as soon as SQLDriverConnectW is being called. If a single byte connect string is sent (as it is done by DG4ODBC and handled by several commercial ODBC driver vendors) unixODBC is only interpreting every second character and thus corrupts the previously sent connect string which will leqad in an ORA-28500 error message.
Thus it is mandatory to use HS_LANGUAGE with a single byte character with unixODBC driver manager to correctly handle the connect string - but this forces the driver manager to use the SQLDriverConnect function instead of its unicode equivalent.

This SQLDriverConnect function is not able to handle UTF8 character sets and thus corrupts the content.
Solution

To solve your problem you now have 3 options:
- report this issue to the unixODBC vendor => this was already done a couple of times by other customers, but the problem still exists in the latest 2.3 release.
- use a commercial ODBC driver like the ODBC drivers from Data Direct
- use DG4ODBC on Windows with the MySQL ODBC driver. You can connect from a suitable Oracle database release on any platform to DG4ODBC on Windows which then uses the MySQL ODBC driver to connect to the MySQL database.