通過HSODBC訪問mysql

一、環境

OS：Linux myhostname 2.6.9-42.ELsmp #1 SMP Sat Aug 12 09:39:11 CDT 2006 i686 i686 i386 GNU/Linux

      CentOS release 4.4 (Final)

Oracle：Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod，資料庫已經安裝好

mysql：5.1.34-community for windows

 

二、安裝配置

1. 安裝unixODBC，用root使用者

rpm -Uvh unixODBC-2.2.12-1.el4s1.1.i386.rpm

 

2. 安裝mysql ODBC，用root使用者

rpm -Uvh mysql-connector-odbc-5.1.5-0.i386.rpm

 

3. 安裝oracle gateway，用oracle使用者

我裝的是10201_gateways_linux32.zip

unzip 10201_gateways_linux32.zip

cd gateways

./runInstaller

安裝方法和oracle db 軟體一樣，我把gateway和db裝一起了，共用一個OracleHOME

 

4. 配置/etc/odbc.ini

[DSName]
Driver         =/usr/lib/libmyodbc5.so
Description    =MySQL
Server         =xxx.xxx.xxx.xxx
Port           =3306
User           =root
UID            =root
Password   = mypass
Database    =mysqldbname
Option         =3
Socket         =
charset        =utf8

 

測試ODBC

isql -v DSName root mypass

 

5. 配置$ORACLE_HOME/hs/admin/initDSName.ora

HS_FDS_CONNECT_INFO = DSName
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME = /usr/lib/libmyodbc5.so

 

6. 配置listener.ora，加紅色部分

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /usr/u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = BOSS)
      (ORACLE_HOME = /usr/u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME = BOSS)
    )
    (SID_DESC =
      (SID_NAME = phpcms)
      (ORACLE_HOME = /usr/u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = hsodbc)
    )
  )

 

7. 配置tnsnames.ora，添加

DSName =
(DESCRIPTION =
       (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.125)(PORT = 1521))
       )
       (CONNECT_DATA = (SERVICE_NAME = DSName))
       (HS = OK)
)

 

8. 重啟監聽器並測試

lsnrctl reload

 

lsnrctl service

 

Service "DSName" has 1 instance(s).
  Instance "DSName", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3 refused:0
         LOCAL SERVER
The command completed successfully

 

tnsping DSName

 

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.125)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DSName)) (HS = OK))
OK (0 msec)

 

9. 建立dblink

CREATE PUBLIC DATABASE LINK linkname
 CONNECT TO "root"
 IDENTIFIED BY <PWD>
 USING 'DSName';

10. 測試

select "name" from t1@linkname;

 

三、遺留問題

1. 字元集問題，最好oracle和mysql是utf8，否則中文有問題

2. text欄位會報錯：

select "textcol" from t1@linkname;

 

ORA-28500: 串連 ORACLE 到非 Oracle 系統時返回此資訊:
[Generic Connectivity Using ODBC][MySQL][ODBC 5.1 Driver][mysqld-5.1.34-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"t1" WHERE "id"=1' at line 1 (SQL State: 37000; SQL Code: 1064)
ORA-02063: 緊接著 2 lines (起自 DSName)

 

本來想用datadirect的mysql ODBC試試，可只支援mysql enterprise版本，實在不好找，以後有機會再說吧。