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:	8 Mar 2007 23:08	Modified:	2 Dec 2010 8:48
Reporter:	Erica Moss	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	5.1.3	OS:	Windows (win xp)
Assigned to:	Lawrenty Novitsky	CPU Architecture:	Any

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

Eric, please reverify if necessary.

The test (ado-compliance\trunk\bugs\26973.vbs) passes without errors in Connector/ODBC 3.51.27 and 5.1.8.
Closing the bug