Access MySQL Databases with Oracle HSODBC

Sometimes you need to access data stored in an different RDBMS like MySQL. Oracle provides an convenient way to access external data-sources via ODBC. To use Oracle HSODBC you can follow my step-by-step notes:

  • Install unixODBC and the MySQL ODBC Driver
    For Fedora Core 6 I installed the following RPMs:

    • unixODBC-2.2.11-7.1
    • mysql-connector-odbc-3.51.12-2.2
  • Configure /etc/odbc.ini
    [ODBC Data Sources]
    TimeSheetTest = MySQL ODBC PHP.TimeSheetTest DSN
    
    [TimeSheetTest]
    Driver       = /usr/lib/libmyodbc3.so
    Description  = MySQL ODBC 3.51 Driver DSN
    SERVER       = localhost
    PORT         = 3306
    USER         = root
    Password     = SECRET
    Database     = timesheet
    OPTION       = 3
    SOCKET       =
    
  • Test the ODBC Connection via isql
    [frank@w0004]$ isql TimeSheetTest
    Connected!
    sql-statement
    help [tablename]
    quit
    SQL> 
    
  • Oracle Configuration
    First ensure that the shell environment used to start the Oracle listener includes the following
    environment variables:

    export ODBCINI=/etc/odbc.ini
    export ODBCSYSINI=/etc
    

    To integrate that ODBC connection into Oracle you have to edit several configuration files:

    $ORACLE_HOME/hs/admin/initTimeSheetTest.ora

    HS_FDS_CONNECT_INFO = TimeSheetTest
    HS_FDS_TRACE_LEVEL = off
    HS_FDS_SHAREABLE_NAME = /usr/lib/libmyodbc3.so
    

    $ORACLE_HOME/network/admin/listener.ora

    ...
    SID_LIST_LISTENER =
    ...
        (SID_DESC =
          (PROGRAM = hsodbc)
          (ORACLE_HOME = /opt/oracle/ora10g)
          (SID_NAME = TimeSheetTest)
          (ENVS=LD_LIBRARY_PATH = /usr/lib:/opt/oracle/ora10g/lib)
        )
      )
    ...
    

    $ORACLE_HOME/network/admin/tnsnames.ora

    ...
    TIMESHEET = (DESCRIPTION = 
        (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)))
        (CONNECT_DATA = (SID= TimeSheetTest))(HS=OK)
      )
    ...
    
  • Test the Configuration with tnsping
    $ tnsping timesheet
    
    TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 05-SEP-2007 18:26:16
    
    Copyright (c) 1997, 2005, Oracle.  All rights reserved.
    
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
    (HOST = localhost)(PORT = 1521))) (CONNECT_DATA = (SID= TimeSheetTest))(HS=OK))
    OK (10 msec)
    
  • Create an Oracle Link in your Oracle Database instance
    SQL> CREATE PUBLIC DATABASE LINK timesheet
      CONNECT TO "root" IDENTIFIED BY "SECRET" USING 'TIMESHEET';
    

Please note that MySQL is case-sensitive on tablenames, so you have to use:

SQL> select * from "timesheet_times"@timesheet;
...