MySQL Bugs: #70765: Error for fetching values from TEXT field

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:	Windows
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

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)

Row with right data

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

Thank you for the bug report. Please see attached screenshot. Thanks

Example file

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

Godofredo, try with the new file, at the second msgbox, it doesnt show the row value. Thanks

Opened again for double check.

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

Screenshot showing the issue

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

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.

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.

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