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.
