This tutorial shows how to get a count of all records in a SQL query and use a cursor to efficiently paginate over the results. You can paginate forward and backward including skipping forward and backward.
Set up the API Console
- Ensure the FairCom server is installed and running.
- Confirm server access by running FairCom's Control Center.
- Navigate to the Code tab.
- Select API Console from the Code menu.

How to use the web application to run a JSON Action
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Either select the appropriate action from the JSON Actions dropdown menu, search for the desired action, or paste JSON action code into the API Console, such as the following:
{ "api": "db", "action": "createDatabase", "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken", "params": { "databaseName": "new_database_name" } } - Click the Autofill button (
) if Autofill has not been turned on to set the "authToken","requestId", and"cursorId"to the current session's values. - Click the Run button (
) to run the action. - Verify the action completed successfully.
-
"errorCode"with a value of0indicates success. -
"errorCode"with a non-zero value indicates a failure. - See Errors and contact FairCom for more information about an error.
-
You will receive error 60031 if you do not click Apply defaults to JSON request to set the
"authToken"to a valid session."errorCode": 60031, "errorMessage": "Not possible to find valid session for the provided token." }
Prepare a table for testing
Create a table named testcount
The JSON DB API creates the fields you specify, such as the description field in the example below. It automatically creates the following additional fields:
- The
idfield is the primary key. It contains a unique numeric identifier that auto-increments with each newly inserted record. It is also indexed with a unique index named"id_pk". - The
changeidfield contains the transaction number that last inserted or updated the record. The JSON DB API uses this field to implement optimistic locking. When you update the record, you must include this field and value. The server compares yourchangeidvalue with the current value in the record. If they match, the server updates the record; otherwise, it returns an error indicating another process updated the record since you retrieved it.
When you create a field, such as the "description" field in the example below, it is optional and nullable unless you add "nullable": false to the field definition.
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Paste this code into the API Console:
{ "api": "db", "action": "createTable", "params": { "tableName": "testcount", "fields": [ { "name": "description", "type": "varchar", "length": 250, "nullable": false } ] }, "authToken": "replaceWithAuthtokenFromCreateSession" }
- Click the Run button.
Insert 5 records
When you insert records, you must not include the id and changeid fields. The server automatically puts values in them.
You must supply values to required fields that are not nullable. The "description" field is required.
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Paste this code into the API Console:
{ "api": "db", "action": "insertRecords", "params": { "tableName": "testcount", "dataFormat": "objects", "sourceData": [ { "description": "1" }, { "description": "2" }, { "description": "3" }, { "description": "4" }, { "description": "5" } ] }, "authToken": "replaceWithAuthtokenFromCreateSession" }
- Click the Run button.
View records using the web application
- Navigate to the Data tab.
- Select Tables from the Data menu.
- Click on the table name. For this tutorial, click the
"testcount"table - Observe the records.
Query records, get a record count, and return a cursor
The following query selects the two records where the value in the id field is greater than 3.
- Add
"forceRecordCount": trueto the request, and the FairCom server counts the records returned by the SQL statement. If your query selects many records, the record count slows down the request. The server returns the count in the response property"totalRecordCount". - Add
"returnCursor": trueto the request, and the FairCom server returns a cursor ID in the response property"cursorId"that lets you paginate through the SQL query results. - Save the
"cursorId"value in the response for use in subsequent cursor actions.
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Paste this code into the API Console:
{ "api": "db", "action": "getRecordsUsingSQL", "params": { "sql": "SELECT * from testcount where id > 3", "forceRecordCount": true, "returnCursor": true }, "debug": "none", "authToken": "replaceWithAuthtokenFromCreateSession" }
- Click the Run button.
Results
Notice that "totalRecordCount" in the example output is set to 2. This is the only time the server returns the query's record count. It is not available later when you use a cursor to paginate through the records.
{
"authToken": "replaceWithAuthtokenFromCreateSession",
"result": {
"cursorId": "replaceWithCursorFromGetRecords",
"totalRecordCount": 2
},
"errorCode": 0,
"errorMessage": ""
}
If you do not use a cursor regularly, it will time out and become unusable. If you get
error 14702, invalid 'cursorId'when running the subsequent examples, run this example query again to create a new cursor. Save the new"cursorId"value in the response for use in subsequent cursor actions.
Use a Cursor to paginate through query results
A cursor moves forward and backward through the queried records and can also skip records forward and backward. You can combine skipping and fetching in any way.
A FairCom server tracks the cursor's record position. When you fetch or skip records, you move the cursor position. Each call you make to "getRecordsFromCursor" starts where the last call left off to make pagination efficient and fast. Fetching thousands of records at a time also increases efficiency and performance.
Add "cursorId" to the request with the value from the previous query if Autofill is not enabled. When Autofill is enabled in the app, it will fill in the placeholder value with a valid open cursor value. Hover over the cursorId value to select if multiple cursors are open:

