Bug #33388 MyODBC 5.1 / ADO unable to delete from a static server side cursor
Submitted: 20 Dec 2007 3:14 Modified: 14 Feb 2008 16:36
Reporter: Erica Moss Email Updates:
Status: Closed Impact on me:
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.1.1 OS:Microsoft Windows (win xp sp 2)
Assigned to: Jess Balint CPU Architecture:Any
Tags: ADO adUseServer adOpenStatic Delete SQL_DELETE

[20 Dec 2007 3:14] Erica Moss
When the code below is run then the out will be something like the following:

Trace: Record: 1 Value: 1
Trace: Record: 2 Value: 1
Trace: Record: 3 Value: 1
Trace: Record: 4 Value: 1
Trace: Record: 5 Value: 1
Trace: Record: 6 Value: 1
Trace: Record: 7 Value: 1
Trace: Record: 8 Value: 1

Note the loop continues past the point that the EOF should have been hit.  Also not that the record set never increments beyond the first record whose value is "1".  If the line oRs.Delete is commented out, then the record set will behave as expected and loop through all five records and then hitting EOF.  It will also work if the cursor type is set to ForwardOnly.  This test works as is when run against MyODBC 3.51.22

How to repeat:
mysql>  "INSERT INTO t VALUES (1),(2),(3),(4),(5)" 

Sub Test()
   On Error Resume Next
   Dim strSQL, oConn, oRs, iCount
   Set oConn = CreateObject("ADODB.Connection")
   Set oRS = CreateObject("ADODB.RecordSet")
   strSQL = "SELECT C1 FROM t"
   ' open connection
   oConn.ConnectionString = connstr
   oConn.CursorLocation = adUseServer
   If Err.Number <> 0 Then
      Call Cleanup (oConn, oRs)
      Assert.Trace Err.Number & "::" & Err.Description
      On Error Goto 0
      Assert.Failure "Error opening connection"
    End If
   ' open record set
    oRS.Open strSQL, oConn, adOpenStatic, adLockOptimistic, adCmdText
    If Err.Number <> 0 Then
       Call Cleanup (oConn, oRs)
       Assert.Trace Err.Number & "::" & Err.Description
       On Error Goto 0
       Assert.Failure "Error opening Record Set"
    End If

    iCount = 1
    While Not oRs.EOF
        If iCount = 101 Then
           Call Cleanup (oConn, oRs)
           On Error Goto 0
           Assert.Failure "Test Failed EOF never hit"
        End If
        Assert.Trace "Record: " & iCount & " Value: " & oRs.Fields("C1").Value
        iCount = iCount + 1
     If oRs.EOF = True Then
        Assert.Trace "TEST PASSED, EOF hit at record: " & iCount-1
     End If
     Call Cleanup (oConn, oRs)
End Sub
[20 Dec 2007 3:54] Erica Moss
Added test case for this to the ADO conformance test suite:
[31 Dec 2007 21:01] Jess Balint

Attachment: x8.txt (text/plain), 2.45 KiB.

[2 Jan 2008 4:58] Jess Balint
updated patch, also changes fix for bug#29765, and consolidates test cases

Attachment: x10.txt (text/plain), 5.67 KiB.

[9 Jan 2008 18:27] Jess Balint
updated patch, fix test not to mix SQLFetch/SQLFetchScroll (iODBC doesn't allow it...)

Attachment: bug33388.diff (application/octet-stream, text), 5.85 KiB.

[24 Jan 2008 2:15] Jess Balint
Patch committed as rev1009 and will be released in 5.1.2.
[14 Feb 2008 16:36] MC Brown
A note has been added to the 5.1.2 changelog: 

When deleting rows from a static cursor, the cursor position would be incorrectly reported.