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

[8 Mar 2007 23:08] Erica Moss
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
[31 Oct 2007 7:31] Erica Moss
Tested against 3.51.20 and 5.1.0.  Problem can't be repeated
Added test case to:
ado-compliance\trunk\bugs\26973.vbs
[31 Oct 2007 9:25] Susanne Ebrecht
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
[12 Apr 2008 3:41] Erica Moss
trace run against myodbc 5.1.2

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

[12 Apr 2008 3:41] Erica Moss
trace run against myodbc 5.1.3

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

[12 Apr 2008 6:08] Erica Moss
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
[24 Feb 2010 13:56] Tonci Grgin
Eric, please reverify if necessary.
[2 Dec 2010 8:48] Bogdan Degtyariov
The test (ado-compliance\trunk\bugs\26973.vbs) passes without errors in Connector/ODBC 3.51.27 and 5.1.8.
Closing the bug