The type of connection that we will be performing in this article will utilize Oracle's Heterogeneous Services ODBC - generic connectivity (HSODBC) on Sun Solaris (Oracle 9.2.0.8.0).

This article is divided into the following sections:

• Setting up an ODBC driver manager for Sun Solaris
• Setting up a MySQL ODBC driver for Sun Solaris
• Configuring the HSODBC process on Oracle9i

Note that Heterogeneous Services requires the 32-bit version of ODBC.

Before moving into the details on the how to configure HSODBC, let's first discuss the setup I will be using for this article. This configuration contains two servers:

1. Sun SPARC
   • Solaris 10 Operating Environment
   • Oracle RDBMS 9.2.0.8.0
   • Host name = alex
   • Database Name = ORA920

2. Linux Server
   • Red Hat Linux 4 Update 4
   • MySQL 4.1.20
   • Host name = linux2
   • Database Name = test

This article should work for Oracle 8i, 9.0.1, and 10g releases running on either Solaris 9 or Solaris 10. Also note that this article has been tested with MySQL 4.0.20-standard running on Red Hat Linux - (Fedora Core 1) as well as MySQL 4.1.20 running on Red Hat Linux 4 Update 4 - (actually CentOS 4.4).

# mysql -u root mysql

mysql> GRANT ALL PRIVILEGES ON test.* TO oracle@alex
    -> IDENTIFIED BY 'manager' WITH GRANT OPTION;

mysql> FLUSH PRIVILEGES;

LD_LIBRARY_PATH=/usr/local/lib:$LD_LIBRARY_PATH:/usr/lib:/usr/openwin/lib:/usr/dt/lib
export LD_LIBRARY_PATH

ODBCINI=/usr/local/etc/odbc.ini
export ODBCINI

ODBCSYSINI=/usr/local/etc     # (for unixODBC)
export ODBCSYSINI

With the LD_LIBRARY_PATH, we need to ensure that it contains the path for both libraries we just installed - ODBC Driver Manager and MySQL ODBC Driver. For this example, we installed both sets of libraries to /usr/local/lib, so this will need to be included in the LD_LIBRARY_PATH environment variable.

Login to the database server (for the purpose of this article, my database server is alex) as the Oracle software owner (i.e. oracle) and run the following:

$ id
uid=175(oracle) gid=115(dba)

$ isql myodbc3 oracle manager -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> create table emp (id int, name varchar(100));
0 rows affected

SQL> insert into emp values (100, 'Jeff');
1 rows affected

SQL> insert into emp values (100, 'Melody');
1 rows affected

SQL> commit;
0 rows affected

SQL> quit
$

In general the following things must be configured:

1. Oracle TNS listener
2. tnsnames.ora file
3. init<SID>.ora of the HS subsystem (also known as the HS Gateway)
4. environment
5. Oracle database

Oracle TNS Listener

Let's start by adding the following new SID to the listener.ora file:

(SID_DESC =
  (PROGRAM = hsodbc)
  (ORACLE_HOME = /u01/app/oracle/product/9.2.0)
  (SID_NAME = myodbc3)
  (ENVS=LD_LIBRARY_PATH = /usr/local/lib:/u01/app/oracle/product/9.2.0/lib)
)

Example listener.ora File

# LISTENER.ORA Network Configuration File: # /u01/app/oracle/product/9.2.0/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = alex)(PORT = 1521)) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/9.2.0) (PROGRAM = extproc) ) (SID_DESC = (PROGRAM = hsodbc) (ORACLE_HOME = /u01/app/oracle/product/9.2.0) (SID_NAME = myodbc3) (ENVS=LD_LIBRARY_PATH = /usr/local/lib:/u01/app/oracle/product/9.2.0/lib) ) )

Correct the ORACLE_HOME entry and the ENVS entry according to your installation.

• ORACLE_HOME must point to your ORACLE_HOME directory
• The ENVS string contains entries for the LD_LIBRARY_PATH
• The minimum of the LD_LIBRARY_PATH setting must contain the Oracle library and the odbc library path; both 32 bit
• HSODBC is a 32 bit libray and thus it needs a 32 bit ODBC driver, however, see NOTE below

