Athlete tutorial

Explore the JSON DB API using FairCom's Control Center

This tutorial uses FairCom's Control Center to create an athlete table with indexes, insert records, create a transaction to update and delete records, and roll back the transaction. This tutorial also shows how to return records using cursors and SQL.

 

Set up the API Console

Steps to set up the FairCom Control Center for running a JSON API tutorial.
  1. Ensure the FairCom server is installed and running.
  2. Confirm server access by running FairCom's Control Center.
  3. Navigate to the Code tab.
  4. Select API Console from the Code menu.

 

How to use the web application to run a JSON Action

  1. Navigate to the Code tab. 
  2. Select API Console from the Code menu.
  3. 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": "replaceThisWithValidAuthToken",
      "params": {
        "databaseName": "new_database_name"
      }
    }
  4. Click the Autofill button () if Autofill has not been turned on to set the "authToken", "requestId", and "cursorId" to the current session's values.
  5. Click the Run button () to run the action.
  6. 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.

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

 

Create the athlete table

  1. Navigate to the Code tab. 
  2. Select API Console from the Code menu.
  3. Paste this code into the API Console:
    1. {
          "api": "db",
          "action": "createTable",
          "params": {
              "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": "replaceWithAuthTokenFromCreateSession"
      }
  4. Click the Run button.

 

Create indexes on the athlete table

  1. Navigate to the Code tab. 
  2. Select API Console from the Code menu.
  3. Paste this code into the API Console and click the Run button to create the "ranking" index on the ranking field:
    1. {
          "api": "db",
          "action": "createIndex",
          "params": {
              "ownerName": "admin",
              "tableName": "athlete",
              "indexName": "ranking",
              "fields": [
                  {
                      "name": "ranking"
                  }
              ],
              "waitToBeLoaded": true
          },
          "authToken": "replaceWithAuthTokenFromCreateSession"
      }
  4. Paste this code into the API Console and click the Run button to create the "earnings" index on the earnings field:
    1. {
        "api": "db",
        "action": "createIndex",
        "params": {
          "tableName": "athlete",
          "indexName": "earnings",
          "fields": [
            {
              "name": "earnings"
            }
          ],
          "waitToBeLoaded": true
        },
        "authToken": "replaceWithAuthTokenFromCreateSession"
      }
  5. Paste this code into the API Console and click the Run button to create the "name_livedpast2000" index on the name field:
    1. {
        "api": "db",
        "action": "createIndex",
        "params": {
          "databaseName": "faircom", 
          "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": "replaceWithAuthTokenFromCreateSession",
        "apiVersion": "1.0",
        "requestId": "4",
        "debug": "max"
      }

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 using JSON Actions

  1. Navigate to the Code tab. 
  2. Select API Console from the Code menu.
  3. Paste this code into the API Console:
    1. {
          "api": "db",
          "action": "insertRecords",
          "params": {
              "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": "replaceWithAuthTokenFromCreateSession"
      }
  4. Click the Run button. 

 

View records

View records using the web application

  1. Navigate to the Data tab. 
  2. Select Tables from the Data menu. 
  3. Click on the table name. For this tutorial, click the "athlete" table 
  4. Observe the records.

 

View records using SQL statements

  1. Navigate to the Data tab
  2. Select SQL Commands from the Data menu.
  3. Paste this code into the SQL Script Request editor:
    1. SELECT * FROM athlete;
  4. Click the Run button.
  5. Observe the Script results in the Execution Log .

 

Get athlete records by ID

  1. Navigate to the Code tab. 
  2. Select API Console from the Code menu.
  3. Paste this code into the API Console and click the Run button to return two records from the "athlete" table with "id" values of 1 and 3:
    1. {
          "api": "db",
          "action": "getRecordsByIds",
          "params": {
              "tableName": "athlete",
              "ids": [1,3]
          },
          "authToken": "replaceWithAuthTokenFromCreateSession"
      }
    2. The "getRecordsById" action is the best way to retrieve records using primary and foreign keys.

 

Get athlete records in table order

  1. Navigate to the Code tab. 
  2. Select API Console from the Code menu.
  3. Paste this code into the API Console and click the Run button to return all records in a table in table order. This is the fastest query technique.
    1. {
          "api": "db",
          "action": "getRecordsByTable",
          "params": {
              "databaseName": "faircom", 
              "tableName": "athlete"
          },
          "authToken": "replaceWithAuthTokenFromCreateSession"
      }

Note 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

  1. Navigate to the Code tab. 
  2. Select API Console from the Code menu.
  3. Paste this code into the API Console and click the Run button to return all records in a table in index order. Using the "getRecordsByIndex" action without a cursor returns all records at the fastest possible speed for sorted data. Set "reverseOrder": true to return records in the reverse order of the index.
    1. {
          "api": "db",
          "action": "getRecordsByIndex",
          "params": {
              "tableName": "athlete",
              "indexName": "id_pk",
              "reverseOrder": true
          },
          "authToken": "replaceWithAuthTokenFromCreateSession"
      }

Note 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 getRecordsByIndex.

 

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.
  1. Navigate to the Code tab. 
  2. Select API Console from the Code menu.
  3. Paste this code into the API Console and click the Run button to return the first two records stored in the "athlete" table:
    1. {
        "api": "db",
        "action": "getRecordsByTable",
        "params": {
          "databaseName": "faircom",
          "tableName": "athlete",
          "skipRecords": 0,
          "maxRecords": 2
        },
        "authToken": "replaceWithAuthTokenFromCreateSession"
      }

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

 

Get athlete records matching a partial key

The "getRecordsByPartialKeyRange" action gets records matching a partial key.

  1. Navigate to the Code tab. 
  2. Select API Console from the Code menu.
  3. Paste this code into the API Console and click the Run button:
    1. {
        "api": "db",
        "action": "getRecordsByPartialKeyRange",
        "params": {
          "databaseName": "faircom",
          "tableName": "athlete",
          "indexFilter": {
            "indexName": "name_livedpast2000",
            "partialKey": "Mi"
          }
        },
        "authToken": "replaceWithAuthTokenFromCreateSession"
      }

 

Get athlete records starting near a key

The "getRecordsStartingAtKey" action is the best way to return records in index order starting with the record that most closely matches the key value.

  1. Navigate to the Code tab. 
  2. Select API Console from the Code menu.
  3. Paste this code into the API Console and click the Run button to get records with an id value of 4 or greater:
    1. {
          "api": "db",
          "action": "getRecordsStartingAtKey",
          "params": {
              "tableName": "athlete",
              "indexFilter": {
                  "indexName": "admin_athlete_id_pk",
                  "operator": "=",
                  "indexFields": [
                      {
                          "fieldName": "id",
                          "value": "4"
                      }
                  ]
              }
          },
          "authToken": "replaceWithAuthTokenFromCreateSession"
      }

 

Get athlete records between keys

The "getRecordsInKeyRange" action gets records within a range of index values.

  1. Navigate to the Code tab. 
  2. Select API Console from the Code menu.
  3. Paste this code into the API Console and click the Run button to get records where the id is greater than or equal to 5:
    1. {
          "api": "db",
          "action": "getRecordsInKeyRange",
          "params": {
              "tableName": "athlete",
              "indexFilter": {
                  "indexName": "admin_athlete_id_pk",
                  "indexFieldFilters": [
                      {
                          "fieldName": "id",
                          "operator": ">=",
                          "value": "5"
                      }
                  ]
              }
          },
          "authToken": "replaceWithAuthTokenFromCreateSession"
      }

 

Use a cursor to get athlete records

Adding "returnCursor": true to any getRecords action is the fastest and most efficient way to paginate data.

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.

  1. Navigate to the Code tab. 
  2. Select API Console from the Code menu.
  3. Paste this code into the API Console and click the Run button 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": "replaceWithAuthTokenFromCreateSession"
    }
  4. Copy and save the "cursorId" from the Response to use in subsequent calls to "getRecordsFromCursor".

 

Get records from a cursor

  1. Paste the following code into the API Console. Note that you must have a valid "cursorId" by calling another "getRecords…" action.
    1. {
          "api": "db",
          "action": "getRecordsFromCursor",
          "params": {
              "cursorId": "replaceThisWithTheLastCreatedCursor",
              "fetchRecords": 1
          },
          "authToken": "replaceThisWithValidAuthToken"
      }
  2. Click the Run button.
  3. Continue to click the Run button to move through each record.
    1. The same code fetches different records each time it is run because each call moves the cursor position.
  4. To fetch records from different locations in the recordset, change the values of "startFrom", "skipRecords", and "fetchRecords".
    1. The API Console automatically recognizes the "cursorId" in a response and remembers it. So, when you select the "getRecordsFromCursor" action from the JSON Actions dropdown menu, the API Console automatically populates the action with the latest "cursorId" value to simplify 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": -3
  • 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.

  1. Navigate to the Code tab. 
  2. Select API Console from the Code menu.
  3. Paste this code into the API Console and click the Run button:
    1. {
          "api": "db",
          "action": "closeCursor",
          "params": {
              "cursorId": "replaceThisWithTheLastCreatedCursor"
          },
          "authToken": "replaceThisWithValidAuthToken"
      }

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