Cursors

Overview

Understanding cursors can be accomplished by understanding the following three questions:

• Which cursor type to use?
• Which cursor location to use? client-side or server-side?
• Which locking scheme to use?

The answer: it depends on the application! 

• Consider the definitions of the following cursor types

• Consider the following two tables, which show how cursor location affects the cursor type. The requested cursor type is not necessarily what will be returned back: It depends on the provider, the cursor location and the locking type as follows - 

Server-Side Cursors

• Server-side cursors are those allocated and managed by the server.
• Sever cursors are useful when updating / inserting / deleting records.
• Sever cursors allow us to have multiple active statements on the same connection
• Server-side cursors maintain a connection to the database. All processing happens on the server.
• Any the following four ways will result in a server cursor:
  • CursorLocation = adUseServer.
  • CusorType = adOpenKeyset or adOpenDynamic or adOpenStatic forces the use a server cursor
  • LockType = adLockOptimistic, adLockPessimistic, adLockBatchOptimistic forces the use a server cursor.
  • CacheSize other than 1 forces the use a server cursor.
• The defaults when opening a connection are:
  • CursorLocation = adUseServer.
  • CusorType = adOpenForwardOnly.
  • LockType = adLockReadOnly.
    
    The CursorLocation can be set on the connection object before the recordset is opened. The recordset, when opened, will inherit the CursorLocation  property of the connection object. So if the connection object used adUseClient, the recordset when opened using this connection will have its CursorLocation = adUseClient. Note that for CursorLocation = adUseClient the CursorType is always adOpenStatic. Keep the inheritance in mind when using statements like

dim oRS as new ADODB.Recordset
oRS.CursorLocation = adUseClient
set oRS = oCN.Execute("select * from T1")

Here oRS will inherit the CursorLocation property of the connection (default is adUseServer), overwriting the adUseClient property. To prevent this behavior use the Recordset.Open method.

oRS.Open "select * from T1", , adOpenKeyset, adLockBatchOptimistic

Client-Side Cursors

• Client-side cursors are those allocated and managed by the client.
• Client cursors copy the entire data for a given rowset from the sever machine to the client machine. The client then deals with a local copy of the recordset, irrespective of whether the database connection is available or not.
• Client-side cursors will force the cursor type to be adOpenStatic.
• Client-side cursors with adOpenStatic can still be updated back to the server as long as the locking scheme is not set to adOpenReadOnly.