Cursor Location

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:

• You cannot change cursor itself. So you need to send to DB Server some UPDATE command of SQL.
• After DB Server changes record, your cursor becomes out of date. So you need execute again previous SELECT command.
• Okay, you have executed the SELECT command again and you have some associated GUI, and you need yet synch GUI to new cursor records, so for user it looks smooth.

Performance issues with a client-side cursor:

So even to update a single field of a record client-server cursor it has to:

• BUILD STRING in the RAM of client computer with UPDATE command.
• SEND STRING to the DB Server.
• Server PARSES STRING, produces helper structures then execute command.
• SEND SELECT command string to server … and who knows how complex is this SELECT, and how big Table(s), Jons are…
• again PARSER and EXECUTE of this SELECT.
• SEND cursor back with almost the same information.
• yet some synch efforts in code of developer on client side.

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!

• there is no any job with UPDATE strings on client side.
• By network is sent only 4 bytes value + 4 bytes UPDATE command index.
• there is no PARSER job on server side
• there is no need to send second SELECT and parser and execute it.
• there is no need to write any special code to sync GUI and new cursor.

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.