I have a query that I have put together. The query is an attempt to retrieve records
from one table, main_db, whose keys are not present in another, featureenable. I am
using the NOT EXISTS keywords and continue to receive an ODBC---Call Fail error. I
traced the ODBC calls and see something very odd.
It appears from the trace that the query being passed from an MS Access front-end to
the ODBC connector is being corrupted I've extracted the lines of interest shown
below. Please note the MS2 referenced in the 2nd line. These are not in the original
query I passed in. Why is the query being passed to the ODBC connector changed? The
full queries are also shown.
.........EXISTS (SELECT* FROM featureenable WHERE main_db.FeatureKey =
featureenable.FeatureKey);
.........EXISTS (SELECT `MS2`.`ID` FROM `featureenable` `MS2` WHERE (`MS2`.`FeatureKey
Original Query
==================================================================================
SELECT DISTINCT [functionalsubgroup].[Functional], [functionalsubgroup].[SubGroup],
[main_db].[Feature], [main_db].[FeatureKey], [functionalsubgroup].[FSKey]
FROM featureenable INNER JOIN (functionalsubgroup INNER JOIN main_db ON
([functionalsubgroup].[FSKey]=[main_db].[FSKey]) AND
([functionalsubgroup].[FSKey]=[main_db].[FSKey]) AND
([functionalsubgroup].[FSKey]=[main_db].[FSKey]) AND
([functionalsubgroup].[FSKey]=[main_db].[FSKey])) ON
[featureenable].[FeatureKey]=[main_db].[FeatureKey]
WHERE NOT EXISTS (SELECT* FROM featureenable WHERE main_db.FeatureKey =
featureenable.FeatureKey);
ODBC Call Trace
============================================================================
STRDB-v2.0b14 83c-218 ENTER SQLExecDirectW
HSTMT 08DC1C30
WCHAR * 0x0F271F40 [ -3] "SELECT DISTINCT
`functionalsubgroup`.`Functional` ,`functionalsubgroup`.`SubGroup`
,`main_db`.`Feature` ,`main_db`.`FeatureKey` ,`functionalsubgroup`.`FSKey` FROM
`main_db`,`functionalsubgroup`,`featureenable` `MS1` WHERE
((((((`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) AND
(`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND
(`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND
(`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`MS1`.`FeatureKey` =
`main_db`.`FeatureKey` ) ) AND NOT(EXISTS (SELECT `MS2`.`ID` FROM `featureenable`
`MS2` WHERE (`MS2`.`FeatureKey` = `main_db`.`FeatureKey` ) )) ) \ 0"
SDWORD -3
STRDB-v2.0b14 83c-218 EXIT SQLExecDirectW with return code -1 (SQL_ERROR)
HSTMT 08DC1C30
WCHAR * 0x0F271F40 [ -3] "SELECT DISTINCT
`functionalsubgroup`.`Functional` ,`functionalsubgroup`.`SubGroup`
,`main_db`.`Feature` ,`main_db`.`FeatureKey` ,`functionalsubgroup`.`FSKey` FROM
`main_db`,`functionalsubgroup`,`featureenable` `MS1` WHERE
((((((`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) AND
(`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND
(`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND
(`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`MS1`.`FeatureKey` =
`main_db`.`FeatureKey` ) ) AND NOT(EXISTS (SELECT `MS2`.`ID` FROM `featureenable`
`MS2` WHERE (`MS2`.`FeatureKey` = `main_db`.`FeatureKey` ) )) ) \ 0"
SDWORD -3
DIAG [37000] [MySQL][ODBC 3.51 Driver][mysqld-4.0.20-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 'EXISTS (SELECT `MS2`.`ID` FROM
`featureenable` `MS2` WHERE (`M (1064)
Robert M. Bartis
Lucent Technologies, Inc
Tel: +1 732 949 4565
Mail: <[EMAIL PROTECTED]>
Pgr: <[EMAIL PROTECTED]>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
