| Bug #45296 | Issue while querying MySQL tables through Oracle Generic Connectivity Using ODBC | ||
|---|---|---|---|
| Submitted: | 3 Jun 2009 10:54 | Modified: | 8 Nov 2011 8:33 |
| Reporter: | Yogaraj Kathirvelu | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | Server version: 5.0.81 | OS: | Windows (XP SP2) |
| Assigned to: | Bogdan Degtyariov | CPU Architecture: | Any |
| Tags: | Issue while querying MySQL Table from Oracle through Generic Connectivity Using | ||
[4 Jun 2009 13:06]
Valeriy Kravchuk
Thank you for the problem report. What exact versions of Connector/ODBC and Oracle server do you use?
[8 Jun 2009 6:32]
Yogaraj Kathirvelu
I am using the MySQL ODBC Connector "mysql-connector-odbc-5.1.5". Oracle Server version is Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 Please let me know if you need further details. Thanks, Yoga K.
[9 Jun 2009 16:12]
Tonci Grgin
Yogaraj, you are using numerous 3rd party tools/servers which I have no access to so we'll have to do it hard way... Please attach ODBC trace from DM. Mind you, ODBC creates enormous traces so be sure to just repeat necessary steps causing the error to appear. Also, I noticed OHS are talking of table "WORKER" while your table is "worker". Is MySQL server installed on case sensitive OS?
[9 Jul 2009 23:01]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[29 Oct 2009 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[29 Oct 2009 7:20]
Tonci Grgin
Yogaraj, ping.
[30 Nov 2009 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[1 Dec 2010 9:55]
Loga a
Hi, I have the same problem. Issue while querying MySQL tables through Oracle Generic Connectivity Using ODBC for varchar type columns only. please provide any solution how to retrive varchar datatype columns. Thanks, Loga
[8 Nov 2011 8:33]
Bogdan Degtyariov
Thank you for your bug report. This issue has been fixed in the latest released version of Connector/ODBC 5.1.9, which you can download at http://www.mysql.com/downloads/connector/odbc/5.1.html
[27 Sep 2012 13:33]
Irene Odera
This seems to be happening again with ODBC 5.2.2 and Oracle DB 11g
Description: Hi, I have configured Oracle hetrogeneous service for MySQL 5.0.81 version using MySQL Connector/ODBC 5.1 I created a table called emp in MySQL Which is similar to Oracle's scott.emp table. My ODBC name is MySQLDB I created a Public DBlink to access the MySQL. When i tried to query the table SQL> select * from "emp"@mysql; empno mgr hiredate sal comm deptno ---------- ---------- --------- ---------- ---------- ---------- 7108 7698 02-JUN-09 5000 7109 7698 02-JUN-09 5000 7369 7902 17-DEC-80 800 20 7499 7698 20-FEB-81 1600 300 30 7521 7698 22-FEB-81 1250 500 30 7566 7839 02-APR-81 2975 20 7654 7698 28-SEP-81 1250 1400 30 7698 7839 01-MAY-81 2850 30 7782 7839 09-JUN-81 2450 10 7788 7566 19-APR-87 3000 20 7839 17-NOV-81 5000 10 7844 7698 08-SEP-81 1500 0 30 7876 7788 23-MAY-87 1100 20 7900 7698 03-DEC-81 950 30 7902 7566 03-DEC-81 3000 20 7934 7782 23-JAN-82 1300 10 16 rows selected. It returns the above result set with out the Columns Ename Job the above two columns are the type of Varchar. then I created a table called worker with only varchar columns. when i tried to query that table i got the following error SELECT * FROM "worker"@MySQL; ORA-00942: table or view does not exist [Generic Connectivity Using ODBC]Table WORKER has no fields. Loading failed ORA-02063: preceding 2 lines from MYSQL after that i have tried to describe the emp table using SQL> desc emp@salesdb; Name Null? Type ----------------------------------------- -------- ---------------------- empno NOT NULL NUMBER(10) mgr NUMBER(10) hiredate DATE sal NUMBER(7,2) comm NUMBER(7,2) deptno NUMBER(10) it didn't display the varchar columns. for the same table when i issue the describe statement the following is the result set in MySQL mysql> desc emp; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | empno | int(4) | NO | PRI | NULL | | | ename | varchar(10) | YES | | NULL | | | job | varchar(9) | YES | | NULL | | | mgr | int(4) | YES | | NULL | | | hiredate | datetime | YES | | NULL | | | sal | decimal(7,2) | YES | | NULL | | | comm | decimal(7,2) | YES | | NULL | | | deptno | int(2) | YES | MUL | NULL | | +----------+--------------+------+-----+---------+-------+ 8 rows in set (0.02 sec) so when i am querying MySQL tables from oracle using transparent gateways Varchar fields are not recognized. Please let me know any kind of work arounds is there. How to repeat: Configure the Oracle generic Connectivity to access non-oracle system using the below mentioned steps Login to the MySQL grant all privileges to the user root using the Grant all on *.* to 'test'@'localhost' identified by 'test'; Create an ODBC connectivity with the name MySQLDB using the MySQL Connector/ODBC 5.1 driver in System DSN and test it with the user 'test' which we created previously. goto $ORACLE_HOME \hs\admin Create a new file called “initMySQLDB.ora" from an existing file called “inithsodbc.ora”. HS_FDS_CONNECT_INFO = MySQLDB HS_FDS_TRACE_LEVEL = 0 Set the above mentioned parameters. go to $ORACLE_HOME \NETWORK\ADMIN edit listener.ora to add The following is entry SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = D:\Oracle\Product\db_1) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = MySQLDB) (ORACLE_HOME = D:\Oracle\Product\db_1) (PROGRAM = hsodbc) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = BLRD9196.symphonysv.com)(PORT = 1521)))) stop and start the oracle listener and check the status that MySQLDB is running. goto $ORACLE_HOME \NETWORK\ADMIN add an entry into tnsnames.ora file as like below MySQLDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA =(SID = MySQLDB)) (HS= OK) ) connect to oracle as sysdba and craete the DBlink using the below statement Create public database link mysql Connect to "root" identified by "admin" using 'MySQLDB'; execute the below Block to create table called dept1 DECLARE num_rows INTEGER; BEGIN num_rows := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@MySQL ('CREATE TABLE dept1 (deptNumber SMALLINT, loc CHARACTER(10))'); END; Insert a row into dept1 INSERT INTO dept1@mysql VALUES(20); SELECT * FROM dept1@mysql will display only the deptNumber column INSERT INTO dept1@mysql VALUES(20,'usa') if you execute the above statement you will get the below error ORA-00913: too many VALUES Thanks, Yoga K.