Hello all,
Today I tried setting up a database link from an Oracle 10gR2 database (running on RedHat EL 4, x86_64) to a MySQL database running on a remote server (also Linux). As Oracle Heterogeneous Services was not supported for 64bit Linux and 10gR2, I am trying to accomplish this using
Oracle 11g Database Gateway for ODBC. I installed the software, and configured according to the manual. But, on running any query I get MySQL errors returned.
I am using:
Oracle 11g Database Gateway for ODBC 11.1.0.6.0
unixODBC-2.2.11-1 (the ODBC Driver Manager)
mysql-connector-odbc-setup-3.51.21-0-x86_64 (The MySQL ODBC Driver)
mysql-connector-odbc-3.51.21-0-x86_64
I setup the ODBC so that I have a DSN "mysql_test". "isql mysql_test" returns nicely, and I can select from the tables in the mysql database. So, the ODBC installation and configuration seems OK.
The gateway and listener (11g) are configured so that the SID for this ODBC gateway is dg4mysql. "tnsping dg4mysql" returns OK. So, the gateway installation and configuration seems also OK.
In my 10gR2 database, I ran
SQL> create database link mysql_test
connect to "mirjam" identified by "<password was here>"
using 'dg4mysql';
SQL> select db_link, username, host from user_db_links;
DB_LINK USERNAME HOST
------------------------------ ---------- --------------------
MYSQL_TEST.BIOINF.ERASMUSMC.NL mirjam dg4mysql
So the database link seems OK also....
In the mysql database there is a table Users, with columns UserID, UName, PWord, FullName, DefaultProject, and Comments.
SQL> select * from "Users"@mysql_test;
select * from "Users"@mysql_test
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[MySQL][ODBC 3.51 Driver][mysqld-4.1.20]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
'"UserID",A1."UName",A1."PWord",A1."FullName",A1."DefaultProject",A1."Comments"
F' at line 1
ORA-02063: preceding 2 lines from MYSQL_TEST
This gives an SQL error, as it seems there are too many quotes in the sql send of to the mysql server. In the gateways oracle home /hs/log/dg4mysql_agt_32530.trc trace file I see the following:
SQL text from hgopars, id=1, len=104 ...
00: 454C4553 41205443 55222E31 49726573 [SELECT A1."UserI]
10: 412C2244 55222E31 656D614E 31412C22 [D",A1."UName",A1]
20: 5750222E 2264726F 2E31412C 6C754622 [."PWord",A1."Ful]
30: 6D614E6C 412C2265 44222E31 75616665 [lName",A1."Defau]
40: 7250746C 63656A6F 412C2274 43222E31 [ltProject",A1."C]
50: 656D6D6F 2273746E 4F524620 5522204D [omments" FROM "U]
60: 73726573 31412022 [sers" A1]
hgopars, line 367: calling SQLNumResultCols got sqlstate 42000
So... part of the connection is working, as somewhere the system finds out about the correct column names to query from the mysql database, but the system puts all quotes around the column names, which is incorrect syntax according to mysql. (If I paste the generated sql with all the quotes into the mysql client I get the same error message)
I see the same behaviour if I use the same ODBC/gateways setup to connect to a mysql 5 database.
Can anybody help me? I need to tell the system not to put quotes around the column and table names..... but how???
Kind regards,
Mirjam