Due to a staging problem with Oracle 9iR2 this hsodbc configuration must have the $ORACLE_HOME/lib directory instead of the lib32 directory in the ENVS path setting; the lib32 directory does not contain ALL required libraries! With Oracle 10g all libraries are placed in the lib32 directory again. A correct setting of the path can be verified by typing $ hsodbc Oracle Corporation --- WEDNESDAY FEB 28 2007 12:39:38.741 Heterogeneous Agent Release 9.2.0.8.0 - Production Built with Driver for ODBC If the LD_LIBRARY_PATH contains the correct libraries, the version number of the HSODBC agent should be displayed.

Be aware to restart the listener after changing the listener.ora!

# lsnrctl stop
# lsnrctl start

The tnsnames.ora needs an entry for the HSODBC alias:

HSODBC.IDEVELOPMENT.INFO =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS =
        (PROTOCOL = TCP)
        (HOST = alex.idevelopment.info)
        (PORT = 1521)
      )
    )
    (CONNECT_DATA =
      (SID= myodbc3)
    )
    (HS=OK)
  )

The domain of the TNS alias can differ from the one used above (idevelopment.info), depending on the parameter in the sqlnet.ora:

NAMES.DEFAULT_DOMAIN = idevelopment.info

But the important entry is the (HS=) or (HS=OK) key word. This key word must be added manually and keep in mind that opening the Net Configuration Assistants will remove this entries from your tnsnames.ora file! The (HS=OK) parameter must be outside the SID section and specifies that this connector uses the Oracle Heterogeneous Service Option.

After adding the tnsnames alias and restarting the listener, a connectivity check is to use: tnsping <alias>:

# tnsping hsodbc
TNS Ping Utility for Solaris: Version 9.2.0.8.0 - Production on 28-FEB-2007 12:52:39

Copyright (c) 1997, 2006, Oracle Corporation.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/9.2.0/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = alex.idevelopment.info) (PORT = 1521))) (CONNECT_DATA = (SID= myodbc3)) (HS=OK))
OK (70 msec)

should come back with a successful message (as shown above).

The SID name used in this article to configure Oracle HS functionality is called myodbc3. This was defined in the ODBC driver manager configuration file: /usr/local/etc/odbc.ini. There are some restrictions on how to name the SID (described in the Net Administrators Guide in detail). A short note about the name of the SID — don't use dots in the SID and keep it short!

The SID is also relevant for the init.ora file of the gateway. The name of the file is init<SID>.ora. In this article it is called initmyodbc3.ora. The file should be located in $ORACLE_HOME/hs/admin.

It should contain the following entries:

$ORACLE_HOME/hs/admin/initmyodbc3.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 = myodbc3 HS_FDS_TRACE_LEVEL = off HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so # # ODBC specific environment variables # set ODBCINI=/usr/local/etc/odbc.ini #

Short explanation of the parameters:

• HS_FDS_CONNECT_INFO — points to the ODBC DSN configured earlier in this article.
• HS_FDS_SHAREABLE_NAME — points to the ODBC Driver Manager library at $unixODBC/lib/<ODBC_Driver_MANAGER_LIB>.
• The set ODBCINI=... points to the location of an odbc.ini file you want to use with this hsodbc configuration.

We first create a database link that connects us to the MySQL database. We will be connecting to the test database (running on linux2) as the oracle@alex user. The syntax to create the database link:

CREATE [public] DATABASE LINK <name> 
CONNECT TO <UID> IDENTIFIED BY <pwd> USING '<tnsalias>';

$ sqlplus system/manager

SQL> CREATE PUBLIC DATABASE LINK testdb
  2  CONNECT TO "oracle" IDENTIFIED BY "manager" USING 'HSODBC';

The db link name is testdb. Username and password must be in double quotes, because the username and password are case sensitive. 'HSODBC' points to the alias in the tnsnames.ora file that calls the HS subsystem (gateway). If everything is configured well, a select of a table should be successful.

Now let's test the HS agent by performing some DML to our emp table created earilier in this article.

Tables within the MySQL database are case sensitive. Thus table names must be surrounded by double quotes.

SQL> column id format 999
SQL> column name format a15

SQL> select * from "emp"@testdb;

  id name
---- ---------------
 100 Jeff
 100 Melody

SQL> insert into "emp"@testdb values(100, 'Alex');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from "emp"@testdb;

  id name
---- ---------------
 100 Jeff
 100 Melody
 100 Alex