MySQL Bugs: #30349: MyODBC 3/ADO Unable to update records that contain bit data type-regression

Bug #30349	MyODBC 3/ADO Unable to update records that contain bit data type-regression
Submitted:	10 Aug 2007 6:36	Modified:	21 Dec 2010 13:56
Reporter:	Erica Moss	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.18	OS:	Windows (Windows XP SP2)
Assigned to:	Lawrenty Novitsky	CPU Architecture:	Any
Tags:	ADO BIT

Description:
This bug was introduced in .18.  This test will work in versions previous

The following error is thrown when the update method is called.
-2147467259:[MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-nt-log]Truncated incorrect DOUBLE value: ''

The same error is thrown for any BIT(n) column where n>=1
The test only fails when the adUseServer cursor is used, adUseClient works.

How to repeat:
Setup:
CREATE TABLE bittest (C1 INT PRIMARY KEY, C2 CHAR(10), C3 BIT(2));
INSERT INTO bittest VALUES (1,'FOO',1),(2,'BAR',2);

VB Code:
Private Sub bittest()
    On Error GoTo EH
    Dim strSQL, strConn As String
    Dim connection1 As adodb.Connection
    Dim recset1 As adodb.Recordset
    Dim fld As Field

    Set connection1 = New adodb.Connection
    Set recset1 = New adodb.Recordset

    strSQL = "SELECT * FROM bittest"
                
    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
    
   ' open recordset
    recset1.Open strSQL, connection1, adOpenForwardOnly, _
             adLockOptimistic, adCmdText

    While Not recset1.EOF
        For Each fld In recset1.Fields
            If fld.Type = adChar Then
                fld.Value = "test"
                recset1.Update
            End If
        Next
        recset1.MoveNext
    Wend
           
    recset1.Close
    Set recset1 = Nothing
    connection1.Close
    Set connection1 = Nothing
    Exit Sub
EH:
    Debug.Print Err.Number & ":" & Err.Description
    recset1.Close
    Set recset1 = Nothing
    connection1.Close
    Set connection1 = Nothing
End Sub

Forgot to mention, although the error seems to indicate that the data was simply truncated, the update never occurs, and there is no statement in the General Log to indicate an attempt

Hi Eric,

thank you for writing a bug report.

Please test, if the error still occur in 3.51.19.

Regards,

Susanne

Retested against 3.51.22 and 5.1.1 - Result for both drivers was identical.

The error received now is different but still occurs on the update statement
"Trace: 3219::Operation is not allowed in this context."

TestCase added to the ADO conformance tests
\ado_conformance\trunk\tests\bugs\30349.vbs

Bug#35615 has been marked as duplicate of this one.

Strange, the test failed for me (3.51.27) with the following error:

-2147467259:Query-based update failed because the row to update could not be found.

5.1.8 did not do any updates at all.