JSON DB API tutorials

Tutorials for the JSON DB API using the `athlete` table in the API Explorer user interface

Abstract

Tutorials for the JSON DB API using the athlete table in the API Explorer user interface

Tutorial	Description
Athlete tutorial	This tutorial uses Data Explorer and API Explorer web apps to create an athlete table with indexes. It inserts records. It creates a transaction to update and delete records and rolls back the transaction. It retrieves records using cursors and SQL.
Count and paginate records	This tutorial shows how to perform a SQL query and use cursors to paginate results with a count of records read for each page.

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.

Get records from a cursor

Run the following JSON action to retrieve the next record from the cursor.

{
    "api": "db",
    "action": "getRecordsFromCursor",
    "params": {
        "cursorId": "clickApplyDefaultsToReplaceThisWithTheLastCreatedCursor",
        "fetchRecords": 1
    },
    "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken"
}

Click Send request ().
Continue to click Send request to move through each record.
Note
The same code fetches different records each time it is run because each call moves the cursor position.
To fetch records from different locations in the recordset, change the values of "startFrom", "skipRecords", and "fetchRecords".

Note

The API Explorer automatically recognizes the "cursorId" in a response and remembers it so when you select the "getRecordsFromCursor" from the JSON Actions dropdown menu, API Explorer automatically populates the action with the latest "cursorId" value to simply using cursors.

Ways to move a cursor and retrieve records

The following lists contain techniques, examples, and capabilities of cursors.

Various ways to combine "fetchRecords", "skipRecords", and "startFrom" to move the cursor:

To fetch forwards, assign a positive integer number to "fetchRecords".
To fetch backwards, assign a negative integer number to "fetchRecords".
To skip forwards, assign a positive integer number to "skipRecords".
To skip backwards, assign a negative integer number to "skipRecords".
To move a cursor to the beginning of the recordset, set the "startFrom" property to "beforeFirstRecord".
To move a cursor to the end of the recordset, set the "startFrom" property to "afterLastRecord".

Examples:

To return the next two records:
```
"fetchRecords": 2
```
To return the previous three records:
```
"fetchRecords": -31
```
To skip the next two records and fetch 1 record:
```
"skipRecords": 2, 
"fetchRecords": 1
```
To skip the previous record before the next 3 previous records:
```
"skipRecords": -1 , 
"fetchRecords": -3
```

To fetch the first 3 records in the recordset:

"startFrom": "beforeFirstRecord", 
"fetchRecords": 3

To fetch the last 3 records in the recordset:

"startFrom": "afterLastRecord", 
"skipRecords": -3, 
"fetchRecords": 3

Cursors that only move forward:

"getRecordsFromTable" allows the cursor to move through every record in the table from beginning to end.
"getRecordsUsingSQL" allows the cursor to move through every record returned by the SQL query from beginning to end.

Bidirectional cursors:

"getRecordsByIndex" allows the cursor to move through every record in the index.
"getRecordsStartingAtKey" allows the cursor to move through every record in the index starting with the closest match to the key.
"getRecordsByPartialKeyRange" allows the cursor to move through every record in the index that matches the partial key.
"getRecordsInKeyRange" allows the cursor to move through every record in the index within the specified key range.