Query techniques

"getRecordsByIndex" creates a cursor that walks records in index order. Only records in the index that are not excluded by the table filter can be retrieved by the cursor.

An index cursor is the fastest possible way to retrieve all indexed records in sorted order (ascending or descending). Another technique is to create a specialized index that contains a filtered subset of a table’s records. This is the fastest possible way to retrieve these filtered records in index order at the cost of slightly slowing inserts, updates, and deletes.

You can set the property I don't "startFrom" to "beforeFirstRecord" to position the cursor before the first record in the index to start skipping and fetching forward from there. Likewise, "afterLastRecord" positions the cursor after the last record in the index to start skipping and fetching backward from there.

"getRecordsByPartialKeyRange" works like "getRecordsInKeyRange". The difference is that the range is defined by a partial key value.

"getRecordsByTable" creates a cursor that walks records in table order. It walks all records in the table that are not excluded by the table filter.

A table cursor is the fastest possible way to "tail a file" by repeatedly retrieving newly inserted records at the end of a table. It is also the fastest possible way to retrieve all records in a table. When an index is not available to filter records, it is the fastest possible way to scan all records in a table and return a filtered subset of records. It is also typically the fastest possible way to retrieve more than 33% of a table’s records – even when indexes are available for filtering.

You can set the property "startFrom" to "beforeFirstRecord" to position the cursor before the first record in the table to start skipping and fetching forward from there. Likewise, "afterLastRecord" positions the cursor after the last record in the table to start skipping and fetching backward from there.

Each call to this action optionally moves the cursor position and fetches one or more records. The server remembers the ending cursor position so that subsequent calls to this action can return the next set of records.

A cursor is always positioned before or after a record. The direction the cursor moves determines whether the cursor is considered before or after a record. When moving forward, the cursor is considered before the current record and it moves the specified number of records down the recordset from the beginning toward the end. Conversely, when moving backward, the cursor is considered after the current record, and it moves the specified number of records from that position in reverse order from the end toward the beginning.

When a cursor is before the first record, it cannot be moved backward because there are no more records to fetch in that direction. Likewise, when a cursor is after the last record, it cannot be moved forward. Fetching records beyond these boundaries puts the cursor in its finished position.

Cursor state and action call order

Because a cursor is stateful, the order of calls is important. Thus, the transport protocol underlying JSON NAV must ensure this action is delivered to the server only once and it is delivered in order. When you desire a cursor to be stateless, you can set "startFrom" to a desired starting position, use "skipRecords" to move to the desired offset position, and set "fetchRecords" to the number of records you want to retrieve.

When a cursor is created from a recordset that contains records, its state is set to a Start Position. This allows "getRecordsFromCursor" to walk records forward or backward from that initial position, which may be anywhere in the recordset. When a cursor is first created, its starting position and current position are the same.

The cursor position moves as records are skipped or fetched. This is the Current Position.

Cursors

To randomly access any record or set of records relative to the beginning or end of the recordset or to the initial record position, use the "startFrom" property to reset the cursor to a known location. Set the "skipRecords" property to a positive or negative number to move to the desired position. Set "fetchRecords" to a positive or negative number to retrieve records. Positive numbers move and fetch forward from the current record position and negative numbers move and fetch backward.

To retrieve a span of records before and after the current record position, use a negative number in "skipRecords" to move the cursor backward the number of records you want to retrieve before the current record, and set "fetchRecords" to the total number of records you want to retrieve in the forward direction. Or vice versa, use a positive number in "skipRecords" to move the cursor forward the number of records you want to retrieve after the current record, and set "fetchRecords" to a negative number that represents the total number of records you want to retrieve in the backward direction.

To retrieve all records, set "fetchRecords" to "all". To retrieve all records starting from the current record position to the end of the recordset, set "fetchRecords" to "allFollowing". To retrieve all records starting from the current record position to the beginning of the recordset, set "fetchRecords" to "allPrevious".

Using cursors with different types of queries

The other get records actions create a recordset that can be walked by a cursor. All cursor features are available for all types of recordsets. The underlying data mechanism of each recordset varies. This mechanism defines how a cursor walks the data and how its boundaries are defined.

"getRecordsInKeyRange" creates a cursor that walks records in index order from the first specified index key to the last that are not excluded by the table filter. Only records in the index within the index range can be retrieved by the cursor.

You can set the property "startFrom" to "beforeFirstRecord" to position the cursor before the first record in the key range to start skipping and fetching forward from there. Likewise, "afterLastRecord" positions the cursor after the last record in the key range to start skipping and fetching backward from there. The cursor cannot retrieve records outside the specified key range.

"getRecordsStartingAtKey" creates a cursor that walks records in index order starting with the first matching index key, which may be a partial matching key. The cursor walks all records in the index that are not excluded by the table filter. Only records in the index can be retrieved by the cursor. The cursor is first positioned next to the first record in the index that most closely matches the specified key. If there is no matching value, the cursor state changes to the Finished Position.

You can set the property "startFrom" to "beforeFirstRecord" to position the cursor before the first record in the index to start skipping and fetching forward from there. Likewise, "afterLastRecord" positions the cursor after the last record in the index to start skipping and fetching backward from there.

If the key is not found, a cursor is returned but it is not positioned to any record. The errorCode is set to  4046 indicating no match. If you desire, the cursor can still return records starting from the beginning or end of the index. To retrieve records from the beginning, fetch records using a positive number. To retrieve records from the end, fetch using a negative number.

JSON actions that use SQL statements, use the FairCom "Direct SQL" API, which accesses data at full speed.

The "getRecordsUsingSQL" action runs a SQL query that creates a recordset that can be walked by a cursor in the order returned by SQL. Only records included in the SQL results can be retrieved by the cursor. A SQL cursor is particularly useful when joining data from multiple tables, for aggregating data, and for sorting these results.

You can set the "startFrom" property to "beforeFirstRecord" which will position the cursor before the first record in the recordset to start skipping and fetching forward from there. Likewise, "afterLastRecord" positions the cursor after the last record in the recordset to start skipping and fetching backward from there.

When creating a SQL recordset, you may optionally set "sqlForwardOnly" to true to create a fast, forward-only cursor. A forward-only cursor limits "skipRecords" and "fetchRecords" to positive numbers.