| Bug #28809 | MyODBC 3.51.15 looses data if select using date_format | ||
|---|---|---|---|
| Submitted: | 31 May 2007 14:43 | Modified: | 28 May 2013 8:23 |
| Reporter: | haroon chaudhry | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S3 (Non-critical) |
| Version: | 3.51.15 | OS: | Any (Server using Linux clients on windows) |
| Assigned to: | CPU Architecture: | Any | |
[1 Jun 2007 17:02]
Tonci Grgin
Hi Haroon and thanks for your report. I am sorry but there seems to be something wrong with your configuration regarding MS SW. - MySQL server 5.0.38BK on WinXP Pro SP2 localhost, fully patched - MS Office 2003 fully patched - MyODBC 3.51.15GA, DSN, Option values: Don't optimize column width. C:\mysql507\bin>mysql -uroot -T test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.0.38-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table odbcBug (dateColumn Datetime default null); Query OK, 0 rows affected (0.06 sec) mysql> insert into odbcBug select now(); Query OK, 1 row affected (0.05 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from odbcBug; Field 1: `dateColumn` Catalog: `def` Database: `test` Table: `odbcBug` Org_table: `odbcbug` Type: DATETIME Collation: binary (63) Length: 19 Max_length: 19 Decimals: 0 Flags: BINARY +---------------------+ | dateColumn | +---------------------+ | 2007-06-01 18:49:31 | +---------------------+ 1 row in set (0.00 sec) mysql> create view odbcBugView as select date_format(dateColumn,'%M %e, %Y') fro m odbcBug; Query OK, 0 rows affected (0.02 sec) mysql> select * from odbcBugView; Field 1: `date_format(dateColumn,'%M %e, %Y')` Catalog: `def` Database: `` Table: `odbcBugView` Org_table: `` Type: VAR_STRING Collation: latin1_swedish_ci (8) Length: 73 Max_length: 12 Decimals: 0 Flags: +-------------------------------------+ | date_format(dateColumn,'%M %e, %Y') | +-------------------------------------+ | June 1, 2007 | +-------------------------------------+ 1 row in set (0.01 sec) ODBCte32.exe, MS generic ODBC client: Full Connect(Use Driver) Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3 Successfully connected to DSN 'myodbc1cln'. SQLExecDirect: In:hstmt = 0x008B1978, szSqlStr = "select * from odbcBug",cbSqlStr = -3 Return: SQL_SUCCESS=0 Get Data All: "dateColumn" 2007-06-01 18:49:31 1 row fetched from 1 column. SQLExecDirect: In:hstmt=0x008B1978,szSqlStr= "select * from odbcBugView",cbSqlStr = -3 Return: SQL_SUCCESS=0 Get Data All: "date_format(dateColumn,'%M %e, %Y')" "June 1, 2007" 1 row fetched from 1 column. Access works also, both linking and importing... Now, if the problem was in mysql> select * from odbcBug; Field 1: `dateColumn` Catalog: `def` Database: `test` Table: `odbcBug` Org_table: `odbcbug` Type: DATETIME Collation: binary (63) Length: 19 Max_length: 19 Decimals: 0 Flags: BINARY I would have said it's due to BINARY flag set but it isn't. Please try upgrading your SW and come back to me with results.
[1 Jul 2007 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[28 May 2013 8:23]
Bogdan Degtyariov
Cannot continue without feedback from the reporter. The bug is closed.
Description: Environment MySQL server 5.0.27 on FedoraCore 5 ODBC Connector version 3.51.15 (WinXP, Windows 2003 server, win98) ODBC can't properly read columns selected as select date_format(someDateColumn,'%M %e, %Y'); If I replace select statement with select someDateColumn; It works fine, same query works using driver 3.51.12 (I think earlier version were also working) I tried to "manually" formate date using select concat('',monthname(datec),' ', day(datec),', ',year(datec)) ; but it still does not work. Any idea how to fix it? or any work around? Thanks Haroon How to repeat: 1. Create sample data: mysql> create table odbcBug (dateColumn Datetime default null); mysql> insert into odbcBug select now(); mysql> select * from odbcBug; mysql>create view odbcBugView as select date_format(dateColumn,'%M %e, %Y') from odbcBug; 2. Connect to this databse using ODBC driver 3.51.15 Create a User or System DSN for database used above. Now try to open this view odbcBugView using ODBC DSN just created, I tried using MS World mail merge data source and MS Access, MS Excel etc. MS Excel is easy to explain, open a new spread sheet goto Menu->Data->Import select new Data Connection select ODBC DSN, pick DNS create above select table odbcBug, it will show date, but if you select odbcBugView it will give error saying no data is returned.