When we do a SELECT query to a remote database server, in a client-server environment there are two abilities:
All found records are moved into the RAM of a client computer and DB server cleanups anything related to this query. After this, the client computer does not touch server, when navigates from record to record.
Such cursor can be read-only, because client cannot communicate with server anymore. Even if you specify kReadWrite in VDatabase.SqlSelect() this will be ignored and cursor still will be ReadOnly.
This cursor is good for relatively small selections of records, because big selection can cause delay or even not fit into the RAM of a client computer.
Note: Some databases have only this kind of cursors. For example, mySQL (3.x - 5.x).
Only one record (or few) is moved into the RAM of a client computer. Server still keeps selected records and record locks. When client navigates to a record, which is not present in the its RAM, then a request is sent to server.
Since server keeps record locks, you can update/delete records of such cursor if locks are Read-Write. This is a big advantage of server-side cursors.
Sometimes, developer knows that records of cursor will be iterated forward only, so it is wise to inform about this DB Server, because this will allow to unlock record on move to the next record. To support this optimization, VDatabase.SqlSelect() has parameter inCursorDirection, which can be 'kForwardOnly' or 'kRandom'.
This cursor is good for huge selections.
Example:
curs = db.SqlSelect( "SELECT * FROM T WHERE ...", EVCursorLocation.kServerSide, EVLockType.kReadWrite, EVCursorDirection.kRandom ) ... some job ... come TO some Nth record ... now you want UPDATE it: curs.FIELD( "f1" ).value = 55 curs.FIELD( "f7" ).value = 'new value' curs.UpdateRecord()
To choose which cursor to use, you can consider the following question: do I want to change records of cursor? If yes, then you need server-side cursor. Exception can be if DB server is remote and response time is not fast enough. Then you may want to use client-side cursor and use separate UPDATE/DELETE commands to do changes.
Let's imagine that you work with DBMS that supports only client-side cursors. While you work with small selections and read only, its all clear and looks easy. But let you need also update/delete records of cursor. How to do this?
Developer Problems with a client-side cursor:
Performance issues with a client-side cursor:
So even to update a single field of a record client-server cursor it has to:
Now lets consider how all this becomes much more simple for developer and his code and for performance. To update value of one field of a record developer need write only:
curs.FIELD(f1).value = 555; curs.UpdateRecord(); <<<<<< this DOES NOT produce any SQL strings behind, AS do ALL ORM frameworks btw.
And that is all!
So if you have DB Server on a localhost, choice of server-side cursors can be very good idea from point of view to simplify your code and even increase performance.