MySQL Bugs: #28343: MyODBC 3/ADO error after updating long text field (invalid string or buffer len)

Bug #28343	MyODBC 3/ADO error after updating long text field (invalid string or buffer len)
Submitted:	9 May 2007 22:59	Modified:	3 Dec 2010 4:45
Reporter:	Erica Moss	Email Updates:
Status:	Duplicate	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:
In the code below, when the the line recset1.Update is called, this error is thrown:

[Microsoft][ODBC Driver Manager] Invalid string or buffer length -2147467259

This only occurs when the data type is LONGTEXT and a ServerSide(driver) cursor is used.  If the cursor location is changed to adUseClient then this code will run.

How to repeat:
SETUP:
DROP TABLE IF EXISTS ado_test;
CREATE TABLE ado_test (C1 INT PRIMARY KEY, C2 LONGTEXT, 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 = adUseServer
    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), 62.85 KiB.

I've verified this against 3.51.19, and raised the priority a bit

The added complexity of using the append method cited in the included test function isn't really necessary.  The problem occurs by doing a simple update to any record set that contains a LongText field IE:
For Each fld In recset1.Fields
   If fld.Name = "LngText" Then
      fld.Value = "test"
   End If
Next
recset1.Update

It also doesn't matter whether the LongText field itself has actually been modified, only that it is present in the recordSet when the update is attempted.

This "could" be a bug in the ADODB driver, not sure.

The MySQL driver correctly returns the max size of
the LONGTEXT object to be 4294967295 (0xffffffff)
as an unsigned integer. In the log it looks like
the ADODB driver have tried allocate memory to store
the LONGTEXT result (how could it succeed in that?!)
and calls SQLBindParameter() with both ColumnSize and
BufferLength set tp the LONGTEXT size. Problem is,
BufferLength is a signed integer, and the size
then becomes -1 (as can be seen in the trace file).

The error message "Invalid string or buffer length"
is specified in the Microsoft documentation to be
issued when BufferLength is less than zero.

Don't know what to do about this. One solution
might be to let SQLDescribeCol() return 0 as
column size, that means "can't be determined".
This might force the ADODB driver to revert to
another method for accessing the data, doing it
in chunks with SQLGetData() maybe. This has to
be tried out.

Connector/ODBC 3.51.27 and 5.1.8 passed the test without errors.
Setting the status to "Duplicate".

Erica,

we probably need the test case for ADO test suite.
Thanks.