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:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:Server version: 5.0.81 OS:Microsoft Windows (XP SP2)
Assigned to: Bogdan Degtyariov CPU Architecture:Any
Tags: Issue while querying MySQL Table from Oracle through Generic Connectivity Using

[3 Jun 2009 10:54] Yogaraj Kathirvelu
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.
[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