MySQL Bugs: #33388: MyODBC 5.1 / ADO unable to delete from a static server side cursor

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:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1.1	OS:	Windows (win xp sp 2)
Assigned to:	Jess Balint	CPU Architecture:	Any
Tags:	ADO adUseServer adOpenStatic Delete SQL_DELETE

Description:
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:
SETUP:
mysql>	"CREATE TABLE t (C1 INT PRIMARY KEY)"
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
	
   Err.Clear
   oConn.Open
   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
    oRS.Movefirst

    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
	
        oRS.Delete		
        oRs.Update
        oRs.MoveNext
     Wend
    
     If oRs.EOF = True Then
        Assert.Trace "TEST PASSED, EOF hit at record: " & iCount-1
     End If
	
     Call Cleanup (oConn, oRs)
End Sub

Added test case for this to the ADO conformance test suite:
/trunk/tests/bugs/33388.vbs

bugfix

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

updated patch, also changes fix for bug#29765, and consolidates test cases

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

updated patch, fix test not to mix SQLFetch/SQLFetchScroll (iODBC doesn't allow it...)

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

Patch committed as rev1009 and will be released in 5.1.2.

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.