MySQL Bugs: #25481: ODBC appears to periodically garble data sent to Excel

Bug #25481	ODBC appears to periodically garble data sent to Excel
Submitted:	8 Jan 2007 23:13	Modified:	4 Mar 2008 20:47
Reporter:	Shawn Green	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1	OS:	Windows (Windows XP-pro)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	Excel, MS Query, ODBC

Description:
While reading data through the same version of the ODBC driver (v5.0.10), Excel 2000 does not randomly garble retrieved data. However the same tests performed with Excel 2002 results in random data in both text and numeric fields.

The tables are setup for unicode data as they need to contain both French and Japanese data.

How to repeat:
Start with Excel 2002 (10.6789.6735) SP3, the attached (private) workbook, and the attached (private) database schema.

Connect to the database from it, then
right click in top left box and say Edit Query. You will find that MS Query
launches and displays the information correctly. If you then exit MS Query,
the values returned to the Worksheet are incorrect in Title_line_1. If you
Right click in A1 and say Refresh Data, the bad values will change to
different bad values.

Suggested fix:
Update C/ODBC so that it works as well with Excel 2002 as it does Excel 2000

Re-opening to investigate further. I don't think this is fixed.

I found a similar problem. Difference is that the garbage returned seems to be periodic, and sometimes partly correct. Especially date and time values are badly mixed up and may not be returned at all. In the latter case the column heading is also missing.

Furthermore, when calling the same query from a visual basic statement, the same garbage is returned. VB code:

    With ActiveSheet.QueryTables.Add(Connection:=Array( _
        "ODBC;PORT=;SERVER=<<ip-address>>;DRIVER=MySQL Connector/ODBC v5;DATABASE=<<name of database>>;UID=<<username>>;PWD=<<password>>"), _
        Destination:=Range("B4"))
        .CommandText = Array(<<query text>>)
        .Name = <<query name>>
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .Refresh BackgroundQuery:=False
    End With

Follow-up:
OS: Microsoft Windows XP Professional 5.1.2600 SP2.0
Office: Microsoft Office Excel 2003 (11.8012.8122) SP2

Workaround for now will be to use MySQL ODBC 3.51 Driver
The only issue that remains is a known bug in Excel concerning properly rendering date/time values. Times should be cast to strings in your queries using a cast like 

SELECT CAST(mytime AS CHAR) AS "mytime"

instead of just

SELECT mytime

I can reproduce this behaviour by using MyODBC version 5.1, MySQL server version 5.1.22 and MS Excel 2007.

I only tested with German "umlauts" (äöüß) and the results have encoding mishmash in both directions.

For Example: 'ß' will be displayed as 'á' in Excel. And an 'ä' updated via MS Query will be stored as 'õ'.

MyODBC 5.1.1-beta returns the data in excel surprisingly well. However, MS Query shows scrambled german characters before passing the data to Excel.

With MS SQL 2005 server MS Query shows incorrect data too... So this is expected behavior. The problem has been fixed in MyODBC 5.1.2.