MySQL Bugs: #27351: MyODBC 3 Option 32 is automatically setting ADO cursor type to Dynamic

Bug #27351	MyODBC 3 Option 32 is automatically setting ADO cursor type to Dynamic
Submitted:	21 Mar 2007 18:24	Modified:	14 Feb 2008 16:36
Reporter:	Erica Moss	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.14	OS:	Windows (XP sp2)
Assigned to:	Jess Balint	CPU Architecture:	Any

Description:
When MyODBC Option 32 is requested via DSN or connection string, the cursor type can no longer be set to adOpenStatic.  Once this option is set, if you attempt to open the cursor as static it is instead opened as Dynamic.  This could be seen as a limitation given that our manual states that the option, "enables" Dynamic Cursor rather than mandates it.

The below code will either display cursorType = 2 (dynamic) or 3 ( static ) depending on which connect string you enable irrespective of the fact that the open command specifies adOpenStatic.

According to our manual
Option 32 = Enable Dynamic Cursor 
Enable or disable the dynamic cursor support. (Not allowed in Connector/ODBC 2.50.)

How to repeat:
Private Sub CurTypeTest()
    Dim strSQL As String
    Dim strConn As String
    Dim connection1 As ADODB.Connection
    Dim recset1 As ADODB.Recordset

    Set connection1 = New ADODB.Connection
    Set recset1 = New ADODB.Recordset

    strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _
        "SERVER=localhost;DATABASE=ado" & _
        ";USER=root;PASSWORD=mypass;OPTION=35;"

 '   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.Open

    strSQL = "Select 1"
 
   ' open recordset
    recset1.Open strSQL, connection1, adOpenStatic, _
             adLockOptimistic, adCmdText
    MsgBox "CurType: " & recset1.CursorType

    recset1.Close
    Set recset1 = Nothing
    connection1.Close
    Set connection1 = Nothing
End Sub

EricM will add ADO regression test for this. Patch below.

Index: driver/info.c
===================================================================
--- driver/info.c       (revision 1012)
+++ driver/info.c       (working copy)
@@ -512,7 +512,7 @@
     break;

   case SQL_GETDATA_EXTENSIONS:
-    *((SQLUINTEGER *)rgbInfoValue)= (SQL_GD_ANY_COLUMN |
+    *((SQLUINTEGER *)rgbInfoValue)= (SQL_GD_ANY_COLUMN | SQL_GD_BLOCK |
                                      SQL_GD_ANY_ORDER |
                                      SQL_GD_BOUND);
     *pcbInfoValue= sizeof(SQLUINTEGER);

Bug#29883 has been marked as a duplicate of this.

This has been committed as rev1019 and will be released in 3.51.24.

A note has been added to the 3.51.24 changelog: 

Static cursor was unable to be used through ADO when dynamic cursors were enabled