MySQL Bugs: #27961: MyODBC 5/ ADO Not possible to update a client side cursor

Bug #27961	MyODBC 5/ ADO Not possible to update a client side cursor
Submitted:	19 Apr 2007 19:22	Modified:	14 Dec 2007 15:58
Reporter:	Erica Moss	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	5.1	OS:	Windows (XP sp 2)
Assigned to:	Jess Balint	CPU Architecture:	Any

Description:
MySQL SERVER VERSION: 5.0.37

It is not possible to update client side cursors.  The data type of the field and the schema of the table do not appear to be relevant.  In each case the driver builds an update similar to this one which is then rejected by the server...
VB6             a2c-24c	ENTER SQLExecDirectW 
HSTMT               04112648
WCHAR *             0x03ECEA80 [      -3] "UPDATE `def`.`ado`.`ado_test` SET `C2`=? WHERE `C1`=? AND `C2`=?\ 0"
SDWORD                    -3

Resulting in an ADO error:
[MySQL][MyODBC 5.00.12][MySQL] 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`ado_test` SET `C2`=99 WHERE `C1`=1 AND `C2`=100' at line 1 -2147467259

An ODBC trace is attached, SQL.LOG

How to repeat:
SETUP:
mysql> CREATE DATABASE ado;
mysql> DROP TABLE IF EXISTS ado_test;
mysql> CREATE TABLE ado_test (C1 INT PRIMARY KEY, C2 INT);
mysql> INSERT INTO ado_test VALUES (1,100),(2,200);

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
    Set connection1 = New ADODB.Connection
    Set recset1 = New ADODB.Recordset
    
    strSQL = "SELECT * FROM ado_test"
        
    strConn = "DRIVER={MySQL Connector/ODBC v5};" & _
            "SERVER=localhost;DATABASE=ado" & _
            ";UID=root;PWD=mypass;OPTION=3;"
        
    ' open connection
    connection1.ConnectionString = strConn
    connection1.CursorLocation = adUseClient
    connection1.Open

    recset1.Open strSQL, connection1, adOpenStatic, adLockOptimistic, adCmdText
    
    recset1.MoveFirst
    While Not recset1.EOF
        For Each fld In recset1.Fields
            If fld.Name = "C2" Then
                fld.Value = 99
                recset1.Update
            End If
        Next
        recset1.MoveNext
    Wend
    Exit Sub
EH:
    Debug.Print Err.Description & " " & Err.Number & vbNewLine
End Sub

odbc trace

Attachment: SQL.LOG (application/octet-stream, text), 90.06 KiB.

This is a duplicate of bug #27903

This is a duplicate of bug #26985

Not a duplicate of bug#26985

odbc trace for ADO bug test found in the SVN conformance tests

Attachment: SQL.LOG (application/octet-stream, text), 106.22 KiB.

Fix committed and will be released in 5.1.1.

A note has been added to the 5.1.1 changelog: 

ADO Not possible to update a client side cursor.