How to Setup Generic Connectivity - HSODBC - to MySQL

출처  :  http://metalink.oracle.com




How to Setup Generic Connectivity - HSODBC - to MySQL [ID 260981.1]
--------------------------------------------------------------------------------
 
수정 날짜 20-OCT-2010     유형 BULLETIN     상태 PUBLISHED  




PURRPOSE
--------------------------------------------------------------------------------

This is a quick step by step guide on how to setup generic connectivity
on SUN Solaris connecting to a MySQL database with MySQL ODBC driver.



SCOPE & APPLICATION
--------------------------------------------------------------------------------

This note describes how to set up HSODBC (generic connectivity) for SUN using Oracle 9.2 release.
It is similar for 10g release.

There are a few issues with MySQL TEXT columns. They are mapped to Oracle long data types.
The ODBC driver does not properly handle this kind of data type. MySQL have not yet provided a fix
for this issue. 2 workarounds can be used: use OPENLINK ODBC driver  (www.openlinksw.com)
or change the text data type within MySQL to varchar.



How to Setup HSODBC (generic connectivity) on SUN with MySQL ODBC
--------------------------------------------------------------------------------
 
This note is divided into two different parts:
Part I describes how to set up the ODBC driver
Part II describes the configuration process of HSODBC



Part I: Setting up the ODBC driver
--------------------------------------------------------------------------------
Download the MySQL ODBC driver from www.mysql.org. This driver needs a driver manager.
According to the documentation this can be downloaded from www.unixodbc.org.

Installing the driver (MyODBC-3.51.06-sun-solaris2.9-sparc) is simple.
Just untar it into a directory.
Assuming that this directory is
/export/home/user/kgronau/mysql/MyODBC-3.51.06-sun-solaris2.9-sparc (=$MySQL).


The same must be done with the driver manager from unixODBC. Untar
it into a directory like: /export/home/user/kgronau/mysql/unixODBC ($unixODBC).


Setting up the ODBC connection to the MySQL database requires an odbc.ini file.
It could look like:
[mysql]
Driver        = /export/home/user/kgronau/mysql/MyODBC-3.51.06-sun-solaris2.9-sparc/libmyodbc3.so
Description  = mysql connection to test
SERVER       =
PORT         = 3306
USER         = root
Password     =
Database     = Test
OPTION       = 3
SOCKET       =


Make sure that the library mentioned in DRIVER exists.

To test the connection, unixODBC provides a test tool called isql.


In the unix shell make sure the LD_LIBRARY_PATH contains both library paths;
the LD_LIBRARY_PATH of the unixODBC lib directory and the MySQL ODBC driver lib.
LD_LIBRARY_PATH=/export/home/user/kgronau/mysql/MyODBC-3.51.06-sun-solaris2.9-sparc:/export/home/user/kgronau/mysql/unixODBC/lib
The next value that needs to be defined is the ODBCINI variable:
ODBCINI=/export/home/user/kgronau/mysql/unixODBC/odbc.ini


If both are set correctly, go to the $unixODBC/bin directory and execute:
isql  root  -v
You should get a window like:

+---------------------------------------+
| Connected!                                          |
|                                                           |
| sql-statement                                       |
| help [tablename]                                  |
| quit                                                      |
|                                                            |
+---------------------------------------+
SQL>





Part II: How to configure HSODBC
--------------------------------------------------------------------------------
(This section assumes that everything is done in the Oracle user account that starts the listener!)

In general the following things must be configured:
1) listener
2) tnsnames
3) init.ora of the hs subsystem
4) environment
5) Oracle database


1) The listener needs a new SID entry like the following:
    (SID_DESC =
      (PROGRAM = hsodbc)
      (ORACLE_HOME = /export/home/oracle/app/32bit/product/10.1.0)
      (SID_NAME = mysql)
      (ENVS=LD_LIBRARY_PATH=/export/home/oracle/app/32bit/product/10.1.0/lib32:/ex
port/home/user/kgronau/mysql/MyODBC-3.51.06-sun-solaris2.9-sparc:/export/home/us
er/kgronau/mysql/unixODBC/lib)
       )


Please correct the ORACLE_HOME entry and the ENVS entry according to your installation.

ORACLE_HOME must point to your ORACLE_HOME directory and 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.

ATTENTION:
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

then pressing  at the SUN console. If the LD_LIBRARY_PATH contains the correct libraries,
the version number of HSODBC should be displayed.

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


2) The tnsnames.ora needs an entry for the HSODBC alias:
mysql.de.oracle.com=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
    )
    (CONNECT_DATA =
      ( SID= mysql))
      (HS=OK)
  )


The domain of the tns alias can differ from the one used above (de.oracle.com),
depending on the parameter in the sqlnet.ora:
NAMES.DEFAULT_DOMAIN = de.oracle.com


But the important entry is the (HS=)or (HS=OK) key word. This key word must be added manually
and 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 .
tnsping hsodbc should come back with a successfull message.



3) init.ora of the gateway:
The SID to use HS functionality is called in this example hsodbc. There are some restrictions
how to name the SID (described in the Net Administrators Guide in detail).
At this place only a short note: 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.ora. 
In this example it is called initmysql.ora.
The file is located at $ORACLE_HOME/hs/admin.

It should contain the following entries:
# 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 = mysql
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /export/home/user/kgronau/mysql/unixODBC/lib/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=/export/home/user/kgronau/mysql/unixODBC/odbc.ini
#

Short explanation of the parameters:
HS_FDS_CONNECT_INFO points to the ODBC DSN configured in PART I of this note.
HS_FDS_SHAREABLE_NAME points to the ODBC Driver Manager library at
$unixODBC/lib/.

The set ODBCINI=... points to the location of an odbc.ini
file you want to use with this hsodbc configuration.


4) Configuring the environment:
Normally there is nothing to configure anymore. But to test the odbc connectivity for the
Oracle user the following should be performed:
Set the ODBCINI and LD_LIBRARY_PATH as mentioned in the ODBC section above.
Now execute as the ORACLE User (who starts the listener) the isql program like already done in section I.


5)Configuring the Oracle database
The only thing that must be done here is to create a database link:
connect with the username/password that has sufficient rights to create a database link (i.e. system).
The syntax is:
create [public] database link 
  connect to  identified by  using '';


In other words, to connect to the MySQL database configured in the last steps, the syntax must be:
CREATE DATABASE LINK mysql
 CONNECT TO "root" IDENTIFIED BY "pasword" USING 'mysql';


The db link name is mysql. Username and password must be in double quotes,
because the username and password are case sensitive. 'mysql' points to the alias in the tnsnames.ora
file that calls the HS subsystem. "@mysql" points to the name of the database link to the MySQL database.
If everything is configured well, a select of a table should be successful:

(Side note: Tables at the MySQL database are case sensitive.Thus tablenames must be surrounded
by double quotes).