Bug #70765 Error for fetching values from TEXT field
Submitted: 29 Oct 2013 16:20 Modified: 10 Jan 2014 11:54
Reporter: Rafael Caesar Lenzi Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:5.2.6 OS:Microsoft Windows
Assigned to: Bogdan Degtyariov CPU Architecture:Any

[29 Oct 2013 16:20] Rafael Caesar Lenzi
Description:
Using mysql-connector-odbc-5.2.6-win32 on a MS VBA project, when will use a TEXT field in database, the ODBC return null value and cause error on program.

How to repeat:
Create a VBA excel project, insert a simple module, add Microsoft ADO 2.8 reference and use the following function:

Public Function MyODBCConnectionTest()
    Dim myConnection As New ADODB.Connection
    
    myConnection.Open "DRIVER={MySQL ODBC 5.2 ANSI DRIVER};Server=servername;DB=databasename;User=username;Password=password;"
    
    Dim myCommand As New ADODB.Command, myRecordset As ADODB.Recordset
    
    myCommand.ActiveConnection = myConnection
    myCommand.CommandText = "SELECT * FROM testtable;"
    Set myRecordset = myCommand.Execute
    
    MsgBox myRecordset!name 'Everything ok on the first time
    MsgBox myRecordset!name 'Error on the second time
End Function

Table definition:
CREATE TABLE `testtable` (
  `name` text
) ENGINE=InnoDB;

Mysql server version: 5.5.23 MySQL Community Server (GPL)
[29 Oct 2013 18:44] MySQL Verification Team
Row with right data

Attachment: odbc_text_field.png (image/png, text), 90.65 KiB.

[29 Oct 2013 18:45] MySQL Verification Team
Thank you for the bug report. Please see attached screenshot. Thanks
[1 Nov 2013 13:32] Rafael Caesar Lenzi
Example file

Attachment: MySQL test2.xls (application/vnd.ms-excel, text), 30.00 KiB.

[1 Nov 2013 13:34] Rafael Caesar Lenzi
Godofredo, try with the new file, at the second msgbox, it doesnt show the row value. Thanks
[6 Nov 2013 16:01] MySQL Verification Team
Opened again for double check.
[13 Nov 2013 11:20] Bogdan Degtyariov
I was able to repeat the problem using the provided xls file.
First time the contents of the TEXT column displayed without any issues, but trying to read it for the second time resulted in an empty string.
Please check the screenshot (excel_empty_result.png).
[13 Nov 2013 11:20] Bogdan Degtyariov
Screenshot showing the issue

Attachment: excel_empty_result.png (image/png, text), 21.74 KiB.

[15 Sep 2014 20:41] Davi Furuya
I experienced the same issue. I will post a workaround that worked for me.

__My settings__

- MySQL Server 5.7 (5.7.4-m14)

- MySQL ODBC 5.3 Unicode Driver (5.3.4-win32)

- Excel 2010 (14.0.7128.5000 (32-bit)) - Microsoft Office Home and Business 2010

- Windows 7 Home Basic x64 (6.1.7601 Service Pack 1 Build 7601) in Taiwanese (ZH-TW)

__Description and Workaround__

- In my program, I have a ADODB.Recordset object (I will call it `oRec`) with many fields (each field correspond to a column)

- One of its fields (third column, or field number #2) comes from a TEXT column (I will call it `colTEXT`)

- Only `colTEXT` returns NULL value (in database it is not NULL). That's the bug described here by Rafael Caesar Lenzi.

- In the first time I individually access `colTEXT`, it returns the correct value. The command used is `oRec.Fields.Item(2).Value` (`colTEXT` is field number #2)

- In all the subsequent times I individually access it, it returns NULL (the bug described here)

- **BUT**, if I access the last field -- via the command `oRec.Fields.Item(oRec.Fields.Count - 1).Value` -- and _then_ access `colTEXT` once again, it now returns the correct value

__Notes__

- I'm able to reproduce this behavior consistently, not only with `colTEXT` (I have other columns too that exhibit the exact same behavior)

- It also returns NULL if -- instead of accessing it individually -- I use the method `rangeObject.CopyFromRecordset(oRec)` (regardless if it's the first time or not I access it)

I hope this helps to get his bug fixed.
[15 Sep 2014 21:05] Davi Furuya
How to edit my message? I have to rectify my previous message.

The workaround is hard to explain with words (aka just my incompetence with writing).

So I will just use an example of how to get the correct values instead of NULL. Remember that field #2 represents the TEXT column (`colTEXT` described above) and holds the value of interest.

Access #01: oRec.Fields.Item(2).Value -> returns correct value
Access #02: oRec.Fields.Item(2).Value -> returns NULL
Access #03: oRec.Fields.Item(5).Value -> (let's try item 5)
Access #04: oRec.Fields.Item(2).Value -> returns correct value
Access #05: oRec.Fields.Item(5).Value -> (let's try item 5 again)
Access #06: oRec.Fields.Item(2).Value -> returns NULL
Access #07: oRec.Fields.Item(7).Value -> (let's try item 7)
Access #08: oRec.Fields.Item(2).Value -> returns correct value
Access #09: oRec.Fields.Item(8).Value -> (let's not try item 7 again)
Access #10: oRec.Fields.Item(2).Value -> returns correct value

I think you can have an idea now how this workaround works.
[30 Jan 2015 14:02] NO WAY
I also had the bug, in excel vba, using 3.52 or 5.1 odbc driver, with ado 2.6 o 2.8.

in my case, it was a "cursor" problem

setting "CursorLocation = adUseClient" before opening the recordset made the field value behave right.

hth

Marco