通过odbc配置oracle到mysql的连接

**************
public信息
**************

SQL> select * from dba_db_links;

OWNER                          DB_LINK                                  USERNAME                       HOST                                               CREATED
------------------------------ ---------------------------------------- ------------------------------ -------------------------------------------------- ------------
PUBLIC                         TOMOT                                    CRM                            (DESCRIPTION =                                     08-AUG-17
                                                                                                           (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.245.3
                                                                                                       2)(PORT = 1521))
                                                                                                           (CONNECT_DATA =
                                                                                                             (SERVER = DEDICATED)
                                                                                                             (SERVICE_NAME = orcl)
                                                                                                           )
                                                                                                         )

PUBLIC                         GWTOCRMHIS                               pifuser                        toCrmHis                                           01-AUG-17
PUBLIC                         LINK2TOUGU                               syn                            TOTOUGU                                            11-OCT-18

OWNER                          DB_LINK                                  USERNAME                       HOST                                               CREATED
------------------------------ ---------------------------------------- ------------------------------ -------------------------------------------------- ------------
PUBLIC                         GWTOOTC                                  pifuser                        toOtc                                              30-JUN-17
PUBLIC                         GWTOKCBL                                 pifuser                        toKCBL                                             30-JUN-17
PUBLIC                         GZ20                                     GZREADER                        (DESCRIPTION =                                    30-JUN-17
                                                                                                           (ADDRESS = (PROTOCOL = TCP)(HOST = 10.17.29.67
                                                                                                       )(PORT = 1521))
                                                                                                           (CONNECT_DATA =
                                                                                                             (SERVER = DEDICATED)
                                                                                                             (SERVICE_NAME = guzhibk)
                                                                                                           )
                                                                                                         )


OWNER                          DB_LINK                                  USERNAME                       HOST                                               CREATED
------------------------------ ---------------------------------------- ------------------------------ -------------------------------------------------- ------------
PUBLIC                         GWTOCOS                                  pifuser                        toCos                                              30-JUN-17
PUBLIC                         GWTOCCRM                                 pifuser                        toCcrm                                             30-JUN-17
PUBLIC                         GWTOCRMOF                                pifuser                        toCrmof                                            30-JUN-17
PUBLIC                         GWTOCRM                                  pifuser                        toCrm                                              30-JUN-17
PUBLIC                         GWTOSJZD                                 pifuser                        dg4msql                                            30-JUN-17





/oracle/app/oracle/product/11.2.0.4/tg_1/network/admin
/oracle/app/oracle/product/11.2.0.4/tg_1/dg4msql/admin

create public database link KBSSACCT connect to "sa" IDENTIFIED by "bushisa" using 'dg4msql';

/oracle/app/oracle/product/11.2.0.4/tg_1/network/admin
/oracle/app/oracle/product/11.2.0.4/tg_1/dg4msql/admin




TOTOUGU =
  (DESCRIPTION=
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.232.220)(PORT = 1521))
   )
    (CONNECT_DATA =
      (SERVICE_NAME = totougu)
    )
    (HS=OK)
  )
  
SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=dg4msql)
         (ORACLE_HOME=/oracle/app/oracle/product/11.2.0.4/tg_1)
         (PROGRAM=dg4msql)
      )
     (SID_DESC=
         (SID_NAME=toCrm)
         (ORACLE_HOME=/oracle/app/oracle/product/11.2.0.4/tg_1)
         (PROGRAM=dg4msql)
      )
      (SID_DESC=
         (SID_NAME=toCrmof)
         (ORACLE_HOME=/oracle/app/oracle/product/11.2.0.4/tg_1)
         (PROGRAM=dg4msql)
      )
      (SID_DESC=
         (SID_NAME=toCos)
         (ORACLE_HOME=/oracle/app/oracle/product/11.2.0.4/tg_1)
         (PROGRAM=dg4msql)
      )
      (SID_DESC=
         (SID_NAME=toCcrm)
         (ORACLE_HOME=/oracle/app/oracle/product/11.2.0.4/tg_1)
         (PROGRAM=dg4msql)
      )
      (SID_DESC=
         (SID_NAME=toKCBL)
         (ORACLE_HOME=/oracle/app/oracle/product/11.2.0.4/tg_1)
         (PROGRAM=dg4msql)
      )
      (SID_DESC=
         (SID_NAME=toOtc)
         (ORACLE_HOME=/oracle/app/oracle/product/11.2.0.4/tg_1)
         (PROGRAM=dg4msql)
      )
      (SID_DESC=
        (SID_NAME=totougu)
        (ORACLE_HOME=/oracle/app/oracle/product/11.2.0.4/dbhome_1)
        (PROGRAM=dg4odbc)
        (ENVS=LD_LIBRARY_PATH=/usr/lib64:/oracle/app/oracle/product/11.2.0.4/dbhome_1/lib)
      )
  )
  
