MySQL Bugs: #43275: ODBC Connector 5.1.5 does not work with Microsoft Word and text fields

Bug #43275	ODBC Connector 5.1.5 does not work with Microsoft Word and text fields
Submitted:	28 Feb 2009 7:59	Modified:	3 Mar 2009 18:25
Reporter:	John Kounis	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	5.1.5	OS:	Windows (XP/SP3)
Assigned to:		CPU Architecture:	Any
Tags:	Mailmerge ODBC

Description:
Microsoft Word receives a null string from ODBC connector 5.1.5 when it provides text fields from a table. It works fine with the 3.51 connector, or with literal text fields not retrieved from a table.

My server version is as follows:
mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.0.67-0ubuntu6 |
+-----------------+
1 row in set (0.00 sec)

NOTE: I flagged this as S3(non-critical), since the work-around is to use the 3.51 connector. If the 3.51 connector weren't available, I suppose it would be critical, since I can't figure out a way to perform a mail-merge with MySQL data otherwise.

How to repeat:
(1) Create two DSNs (one for 5.1.5 and the other for 3.51.27.
(2) Create a temp table, insert one test row, and also create a view that simply returns a literal text string:

CREATE TABLE tbl515Test (
  Line1 VARCHAR(20),
  PRIMARY KEY (Line1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

INSERT INTO tbl515Test(Line1) VALUES('tbl515Test String');

CREATE VIEW qryTest2
AS
  SELECT 'this is a test' AS Line1;

(3) Try the following code:

Sub InvokeODBCTest()
Dim wrdApp As Word.Application
  Set wrdApp = New Word.Application
  wrdApp.Visible = True

  MsgBox "Press OK to try SELECT with ODBCv5.1.5 connector", vbOKOnly
  ODBCTest wrdApp, "DSN=SubDBv3;DATABASE=SubDBv3;User=XXXX;Password=XXXX;", "SELECT Line1 FROM tbl515Test"
  
  MsgBox "Press OK to try SELECT with ODBCv 3.51 connector", vbOKOnly
  ODBCTest wrdApp, "DSN=SubDBv3.51;DATABASE=SubDBv3;User=XXXX;Password=XXXX;", "SELECT Line1 FROM tbl515Test"
  
  MsgBox "Press OK to try qryTest2 with ODBCv5.1.5 connector", vbOKOnly
  ODBCTest wrdApp, "DSN=SubDBv3;DATABASE=SubDBv3;User=XXXX;Password=XXXX;", "SELECT Line1 FROM qryTest2"
  
  MsgBox "Press OK to try qryTest2 with ODBCv3.51 connector", vbOKOnly
  ODBCTest wrdApp, "DSN=SubDBv3.51;DATABASE=SubDBv3;User=XXXX;Password=XXXX;", "SELECT Line1 FROM qryTest2"
  
End Sub

Sub ODBCTest(ByRef wrdApp As Word.Application, ByVal strConnStr As String, ByVal strQueryParm As String)
Dim wrdDoc As Word.document

  
  Set wrdDoc = wrdApp.Documents.Add
  wrdDoc.MailMerge.MainDocumentType = wdFormLetters
  wrdDoc.MailMerge.Fields.Add wrdDoc.Range, "Line1"
  wrdDoc.MailMerge.OpenDataSource Name:="", Linktosource:=True, Connection:=strConnStr, SQLStatement:=strQueryParm
  MsgBox "The value of line 1 is: " & wrdDoc.MailMerge.DataSource.DataFields(1).Value
End Sub

(4) The first call to ODBCTest results in a word document that shows a blank string as the value of "Line1" (It should contain the text 'tbl515Test String'). All other combinations work as expected.

(5) The retrieval of wrdDoc.MailMerge.DataSource.DataFields(1).Value returns the following error in test case 1 (all other invocations work fine):

Run-time error '-2147467259(80004005)':

Automation error
Unspecified error

Hi John and thanks for your report.

I am unable to repeat it using following environment:
  o MySQL community server 5.1.31 x64 on OpenSolaris 2008.11
  o WinXP Pro client with Office2003 Pro
  o c/ODBC 5.1.6 and 3.51.27
  o VB code as provided

I suggest you download nightly snapshot of c/ODBC 5.1 from http://downloads.mysql.com/snapshots.php and retest.

Server version: 5.1.31-log MySQL Community Server (GPL)

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

mysql> create table bug43275 (Line1 VARCHAR(20) NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.10 sec)

mysql> create view vbug43275 as select "this is a text" as Line1;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into bug43275 values ("some test string");
Query OK, 1 row affected (0.05 sec)

Sub InvokeODBCTest()
Dim wrdApp As Word.Application
  Set wrdApp = New Word.Application
  wrdApp.Visible = True

  MsgBox "Press OK to try SELECT with ODBCv5.1.5 connector", vbOKOnly
  ODBCTest wrdApp, "DSN=5-1-6-on-opensol;DATABASE=test;User=**;Password=**;", "SELECT Line1 FROM bug43275"
  
  MsgBox "Press OK to try SELECT with ODBCv 3.51 connector", vbOKOnly
  ODBCTest wrdApp, "DSN=3-51-27-on-opensol;DATABASE=test;User=**;Password=**;", "SELECT Line1 FROM bug43275"
  
  MsgBox "Press OK to try qryTest2 with ODBCv5.1.5 connector", vbOKOnly
  ODBCTest wrdApp, "DSN=5-1-6-on-opensol;DATABASE=test;User=**;Password=**;", "SELECT Line1 FROM vbug43275"
  
  MsgBox "Press OK to try qryTest2 with ODBCv3.51 connector", vbOKOnly
  ODBCTest wrdApp, "DSN=3-51-27-on-opensol;DATABASE=test;User=**;Password=**;", "SELECT Line1 FROM vbug43275"
  
End Sub

Sub ODBCTest(ByRef wrdApp As Word.Application, ByVal strConnStr As String, ByVal strQueryParm As String)
Dim wrdDoc As Word.Document

  Set wrdDoc = wrdApp.Documents.Add
  wrdDoc.MailMerge.MainDocumentType = wdFormLetters
  wrdDoc.MailMerge.Fields.Add wrdDoc.Range, "Line1"
  wrdDoc.MailMerge.OpenDataSource Name:="", Linktosource:=True, Connection:=strConnStr, SQLStatement:=strQueryParm
  MsgBox "The value of line 1 is: " & wrdDoc.MailMerge.DataSource.DataFields(1).Value
End Sub

Running same queries in MSQRY32, which provides data to Word anyway, also goes as expected.

I just tried it with ODBC connector 5.1.6 and I still have the erroneous behavior when trying to retrieve data with a select statement directly from a table. I'm running the following configuration:

mysql: 5.0.67-0ubuntu6
kernel: Linux 2.6.27-11-generic #1 SMP (x86_64 GNU/Linux)
Ubuntu: version 8.10
MyODBC Connector: 5.1.6.0
Word 2003 SP3

I suppose the problem is related to the fact that I'm using server version 5.0 and you're using 5.1? That's the only obvious difference between your configuration and my configuration.

If so, I'll likely have to wait, since I don't think a .deb binary download of 5.1 is available for my configuration and I'm not smart enough to build it from source.

Thanks for looking into this.

John, I don't think it's the server... My thoughts go rather to something in Windows/Office. Can you try running same thing against MySQL server 5.1 for the sake of completeness?

I'd be happy to run it against MySQL 5.1. However, I only have a Debian-based server (Ubuntu), and I'm not sure how to build a 5.1 server without breaking my current 5.0-based configuration. Is there a binary 5.1 ".deb" build available?

Since the 3.51 connector works fine for me, I don't need implement a  fix. However, I would like to help troubleshoot this problem if I can.