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

[8 Jan 2007 23:13] Shawn Green
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
[26 Feb 2007 16:47] Jess Balint
Re-opening to investigate further. I don't think this is fixed.
[5 Mar 2007 17:04] marten visser
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
[5 Mar 2007 17:09] marten visser
Follow-up:
OS: Microsoft Windows XP Professional 5.1.2600 SP2.0
Office: Microsoft Office Excel 2003 (11.8012.8122) SP2
[6 Mar 2007 10:49] marten visser
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
[29 Oct 2007 13:32] Susanne Ebrecht
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 'õ'.
[6 Feb 2008 14:07] Bogdan Degtyariov
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.
[4 Mar 2008 20:47] Bogdan Degtyariov
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.