MySQL Bugs: #40553: Problem with MSAccess and Excel display of Varbinary

Bug #40553	Problem with MSAccess and Excel display of Varbinary
Submitted:	6 Nov 2008 15:37	Modified:	7 Nov 2008 16:40
Reporter:	Fred S	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51.26 & 5.1.7	OS:	Windows (2003 Server and XP)
Assigned to:		CPU Architecture:	Any
Tags:	MSAccess, ODBC, varbinary

Description:
I am using MySQL ODBC 3.51.26.00 and MS Access 2000 (from Windows XP) with MySQL 5.0.67 (on 2003 Server). When I link from Access via ODBC to a MySQL table, fields with datatype Varbinary do not display correctly (a bunch of boxes like undefined characters). I have also found that these fields do not return to Excel though they display in MSQuery. I am assuming this is due to the binary fields not being handled properly as text but I do not know what to do about it?

Tried latest rev of ODBC driver (5.1.7) and got same result. 

The same arrangment except MySQL ODBC 3.51.11 and MySQL 4.1.11 did not have this problem. I am porting to a new server and wanted to rev up in the process but it is causing me headaches. Is there a fix or must I rev back down? 

I have searched forums and bugs and cannot find a work-around.  I looked at 19.1.4.2 Connector/ODBC Connection Parameters and have tried setting option FLAG_NO_BINARY_RESULT based on other forum posts that seemed similar with no good result.

How to repeat:
The Varbinary fields I am having problems with are created by using CONCAT on two or more CHAR fields - I guess MySQL automatically creates this as a Varbinary?

Hi Fred and thanks for your report.

Verified just as described.

Now, what appears to be happening is that FLAG_NO_BINARY_RESULT works only for ad-hoc queries but not for table fields. I would call it expected behaviour but guys tell me it should work for tables too. Both c/ODBC versions affected.

Server version: 5.0.68-pb10-log MySQL Pushbuild Edition, build 10

mysql> create table bug40553 SELECT CONCAT(1,9) AS Fld1;

mysql> show create table bug40553;
+----------+--------------------------------------------------------------------
--------------------------------------+
| Table    | Create Table
                                      |
+----------+--------------------------------------------------------------------
--------------------------------------+
| bug40553 | CREATE TABLE `bug40553` (
  `Fld1` varbinary(2) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+----------+--------------------------------------------------------------------
--------------------------------------+
1 row in set (0.00 sec)

mysql> select * from bug40553;
Field   1:  `Fld1`
Catalog:    `def`
Database:   `test`
Table:      `bug40553`
Org_table:  `bug40553`
Type:       VAR_STRING
Collation:  binary (63)
Length:     2
Max_length: 2
Decimals:   0
Flags:      NOT_NULL BINARY

+------+
| Fld1 |
+------+
| 19   |
+------+
1 row in set (0.00 sec)

odbcte32.exe:
select * from bug40553
		Return:	SQL_SUCCESS=0

Get Data All:
"bug40553.Fld1"
0x19
1 row fetched from 1 column.

Access:
MSACCESS        1b34-f04	ENTER SQLPrepareW 
		HSTMT               08051FA0
		WCHAR *             0x094D1AA8 [      -3] "SELECT `Fld1`  FROM `bug40553`  WHERE `Fld1` = ? OR `Fld1` = ? OR `Fld1` = ? OR `Fld1` = ? OR `Fld1` = ? OR `Fld1` = ? OR `Fld1` = ? OR `Fld1` = ? OR `Fld1` = ? OR `Fld1` = ?\ 0"
		SDWORD                    -3

MSACCESS        1b34-f04	ENTER SQLBindParameter 
		HSTMT               08051FA0
		UWORD                        1 
		SWORD                        1 <SQL_PARAM_INPUT>
		SWORD                       99 <SQL_C_DEFAULT>
		SWORD                       -3 <SQL_VARBINARY>
		SQLULEN                    2
		SWORD                        0 
		PTR                0x094D1C14
		SQLLEN                     0
		SQLLEN *            0x094D1C10
and so on

MSACCESS        1b34-f04	ENTER SQLGetData 
		HSTMT               08051FA0
		UWORD                        1 
		SWORD                       -2 <SQL_C_BINARY>
		PTR                 <unknown type>
		SQLLEN                   512
		SQLLEN *            0x0013CB98

and so on.

For now, please use CAST AS CHAR to bypass the problem.

Sorry, have to overrule myself.

It is as I suspected. FLAG_NO_BINARY_RESULT was introduced to bypass a flaw in MySQL server regarding metadata sent when using *functions* or *ad-hoc* queries and was never intended to change database fields. Please see Bug#10491 for explanation (quoting myself):
    Connector can't work around this, at least not reliably. Ad hoc user queries do not allow connector to distinguish between "SHOW CREATE TABLE", where it should treat BINARY as UTF8, and "SELECT varbinary_col FROM some_table", where it really should be binary.

So as you see, the intention of FLAG_NO_BINARY_RESULT was never to change field type already present in table.
Please use CAST or change your underlying table's field type.