Use cursors

When you plan on retrieving a large recordset of data, a cursor is the most efficient approach. This is because a cursor allows you to call an action once to generate the recordset. Afterwards, you can call the "getRecordsFromCursor" action as many times as desired to retrieve any part of the recordset. In contrast, each time you call an action, it runs again from scratch. Some action can be rerun with little overhead, but in all cases a cursor is most efficient.

A cursor retains the state of the recordset on the server so it can repeatedly traverse the result set without regenerating it. A SQL recordset remains cached in memory. In all other cases, the database walks the recordset without caching it. This is a unique capability of the FairCom database engine. It is designed to walk records in table or index order at high speed. It keeps track of the record position and can easily move to the next or previous N records. It can randomly access any record in the recordset. It can also filter records while it is walking, which automatically and transparently skips filtered records.

A cursor is flexible and fast. It gives you full control over navigating the records in its results. It allows you to retrieve any number of records from any record position. The cursor tracks the current record position.

Ways you can use a cursor:

You can start at the beginning and work your way forward – returning as many records as you want in each request.
You can also start at the end and work your way backward in reverse order.
You can skip forward and backward from the current position.
You can also start at the beginning and skip forward to any random record in the results.
Likewise, you can start at the end and skip backward to any record.
In a single request, you can return a specified number of records before and after a position.

The database automatically releases cursors when they become idle. Each call to the action, "getRecordsFromCursor" extends its life. Cursors automatically expire when there is no activity for the number of seconds defined by the "idleCursorTimeoutSeconds" property. When you create a cursor, you can set the "idleCursorTimeoutSeconds" property to a value that meets your needs, or you can set it to 0 to disable the idle timeout.

When you are done using a cursor, you should call "closeCursor" to release its resources on the server. Doing so frees the server resources used by the cursor; otherwise, these resources remain unavailable for other requests until the idle timeout occurs.

Calling "getRecordsFromCursor" with an expired cursor, returns an error response. Calling "closeCursor" with an expired cursor, does not return an error response because the cursor is closed.

Example 1. Request message that uses a cursor

{
  "requestId": "1",
  "action": "getRecordsByPartialKeyRange",
  "params": 
  {
    "databaseName": "myDatabase",
    "tableName": "persons",

    "returnCursor": true,

    "indexFilter": 
    {
      "indexName": "NameOfIndex",
      "partialKey": "Mi" 
    },

    "apiVersion": "1.0",
    "authToken": "anAuthorizationTokenFromTheServer"
  }
}

Example 2. Response message that uses a cursor

Notice that the response message contains a "cursorId" property containing a unique cursor identifier. You use this cursor ID when you call each time you call "getRecordsFromCursor".

{
  "requestId": "theUniqueIdFromTheClient",
  "result": 
  {
    "cursorId": "aUniqueIdFromTheServerRepresentingACursor",
    "totalRecordCount": -1
  }
}

Cursor properties

The "cursorId" property:
- This property a string that contains a unique number that represents the cursor on the server.
- You pass it into "getRecordsFromCursor" to retrieve sets of records from the cursor or into "closeCursor" to close it
- An empty string is a closed cursor.
- When it is an empty string this occurs:
  - When there is an error creating the cursor, the "errorCode" property will be non-zero.
  - When there are no results:
    The "totalRecordCount" property will be 0.
    The "warningCode" property is set to a value of 2.
    The "errorMessage" property is set to "The cursor is automatically closed due to no results."
The "totalRecordCount" property
- This property contains the number of records returned by the cursor.
- This helps the client decide what to do with the results.
- If there are no results:
  - "totalRecordCount" is 0.
  - A cursor that returns no results is immediately closed.
  - No results can be caused by two things:
    A query that found no matches.
    An error.
- If there are results:
  - "totalRecordCount" is -1 or >0.
    A value of -1 means the count is unknown.
    This occurs when a query has found one or more results, but counting them is too expensive.
    A value >0 means the precise count is known.