Bug #20064 | Integer values inserted into a double field through MSAccess are not selectable | ||
---|---|---|---|
Submitted: | 25 May 2006 9:39 | Modified: | 26 May 2006 9:49 |
Reporter: | Stefano Sapienti | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | Connector / ODBC | Severity: | S2 (Serious) |
Version: | 3.51.09 > | OS: | Windows (Windows2000/XP) |
Assigned to: | CPU Architecture: | Any |
[25 May 2006 9:39]
Stefano Sapienti
[25 May 2006 20:21]
Tonci Grgin
Hi Stefano. Thanks for your problem report. Sorry I was unable to repeat it. Environment: MySQL server 4.1.18-nt-max-log MyODBC 3.51.12 - standard WIN XP SP2 Access v11.0 build 6566 (Access 2003) Jet 2.x msrd2x35.dll v. 3.51.0623.0 (From MS Access About box) Server log file: MySql, Version: 4.1.18-nt-max-log. started with: TCP Port: 3306, Named Pipe: MySQL Time Id Command Argument 060525 21:11:30 1 Connect root@localhost on 1 Query SET AUTOCOMMIT=0 060525 21:11:31 1 Query SET AUTOCOMMIT=1 1 Query SHOW DATABASES 060525 21:11:55 1 Query USE test 1 Query SHOW TABLES 060525 21:13:19 1 Query create table test20064 ( ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, DblFld DOUBLE (10,4) ) 1 Query USE test 060525 21:17:14 2 Connect root@localhost on test 2 Quit 060525 21:20:59 3 Connect root@localhost on test 3 Query SELECT Config, nValue FROM MSysConf 3 Query show tables 3 Query SHOW TABLES FROM `mysql` like '%' 060525 21:21:04 3 Field List test20064 3 Field List test20064 3 Query SHOW KEYS FROM `test20064` 060525 21:21:05 3 Query SELECT `ID` ,`DblFld` FROM `test20064` 3 Query SET AUTOCOMMIT=0 3 Query COMMIT 3 Query SET AUTOCOMMIT=1 060525 21:26:47 3 Quit 060525 21:28:23 4 Connect root@localhost on test 4 Query SELECT Config, nValue FROM MSysConf 4 Query show tables 4 Query SHOW TABLES FROM `mysql` like '%' 060525 21:28:33 4 Field List test20064 4 Field List test20064 4 Query SHOW KEYS FROM `test20064` 060525 21:28:38 4 Query SELECT `test20064`.`ID` FROM `test20064` 060525 21:28:41 4 Query SET AUTOCOMMIT=0 4 Query INSERT INTO `test20064` (`DblFld`) VALUES (1.17000000000000000e+002) 4 Query COMMIT 4 Query SET AUTOCOMMIT=1 4 Query SELECT `ID`,`DblFld` FROM `test20064` WHERE `ID` IS NULL 4 Query SELECT `ID`,`DblFld` FROM `test20064` WHERE `ID` IS NULL 060525 21:29:04 4 Query SELECT `test20064`.`ID` FROM `test20064` WHERE (`DblFld` = 117 ) 4 Query SELECT `ID`,`DblFld` FROM `test20064` WHERE `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 060525 21:30:27 4 Query SET AUTOCOMMIT=0 4 Query INSERT INTO `test20064` (`DblFld`) VALUES (2.00000000000000000e+002) 4 Query COMMIT 4 Query SET AUTOCOMMIT=1 4 Query SELECT `ID`,`DblFld` FROM `test20064` WHERE `ID` IS NULL 4 Query SELECT `ID`,`DblFld` FROM `test20064` WHERE `ID` IS NULL 4 Query SELECT `ID`,`DblFld` FROM `test20064` WHERE `ID` IS NULL 060525 21:30:38 4 Query SET AUTOCOMMIT=0 4 Query INSERT INTO `test20064` (`DblFld`) VALUES (5.46780000000000000e+004) 4 Query COMMIT 4 Query SET AUTOCOMMIT=1 4 Query SELECT `ID`,`DblFld` FROM `test20064` WHERE `ID` IS NULL 4 Query SELECT `ID`,`DblFld` FROM `test20064` WHERE `ID` IS NULL 4 Query SELECT `ID`,`DblFld` FROM `test20064` WHERE `ID` IS NULL 060525 21:30:42 4 Query SELECT `test20064`.`ID` FROM `test20064` WHERE (`DblFld` = 117 ) 4 Query SELECT `ID`,`DblFld` FROM `test20064` WHERE `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 060525 21:30:53 4 Query SELECT `test20064`.`ID` FROM `test20064` WHERE (`DblFld` = 200 ) 4 Query SELECT `ID`,`DblFld` FROM `test20064` WHERE `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 060525 21:31:12 4 Query SELECT `test20064`.`ID` FROM `test20064` 4 Query SELECT `ID`,`DblFld` FROM `test20064` WHERE `ID` = 1 OR `ID` = 2 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 060525 21:32:21 4 Query SELECT `test20064`.`ID` FROM `test20064` WHERE (`DblFld` = 200 ) 4 Query SELECT `ID`,`DblFld` FROM `test20064` WHERE `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 060525 21:32:24 4 Query SELECT `test20064`.`ID` FROM `test20064` WHERE (`DblFld` = 200 ) 4 Query SELECT `ID`,`DblFld` FROM `test20064` WHERE `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 060525 21:32:57 1 Query SELECT * FROM test20064 1 Query SELECT * FROM test20064 1 Query SHOW COLUMNS FROM test20064 1 Query SHOW COLUMNS FROM test20064 060525 21:33:09 1 Quit However it is visible that Access is sending wrong request to MyODBC: This is to fetch auto-increment field: 060525 21:29:04 4 Query SELECT `test20064`.`ID` FROM `test20064` WHERE (`DblFld` = 117 ) After fetching auto increment value of new record, access sends incorrect query regarding Row to be fetched (DblFld = 117): 4 Query SELECT `ID`,`DblFld` FROM `test20064` WHERE `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 but this does not affect the result which is correct. The Access error regarding fetching of all records is visible here: 4 Query SELECT `ID`,`DblFld` FROM `test20064` WHERE `ID` = 1 OR `ID` = 2 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 Instead of sending "SELECT * FROM ..." to MyODBC it taxates *ALL* the distinct values of primary key... Now since there's been a lot of commotion about MSAccess -> MyODBC link, let's take a look at Access log snippet: MSACCESS a10-150 ENTER SQLPrepareW HSTMT 07E03410 WCHAR * 0x0F371D48 [ -3] "SELECT `ID`,`DblFld` FROM `test20064` WHERE `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ?\ 0" SDWORD -3 MSACCESS a10-150 EXIT SQLPrepareW with return code 0 (SQL_SUCCESS) HSTMT 07E03410 WCHAR * 0x0F371D48 [ -3] "SELECT `ID`,`DblFld` FROM `test20064` WHERE `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ?\ 0" SDWORD -3 Sadly this is what comes to MyODBC from Access query builder... Access is notorious for it's behavior, try typing "SELECT Config, nValue FROM MSysConf" in google and check, for example, http://www.experts-exchange.com/Databases/Btrieve/Q_20772248.html.
[25 May 2006 21:23]
Tonci Grgin
Picture showing MS Access ws
Attachment: 20064.jpg (image/jpeg, text), 58.06 KiB.
[26 May 2006 9:49]
Stefano Sapienti
Well, I upgrade from version 4.1.10 to 4.1.19 and now it works. Old values still have the problem but updating them fixes it, also with and old ODBC version. So I think the issue can be retain solved with the upgrade, sorry for inserting a bug but maybe can be of help for someone. Thanks again.
[26 May 2006 9:59]
Tonci Grgin
Stefano, glad you solved your problem although making float comparison like that is not a good practice.