敗家貓

 測試的環境
os:centos 3.2
oracle:10.1.0.2
mysql:4.1.12

 1、檢查所需的軟体(在光碟片中都有)，有少的請自行安裝

mysql-4.1.12-3.RHEL4.1 mysqlclient10-3.23.58-4.RHEL4.1 mysql-bench-4.1.12-3.RHEL4.1 mysql-server-4.1.12-3.RHEL4.1 mysql-devel-4.1.12-3.RHEL4.1 unixODBC-kde-2.2.9-1 MyODBC-2.50.39-21.RHEL4.1 unixODBC-devel-2.2.9-1 unixODBC-2.2.9-1

2、建立mysql的測試帳號

mysql> grant all privileges on *.* to oracle@"%" identified by 'test' with grant option;  mysql> flush privileges;

3、設定odbc
/etc/odbcinst.ini

# Included in the unixODBC package [PostgreSQL] Description     = ODBC for PostgreSQL Driver          = /usr/lib/libodbcpsql.so Setup           = /usr/lib/libodbcpsqlS.so FileUsage       = 1 # Driver from the MyODBC package # Setup from the unixODBC package [MySQL] Description     = ODBC for MySQL Driver          = /usr/lib/libmyodbc.so Setup           = /usr/lib/libodbcmyS.so FileUsage       = 1 #自已加上一組 [MySQL-test] Description     = ODBC for MySQL Driver          = /usr/lib/libmyodbc.so Setup           = /usr/lib/libodbcmyS.so FileUsage       = 1

/etc/odbc.ini

#加上要測試的sid [MySQL-test] Description     = MySQL database test Driver          = MySQL Server          = localhost Database        = test Port            = 3306 Socket          = Option          = Stmt            = USER            = oracle Password        = test

4、在oracle的環境中加上odbc的變數

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

5、註冊我們測試的sid

[oracle@odb admin]$cd $ORACLE_HOME/hs/admin [oracle@odb admin]$vi  initMySQL-test.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 = MySQL-test HS_FDS_TRACE_LEVEL = off HS_FDS_SHAREABLE_NAME = /usr/lib/libodbc.so # # ODBC specific environment variables # set ODBCINI=/etc/odbc.ini # # Environment variables required for the non-Oracle system #

6、增加到tnsname.ora

MySQL-test =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.111)(PORT = 1522))     )     (CONNECT_DATA =       (SID = MySQL-test)     )     (HS=OK)   )

7、增加到listener.ora

SID_LIST_MySQL-test =   (SID_LIST =     (SID_DESC =       (SID_NAME = MySQL-test)       (ORACLE_HOME = /u01/app/oracle/product/10.1.0/db_1)       (LD_LIBRARY_PATH=/u01/app/oracle/product/10.1.0/db_1/lib:/usr/lib)       (PROGRAM = hsodbc)     )   ) MySQL-test =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.111)(PORT = 1522))     )   )

8、使用sqlplus登入oracle，並且db link (要注意 " 和 ' 的區分)

create database link mysql connect to "oracle" identified by "test" using 'MySQL-test';

9、進行測試

SQL> select * from emp@mysql1; select * from emp@mysql1               * ERROR at line 1: ORA-00942: table or view does not exist [Generic Connectivity Using ODBC][unixODBC][TCX][MyODBC]Table 'test.EMP' doesn't exist (SQL State: S1000; SQL Code: 1146) ORA-02063: preceding 2 lines from MYSQL1 SQL> select * from 'emp'@mysql; select * from 'emp'@mysql               * ERROR at line 1: ORA-00903: invalid table name SQL> select * from "emp"@mysql;         id name ---------- -------          1 OK          2 OK+OK

PS:oracle會區分大小寫，請注意

參考資料
http://www.linuxhall.org/modules.php?name=News&file=article&sid=32
http://www.idevelopment.info/data/Oracle/DBA_tips/Heterogeneous_Services/HETERO_2.shtml
http://www.odba.idv.tw/mysql_oracle.htm
http://www.cnoug.org/viewthread.php?tid=80436&highlight=odbc
http://www.cnoug.org/viewthread.php?tid=32128&highlight=odbc

心得:整個設定並不難，最容易出錯的地方是連結到mysql的權限，在一開始測試時，可讓mysql對方連結的權限開到最大。另一個是odbc的sid值，名字最好都設成相同，以免產生不必要的問題發生。除了可連mysql當然也可連ms sql、db2等等