MySQL Bugs: #28342: MyODBC 3/ADO extra spaces in data after writing with appendchunk into blob field

Bug #28342	MyODBC 3/ADO extra spaces in data after writing with appendchunk into blob field
Submitted:	9 May 2007 22:47	Modified:	9 May 2007 22:48
Reporter:	Erica Moss	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.14	OS:	Windows (XP sp2)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
If you use the mysql client to read the table data after running the test code below, field C2 will contain the value "b a r " instead of "bar" as expected.

This will be true if the field type is Medium Blob, Blob, or Long Blob

How to repeat:
SETUP:
DROP TABLE IF EXISTS ado_test;
CREATE TABLE ado_test (C1 INT PRIMARY KEY, C2 BLOB, C3 TIMESTAMP);
INSERT INTO ado_test (C1,C2) VALUES (1, 'foo');

VB Code:
Private Sub Test()
    On Error GoTo EH
    Dim strSQL, strConn As String
    Dim connection1 As ADODB.Connection
    Dim recset1 As ADODB.Recordset
    Dim fld As Field
    Dim strTest As Variant
    Set connection1 = New ADODB.Connection
    Set recset1 = New ADODB.Recordset

    strSQL = "SELECT * FROM ado_test"
    strTest = "bar"
        
    strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _
        "SERVER=localhost;DATABASE=ado" & _
        ";USER=root;PASSWORD=mypass;OPTION=3;"

  
    ' open connection
    connection1.ConnectionString = strConn
    connection1.CursorLocation = adUseClient
    connection1.Open
    
    recset1.Open strSQL, connection1, adOpenStatic, adLockOptimistic, adCmdText
    recset1.MoveFirst
    
    ' modify field value
    Set fld = recset1.Fields("C2")
    fld.AppendChunk strTest
    recset1.Update
    
    'Cleanup
    If Not recset1 Is Nothing Then
        If CBool((recset1.State And adStateOpen) = adStateOpen) Then
            recset1.Close
        End If
        Set recset1 = Nothing
    End If
 
    If CBool((connection1.State And adStateOpen) = adStateOpen) Then
        connection1.Close
    End If
    Set connection1 = Nothing
    Exit Sub
EH:
    Debug.Print Err.Description & " " & Err.Number & vbNewLine
    Resume Next
End Sub

ODBC trace file

Attachment: SQL.LOG (application/octet-stream, text), 78.34 KiB.

Most likely ADODB somehow sees the blob as a text blob
with unicode text, and writes in it that way taking two
bytes for each ascii characters.

Unclear when this happen, some clue maybe in the text
starting with "ADO 2.1 and later might..." at the page

  http://support.microsoft.com/kb/194975

Maybe this is not a bug at all? Storing an ADO string
(that is of 'ntext' type?) with AppendChunk into a
Unicode capable SQL data type will use two bytes
per character? Else if storing a string with a
character that doesn't fit into 8 bits would make
storing and retrieving the same string from ADO
impossible.

A column of type TEXT might be another matter, as it
as a specified character set.

Verified in Connector/ODBC 3.51.27 and 5.1.8