Accessing MySQL data via ODBC and Oracle's Heterogenous Services

[mike777]

Hello all.

I keep going back and forth on this as to whether it's an Oracle issue or a MySQL. So, honestly, I'll let you decide and will take your advice.

Not sure if this is allowed, but I've cross-posted this question in the Oracle: Oracle release - 8 and 8i forum, with a similar message there that it is cross-posted here.

Scenario: I am successfully accessing MySQL data via ODBC through Oracle's Heterogeneous Services. There is a problem, however. Hence, this post. Suppose my query looks something like this:

select "f1","f2","f3" from MySQLTable@MySQLDB

where MySQLDB is an Oracle database link that connects, via ODBC, to a MySQL database.

When I run the query in MS Access or MS FoxPro, it works fine. So we know the table name and column names are good (the only difference being that you have to take out the double-quotes, otherwise these programs think the values within the quotes are constants and not column names).

When I run the query in Oracle (where the double-quotes are required, I have learned, when accessing MySQL data via ODBC), exactly as typed above, I get this error (total, verbatim):

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][MySQL][ODBC 3.51 Driver][mysqld-4.0.20a-debug]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to user near "MySQLTable" WHERE "id"=3' at line 1(SQL State: 37000; SQL Code: 1064)
ORA-02063: preceding 2 lines from MySQLDB

Now, when I start to do research, I run this query (in Oracle):

select table_name,column_name,data_type,data_length
from   all_tab_columns@MySQLDB
where  table_name='MySQLTable'

Upon doing so, I get the following results:
===========================================================
TABLE_NAME........COLUMN_NAME........DATA_TYPE........DATA_LENGTH
MySQLTable...........f1..........................VARCHAR2.........25
MySQLTable...........f2..........................NUMBER.............4
MySQLTable...........f3..........................LONG.................2147483647
===========================================================

Seeing this, I am immediately suspicious of column "f3". So I rerun the query that points to the MySQL database with that column excluded as in:

select "f1","f2" from MySQLTable@MySQLDB

and it works just fine.

Can you suggest what might be the problem? I need the data in column "f3".

Many thanks.

-Mike

 

[TonyGroves]

The error message you got is a MySQL error message; it is clear that Oracle is passing a query string to MySQL which includes the text:[tt]
"MySQLTable" WHERE "id"=3[/tt]
That would not be valid MySQL syntax; you need to drop the double-quotes. I've no idea how you would do that while keeping Oracle happy; maybe the Oracle forum could help there.

 

[mike777]

Thanks, Tony.
I've noticed that WHERE "id"=# (some number) gets added to the SQL whenever I pass a query to MySQL and MySQL thinks there's something wrong with it.
I've noticed this to be true even in PHP Admin when writing MySQL queries, and it shows the query text.
And, yep, Oracle (I guess) requires the double-quotes, otherwise I get an ORA-00904: "ID": invalid identifier error. The double-quotes are no problem, though, when there are not other problems with the query.
So I'm kinda stuck for now, I guess!!
Thanks again for the help.
-Mike