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:Microsoft Windows (win xp sp 2)
Assigned to: Jess Balint
Tags: ADO adUseServer adOpenStatic Delete SQL_DELETE

[20 Dec 2007 3:14] Erica Moss
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
[20 Dec 2007 3:54] Erica Moss
Added test case for this to the ADO conformance test suite:
/trunk/tests/bugs/33388.vbs
[31 Dec 2007 21:01] Jess Balint
bugfix

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.