通过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版本，实在不好找，以后有机会再说吧。

上一个：php 读取mysql数据库三种方法
下一个：mysql4.0升级到mysql5(4.1)，解决字符集问题