Bug #29883 MyODBC 3/ADO recordset update if SQL includes a Limit clause
Submitted: 18 Jul 2007 23:48 Modified: 6 Feb 2008 4:48
Reporter: Erica Moss Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.17 OS:Windows (xp sp 2)
Assigned to: Assigned Account CPU Architecture:Any

[18 Jul 2007 23:48] Erica Moss
Description:
If the code below is run with a command that has a LIMIT clause, and a server side cursor, it fails when and update is called because the update statement looks like this...
"UPDATE `ado`..`ado_test` SET c3=? WHERE (c1=? AND c2=? AND c3=? )"

If the same code is run with the same command but a client side cursor it succeeds and the update statement looks like this...
"UPDATE `ado`.`ado_test` SET `c3`=? WHERE `c1`=? AND `c3`=?\ 0"

If the same code is run without the LIMIT clause and a server side cursor it also succeeds and the update looks like this...
"UPDATE ado_test SET c3=? WHERE (c1=? AND c2=? AND c3=? )"

It's not clear whether this might be an ADO bug, or whether we might be able to create some workaround for it.  ODBC traces for the three cases are attached

How to repeat:
SETUP:
DROP TABLE IF EXISTS ado_test;
CREATE TABLE ado_test (C1 INT PRIMARY KEY, C2 CHAR(10), C3 INT);
INSERT INTO ado_test VALUES (1,'One',101),(2,'Two',102),(3,'Three',103);

VB Code:
Sub LimitTest()
    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

    Set connection1 = New ADODB.Connection
    Set recset1 = New ADODB.Recordset
    strSQL = "SELECT c1, c2, c3 from ado_test limit 2"
'    strSQL = "SELECT c1, c2, c3 from ado_test"
    
    strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _
        "SERVER=localhost;DATABASE=ado" & _
        ";USER=root;PASSWORD=mypass;OPTION=3;"

    ' open connection
    connection1.ConnectionString = strConn
    connection1.CursorLocation = adUseServer
'    connection1.CursorLocation = adUseClient
    connection1.Open
    
    ' open recordset
    recset1.Open strSQL, connection1, adOpenStatic, adLockOptimistic, adCmdText

    Set fld = recset1.Fields(2)
    fld.Value = 123
 
    recset1.Update  ' error

    recset1.Close
    Set recset1 = Nothing
    connection1.Close
    Set connection1 = Nothing
    Exit Sub
EH:
    Debug.Print Err.Number & ":" & Err.Description
    recset1.CancelUpdate
    recset1.Close
    Set recset1 = Nothing
    connection1.Close
    Set connection1 = Nothing
End Sub
[18 Jul 2007 23:49] Erica Moss
SQL with limit client cursor

Attachment: SQL with limit client cursor.LOG (application/octet-stream, text), 66.25 KiB.

[18 Jul 2007 23:50] Erica Moss
SQL with limit server cursor

Attachment: SQL with limit server cursor.LOG (application/octet-stream, text), 63.84 KiB.

[18 Jul 2007 23:50] Erica Moss
SQL without limit

Attachment: SQL without limit.LOG (application/octet-stream, text), 64.22 KiB.

[2 Feb 2008 4:03] Jess Balint
This works fine in both 3.51 and 5.1. The following update is issued:

980 Query       UPDATE `ado_test` SET `c3`=123 WHERE `C1`=1 LIMIT 1

Please add ADO regression test.
[6 Feb 2008 3:16] Erica Moss
Tested against 3.51.23 = problem still exists
"Trace: 3219::Operation is not allowed in this context."

Test case added to ADO Conformance tests
/bugs/29883.vbs

ODBC trace added for the server side test case

from trace:
"UPDATE `ado`..`29883` SET C3=? WHERE (C1=? AND C2=? AND C3=? )"
[6 Feb 2008 3:17] Erica Moss
new odbc trace 3.51.23

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

[6 Feb 2008 4:48] Jess Balint
This is a duplicate of bug#27351.