Why is does my cursor location need to be set to server side when connecting to a local sdf database?

Question

I am connecting to a local SQL Server Compact database (.sdf file) on my desktop with the adodbapi package and the connector does not function properly unless the cursor is set to the "server side". Why is this the case?

After looking at the adodbapi source code I can see that the cursor is set to the client side by default (as of version 2.3.0). However I do not see where the cursor location is used by the program or where it is passed to the connection provider. This makes it difficult to determine why I need a server side cursor location for my connection to function properly. I have looked at Difference between ADO client-side vs server-side cursor when the SQL Server database is local? but it seems like the table names (and other attempted queries) would be part of the static record set.

An example of the malfunction is:

 connectionString =  """ 
                    Provider = Microsoft.SQLSERVER.CE.OLEDB.4.0;
                    Data Source = results.sdf;
                    SSCE:Max Database Size = 4000
                    """     
    dbConnection = ado.connect(connectionString)
    db.Connection.ger_table_names()

Which gives the error

com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft SQL Server Compact OLE DB Provider', 'Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.', None, 0, -2147217887), None)

Including the following, however, outputs the correct table names.

dbConnection.connector.CursorLocation = 2

Anyone know the behind the scenes explanation?

Answer 1

Besides OLEDB.4.0 there is also OLEDB.3.5 around if you want to try it instead.