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