MySQL Bugs: #32014: MyODBC / ADO Unable to open record set using dynamic cursor

Bug #32014	MyODBC / ADO Unable to open record set using dynamic cursor
Submitted:	1 Nov 2007 0:16	Modified:	14 Dec 2007 15:59
Reporter:	Erica Moss	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1.0	OS:	Windows (win xp sp 2)
Assigned to:	Lawrenty Novitsky	CPU Architecture:	Any

Description:
When option 32 is passed to the driver, it should be possible to open a server side cursor (adUseServer) as dynamic (adOpenDynamic)

Currently this is possible using MyODBC 3.51.20, however the same test run against MyODBC 5.1.0 yields a record set that uses a static cursor (adOpenStatic)

How to repeat:
mysql> CREATE TABLE testtable (C1 INT);
mysql> INSERT INTO testtable VALUES (1),(2);

VBscript Code:
Sub Test1()
   Dim strSQL, oConn, oRs, strType
   Set oConn = CreateObject("ADODB.Connection")
   Set oRS = CreateObject("ADODB.RecordSet")
   strSQL = "SELECT * FROM testtable"
	
   connstr = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;" & _
		"DATABASE=ado;UID=root;PWD=mypass;OPTION=35"
  
   ' open connection
   oConn.ConnectionString = connstr
   oConn.CursorLocation = adUseServer
   Err.Clear
   oConn.Open
   If Err.Number <> 0 Then
     Debug.Print "failed to open connection"
   End If

   ' open record set
   oRS.Open strSQL, oConn, adOpenDynamic, _
               adLockOptimistic, adCmdText
   If Err.Number <> 0 Then
      Debug.Print "failed to open record set"
   End If

   ' Verify that the cursor type is adOpenDynamic
   Call GetType(oRs.CursorType, strType)
   If oRs.CursorType <> adOpenDynamic Then
      Call Cleanup (oConn, Ors)
      Debug.Print "Record Set opened as " & strType & _
                   " instead of adOpenDynamic"
   Else
      Debug.Print "TEST PASSED" & vbNewLine & "Cursor Type: " & strType
   End If
 
   Call Cleanup (oConn, Ors)
End Sub

Sub GetType(lType, strType)
   Select Case cInt(lType)
     Case 0
         strType = "adOpenForwardOnly"
     Case 1
         strType = "adOpenKeyset"
     Case 2
         strType = "adOpenDynamic"
     Case 3
         StrType = "adOpenStatic"
     Case Else
   End Select
End Sub

Sub Cleanup(conn, recset)
	If recset.State <> adStateClosed Then
		recset.Close
	End If
	Set recset = Nothing
	
	If conn.State <> adStateClosed Then
		conn.Close
	End If
	Set conn = Nothing
End Sub

Added testcase for this problem to the ADO conformance test suite:

ado-compliance\trunk\bugs\32014.vbs

Hi Eric,

many thanks, for writing a bug report.
Please, can you add the trace file here.

Susanne

ODBC trace for ADO bug testcase ado-compliance\trunk\bugs\32014.vbs

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

Patch fixing error in reporting SQL_SCROLL_OPTIONS to an application

Attachment: bug32014.diff (application/octet-stream, text), 659 bytes.

The : before SQL_SO_STATIC should be on the preceding line (and replaced with a space, so SQL_SO_STATIC is lined up how it is now). And there needs to be test cases to make sure this doesn't regress. See t_bug7445 in my_basics.c for an example of a test that makes additional connections with different DSN options.

I suggest this syntax for clarity:
    MYINFO_SET_ULONG(SQL_SO_FORWARD_ONLY |
                     ((dbc->flag & FLAG_FORWARD_CURSOR) ? 0 : SQL_SO_STATIC) |
                     ((dbc->flag & FLAG_DYNAMIC_CURSOR) ? SQL_SO_DYNAMIC : 0));

Patch fixing this bug has been committed to the repository, and will be in 5.1.1.

A note has been added to the 5.1.1 changelog: 

ADO was unable to open record set using dynamic cursor.