The Oracle gateway for ODBC provides an almost seamless data integration between Oracle and other RDBMS. I won’t argue about its performance, limits, or relevance. It serves a few purposes; set it up and you’ll be able, for example, to create database links between Oracle and MySQL. After all, wouldn’t it be nice if you could run some of the following SQL statements?
select o.col1, m.col1 from oracle_tab
o, [email protected] m where o.col1=m.col1;
insert into oracle_tab (select * from [email protected]);
This post is intended to share, the same way Karun did it for SQL Server last year, some tips related to the setup of the Oracle Gateway for ODBC with MySQL Connector ODBC on Linux.
I’ve installed all the configuration on my laptop to test it. It’s running Ubuntu Intrepid Ibex 32bits, but I won’t dig into the challenge of installing the MySQL Connector ODBC 5.1 on it. All I’ll tell you is that, if I understand correctly, the version of iodbc that comes with Intrepid doesn’t support MySQL Connector ODBC 5.1 too well, and the messages that it returns are not quite explicit. To be frank, what I did is put that monkey on Augusto’s back. He sorted out everything in a few minutes. I guess I have to thank Augusto twice, just for this post! I’ve also followed his “Installing Oracle 11gR1 on Ubuntu 8.10 Intrepid Ibex” post to install the Oracle part.
But let’s talk about the prerequisites! You need to have installed and configured the following components:
- Oracle database SE1, SE or EE; I installed 220.127.116.11 but it should work with 10g too. You can check in
ORACLE_HOME/bin, it has the dg4odbc executable.
- MySQL 4.1, 5.0, 5.1 or 6.0. According to the documentation, those are the MySQL versions supported by the Connector ODBC 5.1.
Connector ODBC 5.1. The Oracle Gateway for ODBC checks/relies on some features, such as the ODBC descriptor, that are not available in 3.51.
Creating a MySQL
DEMO database, user and table
For the purpose of the demonstration, I’ve created a database, a user and a table named
DEMO with the mysql client. You’ll find the script below.
The gateway for ODBC doesn’t look to work correctly when data are stored in utf8 in MySQL, whether or not the Connector/ODBC does the transformation into a non-utf8 character set. For this reason, I set the MySQL database default character set to latin1. I suspect somehow the issue is related to the ODBC driver: if I use latin1 on the client side, Oracle should not see any difference, whatever the storing character set is. Anyway, there is also a limitation on the Oracle side, and it doesn’t handle utf8 correctly with the Connector (see MySupport note 756186.1)
$ mysql -uroot -p create database demo character set latin1; grant all privileges on demo.* to 'demo'@'localhost' identified by 'demo' with grant option; flush privileges; exit; $ mysql -udemo -pdemo -Ddemo create table demo ( col1 integer, col2 date, col3 varchar(10), col4 varchar(10) character set utf8, col5 varbinary(10)) engine innodb; insert into demo(col1, col2, col3, col4, col5) values(1, cast(now() as date), '0123456789', '0123456789', '0123456789'); select * from demo \G *********** 1. row ************ col1: 1 col2: 2009-03-11 col3: 0123456789 col4: 0123456789 col5: 0123456789 exit;
Creating an ODBC DSN to access the
Once the database was created, I created a user DSN in the Oracle owner, so that the the listener can get it via the dg4odbc program. By default the file that store the user DSN is
$HOME/.odbc.ini, but you can change it to any file/location that fits your needs. This is how the file looks like on my server:
$ cat ~oracle/.odbc.ini [ODBC Data Sources] demo = MySQL ODBC Driver 5.1 [demo] Driver = /home/oracle/mysql515/lib/libmyodbc5.so DATABASE = demo DESCRIPTION = MySQL ODBC 5.1.5 Connector Sample PORT = 3306 SERVER = 127.0.0.1 # UID = demo # PWD = demo CHARSET = latin1 TRACEFILE = /tmp/myodbc-demodsn.trc TRACE = OFF
Make sure the
CHARSET parameter is set so that it doesn’t use utf8.
Configuring dg4odbc to use the DSN
dg4odbc gets its settings from a file named
init[SID].ora located in
$ORACLE_HOME/hs/admin. In this case,
SID is an arbitrary parameter we’ll configure in the
listener.orafile (see next section). I’ve used
SID=mysql for this demo, and the
initmysql.ora file looks like the one below:
$ cat $ORACLE_HOME/hs/admin/initmysql.ora # # HS init parameters # HS_FDS_CONNECT_INFO=demo HS_FDS_TRACE_LEVEL=0 HS_FDS_SHAREABLE_NAME=/home/oracle/mysql515/lib/libmyodbc5.so HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15 # HS_NLS_NCHAR=AL32UTF8 # # ODBC specific environment variables # set ODBCINI=/home/oracle/.odbc.ini set LD_LIBRARY_PATH=/home/oracle/mysql515/lib # # Environment variables required for the non-Oracle system # set HOME=/home/oracle
As you can see above:
ODBCINIis the location
HS_FDS_CONNECT_INFOpoints to the right DSN.
HS_FDS_SHAREABLE_NAMEpoints to ODBC driver shared library.
HS_LANGUAGEis set to avoid the problem described in Oracle MySupport “756186.1: Error Ora-28500 and Sqlstate I Issuing Selects From a Unicode Oracle RDBMS With Dg4odbc To Mysql”.
Configuring the listener
To configure the listener, I had to change the
listener.ora file to add the SID defined in the previous section and associate it with the gateway for ODBC; here is a copy of my setup used; I’ve kept all the settings (host, port, dynamic registration) default:
$ cat $ORACLE_HOME/network/admin/listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1) (SID_NAME = mysql) (PROGRAM = dg4odbc) (ENVS ="LD_LIBRARY_PATH=/home/oracle/mysql515/lib:/usr/lib:$ORACLE_HOME/lib") ) )
Once the listener is setup, you can bounce or reload it; if it’s not started, just start it:
$ lsnrctl start
And add an entry in the
listener.ora file like the one below; make sure you’ve added
HS=OK and that it’s not in the
MYSQL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521) ) ) (CONNECT_DATA = (SID = mysql) ) (HS = OK) )
Creating a database link to connect to MySQL from Oracle
At this point, and after a little debugging of the different layers, I was able to create a database link from Oracle to MySQL. To do so, I connected to Oracle and used the
CREATE DATABASE LINK command:
$ sqlplus / as sysdba create database link mysql connect to "demo" identified by "demo" using 'mysql'; select "col3" from "demo"@mysql; col3 --------------------------------- 0123456789
The case policy differs between Oracle and MySQL, and you must
always surround the table and columns name with double quotes.
Using the Gateway for ODBC
If you remember correctly, I stored the string “0123456789” in col3, col4, and col5. You’ll see some of the issues with the character set by querying a datum stored in utf8:
select "col4" from "demo"@mysql; col4 ---------------------------------------- 0 1 2 3 4
and one stored in a varbinary:
select "col5" from "demo"@mysql; col5 -------------------- 30313233343536373839
But you’ll also be able to enjoy some of the features of your new Oracle/MySQL integrated environment, and be able to create a table in Oracle using data from MySQL:
create table demo as select "col1" col1, "col2" col2, "col3" col3 from "demo"@mysql; select * from demo; COL1 COL2 COL3 ---- --------- ---------- 1 11-MAR-09 0123456789
Unfortunately, you cannot insert data directly from Oracle into MySQL with an insert as select:
insert into "demo"@mysql("col1"). select 2 from dual; ERROR at line 2: ORA-02025: all tables in the SQL statement must be at the remote database
But you can workaround that issue with some PL/SQL (I’m not saying it’s efficient):
begin for i in (select col1, col2, col3 from demo) loop insert into "demo"@mysql("col1","col2", "col3") values (2,i.col2, i.col3); end loop; end; / select "col1","col2", "col3" from "demo"@mysql; col1 col2 col3 ---- --------- ---------- 1 11-MAR-09 0123456789 2 11-MAR-09 0123456789
To prevent the access to MySQL from Oracle, you can drop the database link:
drop database link mysql;
That is it. It works pretty well so far and, despite the limits of such an approach, it can be quite useful for those that want to migrate from MySQL to Oracle.
[…] Pythian’s Grégory Guillou published How To Access MySQL from Oracle With ODBC and SQL. […]
thanks for your information. This information very helpfull for my homework. I hope you can posting for access postgresql from oracle with odbc and sql too please……
in the section “Configuring the listener” when you want to add “MYSQL” entry: “…And add an entry in the listener.ora file like the one below…”; I think you mean tnsnames.ora no ?
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from MYSQL
Check tnsnames.ora file is SID or SID_NAME (set to SID) and try… will work.
I still have the error ORA-28500.
Could you help me with that please?
help me please, i have the next error:
[MySQL][ODBC 5.1 Driver][mysqld-5.1.23-rc-community]You have an error in your SQL syntax; check the manual that corresponds to to MySQL server version
Easy , Fast and Simple . Great post , worked immediately . As Jeremy says , please modify the post and put
(ADDRESS = (PROTOCOL = TCP)
(HOST = localhost)
(PORT = 1521)
(SID = mysql)
(HS = OK)
in tnsnames.ora not in listener.ora
Now my APEX application is connected to my ZENCART store , FANTASTIC !
SQL> select “col3” from “demo”@mysql
[MySQL][ODBC 5.1 Driver][mysqld-5.0.75-0ubuntu10]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 ‘”col3” FROM “demo” A1’ at line 1
Friends , I have a big problem now . PLease take a look on :
PLease take a look on :
I am having this problem:
ORA-28511: lost RPC connection to heterogeneous remote agent using
[…] https://www.pythian.com/news/1554/how-to-access-mysql-from-oracle-with-odbc-and-sql/ […]
do you know if it is possible to do the other way round, i.e. connecting to an ODBC source FROM MySQL?
Thanx a lot in advance.
HI,I use Oracle11g access mysql with odbc.but alway error!!
ORA-12703: this character set conversion is not supported
ORA-02063: preceding line from MYSQL
now,I modify oracle PARAMETER NLS_CHARACTERSET to UTF-8,mysql charset is utf-8,too. error finally
I think you have a typo here:
“And add an entry in the listener.ora file like the one below; make sure you’ve added HS=OK and that it’s not in the CONNECT_DATA clause:”
should it be tnsnames.ora file instead of listener.ora?
Please help me, I think, I configured correct the Mysql on Linux, but when I tried to execute de query select * from “tableMysql”@mysql.com, then only return a one row, when that table has 2300 rows ¿Why? ¿How can i fix that problem?
Hi please help me. How to insert blob column from oracle database to MySQL database.
Thanks in advance
Hello, I followed your instruction step by step; but I am getting the following error
select “col5” from “demo”@MySQL;
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from MYSQL
My environment : Centos 7, Oracle xe 11.2.0,
I created a public database link;
[[email protected] trace]# isql -v demo
| Connected! |
| sql-statement |
| help [tablename] |
| quit |
Any help would be greatly appreciated.
you fixed your problem, I have the same problem
How do I call MySQL Stored Procedure from oracle using HS link ?
HS link is correct and I can get the tables data just by adding ‘@’ at the end of the table name ,but I can’t call the SP same way !
i am getting the error as
create public database link tst connect to “swarna” identified by “root” using ‘TSTLINK’;
Database link created.
SQL> select “id” from “ss”@tst;
select “id” from “ss”@tst
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified
how to resolve it???
why select * from [email protected] query shows one row in oracle .
Hello, Any idea how to connect from single oracle db to multiple Mysql DB for example one Mysql db for Production and another for Mysql UAT ?