Translate this Page

 

In Oracle DB World, sometime we need an interface to connect From Oracle DB to MySQL DB. Oracle have Heterogenous Service ODBC to help us to do this task. In this posting, I am using Linux Redhat, Oracle DB 10g and MySQL 4. Let’s play :

Part I: Setting up the ODBC driver
———————————-
1. copy file mysql-connector-odbc-3.51.12-1.i586.rpm to Oracle DB server

2. install odbc for mysql.

rpm -i mysql-connector-odbc-3.51.12-1.i586.rpm

3. Edit the file /etc/odbc.ini
Add the Data Source of MySQL DB that we want to connect. I create data source named “trans”.

[ODBC Data Sources]
mysql = MyODBC 3.51 Driver DSN

[trans]
Driver = /usr/lib/libmyodbc3.so
Description = mysql connection to test
SERVER = 172.16.11.213
PORT = 3306
USER = root
Password = password
Database = Transaksi_DB
OPTION = 3
SOCKET =

Part II: How to configure HSODBC
———————————
Configure HSODBC in Oracle:
1. Configure listener.ora in $ORACLE_HOME/network/admin, add :

(SID_DESC =
(PROGRAM = hsodbc)
(ORACLE_HOME = /oracle/app/product/10.2.0)
(SID_NAME = trans)
)

2. Configure tnsnames.ora in $ORACLE_HOME/network/admin, add :

trans=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
)
(CONNECT_DATA =
(SID= trans))
(HS=OK)
)

3. Configure inithsodbc.ora in $ORACLE_HOME/hs/admin, add :

# 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 = trans
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/lib/libmyodbc3.so
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#

4. Setup environment variables

export ODBCINI=/etc/odbc.ini
export LD_LIBRARY_PATH=/usr/lib:$ORACLE_HOME/lib

5. Create Database link in Oracle DB to connect to MySQL DB.

Create database link translink
connect to "root" identified by "password" using 'trans';

6. Testing

Select * from table_name@translink

7. If can see the output,then finish. Have a nice try.

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

   
© 2012 LevICORP's webnote Suffusion theme by Sayontan Sinha