Description:
hi,
i found following failuer while working with MyODBC. i reported it on the forums, but their was no working solution. i am reporting this now as a bug.
Environment:
MySQL 3.23.55 and 4.0 // meaning tested both
MyODBC 3.51 and 2.5 // meaning tested both
MS Access 97 and Access 2k // meaning tested both
running on MS Win 2K
all service packs installed
newest MDAC installed.
the point ist, that i ported data from access to mysql. now we want the old access frontend to work over the new mysql situation. therefore i installed the MyODBC Driver, versions as above!
now i have a subformular in Access with following query output from the SQL-View of access.
SELECT tbl_chimps.code, [Forms]![frm_checksheets]![frm_observations1].[Form]![txtGroup_ID] AS group_ID
FROM tbl_chimps
WHERE (((tbl_chimps.code)
In (
SELECT distinct(chimp_code) FROM tbl_groups WHERE group_id =
[Forms]![frm_checksheets]![frm_observations1].[Form]![txtGroup_ID]))
);
running it, produces an 'ODBC--fail' error message in Access.
so i started watching the MyODBC log.
and recognized the query being truely absurd. its not an MySQL standard.
here is the myodbc log:
MSACCESS 3c8-414 EXIT SQLExecDirect with return code -1 (SQL_ERROR)
HSTMT 0A0A3330
UCHAR * 0x10B41178 [ -3] "SELECT `code` FROM `chimps` WHERE ((`chimps`.`code` )= ANY (SELECT DISTINCT `groups`.`chimp_code` FROM `groups` WHERE (`groups`.`group_ID` = ? ) ))\ 0"
SDWORD -3
DIAG [37000] [MySQL][ODBC 3.51 Driver][mysqld-4.0.21-standard]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 '(SELECT DISTINCT `groups`.`chimp_code` FROM `groups` WHERE (`g (1064)
MSACCESS 3c8-414 ENTER SQLErrorW
HENV 0A0A1540
HDBC 0A0A15E8
HSTMT 0A0A3330
WCHAR * 0x0012EA08 (NYI)
SDWORD * 0x0012EA7C
WCHAR * 0x0012E608
SWORD 512
SWORD * 0x0012EA92
now the part from mysql log:
SELECT `code` FROM `chimps` WHERE ((`chimps`.`code` )= ANY (SELECT DISTINCT `groups`.`chimp_code` FROM `groups` WHERE (`groups`.`group_ID` = 64 ) ));
As You can see here and in the odbc log above, this is not a known SQL Syntax.
How to repeat:
build a formular at ms acc using a subformular that depends on the Primary key of the formular.
the subformular should have a field that proves formerly input made from the user in the same subformular. something like a primary key.
now the query is bound on the access field with an event. like clicking or something else. Now starting it the query will fail, and their will be the odbc fail message.
Suggested fix:
i think, the query translated from odbc is wrong sql. I didnt found (=ANY) in MySQL. so i guess this is the failure. funny is, that the query looks fine at access sql.
however, with best regards
joshua bacher
Description: hi, i found following failuer while working with MyODBC. i reported it on the forums, but their was no working solution. i am reporting this now as a bug. Environment: MySQL 3.23.55 and 4.0 // meaning tested both MyODBC 3.51 and 2.5 // meaning tested both MS Access 97 and Access 2k // meaning tested both running on MS Win 2K all service packs installed newest MDAC installed. the point ist, that i ported data from access to mysql. now we want the old access frontend to work over the new mysql situation. therefore i installed the MyODBC Driver, versions as above! now i have a subformular in Access with following query output from the SQL-View of access. SELECT tbl_chimps.code, [Forms]![frm_checksheets]![frm_observations1].[Form]![txtGroup_ID] AS group_ID FROM tbl_chimps WHERE (((tbl_chimps.code) In ( SELECT distinct(chimp_code) FROM tbl_groups WHERE group_id = [Forms]![frm_checksheets]![frm_observations1].[Form]![txtGroup_ID])) ); running it, produces an 'ODBC--fail' error message in Access. so i started watching the MyODBC log. and recognized the query being truely absurd. its not an MySQL standard. here is the myodbc log: MSACCESS 3c8-414 EXIT SQLExecDirect with return code -1 (SQL_ERROR) HSTMT 0A0A3330 UCHAR * 0x10B41178 [ -3] "SELECT `code` FROM `chimps` WHERE ((`chimps`.`code` )= ANY (SELECT DISTINCT `groups`.`chimp_code` FROM `groups` WHERE (`groups`.`group_ID` = ? ) ))\ 0" SDWORD -3 DIAG [37000] [MySQL][ODBC 3.51 Driver][mysqld-4.0.21-standard]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 '(SELECT DISTINCT `groups`.`chimp_code` FROM `groups` WHERE (`g (1064) MSACCESS 3c8-414 ENTER SQLErrorW HENV 0A0A1540 HDBC 0A0A15E8 HSTMT 0A0A3330 WCHAR * 0x0012EA08 (NYI) SDWORD * 0x0012EA7C WCHAR * 0x0012E608 SWORD 512 SWORD * 0x0012EA92 now the part from mysql log: SELECT `code` FROM `chimps` WHERE ((`chimps`.`code` )= ANY (SELECT DISTINCT `groups`.`chimp_code` FROM `groups` WHERE (`groups`.`group_ID` = 64 ) )); As You can see here and in the odbc log above, this is not a known SQL Syntax. How to repeat: build a formular at ms acc using a subformular that depends on the Primary key of the formular. the subformular should have a field that proves formerly input made from the user in the same subformular. something like a primary key. now the query is bound on the access field with an event. like clicking or something else. Now starting it the query will fail, and their will be the odbc fail message. Suggested fix: i think, the query translated from odbc is wrong sql. I didnt found (=ANY) in MySQL. so i guess this is the failure. funny is, that the query looks fine at access sql. however, with best regards joshua bacher