Accessing data in MySQL server through ORACLE Database

Setup:
MySQL Server version mysqld-4.0.27-nt on Windows 2008 Server x32
Oracle Database 10.2.0.5.0 on Linux 2.6.32
Oracle Database 11.2.0.0.0 "Oracle 11g Gateway Connectivity - dg4odbc" on Linux 2.6.32
MySQL Connector 3.51.27

I uses particular version of MySQL Connector for two reasons:

When I used last 3.51 version which is 3.51.30 in Oracle I got only row from MySQL database. After reading on OTN and Metalink someone suggested 3.51.27 and all worked as a charm.

When I use newer MySQL ODBC connector (5.1.13 or newer) I got error "Connection using old (pre-4.1.1) authentication protol refused (client option 'secure_auth' enabled)".

Procedure:

Install ODBC connector:
[root@localhost ~]#  rpm -Uvh mysql-connector-odbc-3.51.27-0.x86_64.rpm

Check if driver exists in odbcinst.ini

odbcinst.ini is located in /etc or /usr/local/etc

vi /usr/local/etc/odbcinst.ini

[PostgreSQL]
Description             = ODBC for PostgreSQL
Driver          = /usr/lib/libodbcpsql.so
Setup           = /usr/lib/libodbcpsqlS.so
FileUsage               = 1

[MySQL ODBC 3.51 Driver]
DRIVER          = /usr/lib64/libmyodbc3.so
UsageCount              = 1


Create odbc.ini and enter driver and connection information

vi /usr/local/etc/odbc.ini

[baza]
Description     = ODBC for MySQL
Driver          = /usr/lib64/libmyodbc3.so
SERVER = %IP_ADDRESS_OF_MYSQL_SERVER%
PORT = 3306
USER = %USER_NAME_ON_MYSQL_SIDE%
Password = %PASSWORD_ON_MYSQL_SIDE%
Database = baza
CHARSET=latin1


Create entry in listener.ora. Listener is created in Oracle 10g home, but later for connection we will use Oracle 11g Gateway Connectivity - dg4odbc which is installed in separate 11G home.
Reason for that is that 64-bit Oracle database could not use hsodbc driver and it is needed to install Oracle 11g Gateway Connectivity - dg4odbc in separate home.
I had both database homes on same server so it was somewhat easier for me.

vi /u01/app/10.2.0/db/network/admin/listener.ora
baza =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1527))
                                                      )
   )
                 )
SID_LIST_baza =
   (SID_LIST =
        (SID_DESC =
                (PROGRAM = dg4odbc) %Using 11g executable%
                (ORACLE_HOME = /u01/app/11.2.0/db) %Using 11g HOME%
                (SID_NAME = baza)
                (ENVS=LD_LIBRARY_PATH=/usr/lib64:/usr/local/:/usr/local/etc:/usr/lib/:/u01/app/11.2.0/db/lib) %Metalink recomends using ENVS Variable in listener%
        )
   )

Create tnsnames.ora enty (In Oracle 10g Home)

vi /u01/app/10.2.0/db/network/admin/tnsnames.ora

baza=
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost )(PORT = 1527)))
     (CONNECT_DATA =
        (SID = baza))
        (HS=OK)
)

Create ODBC HS ini file (In Oracle 11g Home)

/u01/app/11.2.0/db/hs/admin/initbaza.ora

# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.
                             
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = baza
# HS_FDS_TRACE_LEVEL = DEBUG
# HS_FDS_TRACE_LEVEL=user
HS_FDS_TRACE_LEVEL=0
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

#
# ODBC specific environment variables
#
set ODBCINI=/usr/local/etc/odbc.ini


#
# Environment variables required for the non-Oracle system
#
# set <envvar>=<value>

After that you only need to create database link in Oracle 10g database. Put username and password in quotation marks if columns in MySQL datavase are declared with a case sensitive or binary collation

create database link baza
connect to "exuser"
identified by "userex"
using 'baza';


If you want a column always to be treated in case-sensitive fashion, declare it with a case sensitive or binary collation