windows下oracle10访问mysql

测试环境：

win2003 32,oracle 10.2.0.1,dg4odbc 11.1.0.7,mysql odbc5.1.6

ubuntu 9.04 server,mysql 5.0

 

oracle连接mysql有两种方式，hsodbc和dg4odbc，简单说dg4odbc是hsodbc的升级，在用hsodbc时遇到两个问题，首先是64位版的oracle没有hsodbc.exe这个文件，网上说把32位版的文件复制也能用；然后是看不到mysql的char或是varchar类型的字段，要命的问题，据说用odbc3.5.1就没事。

 

用dg4odbc遇到double quotes问题，原文是引用：

The syntax problem is cuased when MySQL DB is not running in ANSI mode as MYSQL then uses back ticks instead of double quotes to quote obejct names. HSODBC 11.1.0.6 does not accept it and always forces to use double quotes.

 

解决方法：

To work around this issue you need to patch DG4ODBC to 11.1.0.7 (included in the RDBMS patchset) and you also need to apply a patch (6332478) to your 10.2.0.4 Oracle database. If this patch does not exist for your platform, you can request a backport for your 10.2.0.4 release.
Another option would be to switch the MySQL db to ANSI (permanently) or for the DG4ODBC session only.

 

DECLARE
ret integer;
c integer;
BEGIN
c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@mysql;
DBMS_HS_PASSTHROUGH.PARSE@mysql(c, 'SET SESSION SQL_MODE=''ANSI_QUOTES'';');
ret := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@mysql(c);
dbms_output.put_line(ret ||' passthrough output');
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@mysql(c);
END;

 

https://kr.forums.oracle.com/forums/thread.jspa?threadID=935938&tstart=0

 

https://cn.forums.oracle.com/forums/thread.jspa?threadID=2319351&tstart=0&start=0

 

不升级到10.2.0.4就只能在会话开始时设置mysql的sql模式为ansi。

 

详细记下步聚，备忘。

 

1、下载mysql 的odbc驱动   http://mysql.cs.pu.edu.tw/downloads/connector/odbc/index.html

 

2、配置odbc数据源，注意使用系统DSN ，取名为mysqlora，字符集设置成和要连接的mysql数据库一样。

 

 

3、在D:\app\Administrator\product\11.1.0\db_1\hs\admin下建立initmysqlora.ora文件

内容如下：

HS_FDS_CONNECT_INFO = mysqlora
HS_FDS_TRACE_LEVEL = 0

 

4、在D:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN下修改 listener.ora

增加：

 

    (SID_DESC =

      (SID_NAME = mysqlora)

      (ORACLE_HOME = D:\app\Administrator\product\11.1.0\db_1 )

      (PROGRAM = dg4odbc)

    )

 

修改后listener.ora 内容如下：

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = d:\oracle\product\10.2.0\db_1 )

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (SID_NAME = mysqlora)

      (ORACLE_HOME = D:\app\Administrator\product\11.1.0\db_1 )

      (PROGRAM = dg4odbc)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

    )

  )

 

6、重启监听

 

5、在D:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN下 修改tnsnames.ora

增加：

mysqlora =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
            (CONNECT_DATA =
            (SID=mysqlora)
            )
           (HS =ok)
    )

6、创建dblink，注意引号

 

create public database link mysql
  connect to "username"
  identified by "passwd"
  using 'mysqlora';

 

7、创建存储过程

create or replace procedure mysql_mode is
  ret integer;
  c   integer;
BEGIN
  c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@mysql;
  DBMS_HS_PASSTHROUGH.PARSE@mysql(c, 'SET SESSION SQL_MODE=''ANSI_QUOTES'';');
  ret := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@mysql(c);
  dbms_output.put_line(ret || ' passthrough output');
  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@mysql(c);
END;

 

8、访问mysql 库

 

call mysql_mode(); --会话开始时执行。

select "username" from "user"@mysql;

 

相关问题链接：

Oracle HS (Heterogeneous Services)深入解析 及协同Gateway工作流程

https://forums.oracle.com/forums/thread.jspa?threadID=2130517

 

oralce与其他数据库的异构连接