Skip to main content

Manage a database using API Explorer tutorials

These tutorials detail how to perform common database tasks, such as creating a table, indexing, recording, and querying records.

Requirements:

Complete the tutorial requirements before this procedure.

Both create table procedures will create a table named "athlete". You can use either of these procedures to create this table.

Note

This procedure must succeed or all other subsequent steps in the tutorial will fail.

Create a table using a template

  1. Select the DB API from the Select API dropdown menu.

  2. Select the "createTable - athlete" template from the Templates dropdown menu.

  3. Click Send request (Run Icon).

  4. Observe the response and ensure the action completed successfully.

    Note

    "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure, see Errors and contact FairCom with any questions.

Create a table using the API Request editor

  1. Paste this code into the API Request editor.

    {
        "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"
    }
  2. Click Apply defaults to JSON request (Apply) to apply defaults and set the "authToken" to a valid value.

    Note

    You will receive this error if you do not click Apply defaults to JSON request (Apply) to set the "authToken" to a valid session.

    "errorCode": 60031,
        "errorMessage": "Not possible to find valid session for the provided token."
    }
    
  3. Click Send request (Run Icon).

  4. Observe the response and ensure the action completed successfully.

    Note

    "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure, see Errors and contact FairCom with any questions.

  1. Paste this code in the API API Request editor.

    {
        "api": "db",
        "action": "createIndex",
        "params": {
            "databaseName": "ctreeSQL",
            "ownerName": "admin",
            "tableName": "athlete",
            "indexName": "name",
            "fields": [
                {
                    "name": "name"
                }
            ],
            "unique": false,
            "waitToBeLoaded": true
        },
        "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken"
    }
    
  2. Click Apply defaults to JSON request (Apply) to set the "authToken" to a valid value.

  3. Click Send request (Run Icon).

  4. Observe the response and ensure the action completed successfully.

    Note

    "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure, see Errors and contact FairCom with any questions.

  1. Paste this code into the API Request editor.

    {
        "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"
    }
    
  2. Click Apply defaults to JSON request (Apply) to set the "authToken" to a valid value.

  3. Click Send request (Run Icon).

  4. Observe the response and ensure the action completed successfully.

    Note

    "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure, see Errors and contact FairCom with any questions.

  1. Navigate to and select athlete in the Server navigation window through ctreeSQL>admin>Table.

  2. Click the Table Records tab (Table Records Tab).

  1. Click the SQL Queries tab (SQL Queries Tab).

  2. Enter the following SQL Query:

    SELECT * from athlete

    Note

    • Data Explorer automatically adds the TOP 20 SKIP 0 clause to your SQL query to ensure it does not query all records in a table meaning the previous query is automatically changed to:

      SELECT TOP 20 SKIP 0 * 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.

  3. Click Send request (Run Icon).

    Tip

    You can also press CTRL + ENTER on Windows and Linux or for MacOS press COMMAND + ENTER.

  1. Click the SQL Scripts tab (SQL Scripts Tab).

  2. Enter the following SQL query:

    SELECT * from athlete;

    Note

    • The SQL Scripts tab can run multiple SQL statements. Typically these statements create objects and insert, update, and delete records.

    • Sometimes scripts query 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 (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.

  3. Click Run All (Run All).

    Tip

    • The SQL Scripts tab returns results as text which is useful for queries when you want to copy query results into another program. However, It is less friendly than the SQL Queries tab, which returns results in a formatted table.

    • You can select one SQL statement, out of many, and click the Run Selection button (RunSelection.png) to execute the statement.

The "getRecordsById" action is the best way to retrieve records using primary keys and foreign keys. It uses the id property to return one or more records from a table. The JSON DB API always creates a table with the id property, but some other APIs may not. For tables created without an "id" property, the "getRecordsById" action allows you to use the table’s primary key to retrieve records.

This procedure returns two records from the "athlete" table with "id" values of 1 and 3.

  1. Paste this code into the API Request editor.

    {
        "api": "db",
        "action": "getRecordsByIds",
        "params": {
            "tableName": "athlete",
            "ids": [1,3]
        },
        "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken"
    }
    
  2. Click Apply defaults to JSON request (Apply) to set the "authToken" to a valid value.

  3. Click Send request (Run Icon).

  4. Observe the response and ensure the action completed successfully.

    Note

    "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure, see Errors and contact FairCom with any questions.

Using the "getRecordsByTable" action without a cursor is the best way to return all records in a table.

Important

The query shown in this procedure returns all records in a table. 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.

  1. Paste this code into the API Request editor.

    {
        "api": "db",
        "action": "getRecordsByTable",
        "params": {
            "tableName": "athlete"
        },
        "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken"
    }
    
  2. Click Apply defaults to JSON request (Apply) to set the "authToken" to a valid value.

  3. Click Send request (Run Icon).

  4. Observe the response and ensure the action completed successfully.

    Note

    "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure, see Errors and contact FairCom with any questions.

Using the "getRecordsByIndex" action without a cursor is the best way to return all records in a table in index order.

Important

The query shown in this procedure returns all records in a table. 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.

  1. Paste this code into the API Request editor.

    {
        "api": "db",
        "action": "getRecordsByIndex",
        "params": {
            "tableName": "athlete",
            "indexName": "admin_athlete_id_pk"
        },
        "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken"
    }
    
  2. Click Apply defaults to JSON request (Apply) to set the "authToken" to a valid value.

  3. Click Send request (Run Icon).

  4. Observe the response and ensure the action completed successfully.

    Note

    "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure, see Errors and contact FairCom with any questions.

Using the "skipRecords" and "maxRecords" properties is the best way to return the top N records from the "getRecordsByTable", "getRecordsByIndex", "getRecordsByPartialKeyRange", "getRecordsInKeyRange", and "getRecordsStartingAtKey" actions.

This procedure returns the top two records from the "athlete" table.

Important

It is possible to use the "skipRecords" and "maxRecords" properties to paginate data, but a cursor is better. An issue with "skipRecords" is that it causes the server to walk records from the beginning until it has skipped the requested number of records. Skipping over records is slow.

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.

  1. Paste this code into the API Request editor.

    {
        "action": "getRecordsByTable",
        "params": {
            "tableName": "athlete",
            "skipRecords": 0,
            "maxRecords": 2
        },
        "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken"
    }
    
  2. Click Apply defaults to JSON request (Apply) to set the "authToken" to a valid value.

  3. Click Send request (Run Icon).

  4. Observe the response and ensure the action completed successfully.

    Note

    "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure, see Errors and contact FairCom with any questions.

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.

This procedure returns a cursor that can retrieve records from the "athlete" table in sorted order.

  1. Paste this code into the API Request editor.

    {
        "action": "getRecordsByIndex",
        "params": {
            "tableName": "athlete",
            "indexName": "admin_athlete_name",
            "returnCursor": true
        },
        "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken"
    }
    
  2. Click Apply defaults to JSON request (Apply) to set the "authToken" to a valid value.

  3. Click Send request (Run Icon).

  4. Observe the response and ensure the action completed successfully.

    Note

    "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure, see Errors and contact FairCom with any questions.

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

  1. Paste this code into the API Request editor.

    {
        "api": "db",
        "action": "getRecordsFromCursor",
        "params": {
            "cursorId": "clickApplyDefaultsToReplaceThisWithTheLastCreatedCursor",
            "startFrom": "beforeFirstRecord",
            "skipRecords": 1,
            "fetchRecords": 1
        },
        "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken"
    }
    
  2. Click Apply defaults to JSON request (Apply) to set the "authToken" to a valid value.

    Note

    Clicking the Apply defaults to JSON request icon populates the "cursorId" with the most recent value returns by getRecords action.

  3. Click Send request (Run Icon).

  4. Continue to click Send request to move through the records.

    Note

    The same code fetches different records each time it is run because each call moves the cursor position.

  5. To fetch records from different locations in the recordset, change the values of "startFrom", "skipRecords", and "fetchRecords".

  6. Observe the response and ensure the action completed successfully.

    Note

    • "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure, see Errors and contact FairCom with any questions.

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

    • The "cursorId" property is secure because it is a very large crypto-random number — for example, "cursorId": "w0gs3RZz8eD5Qtf0VuQ9V86tsd85IdlvMwUocQwK0U548ew5RlXpNIP0tyaVVX0w".

Prerequisites:

Note

The listed prerequisites need to be run successfully before performing this procedure.

  1. Complete the tutorial requirements.

  2. Create a table.

  3. Create an index on the name field.

  4. Insert records.

  5. Use cursor to get records by index.

This procedure returns records in "id" order starting with the record that has the "id" field set to 4. A cursor that starts returning records can retrieve records from the "athlete" table in sorted order.

Note

The user has the ability to toggle between the property "startFrom" and "beforeFirstRecord" to position the cursor before the first record. In this procedure the index used is created using each athlete records "id", and will return all athletes that have an "id" greater than or equal to 4.

  1. Paste this code into the API Request editor.

    {
        "api": "db",
        "action": "getRecordsFromCursor",
        "params": {
            "cursorId": "clickApplyDefaultsToReplaceThisWithTheLastCreatedCursor",
            "fetchRecords": 1
        },
        "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken"
    }
    
  2. Click Apply defaults to JSON request (Apply) to set the "authToken" to a valid value.

  3. Click Send request (Run Icon).

  4. Observe the response and ensure the action completed successfully.

    Note

    "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure, see Errors and contact FairCom with any questions.

When you are done with a cursor, you should close it using the "closeCursor" action. When the cursor is closed, it is no longer available.

Note

The server automatically closes inactive cursors. To preserve server resources, it is best to close a cursor as soon as you are done using it.

  1. Paste this code into the API Request editor.

    {
        "api": "db",
        "action": "closeCursor",
        "params": {
            "cursorId": "clickApplyDefaultsToReplaceThisWithTheLastCreatedCursor"
        },
        "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken"
    }
    
  2. Click Apply defaults to JSON request (Apply) to set the "authToken" to a valid value.

    Note

    Clicking Apply defaults to JSON request causes the API Explorer to populate the "cursorId" and "authToken" properties with valid values. API Explorer uses the value from the last created cursor to populate the "cursorId".

  3. Click Send request (Run Icon).

  4. Observe the response and ensure the action completed successfully.

    Note

    "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure, see Errors and contact FairCom with any questions.

The "getRecordsStartingAtKey" action is the best way to return records in index order starting with the record that is the closest match to the key.

This procedure returns records in "id" order starting with the record that has the "id" field set to 4. A cursor that starts returning records can retrieve records from the "athlete" table in sorted order.

Note

The user has the ability to toggle between the property "startFrom" and "beforeFirstRecord" to position the cursor before the first record. In this procedure the index used is created using each athlete records "id", and will return all athletes that have an "id" greater than or equal to 4.

  1. Paste this code into the API Request editor.

    {
        "api": "db",
        "action": "getRecordsStartingAtKey",
        "params": {
            "tableName": "athlete",
            "indexFilter": {
                "indexName": "admin_athlete_id_pk",
                "operator": "=",
                "indexFields": [
                    {
                        "fieldName": "id",
                        "value": "4"
                    }
                ]
            }
        },
        "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken"
    }
    
  2. Click Apply defaults to JSON request (Apply) to set the "authToken" to a valid value.

  3. Click Send request (Run Icon).

  4. Observe the response and ensure the action completed successfully.

    Note

    "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure, see Errors and contact FairCom with any questions.

The "getRecordsByPartialKeyRange" action allows the user to create a cursor that contains data filtered by partial key values.

  1. Paste this code into the API Request editor.

    {
        "api": "db",
        "action": "getRecordsByPartialKeyRange",
        "params": {
            "tableName": "athlete",
            "indexFilter": {
                "indexName": "admin_athlete_name",
                "partialKey": "Mi"
            }
        },
        "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken"
    }
    
  2. Click Apply defaults to JSON request (Apply) to set the "authToken" to a valid value.

  3. Click Send request (Run Icon).

  4. Observe the response and ensure the action completed successfully.

    Note

    "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure, see Errors and contact FairCom with any questions.

The "getRecordsInKeyRange" action creates a cursor that parses records starting with the index value specified in the params. Performing this procedure allows you to see all records that have an index that is a bigInt greater than or equal to 5 against the previously created "athlete" table.

  1. Paste this code into the API Request editor.

    {
        "api": "db",
        "action": "getRecordsInKeyRange",
        "params": {
            "tableName": "athlete",
            "indexFilter": {
                "indexName": "admin_athlete_id_pk",
                "indexFieldFilters": [
                    {
                        "fieldName": "id",
                        "operator": ">=",
                        "value": "5"
                    }
                ]
            }
        },
        "authToken": "clickApplyDefaultsToReplaceThisWithValidAuthToken"
    }
    
  2. Click Apply defaults to JSON request (Apply) to set the "authToken" to a valid value.

  3. Click Send request (Run Icon).

  4. Observe the response and ensure the action completed successfully.

    Note

    "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure, see Errors and contact FairCom with any questions.