alter public database link LINK2TOUGU connect to syn identified by "syn";

create public database link LINK2TOUGU2 connect to "syn" IDENTIFIED by "syn" using 'TOTOUGU';




[dbjf-crm-pifda:oracle]:/oracle/app/oracle/product/11.2.0.4/dbhome_1/hs/admin>cat inittotougu.ora
HS_FDS_CONNECT_INFO=totougu # /etc/odbc.ini中的配置
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_FDS_REMOTE_DB_CHARSET=utf8
HS_KEEP_REMOTE_COLUMN_SIZE=ALL
HS_NLS_LENGTH_SEMANTICS=CHAR
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
# Oracle数据库字符集,根据情况进行设置
#HS_FDS_MAP_NCHAR = TRUE
#HS_SQL_HANDLE_STMT_REUSE = TRUE
HS_NLS_NCHAR = UCS2
#UCS2

# ODBC env variables
set ODBCINI=/etc/odbc.ini


[dbjf-crm-pifda:oracle]:/oracle/app/oracle/product/11.2.0.4/dbhome_1/hs/admin>cat /etc/odbc.ini
[totougu]
# 如上为ODBC的连接标识
Driver = /usr/lib64/libmyodbc5.so
# 指定驱动文件路径
Description = Connect to MySQL DB of TouGu system 
# 描述
SERVER = 10.16.244.218
# MySQL服务器IP
PORT = 3306
# MySQL服务器端口
USER = syn
# MySQL数据库用户
PASSWORD = syn
# MySQL数据库密码
DATABASE = robot_portfolio_core
# MySQL数据库的库,注意大小写
OPTION = 0
TRACE = OFF
CHARSET = utf8
STMT = SET NAMES 'utf8'
# 如上两行需要设置为与MySQL数据库字符集相同


export ODBCINI=/etc/odbc.ini
export LD_LIBRARY_PATH=/usr/lib64:/oracle/app/oracle/product/11.2.0.4/dbhome_1/lib
isql totougu -v






[totougu]
Driver = /usr/lib64/libmyodbc5.so
Description = Connect to MySQL DB of TouGu system
SERVER = 10.16.244.218
PORT = 3306
USER = syn
PASSWORD = syn
DATABASE = robot_portfolio_core
OPTION = 0
TRACE = OFF
CHARSET = utf8
STMT = SET NAMES 'utf8'




[root@dbjf-crm-pifda ~]# odbcinst -j
unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
[root@dbjf-crm-pifda ~]# 




root@dbjf-crm-pifda ~]# isql totougu -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> 
[root@dbjf-crm-pifda ~]# 
[root@dbjf-crm-pifda ~]# isql totougu -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> show tables;
+-----------------------------------------------------------------+
| Tables_in_robot_portfolio_core                                  |
+-----------------------------------------------------------------+
| cart_item                                                       |
| content                                                         |
| customer                                                        |
| customer_cart                                                   |
| customer_fof                                                    |
| fund_dividend                                                   |
| fund_dividend_tmp                                               |
| fund_monthly_return                                             |
| fund_performance                                                |
| fund_return                                                     |
| fund_split                                                      |
| fund_split_tmp                                                  |
| fund_value                                                      |
| fund_value20190228                                              |
| fund_value_tmp                                                  |
| fund_weekly_return                                              |
| holiday_period                                                  |
| index_type                                                      |
| portal_role                                                     |
| portal_user_role                                                |
| portfolio                                                       |
| portfolio_adjustment_history                                    |
| portfolio_detail                                                |
| portfolio_max_drawdown                                          |
| portfolio_monthly_return                                        |
| portfolio_performance                                           |
| portfolio_return                                                |
| portfolio_risk_sequence                                         |
| portfolio_setting                                               |
| portfolio_value                                                 |
| portfolio_value20190228                                         |
| portfolio_weekly_return                                         |
| site                                                            |
| site_jwt_key                                                    |
| sub_fund                                                        |
| sub_fund_attachment                                             |
| sub_fund_class                                                  |
| sub_fund_detail                                                 |
| sub_fund_detail_tmp                                             |
| sub_fund_estimated_fee_rate                                     |
| sub_fund_open_date                                              |
| sub_fund_site                                                   |
| sub_fund_tmp                                                    |
| update_log                                                      |
| version                                                         |
+-----------------------------------------------------------------+
SQLRowCount returns 45
45 rows fetched
SQL> 



 

posted @ 2019-08-29 14:21  dayu.liu  阅读(956)  评论(0编辑  收藏  举报