ora-28500 ora-02063 mysql_通过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

USING ‘DSName‘;

10. 测试

select "name" from [email protected];

三、遗留问题

1. 字符集问题，最好oracle和mysql是utf8，否则中文有问题

2. text字段会报错：

select "textcol" from [email protected];

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

原文：http://www.jb51.net/article/20371.htm