need help about CursorType/CursorLocation

[morbid.ivan]

I am getting confused which CursorType and CursorLocation to use when :

- Adding Record
- Viewing Record
- Deleting Record
- Updating Record

which side should I go? Server or Client?

Any help?

[DataMiser]

Server side in most cases.

[Schmidt]

Client side in most cases.

@the OP
You see, the opinions differ a bit on that - but if you're a beginner with ADO (and JET I assume) -
using clientside-cursors has less surprises in stock - the main-reason you could be in need of
serverside cursors (which always bind more resources on the server and don't scale as well) -
is pessimistic locking. Just google for the differences between optimistic and pessimistic locking-
approaches.

In most scenarios optimistic locking is sufficient - and in almost all other areas, the clientside
cursors perform equally well (sometimes faster a bit, sometimes slower - but in general not
much difference) - and they are - as said - the more robust approach.

That's especially true for the JET-engines network-mode against an *.mdb which is hosted on a Share.

The approach which MS followed in ADO.NET is preferring the clientside-cursor too - if that is of any help.

Olaf

[morbid.ivan]

Server side in most cases.

What if I am using MySql database?

[gibra]

Setting the CursorLocation does not depend on the type of database, but by the characteristics required.

The client-side cursor allows you to access properties that are not available on the server side.
For example, the RecordCount property returns the number of records, but only if CursorLocation = adUseClient; if CursorLocation = adUseServer returns -1 .

The same applies to the server side cursor (i.e.transaction methods BeginTrans, CommitTrans and RollbackTrans).

Alos, the grid controls like MSFlexGrid, DataGrid, TrueDBGrid, ... necessarily require a client-side cursor, otherwise the data isn't loaded.

In conclusion, it always uses the client-side cursor, but when you perform UPDATE and / or INSERT operations using transactions (as it should always be) you must use the server-side cursor because the client side does not support transactions.

Since the CursorLocation property is inherited from the ADODB connection, just set it and change it when needed.
For example, you open a cursor side connection:

    Dim CN As ADODB.Connection
    Set CN = New ADODB.Connection
    CN.ConnectionString = <your_conn_string>
    CN.Open
    CN.CursorLocation = adUseClient

When you need to update the data sets adUseServer temporarily, you are upgrading, and after update reset the cursor to adUseClient.
Below a generic function to update your data:

   Public Function UpdateSomething() As Boolean
       On Error GoTo ERR_HANDLER

       Dim bOnTransaction As Boolean
       Dim oldCursor As CursorLocationEnum

       ' start transaction
       oldCursor = CN.CursorLocation ' keep the current cursor
       CN.CursorLocation = adUseServer 
       CN.BeginTrans
       bOnTransaction = True

       Rem ----------------------------
       ' here your code to update data  
       ' you can use you can use either a Recordset, a Command.Execute, or a CN.Execute
       Rem ----------------------------

       ' end transaction
       CN.CommitTrans
       bOnTransaction = False
       CN.CursorLocation = oldCursor 'restore previous cursor

       Exit Function

   ERR_HANDLER
       MsgBox Err.description, vbInformation
       If bOnTransaction Then
           CN.RollbackTrans
          CN.CursorLocation = oldCursor 'restore previous cursor
       End If
    End Function

The properties CursorType and LockType are set in function of the type of approach that is used for accessing data.

Anyway, normally you should use this settings:
- To read: adOpenStatic, adLockReadOnly
- To write: adOpenDynamic, adLockPessimistic


BeginTrans, CommitTrans, and RollbackTrans Methods (ADO)
http://msdn.microsoft.com/en-us/libr...(v=vs.85).aspx

[Schmidt]

In conclusion, it always uses the client-side cursor, but when you perform UPDATE and / or INSERT operations using transactions (as it should always be) you must use the server-side cursor because the client side does not support transactions.

No argument from me, regarding the necessity of transaction-wrapping in "serious applications" -
but it is wrong to state, that Transactions can be used only with serverside-cursors ...
a Connection in adUseClient-mode can initiate, commit and rollback transactions equally well.

Below is some code (for MySQL) which proves that - but it would work equally well also
against a JET-*.mdb or against SQLServer or whatever.

Anyway, normally you should use this settings:
- To read: adOpenStatic, adLockReadOnly
- To write: adOpenDynamic, adLockPessimistic

As said, as long as no pessimistic locking is required (which is rarely the case, optimistic is enough in most cases),
then no serverside-cursor-switch is needed at all - in most of my apps I use a generic:
adOpenStatic, adLockBatchOptimistic
(for each and every retrieved recordset, encapsulated in a function)


'Into a Form (reference to ADO is required, as well as an installed MySQL-ODBC-driver - any DB would server - here the tests are performed against a MySQL-NWind-DB)

Private Cnn As ADODB.Connection

Private Sub Form_Load()
  Set Cnn = New ADODB.Connection
  Cnn.ConnectionString = "Driver={MySQL ODBC 5.1 Driver};" & _
                         "Server=localhost;" & _
                         "Database=NorthWind;" & _
                         "User=root;" & _
                         "Password=asdf;" & _
                         "Option=3;"
  Cnn.CursorLocation = adUseClient
  Cnn.Open
  
 
  Cnn.Execute "DROP    TABLE IF        EXISTS T"
  Cnn.Execute "CREATE TABLE IF NOT EXISTS T(ID INT UNSIGNED AUTO_INCREMENT Not NULL PRIMARY KEY, IntFld Int, TxtFld Text);"
 

  Add50Records
    Debug.Print "Count after adding 50 Records", Cnn.Execute("Select Count(*) From T")(0)
  Add50Records
    Debug.Print "Count after adding another 50 Records", Cnn.Execute("Select Count(*) From T")(0)
  Add50Records True
    Debug.Print "Count after trying to add records with non-unique ID", Cnn.Execute("Select Count(*) From T")(0)
  
End Sub

Function GetRs(SQL As String) As ADODB.Recordset
  Set GetRs = New ADODB.Recordset
      GetRs.Open SQL, Cnn, adOpenStatic, adLockBatchOptimistic
End Function

Private Sub Add50Records(Optional ByVal ProvoceAnError As Boolean)

On Error GoTo RollBack
Cnn.BeginTrans

  Dim i As Long, Rs As Recordset
  Set Rs = GetRs("Select * From T Where 0")
    For i = 1 To 50
      Rs.AddNew: Rs!IntFld = i: Rs!TxtFld = "SomeText" & i
    Next i
    If ProvoceAnError Then Rs.AddNew: Rs!ID = 1 'we add an additional record with an ID which is already existing
  Rs.UpdateBatch
  
Cnn.CommitTrans
Exit Sub

RollBack:
Cnn.RollbackTrans
Debug.Print Err.Description
End Sub

Olaf

[Schmidt]

What if I am using MySql database?

The older MySQL ODBC-drivers (version 3.xx) had a lot of problems with serverside cursors -
that's better in the meantime with the MySQL ODBC 5.xxx drivers - but why not stick with
the clientside cursors, which put much less stress on the serverside (as well as much less
complexity to handle in the ODBC-driver).

Seriously - I'd go with them until you run into something which doesn't work with
clientside cursors - and as said, these cases will be very rare - maybe never happen -
whereas with serverside-cursors you can and will stumble over a whole lot more things
(Gibra listed a few in his post, as Rs.RecordCount-availability, Datagrid-Preferences for clientside-Rs).

Olaf

[vb.elmar]

posting #7
>>'Into a Form (reference to ADO is required, as well as an installed MySQL-ODBC-driver - any
>>DB would server - here the tests are performed against a MySQL-NWind-DB)

-What is a "DB would server" ?

[DataMiser]

Probably a typo

I would guess that was to indicate that any DB would suffice

[Schmidt]

Probably a typo

I would guess that was to indicate that any DB would suffice

Yep, meant to write: "... any DB would serve ... (do, suffice, you name it)" -
sorry for the confusion.

Olaf