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
- Ensure the FairCom server is installed and running.
- Confirm server access by running FairCom's Control Center.
- Navigate to the Code tab.
- Select API Console from the Code menu.

How to use the web application to run a JSON Action
- Navigate to the Code tab.
- Select API Console from the Code menu.
- 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" } } - Click the Autofill button (
) if Autofill has not been turned on to set the "authToken","requestId", and"cursorId"to the current session's values. - Click the Run button (
) to run the action. - Verify the action completed successfully.
-
"errorCode"with a value of0indicates 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
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Paste this code into the API Console:
{ "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" }
- Click the Run button.
Create indexes on the athlete table
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Paste this code into the API Console and click the Run button to create the
"ranking"index on therankingfield:{ "api": "db", "action": "createIndex", "params": { "ownerName": "admin", "tableName": "athlete", "indexName": "ranking", "fields": [ { "name": "ranking" } ], "waitToBeLoaded": true }, "authToken": "replaceWithAuthTokenFromCreateSession" }
- Paste this code into the API Console and click the Run button to create the
"earnings"index on theearningsfield:{ "api": "db", "action": "createIndex", "params": { "tableName": "athlete", "indexName": "earnings", "fields": [ { "name": "earnings" } ], "waitToBeLoaded": true }, "authToken": "replaceWithAuthTokenFromCreateSession" }
- Paste this code into the API Console and click the Run button to create the
"name_livedpast2000"index on thenamefield:{ "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
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Paste this code into the API Console:
{ "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" }
- Click the Run button.
View records
View records using the web application
- Navigate to the Data tab.
- Select Tables from the Data menu.
- Click on the table name. For this tutorial, click the
"athlete"table - Observe the records.
View records using SQL statements
- Navigate to the Data tab
- Select SQL Commands from the Data menu.
- Paste this code into the SQL Script Request editor:
SELECT * FROM athlete;
- Click the Run button.
- Observe the Script results in the Execution Log
.
Get athlete records by ID
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Paste this code into the API Console and click the Run button to return two records from the
"athlete"table with"id"values of1and3:{ "api": "db", "action": "getRecordsByIds", "params": { "tableName": "athlete", "ids": [1,3] }, "authToken": "replaceWithAuthTokenFromCreateSession" }- The
"getRecordsById"action is the best way to retrieve records using primary and foreign keys.
Get athlete records in table order
- Navigate to the Code tab.
- Select API Console from the Code menu.
- 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.
{ "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": trueproperty to return a cursor, which allows you to paginate through the table quickly and efficiently, see getRecordsByTable.
Get athlete records by index
- Navigate to the Code tab.
- Select API Console from the Code menu.
- 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": trueto return records in the reverse order of the index.{ "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": trueproperty 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
"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.
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Paste this code into the API Console and click the Run button to return the first two records stored in the
"athlete"table:{ "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": trueproperty 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.
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Paste this code into the API Console and click the Run button:
{ "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.
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Paste this code into the API Console and click the Run button to get records with an
idvalue of4or greater:{ "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.
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Paste this code into the API Console and click the Run button to get records where the
idis greater than or equal to5:{ "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.
- Navigate to the Code tab.
- Select API Console from the Code menu.
- 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" } - Copy and save the
"cursorId"from theResponseto use in subsequent calls to"getRecordsFromCursor".
Get records from a cursor
- Paste the following code into the API Console. Note that you must have a valid
"cursorId"by calling another"getRecords…"action.{ "api": "db", "action": "getRecordsFromCursor", "params": { "cursorId": "replaceThisWithTheLastCreatedCursor", "fetchRecords": 1 }, "authToken": "replaceThisWithValidAuthToken" }
- Click the Run button.
- Continue to click the Run button to move through each record.
- 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".- 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.
- The API Console automatically recognizes the
Ways to move a cursor and retrieve records
The following lists contain techniques, examples, and capabilities of cursors.
"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".
- 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
-
"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.
-
"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.
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Paste this code into the API Console and click the Run button:
{ "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.
