MySQL Bugs: #28841: VARBINARY can not be imported in Excel with ODBC 3.51.15

Bug #28841	VARBINARY can not be imported in Excel with ODBC 3.51.15
Submitted:	1 Jun 2007 17:24	Modified:	31 Jul 2007 7:52
Reporter:	Daniel Pützschler	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.15	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	Excel, import, ODBC, varbinary

Description:
By trying to import different type of field from MYSQL to EXCEL, with the ODBC version 3.51.06 all field could be imported without problem.

With the version 3.51.15, the field types VARBINARY in MYSQL can no longer be imported. EXCEL just ignore this field and the result is blank. 

How to repeat:
Create a DB.
Create 2 fields: the first as an Integer, the second as a VARBINARY.
Fill them.
Install the ODBC version 3.51.15.
Open Excel and import this DB with this ODBC connection.
You will see only the integer values, but none VARBINARY!

With ODBC version 3.51.06 it function.

Suggested fix:
?

Hi Daniel and thanks for your report.

Please attach SQL script containing table definition and some data. Also, do attach my.cnf/my.ini file.
Please provide more info on server and system configuration you use, you can gather it with MDAC.

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".

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

Hi Daniel. This problem is not repeatable with MyODBC 3.51.17 (a paste from Excel):
Id	VBFld1
1	Row 1 FLD 2
2	Row 2 FLD 2

Your MySQL connection id is 6
Server version: 5.0.44-max-nt-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE bug28841 (
    -> Id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> VBFld1 VARBINARY(50) DEFAULT NULL,
    -> VBFld2 BINARY(50));
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO bug28841 VALUES (NULL, "Row 1 FLD 2", "Row1 FLD 3");
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO bug28841 VALUES (NULL, "Row 2 FLD 2", "Row2 FLD 3");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM bug28841;
+----+-------------+----------------------------------------------------+
| Id | VBFld1      | VBFld2                                             |
+----+-------------+----------------------------------------------------+
|  1 | Row 1 FLD 2 | Row1 FLD 3                                         |
|  2 | Row 2 FLD 2 | Row2 FLD 3                                         |
+----+-------------+----------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE bug28841;
| Table    | Create Table                                                                               | 
 bug28841 | CREATE TABLE `bug28841` (
  `Id` int(10) unsigned NOT NULL auto_increment,
  `VBFld1` varbinary(50) default NULL,
  `VBFld2` binary(50) default NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

I think I see what went wrong in 3.51.15, it interpreted VARBINARY as BLOB:
mysql> SELECT * FROM bug28841;
Field   1:  `Id`
Catalog:    `def`
Database:   `test`
Table:      `bug28841`
Org_table:  `bug28841`
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 1
Decimals:   0
Flags:      NOT_NULL PRI_KEY UNSIGNED AUTO_INCREMENT NUM PART_KEY

Field   2:  `VBFld1`
Catalog:    `def`
Database:   `test`
Table:      `bug28841`
Org_table:  `bug28841`
Type:       VAR_STRING
Collation:  binary (63)
Length:     50
Max_length: 11
Decimals:   0
Flags:      BINARY

Field   3:  `VBFld2`
Catalog:    `def`
Database:   `test`
Table:      `bug28841`
Org_table:  `bug28841`
Type:       STRING
Collation:  binary (63)
Length:     50
Max_length: 50
Decimals:   0
Flags:      BINARY

+----+-------------+----------------------------------------------------+
| Id | VBFld1      | VBFld2                                             |
+----+-------------+----------------------------------------------------+
|  1 | Row 1 FLD 2 | Row1 FLD 3                                         |
|  2 | Row 2 FLD 2 | Row2 FLD 3                                         |
+----+-------------+----------------------------------------------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE bug28841 ADD COLUMN BLFld BLOB;
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM bug28841;
Field   1:  `Id`
Catalog:    `def`
Database:   `test`
Table:      `bug28841`
Org_table:  `bug28841`
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 1
Decimals:   0
Flags:      NOT_NULL PRI_KEY UNSIGNED AUTO_INCREMENT NUM PART_KEY

Field   2:  `VBFld1`
Catalog:    `def`
Database:   `test`
Table:      `bug28841`
Org_table:  `bug28841`
Type:       VAR_STRING
Collation:  binary (63)
Length:     50
Max_length: 11
Decimals:   0
Flags:      BINARY

Field   3:  `VBFld2`
Catalog:    `def`
Database:   `test`
Table:      `bug28841`
Org_table:  `bug28841`
Type:       STRING
Collation:  binary (63)
Length:     50
Max_length: 50
Decimals:   0
Flags:      BINARY

Field   4:  `BLFld`
Catalog:    `def`
Database:   `test`
Table:      `bug28841`
Org_table:  `bug28841`
Type:       BLOB
Collation:  binary (63)
Length:     65535
Max_length: 0
Decimals:   0
Flags:      BLOB BINARY

+----+-------------+----------------------------------------------------+-------
+
| Id | VBFld1      | VBFld2                                             | BLFld
|
+----+-------------+----------------------------------------------------+-------
+
|  1 | Row 1 FLD 2 | Row1 FLD 3                                         | NULL
|
|  2 | Row 2 FLD 2 | Row2 FLD 3                                         | NULL
|
+----+-------------+----------------------------------------------------+-------
+
2 rows in set (0.00 sec)

Continued in Bug#30137