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 tutorialAthlete 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.

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.

Create the athlete table

Run the following JSON action.

{
    "api": "db",
    "action": "createTable",
    "params": {
        "databaseName": "ctreeSQL",
        "tableName": "athlete",
        "fields": [
            {
                "name": "name",
                "type": "varchar",
                "length": 30
            },
            {
                "name": "ranking",
                "type": "smallint",
                "nullable": false
            },
            {
                "name": "birthDate",
                "type": "date"
            },
            {
                "name": "playerNumber",
                "type": "number",
                "length": 32,
                "scale": 6
            },
            {
                "name": "livedPast2000",
                "type": "bit"
            },
            {
                "name": "earnings",
                "type": "money",
                "length": 32,
                "scale": 4
            },
            {
                "name": "favoriteSaying",
                "type": "varchar",
                "length": 500
            }
        ]
    },
    "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken"
}

Create indexes on the athlete table

Run the following JSON action to create the "ranking" index on the ranking field:

{
    "api": "db",
    "action": "createIndex",
    "params": {
        "databaseName": "ctreeSQL",
        "ownerName": "admin",
        "tableName": "athlete",
        "indexName": "ranking",
        "fields": [
            {
                "name": "ranking"
            }
        ],
        "waitToBeLoaded": true
    },
    "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken"
}

Run the following JSON action to create the "earnings" index on the earnings field:

{
  "api": "db",
  "action": "createIndex",
  "params": {
    "databaseName": "ctreeSQL",
    "tableName": "athlete",
    "indexName": "earnings",
    "fields": [
      {
        "name": "earnings"
      }
    ],
    "waitToBeLoaded": true
  },
  "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken"
}

Run the following JSON action to create the "name_livedpast2000" index on the name field:

{
  "api": "db",
  "action": "createIndex",
  "params": {
    "tableName": "athlete",
    "indexName": "name_livedpast2000",
    "fields": [
      {
        "name": "name",
        "caseInsensitive": true,
        "sortDescending": true,
        "reverseCompare": false
      }
    ],
    "conditionalExpression": "livedpast2000 == 1",
    "unique": false,
    "immutableKeys": false,
    "waitToBeLoaded": true,
    "filename": "admin_athlete_name_livedpast2000",
    "collectStats": true,
    "compression": "auto"
  },
  "responseOptions": {
    "binaryFormat": "hex",
    "dataFormat": "objects",
    "numberFormat": "string"
  },
  "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken",
  "apiVersion": "1.0",
  "requestId": "4",
  "debug": "max"
}

Note

The "name_livedpast2000" index is a conditional index that includes records only when the "conditionalExpression" is true. Use it to retrieve specific records at high speed.

A conditional index is not visible to SQL because SQL requires an index to contain all records in a table.

Insert records

Insert records into the athlete table.

Run the following JSON action:

{
    "api": "db",
    "action": "insertRecords",
    "params": {
        "databaseName": "ctreeSQL",
        "tableName": "athlete",
        "dataFormat": "objects",
        "sourceData": [
            {
                "name": "Michael Jordan",
                "ranking": 1,
                "birthDate": "19630217",
                "playerNumber": 23,
                "livedPast2000": true,
                "earnings": 1700000000,
                "favoriteSaying": "There is no 'i' in team but there is in win."
            },
            {
                "name": "Babe Ruth",
                "ranking": 2,
                "birthDate": "18950206",
                "playerNumber": 3,
                "livedPast2000": false,
                "earnings": 800000,
                "favoriteSaying": "Every strike brings me closer to the next home run."
            },
            {
                "name": "Muhammad Ali",
                "ranking": 3,
                "birthDate": "19420117",
                "playerNumber": 1,
                "livedPast2000": true,
                "earnings": 60000000,
                "favoriteSaying": "Float like a butterfly, sting like a bee."
            },
            {
                "name": "Pele",
                "ranking": 4,
                "birthDate": "19401023",
                "playerNumber": 10,
                "livedPast2000": true,
                "earnings": 115000000,
                "favoriteSaying": "Everything is practice."
            },
            {
                "name": "Wayne Gretzky",
                "ranking": 5,
                "birthDate": "19610126",
                "playerNumber": 99,
                "livedPast2000": true,
                "earnings": 1720000,
                "favoriteSaying": "You miss 100 percent of the shots you never take."
            },
            {
                "name": "Michael Schumacher",
                "ranking": 6,
                "birthDate": "19690103",
                "playerNumber": 1,
                "livedPast2000": true,
                "earnings": 990000000,
                "favoriteSaying": "Once something is a passion, the motivation is there."
            }
        ]
    },
    "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken"
}

View records in the Table Records Tab

Use Data Explorer's Table Records tab to view the records in the athlete table.

Click on athlete under Database Objects, admin , and Tables.
Click the Table Records tab ().

View records in the SQL Queries tab

Use Data Explorer's SQL Queries tab to view the records in the athlete table.

