| Bug #47680 | Query MySQL table through Oracle APEX database using Oracle database link fails | ||
|---|---|---|---|
| Submitted: | 28 Sep 2009 15:43 | Modified: | 23 Nov 2009 8:11 |
| Reporter: | Jimmy - | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S1 (Critical) |
| Version: | 5.1.6 | OS: | Windows (2003 standard sp2) |
| Assigned to: | CPU Architecture: | Any | |
[28 Sep 2009 15:46]
Jimmy -
MySQL database version number used : 5.1.39-community
[28 Sep 2009 15:57]
Jimmy -
sql statement in "Error No 2:" section of the main description is (SELECT NAME, LASTNAME FROM CUSTOMER@DLINK1) and not (SELECT ID1, NAME, LASTNAME FROM CUSTOMER@DLINK1).
[28 Sep 2009 17:16]
Valeriy Kravchuk
Thank you for the problem report. Looks similar to http://bugs.mysql.com/bug.php?id=45296. Please, check what will happen if you'll refer to column names in lowercase and double quoted: select "id1", "lastname" ... from Oracle.
[29 Sep 2009 14:08]
Jimmy -
Hello again,
i tried the query (select "id1", "lastname" from customer@dlink1) as suggested but no luck. Sql parser responds with : {Error encountered ORA-00942 : table or view does not exist }. I don't think that the problem is in the case of the defined query. As a reminder from my initial report, accessing
an Sql Server database and the same MySQL database using MyODBC driver v3.51.27.00 returns the correct results.
Thanks
[9 Nov 2009 8:56]
Tonci Grgin
jimmyp2, obviously you can not access requested table as such which makes me think you should use "database"."table_or_view_name" notation... Apart from trying this you should also attach ODBC trace and general query log from MySQL server so I can check.
[14 Nov 2009 17:54]
Jimmy -
Oracle sqlnet trace and mysql general query log
Attachment: bug-data-47680.zip (application/zip, text), 12.55 KiB.
[14 Nov 2009 18:05]
Jimmy -
Hello, i have uploaded the trace files(http://bugs.mysql.com/file.php?id=13313) from both oracle sqlnet and mysql general query log as requested by "Tonci Grgin" for examination. I tried to use "database"."table_or_view_name" notation in my select query(select "id", "name", "lastname" from "TEST"."test"@dlink1;) but still no luck. Oracle responds with the following error : ORA-00904: "lastname": invalid identifier 00904. 00000 - "%s: invalid identifier" Note: enabling odbc trace log from the windows Data sources(ODBC) produces no output for the mysql driver.
[17 Nov 2009 10:47]
Tonci Grgin
jimmyp2, can't find anything useful in neither of logs... Can you please attach ODBC DM trace file (as per http://dev.mysql.com/doc/refman/5.1/en/connector-odbc-configuration-trace.html)?
[17 Nov 2009 13:51]
Jimmy -
Hi, as i already mentioned in my last response(in the Note: section) enabling odbc trace log from the windows Data sources(ODBC) produces no output for the mysql driver. Today i upgraded my database to version 5.1.40-community and ODBC connector to the latest version(5.1.6) then retested if something has changed but the same problem continues to exist -> ORA-00904: "lastname": invalid identifier 00904. 00000 - "%s: invalid identifier"
[17 Nov 2009 13:57]
Tonci Grgin
jimmyp2, then I can't help much... Will consult colleagues and see if, maybe, something can be done.
[17 Nov 2009 15:49]
Jimmy -
hi again, thanks for trying finding a solution. maybe you should take a look back in your code archive as a starting point. somewhere in my earlier comments i've mentioned that accessing an Sql Server database and the same MySQL database using MyODBC driver v3.51.27.00 returns the correct results. What has changed since v3.51.27.00 of the driver ?
[20 Nov 2009 10:18]
Tonci Grgin
Jimmyp2, I suppose I can but this takes time and, as it involves 3rd party SW I can not obtain and which is not open-source, it might take a while. Arguments such as "it used to work" don't hold water here... Imagine this situation (and I don't say this is the case here). We've corrected our driver to comply to API specifications better than before and this breaks applications relying on API implementation bug. This scenario surely doesn't represent our bug. So, when time allows, I will revisit this report and try checking code changes. In any case, you are free to do it yourself, after all, we are open-source :)
[20 Nov 2009 10:44]
Tonci Grgin
jimmyp2, we discussed this report once again and here's our thoughts... o Attached trace is of no use as there are no actual ODBC calls logged o Your Oracle APEX obviously bypasses DM so no trace of ODBC calls available o You mention your problems started upgrading from 3.51.27 to 5.1.5. Main codebase is actually the same except that 5.1 is unicode driver while 3.51 is single-byte charsets driver. Could it be that Oracle APEX has problems with UTF-8? Or you configured something wrong in your environment? After all this, is it possible for you to continue using 3.51 driver or, if 5.1 is necessary for some reason, change charset settings for 5.1. Please retest options given and inform us of results.
[20 Nov 2009 15:25]
Jimmy -
Hello, after your last reply i checked everything you suggested to look for and here are the results of my checklist : 1) the specific version of Oracle APEX i'm using is UTF-8 compliant for sure and properly configured. 2) i changed the character set settings to "utf8" of v5.1.6 mysql odbc driver through "Details -> Misc Options" as suggested and finally the problem was resolved - the query returned the correct results. Many thanks to all of you guys for you valuable help - Excellent support team. Consider case closed. Bye.
[23 Nov 2009 8:11]
Tonci Grgin
Ahh good that it works! I was just starting to worry we'll not be able to help you. Thanks for nice words jimmyp2.
Description: Hello, i have created a public database link in my Oracle APEX database in order to access some table data located in a MySQL database(Note: both Oracle and MySQL are on the same machine) however while everything in Oracle database setup for this purpose is setup correctly(this conclusion is made on the fact that with the same configuration accessing an Sql Server database and the same MySQL database using MyODBC driver v3.51.27.00 returns the correct results ) trying to access some table data on MySQL, Sql parser responds with errors like : Suppose the table trying to access its data has the following layout: oracle database link name : DLINK1 table name ( in MySQL ) : customer fields : id1 -> integer, name -> varchar(30), lastname -> varchar(40) Error No 1: using the following sql statement(SELECT ID1, NAME, LASTNAME FROM CUSTOMER@DLINK1) with or without surrounding field names with double or single quotes and with or without using table and column aliases produces the coming error in short and statement fails to return any data : {Error encountered ORA-00904 : "FIELD NAME": invalid identifier} using the following sql statement(SELECT * FROM CUSTOMER@DLINK1) with or without using table aliases produces the coming results : Returns only the data from the integer typed field(ID1). Error No 2: Suppose the table trying to access its data has the following layout: oracle database link name : DLINK1 table name : customer fields : name -> varchar(30), lastname -> varchar(40) using the following sql statement(SELECT ID1, NAME, LASTNAME FROM CUSTOMER@DLINK1) with or without surrounding field names with double or single quotes and with or without using table and column aliases produces the coming error in short and statement fails to return any data : {Error encountered ORA-00942 : table or view does not exist } The same is true for the following sql statement : (SELECT * FROM CUSTOMER@DLINK1) How to repeat: Hello, i have created a public database link in my Oracle APEX database in order to access some table data located in a MySQL database(Note: both Oracle and MySQL are on the same machine) however while everything in Oracle database setup for this purpose is setup correctly(this conclusion is made on the fact that with the same configuration accessing an Sql Server database and the same MySQL database using MyODBC driver v3.51.27.00 returns the correct results ) trying to access some table data on MySQL, Sql parser responds with errors like : Suppose the table trying to access its data has the following layout: oracle database link name : DLINK1 table name ( in MySQL ) : customer fields : id1 -> integer, name -> varchar(30), lastname -> varchar(40) Error No 1: using the following sql statement(SELECT ID1, NAME, LASTNAME FROM CUSTOMER@DLINK1) with or without surrounding field names with double or single quotes and with or without using table and column aliases produces the coming error in short and statement fails to return any data : {Error encountered ORA-00904 : "FIELD NAME": invalid identifier} using the following sql statement(SELECT * FROM CUSTOMER@DLINK1) with or without using table aliases produces the coming results : Returns only the data from the integer typed field(ID1). Error No 2: Suppose the table trying to access its data has the following layout: oracle database link name : DLINK1 table name : customer fields : name -> varchar(30), lastname -> varchar(40) using the following sql statement(SELECT ID1, NAME, LASTNAME FROM CUSTOMER@DLINK1) with or without surrounding field names with double or single quotes and with or without using table and column aliases produces the coming error in short and statement fails to return any data : {Error encountered ORA-00942 : table or view does not exist } The same is true for the following sql statement : (SELECT * FROM CUSTOMER@DLINK1) Suggested fix: Return correct data without errors as it should.