Oracle access MySql via unixODBC by DBLINK

Oracle access MySql via unixODBC by DBLINK: I asked how to connect oracle to remote mysql. I used hsodbc before, but I cannot display the fields with different attributes. oracle does not seem to have a transparent gateway for mysql. Is there any way to remotely obtain mysql Data? My oracle dedicated analysis will call mysql Data, so I need to obtain mysql Data and do not want to synchronize ------------. Do you understand MySql is limited to general SQL statements, others do not understand at all; but I know there are at least two or more ways to achieve this. It should use unixODBC to access MySql Data. The following is the test process. Environment: MySql5.6 For Windows2003Oracle10G For CentOS5.7 0. mySql installation and deployment data preparation MySql installation and deployment on windows2003 is very simple, just one step at a time. Here we skipped, we will create a database user gtlions, create a table access_by_oracle and insert several pieces of test data. C: \ Documents ents and Settings \ Administrator> mysql-u root-pEnter password: ****** Welcome to the MySQL monitor. commands end with; or \ g. your MySQL connection id is 2 Server version: 5.6.10-log MySQL Community Server (GPL) Copyright (c) 2000,201 3, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners. type 'help; 'or' \ H' for help. type '\ C' to clear the current input statement. mysql> help For information about MySQL products and services, visit: http://www.mysql.com/For Developer information, including the MySQL Reference Manual, visit: http://dev.mysql.com/To Buy MySQL Enterprise support, training, or other products, visit: https://shop.mysql.com/ List of all MySQL commands: Note that all text commands must be first on line and end ';'? (\?) Synonym for 'help '. clear (\ c) Clear the current input statement. connect (\ r) Reconnect to the server. optional arguments are db and host. delimiter (\ d) Set statement delimiter. ego (\ G) Send command to mysql server, display result vertically. exit (\ q) Exit mysql. same as quit. go (\ g) Send command to mysql server. help (\ h) Display this help. notee (\ t) Don't write into outfile. print (\ p) Print curren T command. prompt (\ R) Change your mysql prompt. quit (\ q) Quit mysql. rehash (#) Rebuild completion hash. source (\.) execute an SQL script file. takes a file name as an argument. status (\ s) Get status information from the server. tee (\ T) Set outfile [to_outfile]. append everything into given outfile. use (\ u) Use another database. takes database name as argument. charset (\ C) Switch to another charse T. might be needed for processing binlogwith multi-byte charsets. warnings (\ W) Show warnings after every statement. nowarning (\ w) Don't show warnings after every statement. for server side help, type 'help CONTENTS' mysql> create user 'gtlions' @ '%' identified by '123456'; ERROR 000000 (HY000 ): operation create user failed for 'gtlions' @ '%' mysql> create user 'gtlions' identified by '123456'; Query OK, 0 Rows affected (0.00 sec) mysql> grant usage on *. * TO 'gtlion' identified by '000000' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0; Query OK, 0 rows affected (000000 sec) mysql> create database if not exists 'gtlions'; Query OK, 1 row affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON 'gtlions '. * TO 'gtlions'; Query OK, 0 rows affected (0. 00 sec) mysql> exitByeC: \ Documents ents and Settings \ Administrator> mysql-u gtlions-pEnter password: ****** Welcome to the MySQL monitor. commands end with; or \ g. your MySQL connection id is 22 Server version: 5.6.10-log MySQL Community Server (GPL) Copyright (c) 2000,201 3, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. ot Her names may be trademarks of their respectiveowners. type 'help; 'or' \ H' for help. type '\ C' to clear the current input statement. mysql> use gtlionsDatabase changedmysql> create table access_by_oracle (id int (5), name char (20); Query OK, 0 rows affected (0.09 sec) mysql> insert into access_by_oracle values (1, 'gtlions'); Query OK, 1 row affected (0.00 sec) mysql> insert into access_by_oracle value S (2, 'laidye '); Query OK, 1 row affected (0.00 sec) mysql> select * from access_by_oracle; + ------ + --------- + | id | name | + ------ + --------- + | 1 | gtlions | 2 | laidye | + ------ + --------- + 2 rows in set (0.00 sec) mysql> Well, MySql has been installed and deployed and ready for external access. 1. we need to use unixODBC to access the Oracle access end configuration. If the system has its own installation, it can be used directly. If it does not have to be installed, you can use yum or source code package, because the system has been installed here, I will not describe it again. If you need yum to install it, the command is as follows: yum-y unixODBC the following package has been installed on my local machine: [root @ gtserdev ~] # Rpm-qa | edit/etc/odbc after installation of grep unixODBCunixODBC-devel-2.2.11-10.el5unixODBC-libs-2.2.11-10.el5unixODBC-2.2.11-10.el5unixODBC-kde-2.2.11-10.el5. ini: [oracle @ gtserdev admin] $ cat/etc/odbc. ini [test] Driver =/usr/lib/libmydbc3_r.sodescription = MySQLServer = 192.168.56.101Port = 3306 User = gtlionsPassword = 000000 Database = gtlionsOption = 3 Socket = the above content starts from line 1 to the MySql server address, port, user, password, database, modify it as needed. Run the following command to test the function. If you can successfully log on to the MySQL Client window, the unixODBC function is normal: [root @ gtserdev ~] # Isql-v test + --------------------------------------- + | Connected! | SQL-statement | help [tablename] | quit | + ----------------------------------------- + SQL> you can use the select statement to query the access_by_oracle table created in MySql. data: SQL> select * from access_by_oracle; + ----------- + response + | id | name | + ----------- + response + | 1 | gtlions | 2 | laidye | + ----------- + ------------------- + SQLRowCount returns 22 rows fetched, everything works. Next, we need to configure the ability to directly access MySql Data in Oracle. First configure the data access connection file: [oracle @ gtserdev ~] $ Vi $ ORACLE_HOME/hs/admin/inittest. ora [oracle @ gtserdev admin] $ cat $ ORACLE_HOME/hs/admin/inittest. ora # this is a sample agent init file that contains the HS parameters that are # needed for an ODBC Agent. # HS init parameters # HS_FDS_CONNECT_INFO = testHS_FDS_TRACE_LEVEL = offHS_FDS_SHAREABLE_NAME =/usr/lib/libodbc. so ## ODBC specific environment variables # set ODBCINI =/etc/odbc. ini # modify the listening file and add the following statement: (SID_DESC = (PROGRAM = hsodbc) (ORACLE_HOME =/u01/app/oracle/product/10.2.0/db_1) (SID_NAME = test) (ENVS = LD_LIBRARY_PATH =/usr/lib:/u01/app/oracle/product/10.2.0/db_1/lib) The modified result is as follows: [oracle @ gtserdev admin] $ cat listener. ora # listener. ora Network Configuration File:/u01/app/oracle/product/10.2.0/db_1/network/admin/listener. ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME =/u01/app/oracle/product/10.2.0/db_1) (PROGRAM = extproc )) (SID_DESC = (PROGRAM = hsodbc) (ORACLE_HOME =/u01/app/oracle/product/10.2.0/db_1) (SID_NAME = test) (ENVS = LD_LIBRARY_PATH =/usr/lib: /u01/app/oracle/product/10.2.0/db_1/lib) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = gtserdev) (PORT = 1521) (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC0) configure the connection string: [oracle @ gtserdev admin] $ more $ ORACLE_HOME/network/admin/tnsnames. ora # tnsnames. ora Network Configuration File:/u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames. ora # Generated by Oracle configuration tools. test = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521) (CONNECT_DATA = (SID = test )) (HS = OK) and then start the listener and data, create a link and test: ODBCINI =/etc/odbc. ini; export ODBCINIODBCSYSINI =/etc; export ODBCSYSINI [oracle @ gtserdev admin] $ lsnrctl stop LSNRCTL for Linux: Version 10.2.0.5.0-Production on 26-MAR-2013 17:12:09 Copyright (c) 1991,201 0, Oracle. all rights reserved. connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = gtserdev) (PORT = 1521 ))) the command completed successfully [oracle @ gtserdev admin] $ lsnrctl start LSNRCTL for Linux: Version 10.2.0.5.0-Production on 26-MAR-2013 17:12:19 Copyright (c) 1991,201 0, Oracle. all rights reserved. starting/u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 10.2.0.5.0-ProductionSystem parameter file is/u01/app/oracle/product/10.2.0/db_1/network/admin/listener. oraLog messages written to/u01/app/oracle/product/10.2.0/db_1/network/log/listener. logListening on: (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = gtserdev) (PORT = 1521) Listening on: (DESCRIPTION = (ADDRESS = (PROTOCOL = ipc) (KEY = EXTPROC0) Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = gtserdev) (PORT = 1521 ))) STATUS of the LISTENER ---------------------- Alias LISTENERVersion TNSLSNR for Linux: Version 10.2.0.5.0-ProductionStart Date 26-MAR-2013 17: 12: 19 Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File/u01/app/oracle/product/10.2.0/db_1/network/admin/listener. oraListener Log File/u01/app/oracle/product/10.2.0/db_1/network/log/listener. logListening Endpoints Summary... (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = gtserdev) (PORT = 1521) (DESCRIPTION = (ADDRESS = (PROTOCOL = ipc) (KEY = EXTPROC0) Services Summary... service "PLSExtProc" has 1 instance (s ). instance "PLSExtProc", status UNKNOWN, has 1 handler (s) for this service... service "test" has 1 instance (s ). instance "test", status UNKNOWN, has 1 handler (s) for this service... the command completed successfully [oracle @ gtserdev admin] $ tnsping test TNS Ping Utility for Linux: Version 10.2.0.5.0-Production on 26-MAR-2013 17:20:03 Copyright (c) 1997,201 0, Oracle. all rights reserved. used parameter files: Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521 ))) (CONNECT_DATA = (SID = test) (HS = OK) OK (0 msec) [oracle @ gtserdev admin] $ sqlplus "/as sysdba" SQL * Plus: release 10.2.0.5.0-Production on Tue Mar 26 16:17:37 2013 Copyright (c) 1982,201 0, Oracle. all Rights Reserved. connected to an idle instance. SQL> startupORACLE instance started. total System Global Area 167772160 bytesFixed Size 1272600 bytesVariable Size 62915816 bytesDatabase Buffers 100663296 bytesRedo Buffers 2920448 bytesDatabase mounted. database opened. SQL> drop database link mysql; Database link dropped. SQL> CREATE PUBLIC DATABASE LINK mysql CONNECT TO "gtlions" IDENTIFIED BY "000000" USING 'test'; Database link created. SQL> select * from "access_by_oracle" @ mysql; id name ---------- -------------------- 1 gtlions 2 laidye-EOF-