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
• Status: Closed
• Category: Connector / ODBC
• Severity: S1 (Critical)
• Version: 5.1
• OS: Windows (XP sp 2)
• Assigned to: Jess Balint
• CPU Architecture: Any

[19 Apr 2007 19:22] Erica Moss

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

[19 Apr 2007 19:22] Erica Moss

odbc trace

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

[22 Oct 2007 14:29] Susanne Ebrecht

This is a duplicate of bug #27903

[23 Oct 2007 13:02] Susanne Ebrecht

This is a duplicate of bug #26985

[2 Nov 2007 4:42] Jess Balint

Not a duplicate of bug#26985

[7 Nov 2007 17:28] Erica Moss

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

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

[12 Dec 2007 19:55] Jess Balint

Fix committed and will be released in 5.1.1.

[14 Dec 2007 15:58] MC Brown

A note has been added to the 5.1.1 changelog: 

ADO Not possible to update a client side cursor.