MySQL Bugs: #26973: MyODBC 5/ADO adDbTime type can't be updated

Bug #26973	MyODBC 5/ADO adDbTime type can't be updated - Regression
Submitted:	9 Mar 2007 0:08	Modified:	12 Apr 2008 8:08
Reporter:	Eric MaLossi
Status:	Open
Category:	Connector/ODBC	Severity:	S1 (Critical)
Version:	5.1.3	OS:	Microsoft Windows (win xp)
Assigned to:	Eric MaLossi	Target Version:

Description:
It is currently not possible to update a field with type adDbTime.  If you attempt to
update the field itself, it fails with the error:
13:Type mismatch

If you just have a time field in the record set, and attempt to update the RS after
changing another field, it fails with this error:
-2147467259:[MySQL][MyODBC 5.00.12] Restricted data type attribute violation

How to repeat:
mysql> INSERT INTO foo VALUES (1, 'A', TIME(NOW())), (2, 'B', TIME(NOW());
mysql> INSERT INTO foo VALUES (1, 'A', TIME(NOW())), (2, 'B', TIME(NOW()));

VB test code:
Private Sub TimeTest()
    On Error GoTo EH
    Dim strSQL As String
    Dim strConn As String
    Dim connection1 As ADODB.Connection
    Dim recset1 As ADODB.Recordset
    Dim fld As Field
    Dim fldName As String

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

    strSQL = "select * from foo"

    strConn = "DRIVER={MySQL Connector/ODBC v5};" & _
        "SERVER=localhost;DATABASE=ado" & _
        ";UID=root;PWD=mypass;OPTION=3;"

    ' open connection
    connection1.ConnectionString = strConn
    connection1.CursorLocation = adUseServer
    connection1.Open
   ' open recordset
    recset1.Open strSQL, connection1, adOpenForwardOnly, _
             adLockOptimistic, adCmdText
             
    Dim lngType As Long
    If Not recset1.EOF Then
            recset1.MoveFirst
    End If
    
    If recset1.Supports(adUpdate) Then
        While Not recset1.EOF
            For Each fld In recset1.Fields
                lngType = CLng(fld.Type)
                fldName = fld.Name
                    
                If lngType = adDBTime Then
                    fld.Type = "11:59:59"
                    recset1.Update
                End If
                
                If lngType = adChar Then
                    fld.Value = "test"
                    recset1.Update
                End If
            Next
            recset1.MoveNext
        Wend
    End If
    recset1.Close
    Set recset1 = Nothing
    connection1.Close
    Set connection1 = Nothing
    Exit Sub
EH:
    MsgBox Err.Number & ":" & Err.Description
    MsgBox "failed on column " & fldName
End Sub

Tested against 3.51.20 and 5.1.0.  Problem can't be repeated
Added test case to:
ado-compliance\trunk\bugs\26973.vbs

Hi Eric,

This bug is fixed in our new version MyODBC 5.1.
I'll close the bug.

thank you for trusting MySQL.

Kind Regards,

Susanne

trace run against myodbc 5.1.2

Attachment: SQL_512.LOG (application/octet-stream, text), 154.25 KiB.

trace run against myodbc 5.1.3

Attachment: SQL_513.LOG (application/octet-stream, text), 137.26 KiB.

This case has been functioning up until MyODBC 5.1.2.  As of 5.1.3 an attempt to update a
record with a TIME field will cause the cursor to be lost, and the following error
returned:

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

Test case found in 
/tests/bugs/26973.vbs