Click the SQL Queries tab ().
Enter the following SQL Query:
```
SELECT id, name, ranking, birthdate, favoritesaying FROM athlete
```
Note
- Data Explorer automatically adds the TOP 20 SKIP 0 clause to your SQL query to limit the number of records returned by the query:
```
SELECT TOP 20 SKIP 0 id, name, ranking, birthdate, favoritesaying FROM athlete
```
- You can remove the values from the TOP and SKIP text boxes and Data Explorer will no longer add the TOP 20 SKIP 0 clause to your SQL query.
- You can change the TOP to another value to retrieve a larger or smaller number of records.
- You can change the SKIP to another value to skip a different number of records.
- You can also check the DISTINCT box to add the DISTINCT clause to your SQL query.
Click Execute SQL Statement ().
Tip
You can also press CTRL + ENTER on Windows and Linux or for MacOS press COMMAND + ENTER.

View records in the SQL Scripts tab

Use Data Explorer's SQL Scripts tab to view the records in the athlete table.

Click the SQL Scripts tab ().
Enter the following SQL query:
```
SELECT * from athlete;
```
Tip
- SQL scripts can query records, create objects, and insert, update, and delete records.
- Because the SQL Scripts tab can run multiple SQL statements, each statement must be followed by a semicolon.
  In contrast, the SQL Queries tab () can only run one SQL query at a time; thus, queries in the SQL Queries tab do not need a semicolon at the end.
Click Run All ().
Tip
- You can select one SQL statement, out of many, and click the Run Selection button () to execute the statement.
- The SQL Scripts tab returns results as text which is useful when you want to copy query results.
  In contrast, the SQL Queries tab returns results in a data grid that lets you resize columns and paginate through results.

Get athlete records by ID

Run the following JSON action to return two records from the "athlete" table with "id" values of 1 and 3.

{
    "api": "db",
    "action": "getRecordsByIds",
    "params": {
        "tableName": "athlete",
        "ids": [1,3]
    },
    "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken"
}

Tip

The "getRecordsById" action is the best way to retrieve records using primary and foreign keys.

Get athlete records in table order

Use "getRecordsByTable" without a cursor to return all records in a table in table order. This is the fastest query technique.

Run the following JSON action.

{
    "api": "db",
    "action": "getRecordsByTable",
    "params": {
        "tableName": "athlete"
    },
    "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken"
}

Important

If your table has more than 100,000 records, you should add the "returnCursor": true property to return a cursor, which allows you to paginate through the table quickly and efficiently, see getRecordsByTable.

Get athlete records by index

Use the "getRecordsByIndex" action without a cursor to return all records in a table in index order at the fastest possible speed for sorted data.

Set "reverseOrder": true to return records in the reverse order of the index.

Run the following JSON action.

{
    "api": "db",
    "action": "getRecordsByIndex",
    "params": {
        "tableName": "athlete",
        "indexName": "id_pk",
        "reverseOrder": true
    },
    "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken"
}

Important

Return first N records from the athlete table

Use "maxRecords" to return the top N records.

Use with "getRecordsByTable" to return the first N records stored in the table.
Use with "getRecordsByIndex" to return the first N records in index order or the last N records in reverse index order.
Use with "getRecordsByPartialKeyRange" to return the first N records that match the partial key or the last N records that match the partial key when the index is in reverse index order.
Use with "getRecordsInKeyRange" to return the first N records at the beginning of the index range or the last N records at the end of the index range when the index is in reverse index order.
Use with "getRecordsStartingAtKey" to return the first N records that match the key or the previous N records when the index is in reverse index order.

Run the following JSON action to return the first two records stored in the "athlete" table.

{
  "api": "db",
  "action": "getRecordsByTable",
  "params": {
    "tableName": "athlete",
    "skipRecords": 0,
    "maxRecords": 2
  },
  "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken"
}

Important

It is possible to use the "skipRecords" and "maxRecords" properties to paginate data, but a cursor is faster because "skipRecords" causes the server to walk records from the beginning until it has skipped the requested number of records.

In contrast, a cursor remembers its last position and instantly returns the next set of records. Use the "returnCursor": true property to return a cursor to paginate quickly and efficiently.

Use a cursor to get athlete records

Adding "returnCursor": true to any getRecords action is the fastest and most efficient way to paginate data, see Get records from cursor.

To return a cursor, add the property "returnCursor": true to a getRecords action. The response contains a "cursorId" property that you use in the "getRecordsFromCursor" action to retrieve records from the cursor.

Run the following JSON action to return a cursor that retrieves records from the athlete table in ranking order. The cursor can skip and retrieve records forward and backward.

{
  "api": "db",
  "action": "getRecordsByIndex",
  "params": {
    "tableName": "athlete",
    "indexName": "ranking",
    "returnCursor": true
  },
  "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken"
}

Copy and save the "cursorId" from the Response to use in subsequent calls to "getRecordsFromCursor".

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.

Close the cursor

Close the previously opened cursor on the athlete table.

Run the following JSON action:

{
    "api": "db",
    "action": "closeCursor",
    "params": {
        "cursorId": "clickApplyDefaultsToReplaceThisWithTheLastCreatedCursor"
    },
    "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken"
}

Tip

To preserve server resources, use the "closeCursor" action to close a cursor as soon as you are done using it.