欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

通过HSODBC访问mysql的实现步骤

程序员文章站 2023-12-01 08:59:58
一、环境 os:linux myhostname 2.6.9-42.elsmp #1 smp sat aug 12 09:39:11 cdt 2006 i686 i686...
一、环境
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版本,实在不好找,以后有机会再说吧。