Add "fetchRecords" to the request to specify the maximum number of records the cursor returns. Use a positive integer to fetch forward and a negative integer to fetch backward.
When the "error" property is 0, the response returns the paginated query results in the "data" property.
The response contains three properties that help you with pagination
- The
"moreRecords"property istruewhen you can fetch more records from the query; otherwise, it isfalse. You can fetch records in a loop until"moreRecords"property isfalse. - The
"returnedRecordCount"property is the number of records returned in the request. - The
"requestedRecordCount"property is the number of records you requested.
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Paste this code into the API Console:
{ "api": "db", "action": "getRecordsFromCursor", "params": { "cursorId": "replaceWithCursorFromGetRecords", "fetchRecords": 2000 }, "debug": "none", "authToken": "replaceWithAuthtokenFromCreateSession" }
- Click the Run button.
Results
Because the result action sets "moreRecords" to false, the cursor action returned all records in the query and the server moved the record position after the last record. If you run this action again, it returns no records.
Because the cursor is after the last record, a positive "fetchRecords" value cannot retrieve more records, but a negative "fetchRecords" value can.
{
"result": {
"dataFormat": "objects",
"numberFormat": "number",
"binaryFormat": "hex",
"fields": [
{ "name": "id", "type": "bigint" },
{ "name": "changeid", "type": "bigint" },
{ "name": "description", "type": "varchar", "length": 2000 }
],
"data": [
{
"id": 4,
"description": "4",
"changeid": 2294841
},
{
"id": 5,
"description": "5",
"changeid": 2294841
}
],
"moreRecords": false,
"returnedRecordCount": 2,
"requestedRecordCount": 2000
},
"authToken": "replaceWithAuthTokenFromCreateSession",
"errorCode": 0,
"errorMessage": ""
}
Reset the cursor position to the beginning
When a cursor has retrieved all its records, you can start paginating from the beginning by adding "startFrom": "beforeFirstRecord" to the request.
Because the "startFrom" property anchors the pagination to a fixed point, including it in your request causes the action to return the same results each time.
A cursor can get into an invalid state when certain database events occur. When the
"getRecordsFromCursor"action returnserror 14703 "invalid cursor status", you can use"startFrom": "beforeFirstRecord"or"startFrom": "afterLastRecord"to reset the cursor and continue to use it.
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Paste this code into the API Console:
{ "api": "db", "action": "getRecordsFromCursor", "params": { "cursorId": "replaceWithCursorFromGetRecords", "fetchRecords": 2000, "startFrom": "beforeFirstRecord" }, "debug": "none", "authToken": "replaceWithAuthtokenFromCreateSession" }
- Click the Run button.
Result
After running this action, the cursor position moves after the last record in the results.
Because the cursor is after the last record, a positive "fetchRecords" value cannot retrieve more records, but a negative "fetchRecords" value can.
{
"result": {
"dataFormat": "objects",
"numberFormat": "number",
"binaryFormat": "hex",
"fields": [
{ "name": "id", "type": "bigint" },
{ "name": "changeid", "type": "bigint" },
{ "name": "description", "type": "varchar", "length": 2000 }
],
"data": [
{
"id": 4,
"description": "4",
"changeid": 2294841
},
{
"id": 5,
"description": "5",
"changeid": 2294841
}
],
"moreRecords": false,
"returnedRecordCount": 2,
"requestedRecordCount": 2000
},
"authToken": "replaceWithAuthTokenFromCreateSession",
"errorCode": 0,
"errorMessage": ""
}
Fetch records backward
Set "fetchRecords" to a negative value, such as -2000 to return records backward starting from the current record position.
The previous action moved the record position to after the last record.
The following action retrieves the records in reverse order starting from the end to the beginning. Retrieving records backward is a way to change the sort order of the results.
All cursors go forward and backward except a cursor returned by
getRecordsByTablecontaining variable-length records, which can only go forward.
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Paste this code into the API Console:
{ "api": "db", "action": "getRecordsFromCursor", "params": { "cursorId": "replaceWithCursorFromGetRecords", "fetchRecords": -2000 }, "debug": "none", "authToken": "replaceWithAuthTokenFromCreateSession" }
- Click the Run button.
Result
Notice the results are in reverse order where id 5 is before id 4.
After running this action, the cursor position moves before the first record in the results. Because the cursor is before the first record, a negative "fetchRecords" value cannot retrieve more records, but a positive "fetchRecords" value can.
{
"result": {
"dataFormat": "objects",
"numberFormat": "number",
"binaryFormat": "hex",
"fields": [
{ "name": "id", "type": "bigint" },
{ "name": "changeid", "type": "bigint" },
{ "name": "description", "type": "varchar", "length": 2000 }
],
"data": [
{
"id": 5,
"description": "5",
"changeid": 2294841
},
{
"id": 4,
"description": "4",
"changeid": 2294841
}
],
"moreRecords": false,
"returnedRecordCount": 2,
"requestedRecordCount": 2000
},
"authToken": "replaceWithAuthTokenFromCreateSession",
"errorCode": 0,
"errorMessage": ""
}
Paginate through records in reverse sort order
When you want to retrieve all results in reverse sort order, you can start paginating from the end of the results by adding "startFrom": "afterLastRecord" to the request.
Because the "startFrom" property anchors the pagination to a fixed point, including it in your request causes the action to return the same results each time.
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Paste this code into the API Console:
{ "api": "db", "action": "getRecordsFromCursor", "params": { "cursorId": "replaceWithCursorFromGetRecords", "fetchRecords": -2000, "startFrom": "afterLastRecord" }, "debug": "none", "authToken": "replaceWithAuthTokenFromCreateSession" }
- Click the Run button.
Result
Notice the results are in reverse order with id 5 before id 4.
After running this action, the cursor position moves before the first record in the results. Because the cursor is before the first record, a negative "fetchRecords" value cannot retrieve more records, but a positive "fetchRecords" value can.
{
"result": {
"dataFormat": "objects",
"numberFormat": "number",
"binaryFormat": "hex",
"fields": [
{ "name": "id", "type": "bigint" },
{ "name": "changeid", "type": "bigint" },
{ "name": "description", "type": "varchar", "length": 2000 }
],
"data": [
{
"id": 5,
"description": "5",
"changeid": 2294841
},
{
"id": 4,
"description": "4",
"changeid": 2294841
}
],
"moreRecords": false,
"returnedRecordCount": 2,
"requestedRecordCount": 2000
},
"authToken": "replaceWithAuthtokenFromCreateSession",
"errorCode": 0,
"errorMessage": ""
}
Paginate through records one at a time
Because the server remembers the cursor position, you can run the following action repeatedly to paginate through the query results until the response contains "moreRecords": false.
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Paste this code into the API Console:
{ "api": "db", "action": "getRecordsFromCursor", "params": { "cursorId": "replaceWithCursorFromGetRecords", "fetchRecords": 1 }, "debug": "none", "authToken": "replaceWithAuthTokenFromCreateSession" }
- Click the Run button.
Result
Notice there are more records because the response contains "moreRecords": true.
After running this action, the cursor position moves before the next record in the results.
{
"result": {
"dataFormat": "objects",
"numberFormat": "number",
"binaryFormat": "hex",
"fields": [
{ "name": "id", "type": "bigint" },
{ "name": "changeid", "type": "bigint" },
{ "name": "description", "type": "varchar", "length": 2000 }
],
"data": [
{
"id": 4,
"description": "4",
"changeid": 2294841
}
],
"moreRecords": true,
"returnedRecordCount": 1,
"requestedRecordCount": 1
},
"authToken": "replaceWithAuthtokenFromCreateSession",
"errorCode": 0,
"errorMessage": ""
}
Get the next record
Because the server remembers the cursor position, you can run the same action repeatedly to paginate through the query results until the response contains "moreRecords": false.
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Paste this code into the API Console:
{ "api": "db", "action": "getRecordsFromCursor", "params": { "cursorId": "replaceWithCursorFromGetRecords", "fetchRecords": 1 }, "debug": "none", "authToken": "replaceWithAuthTokenFromCreateSession" }
- Click the Run button.
Result
After running this action, the cursor position moves before the next record in the results.
You may be surprised a query response contains "moreRecords": true when the next fetch returns no records. This occurs when the server hasn’t yet detected that it has run out of records. An application should always handle the case when the "getRecordsFromCursor" action returns no records, such as "data": [].
{
"result": {
"dataFormat": "objects",
"numberFormat": "number",
"binaryFormat": "hex",
"fields": [
{ "name": "id", "type": "bigint" },
{ "name": "changeid", "type": "bigint" },
{ "name": "description", "type": "varchar", "length": 2000 }
],
"data": [
{
"id": 5,
"description": "5",
"changeid": 2294841
}
],
"moreRecords": true,
"returnedRecordCount": 1,
"requestedRecordCount": 1
},
"authToken": "replaceWithAuthtokenFromCreateSession",
"errorCode": 0,
"errorMessage": ""
}
Keep fetching records until "moreRecords": false
Run the same action one more time.
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Paste this code into the API Console:
{ "api": "db", "action": "getRecordsFromCursor", "params": { "cursorId": "replaceWithCursorFromGetRecords", "fetchRecords": 1 }, "debug": "none", "authToken": "replaceWithAuthTokenFromCreateSession" }
- Click the Run button.
Result
Notice there are no more records because the response contains "moreRecords": false.
After running this action, the cursor position moves after the last record in the results.
{
"result": {
"dataFormat": "objects",
"numberFormat": "number",
"binaryFormat": "hex",
"fields": [
{ "name": "id", "type": "bigint" },
{ "name": "changeid", "type": "bigint" },
{ "name": "description", "type": "varchar", "length": 2000 }
],
"data": [],
"moreRecords": false,
"returnedRecordCount": 0,
"requestedRecordCount": 1
},
"authToken": "replaceWithAuthtokenFromCreateSession",
"errorCode": 0,
"errorMessage": ""
}
Skip through records
Use the "skipRecords" property to skip forward or backward over records.
Move the cursor to before the first record in the results, skip it, and retrieve the second record. The first record in the results has id 4, and the second record has id 5.
You can skip records to sample 1 record out of every N records.
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Paste this code into the API Console:
{ "api": "db", "action": "getRecordsFromCursor", "params": { "cursorId": "replaceWithCursorFromGetRecords", "startFrom": "beforeFirstRecord", "skipRecords": 1, "fetchRecords": 1 }, "debug": "none", "authToken": "replaceWithAuthTokenFromCreateSession" }
- Click the Run button.
Result
After running this action, the cursor position moves before the next record in the results.
{
"result": {
"dataFormat": "objects",
"numberFormat": "number",
"binaryFormat": "hex",
"fields": [
{ "name": "id", "type": "bigint" },
{ "name": "changeid", "type": "bigint" },
{ "name": "description", "type": "varchar", "length": 2000 }
],
"data": [
{
"id": 5,
"description": "5",
"changeid": 2294841
}
],
"moreRecords": true,
"returnedRecordCount": 1,
"requestedRecordCount": 1
},
"authToken": "replaceWithAuthtokenFromCreateSession",
"errorCode": 0,
"errorMessage": ""
}
Tail the end of a file
The following cursor tails the last 20 records, which is particularly useful on dynamic queries, such as "getRecordsByTable" that return new records as they are inserted. Because this cursor is based on a SQL query, it is a point-in-time snapshot that does not change.
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Paste this code into the API Console:
{ "api": "db", "action": "getRecordsFromCursor", "params": { "cursorId": "replaceWithCursorFromGetRecords", "startFrom": "afterLastRecord", "skipRecords": -20, "fetchRecords": 20 }, "debug": "none", "authToken": "replaceWithAuthTokenFromCreateSession" }
- Click the Run button.
Result
After running this action, the cursor position moves before the next record in the results.
{
"result": {
"dataFormat": "objects",
"numberFormat": "number",
"binaryFormat": "hex",
"fields": [
{ "name": "id", "type": "bigint" },
{ "name": "changeid", "type": "bigint" },
{ "name": "description", "type": "varchar", "length": 2000 }
],
"data": [
{
"id": 4,
"description": "4",
"changeid": 2294841
},
{
"id": 5,
"description": "5",
"changeid": 2294841
}
],
"moreRecords": false,
"returnedRecordCount": 2,
"requestedRecordCount": 20
},
"authToken": "replaceWithAuthtokenFromCreateSession",
"errorCode": 0,
"errorMessage": ""
}
Clean up the tutorial
Close the cursor.
Important You cannot delete a table when a cursor is using it.
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Paste this code into the API Console and click the Run button to close the cursor:
{ "api": "db", "action": "closeCursor", "params": { "cursorId": "replaceWithCursorFromGetRecords" }, "authToken": "replaceWithAuthTokenFromCreateSession" }
- Paste this code into the API Console and click the Run button to delete the
"testcount"table:{ "api": "db", "action": "deleteTables", "params": { "tableNames": ["testcount"] }, "authToken": "replaceWithAuthTokenFromCreateSession" }
See also
See getRecordsFromCursor for information on cursor concepts.
