Count and paginate records

JSON DB API: Count and paginate records

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 API Explorer

Steps to set up API Explorer for running a JSON API tutorial.

Ensure the FairCom server is installed and running.
Run the Data Explorer web application.
1. Open a Chrome-based web browser and enter https://localhost:8443/ into the address bar.
2. Click on the Data Explorer icon.
Click on the API Explorer Tab.
Optionally select the desired API from the Select API dropdown menu.
Tip
For convenience, each FairCom product defaults to a different API.
- FairCom DB and FairCom RTG default to the DB API.
- FairCom Edge defaults to the HUB API.
- FairCom MQ defaults to the MQ API.

How to use API Explorer to run a JSON Action

Use API Explorer to POST a JSON Action to a FairCom server

Open Data Explorer and click on the API Explorer tab. See Set up API Explorer.

Paste JSON action code into the API Request editor, such as the following:

{
  "api": "db",
  "action": "createDatabase",
  "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken",
  "params": {
    "databaseName": "new_database_name"
  }
}

Or select the "createDatabase" example from the JSON Actions dropdown menu.

Click Apply defaults to JSON request () to set "authToken" to the current session's value.
Click Send request () to POST the JSON to the FairCom server.
Verify the action completed successfully.
- "errorCode" with a value of 0 indicates success.
- "errorCode" with a non-zero value indicates a failure.
- See Errors and contact FairCom for more information about an error.
Note
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 id field 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 changeid field 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 your changeid value 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.

{
  "api": "db",
  "action": "createTable",
  "params": {
    "tableName": "testcount",
    "fields": [
      {
        "name": "description",
        "type": "varchar",
        "length": 250,
        "nullable": false
      }
    ]
  },
  "authToken": "replaceWithAuthtokenFromCreateSession"
}

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.

{
  "api": "db",
  "action": "insertRecords",
  "params": {
    "tableName": "testcount",
    "dataFormat": "objects",
    "sourceData": [
      {
        "description": "1"
      },
      {
        "description": "2"
      },
      {
        "description": "3"
      },
      {
        "description": "4"
      },
      {
        "description": "5"
      }
    ]
  },
  "authToken": "replaceWithAuthtokenFromCreateSession"
}

You can quickly view the inserted records in the Table Records tab of the Data Explorer application.

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": true to 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": true to 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.

{
  "api": "db",
  "action": "getRecordsUsingSQL",
  "params": {
    "sql": "SELECT * from testcount where id > 3",
    "forceRecordCount": true,
    "returnCursor": true
  },
  "debug": "none",
  "authToken": "replaceWithAuthtokenFromCreateSession"
}

Results

Notice that "totalRecordCount" 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": ""
}

Tip

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. This approach makes pagination efficient and fast. Fetching thousands of records at a time also increases efficiency and performance.

Add "cursorId" to the request with the value you saved from the previous query.

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 is true when you can fetch more records from the query; otherwise, it is false. You can fetch records in a loop until "moreRecords" property is false.
The "returnedRecordCount" property is the number of records returned in the request.
The "requestedRecordCount" property is the number of records you requested.

{
  "api": "db",
  "action": "getRecordsFromCursor",
  "params": {
    "cursorId": "replaceWithCursorFromGetRecords",
    "fetchRecords": 2000
  },
  "debug": "none",
  "authToken": "replaceWithAuthtokenFromCreateSession"
}

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.

Tip

A cursor can get into an invalid state when certain database events occur. When the "getRecordsFromCursor" action returns error 14703 "invalid cursor status", you can use "startFrom": "beforeFirstRecord" or "startFrom": "afterLastRecord" to reset the cursor and continue to use it.

{
  "api": "db",
  "action": "getRecordsFromCursor",
  "params": {
    "cursorId": "replaceWithCursorFromGetRecords",
    "fetchRecords": 2000,
    "startFrom": "beforeFirstRecord"
  },
  "debug": "none",
  "authToken": "replaceWithAuthtokenFromCreateSession"
}

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.

Note

All cursors go forward and backward except a cursor on a table containing variable-length records, which can only go forward.

{
  "api": "db",
  "action": "getRecordsFromCursor",
  "params": {
    "cursorId": "replaceWithCursorFromGetRecords",
    "fetchRecords": -2000
  },
  "debug": "none",
  "authToken": "replaceWithAuthtokenFromCreateSession"
}

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.

{
  "api": "db",
  "action": "getRecordsFromCursor",
  "params": {
    "cursorId": "replaceWithCursorFromGetRecords",
    "fetchRecords": -2000,
    "startFrom": "afterLastRecord"
  },
  "debug": "none",
  "authToken": "replaceWithAuthtokenFromCreateSession"
}

Result

Notice the results are in reverse order with ID 5 before ID 4.

{
  "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": ""
}

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.

{
  "api": "db",
  "action": "getRecordsFromCursor",
  "params": {
    "cursorId": "replaceWithCursorFromGetRecords",
    "fetchRecords": 1
  },
  "debug": "none",
  "authToken": "replaceWithAuthtokenFromCreateSession"
}

Result

After running this action, the cursor position moves before the next record in the results.

You may be surprised the response contains "moreRecords": true even though you know there are no more records in the response. The server only returns "moreRecords": true when a fetch fails to retrieve records. The server uses this approach because cursors on non-SQL queries dynamically include records you or other users insert, update, and delete. Cursors on SQL queries are a point-in-time snapshot of the query results and are unaffected by all record changes.

{
  "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.

{
  "api": "db",
  "action": "getRecordsFromCursor",
  "params": {
    "cursorId": "replaceWithCursorFromGetRecords",
    "fetchRecords": 1
  },
  "debug": "none",
  "authToken": "replaceWithAuthtokenFromCreateSession"
}

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.

{
  "api": "db",
  "action": "getRecordsFromCursor",
  "params": {
    "cursorId": "replaceWithCursorFromGetRecords",
    "startFrom": "beforeFirstRecord",
    "skipRecords": 1,
    "fetchRecords": 1
  },
  "debug": "none",
  "authToken": "replaceWithAuthtokenFromCreateSession"
}

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.

{
  "api": "db",
  "action": "getRecordsFromCursor",
  "params": {
    "cursorId": "replaceWithCursorFromGetRecords",
    "startFrom": "afterLastRecord",
    "skipRecords": -20,
    "fetchRecords": 20
  },
  "debug": "none",
  "authToken": "replaceWithAuthtokenFromCreateSession"
}

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.

Note

You cannot delete a table when a cursor is using it.

{
  "api": "db",
  "action": "closeCursor",
  "params": {
    "cursorId": "replaceWithCursorFromGetRecords"
  },
  "authToken": "replaceWithAuthtokenFromCreateSession"
}

Delete the "testcount" table.

{
  "api": "db",
  "action": "deleteTables",
  "params": {
    "tableNames": ["testcount"]
  },
  "authToken": "replaceWithAuthtokenFromCreateSession"
}

Count and paginate records

JSON DB API: Count and paginate records

Set up API Explorer

Tip

How to use API Explorer to run a JSON Action

Note

Prepare a table for testing

Create a table named testcount

Insert 5 records

Query records, get a record count, and return a cursor

Results

Tip

Use a Cursor to paginate through query results

Results

Reset the cursor position to the beginning

Tip

Result

Fetch records backward

Note

Result

Paginate through records in reverse sort order

Result

Get the next record

Result

Keep fetching records until "moreRecords": false

Result

Skip through records

Result

Tail the end of a file

Result

Clean up the tutorial

Note

See also

Keep fetching records until `"moreRecords": false`