| Bug #28420 | ODBC 3.51.15 driver doesn't translate MySQL 5.0.41 dates | ||
|---|---|---|---|
| Submitted: | 14 May 2007 16:34 | Modified: | 23 May 2007 12:59 |
| Reporter: | Richard de Courtney | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S1 (Critical) |
| Version: | 3.51 & 5.1 latest sources | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | dates, ODBC | ||
[21 May 2007 11:39]
Jeff Holmstedt
Have same issue using ODBC 3.51.12. Cannot interpret use of Date() function using MS Access. Reverted back to 5.0.27 and works fine again. Unfortunately, I found that going back to pervious version was a difficult. I had to end up removing MYSQL, and reinstall version 5.0.27 then restoring the database. I think 5.0.41 changed something within the ibData file where you could no longer use data with pervious version.
[23 May 2007 12:59]
Tonci Grgin
Hi Richard and thanks for your report. This is actually a duplicate of Bug#10491 which is worked on and here's why: C:\mysql507\bin>mysql -uroot -T test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.38-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SELECT DATE_ADD('2007-01-01', INTERVAL -weekday('2007-01-01') DAY) AS Wee kStart, date(DATE_ADD('2007-01-01', INTERVAL +(7-weekday('2007-01-01')) DAY)) AS WeekEnd; Field 1: `WeekStart` Catalog: `def` Database: `` Table: `` Org_table: `` Type: STRING Collation: binary (63) Length: 29 Max_length: 10 Decimals: 31 Flags: BINARY Field 2: `WeekEnd` Catalog: `def` Database: `` Table: `` Org_table: `` Type: DATE Collation: binary (63) Length: 10 Max_length: 10 Decimals: 31 Flags: BINARY +------------+------------+ | WeekStart | WeekEnd | +------------+------------+ | 2007-01-01 | 2007-01-08 | +------------+------------+ 1 row in set (0.01 sec) mysql> Notice that both fields have "Flags: BINARY" set. As for ASP page, it actually received "0x2007-01-01" and interpreted it like ????. There is nothing much ODBC connector can do, within specs, with wrong metadata returned from server. The problem arose in 3.51.14 as a side effect of perfectly correct patch.
Description: After upgrading from ODBC 3.51.12 to ODBC 3.51.15, it appears as though the ODBC driver isn't translating a certain format of date outputted by MySQL server (which on our server is formatted as YYYY-MM-DD). How to repeat: When running: SELECT DATE_ADD('2007-01-01', INTERVAL -weekday('2007-01-01') DAY)) AS WeekStart, date(DATE_ADD('2007-01-01', INTERVAL +(7-weekday('2007-01-01')) DAY)) AS WeekEnd The outputted dates are: WeekStart: 2007-01-01 WeekEnd: 2007-01-08 Which works great.. however when retrieving the dates using ASP and ODBC 3.51.15, the dates are returned as ???? unless each date_add above is wrapped in DATE() See: http://friend.ac/dev/dateODBCerror.asp (the output has to be wrapped in cStr() to output the data, otherwise a VBscript runtime error "Type Mismatch" error is show) This worked previously using 3.51.12