createTable

JSON DB "createTable" action creates a new table in a FairCom database

Note In FairCom Edge and FairCom MQ, use the "createIntegrationTable" action to create an insert-read-only table that can transform data. 

All tables created by the JSON DB API are compatible with all other FairCom APIs, but tables created by other APIs are not automatically compatible with all FairCom APIs.

 

Request examples

Minimal

{
  "action": "createTable",
  "params": {
    "tableName": "test1",
    "fields": [
      {
        "name": "name",
        "type": "varchar",
        "length": 50
      }
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Maximal

{
  "api": "db",
  "action": "createTable",
  "params": {
    "databaseName": "ctreeSQL",
    "ownerName": "admin",
    "tableName": "all_types",
    "changeIdField": "signed_int64",
    "primaryKeyFields": [
      "lastname",
      "firstname"
    ],
    "fields": [
      {
        "name": "firstname",
        "type": "varchar"
      },
      {
        "name": "lastname",
        "type": "varchar"
      },
      {
        "name": "nested_json_object_or_array",
        "type": "json",
        "length": 65500
      },
      {
        "name": "boolean_byte",
        "type": "bit"
      },
      {
        "name": "signed_int8",
        "type": "tinyint"
      },
      {
        "name": "signed_int16",
        "type": "smallint"
      },
      {
        "name": "signed_int32",
        "type": "integer",
        "autoValue": "incrementOnInsert"
      },
      {
        "name": "signed_int64",
        "type": "bigint",
        "autoValue": "changeId"
      },
      {
        "name": "iee_base2float32",
        "type": "real"
      },
      {
        "name": "iee_base2float64",
        "type": "float"
      },
      {
        "name": "signed32digits_base10_left32right0",
        "type": "number",
        "length": 32,
        "scale": 0
      },
      {
        "name": "signed32digits_base10_left0right32",
        "type": "number",
        "length": 32,
        "scale": 32
      },
      {
        "name": "signed32digits_base10_left20right12",
        "type": "number",
        "length": 32,
        "scale": 12
      },
      {
        "name": "signed32digits_base10_left30right2",
        "type": "money",
        "length": 32,
        "scale": 2
      },
      {
        "name": "signed32digits_base10_left28right4",
        "type": "money",
        "length": 32,
        "scale": 4
      },
      {
        "name": "date_yyyymmdd",
        "type": "date"
      },
      {
        "name": "time_hhmmssfff",
        "type": "time"
      },
      {
        "name": "datetime_yyyymmddthhmmssfff",
        "type": "timestamp",
        "autoValue": "timestampOnInsert"
      },
      {
        "name": "fixed_string_10bytes",
        "type": "char",
        "length": 10
      },
      {
        "name": "variable_string_up_to_max65500bytes",
        "type": "varchar",
        "length": 65500
      },
      {
        "name": "variable_string_up_to_2GB",
        "type": "lvarchar"
      },
      {
        "name": "fixed_binary_10bytes",
        "type": "binary",
        "length": 10
      },
      {
        "name": "variable_binary_up_to_max65500bytes",
        "type": "varbinary",
        "length": 65500
      },
      {
        "name": "variable_binary_up_to_2GB",
        "type": "lvarbinary"
      }
    ],
    "transactionModel": "logTransactions",
    "growthExtent": 0,
    "folder": "./ctreeSQL.dbs",
    "smallFile": false,
    "createRecByteIndex": false,
    "tableFileExtension": ".dat",
    "indexFileExtension": ".idx"
  },
  "responseOptions": {
    "dataFormat": "objects"
  },
  "apiVersion": "1.0",
  "requestId": "3",
  "debug": "max",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Create an athlete table

{
  "api": "db",
  "action": "createTable",
  "apiVersion": "1.0",
  "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": "replaceWithAuthTokenFromCreateSession"
}
 
 

Create testPrimaryKey table with two primary key fields

{
  "api": "db",
  "action": "createTable",
  "params": {
    "tableName": "testPrimaryKey",
    "primaryKeyFields": [ "lastname", "firstname" ],
    "fields": [
      {
        "name": "firstname",
        "type": "varchar",
        "length": 50
      },
      {
        "name": "lastname",
        "type": "varchar",
        "length": 50
      },
      {
        "name": "description",
        "type": "varchar",
        "length": 100
      }
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

 

Response examples

Success

{
    "result": {
      "dataFormat": "objects",
      "data": [
        {
          "changeIdField": "changeId",
          "createRecByteIndex": false,
          "databaseName": "ctreeSQL",
          "fieldDelimiterValue": 0,
          "fields": [
            {
              "autoValue": "incrementOnInsert",
              "defaultValue": null,
              "length": null,
              "name": "id",
              "nullable": false,
              "primaryKey": 1,
              "scale": null,
              "type": "bigint"
            },
            {
              "autoValue": "changeId",
              "defaultValue": null,
              "length": null,
              "name": "changeId",
              "nullable": true,
              "primaryKey": 0,
              "scale": null,
              "type": "bigint"
            },
            {
              "autoValue": "none",
              "defaultValue": null,
              "length": 50,
              "name": "name",
              "nullable": true,
              "primaryKey": 0,
              "scale": null,
              "type": "varchar"
            }
          ],
          "folder": ".\\ctreeSQL.dbs",
          "growthExtent": 0,
          "indexFileExtension": ".idx",
          "indexes": [
            {
              "collectStats": false,
              "compression": "off",
              "conditionalExpression": null,
              "databaseName": "ctreeSQL",
              "deferIndexing": false,
              "fields": [
                {
                  "caseInsensitive": false,
                  "name": "id",
                  "reverseCompare": false,
                  "sortDescending": false
                }
              ],
              "filename": "admin_test1.idx",
              "immutableKeys": false,
              "indexName": "id_pk",
              "indexNumber": 0,
              "ownerName": "admin",
              "tableName": "test1",
              "unique": true
            }
          ],
          "ownerName": "admin",
          "padValue": 0,
          "path": ".\\ctreeSQL.dbs",
          "primaryKeyFields": [
            "id"
          ],
          "smallFile": false,
          "tableFileExtension": ".dat",
          "tableName": "test1",
          "totalRecordCount": 0,
          "transactionModel": "logTransactions",
          "uid": 1177
        }
      ]
    },
    "errorCode": 0,
    "errorMessage": "",
    "authToken": "replaceWithAuthTokenFromCreateSession"
  }
 
 

Maximal

{
  "result": {
    "dataFormat": "objects",
    "data": [
      {
        "changeIdField": "changeId",
        "createRecByteIndex": false,
        "databaseName": "ctreeSQL",
        "fieldDelimiterValue": 0,
        "primaryIndexName": "id_pk",
        "fields": [
          {
            "autoValue": "incrementOnInsert",
            "defaultValue": null,
            "length": null,
            "name": "id",
            "nullable": false,
            "primaryKey": 1,
            "scale": null,
            "type": "bigint"
          },
          {
            "autoValue": "changeId",
            "defaultValue": null,
            "length": null,
            "name": "changeId",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "bigint"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 50,
            "name": "name",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 16,
            "name": "status",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          }
        ],
        "folder": "./ctreeSQL.dbs",
        "growthExtent": 0,
        "indexFileExtension": ".idx",
        "indexes": [
          {
            "indexName": "id_pk",
            "primaryIndex": "true", 
            "collectStats": false,
            "compression": "off",
            "conditionalExpression": null,
            "databaseName": "ctreeSQL",
            "deferIndexing": false,
            "fields": [
              {
                "caseInsensitive": false,
                "name": "id",
                "reverseCompare": false,
                "sortDescending": false
              }
            ],
            "filename": "admin_all_types.idx",
            "immutableKeys": false,
            "indexName": "id_pk",
            "indexNumber": 0,
            "ownerName": "admin",
            "tableName": "all_types",
            "unique": true
          }
        ],
        "ownerName": "admin",
        "padValue": 0,
        "path": "./ctreeSQL.dbs",
        "primaryKeyFields": [
          "id"
        ],
        "smallFile": false,
        "tableFileExtension": ".dat",
        "tableName": "all_types",
        "totalRecordCount": 0,
        "transactionModel": "logTransactions",
        "uid": 1193
      }
    ]
  },
  "requestId": "3",
  "debugInfo": {
    "request": {
      "api": "db",
      "action": "createTable",
      "params": {
        "databaseName": "ctreeSQL",
        "ownerName": "admin",
        "tableName": "all_types",
        "fields": [
          {
            "name": "name",
            "type": "varchar",
            "length": 50
          }
        ],
        "transactionModel": "logTransactions",
        "growthExtent": 0,
        "folder": "./ctreeSQL.dbs",
        "smallFile": false,
        "createRecByteIndex": false,
        "tableFileExtension": ".dat",
        "indexFileExtension": ".idx"
      },
      "apiVersion": "1.0",
      "requestId": "3",
      "responseOptions": {
        "dataFormat": "objects"
      },
      "debug": "max",
      "authToken": "replaceWithAuthTokenFromCreateSession"
    },
    "serverSuppliedValues": {
      "databaseName": "ctreeSQL",
      "ownerName": "admin"
    },
    "errorData": {
      "errorData": null
    },
    "warnings": []
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Create "athlete" table success

{
  "result": {
    "dataFormat": "objects",
    "data": [
      {
        "changeIdField": "changeId",
        "createRecByteIndex": false,
        "databaseName": "ctreeSQL",
        "fieldDelimiterValue": 0,
        "fields": [
          {
            "autoValue": "incrementOnInsert",
            "defaultValue": null,
            "length": null,
            "name": "id",
            "nullable": false,
            "primaryKey": 1,
            "scale": null,
            "type": "bigint"
          },
          {
            "autoValue": "changeId",
            "defaultValue": null,
            "length": null,
            "name": "changeId",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "bigint"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 30,
            "name": "name",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": null,
            "name": "ranking",
            "nullable": false,
            "primaryKey": 0,
            "scale": null,
            "type": "smallint"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": null,
            "name": "birthDate",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "date"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 32,
            "name": "playerNumber",
            "nullable": true,
            "primaryKey": 0,
            "scale": 6,
            "type": "number"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": null,
            "name": "livedPast2000",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "bit"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 32,
            "name": "earnings",
            "nullable": true,
            "primaryKey": 0,
            "scale": 4,
            "type": "money"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 500,
            "name": "favoriteSaying",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          }
        ],
        "folder": ".\\ctreeSQL.dbs",
        "growthExtent": 0,
        "indexFileExtension": ".idx",
        "indexes": [
          {
            "collectStats": false,
            "compression": "off",
            "conditionalExpression": null,
            "databaseName": "ctreeSQL",
            "deferIndexing": false,
            "fields": [
              {
                "caseInsensitive": false,
                "name": "id",
                "reverseCompare": false,
                "sortDescending": false
              }
            ],
            "filename": "admin_athlete.idx",
            "immutableKeys": false,
            "indexName": "id_pk",
            "indexNumber": 0,
            "ownerName": "admin",
            "tableName": "athlete",
            "unique": true
          }
        ],
        "ownerName": "admin",
        "padValue": 0,
        "path": ".\\ctreeSQL.dbs",
        "primaryKeyFields": [
          "id"
        ],
        "smallFile": false,
        "tableFileExtension": ".dat",
        "tableName": "athlete",
        "totalRecordCount": 0,
        "transactionModel": "logTransactions",
        "uid": 1169
      }
    ]
  },
  "requestId": "2",
  "debugInfo": {
    "request": {
      "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
          }
        ]
      },
      "apiVersion": "1.0",
      "requestId": "2",
      "debug": "max",
      "authToken": "replaceWithAuthTokenFromCreateSession"
    },
    "serverSuppliedValues": {
      "databaseName": "ctreeSQL",
      "ownerName": "admin"
    },
    "errorData": {
      "errorData": null
    },
    "warnings": []
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Table already exists: failure

{
    "errorCode": 4022,
    "errorMessage": "Table 'test1' creation failed",
    "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

 

Other API examples

The following are examples of using other APIs to create tables compatible with FairCom APIs.

 

Use SQL to create tables compatible with JSON DB

If you use SQL to create a table, you must add a primary key and a "changeId" field to leverage all JSON DB features. JSON DB requires a primary key to update records, and a "changeId" field to do optimistic locking.

  • To create a primary key in SQL, create a unique, unfiltered index on one or more fields in a table. The fields in the index are the fields returned by the "primaryKeys" property in JSON DB.
  • To create a "changeId" in SQL, see the storage clause of the SQL documentation for CREATE TABLE.

 

Use CTDB to create tables compatible with JSON DB

When you use the CTDB API to create tables, you must do the following to be compatible with the JSON DB API.

  • Add a unique index with no filter conditions and without null key suppression. It is best to call ctdbSetIndexPrimaryFlag() at index creation to explicitly identify which index defines the primary key. If it is not the first index (in index definition order), the following criteria are used to identify which index identifies the primary key:
    • The index is unique.
    • The index has null key exclusion turned off (IIDX.inulkey == 0).
    • The index is not temporary (xflmod == ctTEMPFILE).
    • The index has no conditional expression.
  • Add a "changeId" field. See ctdbSetChangeIDField() for CTDB or addChangeIDfield for ISAM.

 

Use bookmarks to identify a record

When a table lacks a primary key, you can use a bookmark to uniquely identify a record for read, update, or delete operations. Still, it is less reliable than a primary key.

The potential issue with bookmarks arises from a specific, uncommon scenario:

A user bookmarks a record.

Later, that user or another user deletes the bookmarked record.

Subsequently, a record is inserted or another record is updated to a size smaller than the deleted record.

In this scenario, the server may reuse the space previously occupied by the deleted record for the newly inserted or updated record. If this happens, the original bookmark now refers to the new record. This is problematic because the server will not return an error when the user attempts to read, update, or delete the bookmarked record; instead, the operation will incorrectly target the newly inserted or updated record.

In contrast, if a record is deleted using its primary key, any subsequent attempt to read, update, or delete a record using that same primary key will reliably result in the server returning an error. For this reason, tables should always include a primary key, and you should use the primary key to identify records for reads, updates, and deletes.

If a table is created without a primary key but with the hidden rowid field and index, the bookmark uses its value instead of the record offset to identify records. A bookmark using the hidden rowid field is as reliable as a primary key. Nonetheless, it is best to create a table with a primary key because it is visible. The JSON DB API follows this best practice. 

By default, the SQL engine creates the hidden rowid field and index. Instead, you should create the table with a primary key named "id" and use the following CREATE TABLE SQL clause to eliminate it: STORAGE_ATTRIBUTES 'hotAlter; huge; NOROWID_FLD;'.

JSON DB "createTable" example

{
  "api": "db",
  "action": "createTable",
  "params": {
    "tableName": "table_with_id_pk",
    "fields": [
      { "name": "my_field", "type": "varchar", "length": 50 }
    ]
  },
  "authToken": ""
}

SQL CREATE TABLE example

CREATE TABLE table_with_id_pk 
(
  id       BIGINT IDENTITY (1,1)
, changeid BIGINT DEFAULT autochangeid NOT NULL 
, my_field VARCHAR(50) 
, PRIMARY KEY(id))  
STORAGE_ATTRIBUTES 'hotAlter; huge; norowid_fld; noRecbyt_idx;'
;

 

Request properties ("params")

Property Description Default Type Limits (inclusive)

ownerName

The "ownerName" property specifies the account that owns an object, such as a table or code package. See "createSession" and the "defaultOwnerName" property for more details. 

You specify this property when you want to use a different account instead of the default. Your session's account must have the appropriate privileges to access the code package. 

This property is useful because objects, such as tables and code packages, can have the same name in the same database as long as different accounts own each object. This feature allows you to create duplicate objects for different users on the same server and reuse the same JSON actions on those objects. For example, an administrator can copy objects from a production environment to her account so she can troubleshoot an issue using the same JSON actions, JavaScript, and SQL code.

It is an error to set "ownerName" to the empty string "".

If no default owner is specified during "createSession", the server sets the "defaultOwnerName" to the "defaultOwnerName" value specified in the services.json file.

 Optional with default of the session's "defaultOwnerName" property string 1 to 64 bytes

tableName

The "tableName" property contains the unique, user-defined name of a table.

See table name in System specifications for the table naming requirements and limitations.

 

"params": {
  "tableName": "ctreeTable"
}
 Required - No default value string 1 to 64 bytes

Fields

The "fields" property specifies which fields in a table are included in the index and how each field should be indexed.

  • The "fields" array contains one object for each field in an index.
  • Each field has separate index settings such as:
    • Case sensitivity
    • Sort order
    • Comparison algorithm
  • For example, an index containing two fields can use different settings for each field. It can index the first field as case sensitive, ascending, and forward comparison, and the second field as case insensitive, descending, and reverse comparison.
  • When a string field is indexed as case insensitive, the server ignores the case of the ASCII letters in the field value; otherwise, it does an exact comparison.
  • When a field is indexed for reverse comparison, the server starts comparisons with the last byte in the field value rather than the first. This speeds up the comparison process when the last bytes of a field value are most likely to be dissimilar.
  • When a field is sorted ascending, queries that use the index return results in ascending index order for that field; otherwise, they return results in decreasing order.
  • When you configure multiple fields for sorting in an index, queries return results in the order the fields are listed — for example, if an index has birthday and name fields specified in the fields array in that order, with the birthday sorted descending and the name sorted ascending, then queries return results with the most recent birthdays first and multiple names on the same birthday are returned in ascending alphabetical order.
"fields":
[
  {
    "name": "name",
    "caseInsensitive": true,
    "sortDescending": true,
    "reverseCompare": false
  }
]

Required - No default value

 array of objects

"caseInsensitive"

"name"

"reverseCompare"

"sortDescending"

"autotimestamp"

"autoValue"

"defaultValue"

"length"

"name"

"nullable"

"primaryKey"

"scale"

"type"

fields

.name

The "name" property is the name of a label or field. 

The "group" and "name" properties combined uniquely identify each label. "alterLabel" returns an error if the "group" and "name" properties do not create a unique label name.

The "id" property also uniquely identifies each label, so you can rename a label's group and name without breaking "id" references to the label.

 

 Required - No default value string 1 to 64 bytes

fields

.type

The "type" property specifies the field's data type. See Data types for the limits, valid values, and whether "length" and "scale" are required.

 

Request example

"fields": [
  {
    "name": "j",
    "type": "number"
  }
]
 Required - No default value string

"json"

"bit"

"tinyint"

"smallint"

"integer"

"bigint"

"real"

"float"

"number"

"money"

"date"

"time"

"timestamp"

"char"

"varchar"

"lvarchar"

"binary"

"varbinary"

"lvarbinary"

fields

.length

 The "length" property specifies the length of a table field. For more details, see “length”. Optional with default of null integer 1 to 65500

fields

.scale

The "scale" property specifies the number of fixed decimal places to the right of the decimal point. Its value must always be less than or equal to the field's length. It is required only for the "number" and "money" data types because they require fixed precision. It is ignored for all other data types. See also Data types. The scale 

The value of "scale" must be an integer from 0 to the number of digits specified by the "length" property. 

A scale of 0 creates an integer number. A scale equal to the length creates a number that can only have a fractional value.

The "money" field type must have a scale of 2 or 4. The default is 4.

You may optionally use the "length" property to specify fewer than 32 total digits to limit the total number of digits available to the number. A length limit reduces the maximum size of the scale. For example, a length of 3 allows the scale of a "number" to be 0, 1, 2, or 3. 

Example numbers allowed in "number" and "money" field types with a length of 4 and a scale from 0 to 4.

 

Request Example

Create a table that contains all field types that use the "scale" property.

"fields": [
  {
    "name": "j",
    "type": "number",
    "scale": 32
  },
  {
    "name": "k",
    "type": "number",
    "scale": 4
  }


],
 Optional with default of null integer 0 to 32

fields

.autoValue

 The "autoValue" property indicates when and how the server automatically sets the field value. For more details, see “autoValue”.  Optional with default of "none" string

"none"

"incrementOnInsert"

"timestampOnInsert"

"timestampOnUpdate"

"timestampOnUpdate AndInsert"

"changeid"

transactionModel

The "transactionModel" property defines how the server processes transactions for a table. It is case-insensitive. 

Possible values:
  • "logTransactions"
    • This persists data to data files and writes transaction changes to transaction logs. It supports commit and rollback transactions and data replication. It will not lose data during an unplanned outage. It provides the most durability and the most capabilities, but is slower than the other settings.
  • "ramTransactions"
    • This supports commit and rollback transactions in RAM while persisting data to data files. It does not use a transaction log, which makes it even faster, but an unplanned outage may lose or corrupt unwritten data.
  • "noTransactions"
    • This does not support transactions but still persists data to disk, making it even faster. However, an unplanned outage may lose or corrupt unwritten data.

 

Example request

{
  "api": "db",
  "action": "createTable",
  "params": {
    "transactionModel": "noTransactions"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 Optional with default of "logTransactions" string

"logTransactions"

"ramTransactions"

"noTransactions"

growthExtent

The "growthExtent" property specifies the number of bytes that a server uses to increase a file's size. 

  • A file is extended when adding or updating a record, which requires the file to grow larger.
  • Use a larger number to minimize the number of times a file needs to be extended.
  • Use a smaller number to minimize the amount of unused space in a file.
{
  "api": "db",
  "action": "createTable",
  "params": {
    "growthExtent": 64000000
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 Optional with default of 0 integer 0 to 2147483647

folder

The "folder" property defines the file system folder where an item will be stored. 

Important If it is a zero-length string, the server chooses its own folder; otherwise, it uses this folder.

 

Request example

{
  "api": "db",
  "action": "createTable",
  "params": {
    "tableName": "test_1",
    "folder": "Test_Folder"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 Optional with default of "" string 0 to 2,048 bytes

smallFile

The "smallFile" causes a table to be optimized for data files that cannot grow larger than 4 GB when set to true.

Note Small data files are faster and more efficient than huge files. They consume less disk space and less memory.

 

Example request

{
  "api": "db",
  "action": "createTable",
  "params": {
    "smallFile": true
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 Optional with default of false Boolean

true

false

createRecByteIndex

The "createRecByteIndex" property creates a special index for quickly walking variable-length records backward when set to true. 

Note It is not needed for fixed-length records.

"params": {
  "createRecByteIndex": true
  }
 Optional with default of false Boolean

true

false

tableFileExtension

The "tableFileExtension" property specifies the file system extension to use for a table's data files. 

Note If set to a zero-length string, then newly created data files will have no extension.

 

Example request

"params": {
  "tableName": "test1",
  "tableFileExtension": ".dat"
}
 Optional with default of ".dat" string 0 to 64 bytes

indexFileExtension

The "indexFileExtension" property specifies the file system extension to use for a table's index files. If omitted, it defaults to ".idx".

Note If set to a zero-length string, then newly created index files will have no extension.

"params": {
  "indexFileExtension": ".tidx"
}
 Optional with default of ".idx" string 0 to 64 bytes

 

Response properties ("result")

Property Description Type Limits (inclusive)

dataFormat

The "dataFormat" property (case-insensitive) defines the format of the "data" property. The default for "dataFormat" can be changed during a "createSession" action by assigning a different value to the "dataFormat" property in "defaultResponseOptions".

  • "dataFormat" in the response shows the client how the server formatted the "data" property.
    • Possible values include:
      • "arrays"
        • This is the default and causes the server to return results as an array of arrays, which is the most efficient.
      • "objects"
        • This returns results as an array of objects. This is less efficient but is simpler to generate, read, and troubleshoot.
 string

"autoDetect"

"arrays"

"objects"

data

The "data" property contains a response message. Its contents are defined by the action. It is an empty array when no results are available. The following is an example of the data property from a code package action.

  "result": {
    "data": [
      {
        "codeId": 6,
        "databaseName": "faircom",
        "ownerName": "admin",
        "codeName": "convertAndCategorizeTemperature",
        "codeVersion": 1,
        "clonedCodeId": 1,
        "codeStatus": "active",
        "codeLanguage": "javascript",
        "serviceName": "javascript",
        "codeType": "module",
        "description": "optional new description",
        "metadata": {},
        "createdBy": "ADMIN",
        "createdOn": "2025-08-25T21:48:38.109",
        "updatedBy": "ADMIN",
        "updatedOn": "2025-08-25T21:48:38.109",
        "comment": "Cloned from convertTemperature",
        "codeFormat": "utf8"
      },
    ]
 array of objects The action determines its contents.

data

.changeIdField

The "changeIdField" property specifies the name of the field used for the change-tracking functionality.

If the table already has a change-tracking field, this new field is now used instead for change-tracking.

 string 1 to 64 bytes

data

.createRecByteIndex

The "createRecByteIndex" property creates a special index for quickly walking variable-length records backward when set to true. 

Note It is not needed for fixed-length records.

 Boolean

true

false

data

.databaseName

The "databaseName" property specifies the database that contains the tables. 

Note In the API Explorer, "defaultDatabaseName" is set to "ctreeSQL" in the "createSession" action that happens at login.

  • If the "databaseName" property is omitted or set to null, the server will use the default database name specified at login.
  • If no default database is specified during "createSession", "databaseName" will be set to the "defaultDatabaseName" value that is specified in the services.json file.
  • This property's value is case insensitive. 
 string 1 to 64 bytes

data

.fieldDelimiterValue

The "fieldDelimiterValue" property should only be changed for backward compatibility with legacy c-tree files.

Note Do not set this value without first contacting FairCom customer support.

 integer 0 to 255

data

.fields

The "fields" property is an array of objects. Each object in the array defines a field by specifying its properties.

 

"fields": [
  {
    "autoValue": "none",
    "name": "name",
    "type": "varchar",
    "length": 50,
    "scale": null,
    "defaultValue": null,
    "nullable": false,
    "primaryKey": 0, 
    "autoValue": "none"
  }
]
 array

"autoTimestamp"

"autoValue"

"primaryKey"
"name"
"type"
"length"
"scale"
"defaultValue"
"nullable"

"primaryKey"

"autoValue"

 

updateRecords and deleteRecords only: 

“bookmark”

data

.fields

.autoValue

 The "autoValue" property indicates when and how the server automatically sets the field value. See autoValue for more details.  string

"none"

"incrementOnInsert"

"timestampOnInser"

"timestampOnUpdate"

"timestampOnUpdateAndInsert"

“changeid”

data

.fields

.defaultValue

 The "defaultValue" property specifies the field's default value. string 0 to 65,500 bytes

data

.fields

.length

 Identifies the length of the field. integer 1 to 65500 

data

.fields

.name

The "name" property is the name of a label or field. 

The "group" and "name" properties combined uniquely identify each label. 

The "id" property also uniquely identifies each label so you can rename a label's group and name without breaking "id" references to the label.

 

 string 1 to 64 bytes

data

.fields

.nullable

 “nullable” identifies whether a field can contain a NULL value. Boolean

true

false

data

.fields

.primaryKey

 When > 0, the "primaryKey" property identifies the ordinal position of the field within the table's primary key. integer 0 to 32

data

.indexes

.fields

.caseInsensitive

The "caseInsensitive" property determines if case comparisons for index key values accounts for case. When false, the server stores index key values in mixed case for comparisons.

When true, case comparisons are case-insensitive, and the server stores index key values in upper case for comparisons.

"fields": [
  {
    "caseInsensitive": true
  }
]
 Boolean

true

false

data

.indexes

.fields

.reverseCompare

The "reverseCompare" property specifies whether the bytes in an index key field are compared starting from the beginning to the end of the key.

When true, bytes in an index key field are compared starting from the end to the beginning of the key. This speeds comparisons when the unique parts of the bytes are at the end of keys.

"fields": [
  {
    "reverseCompare": true
  }
]
 Boolean

true

false

data

.indexes

.fields

.sortDescending

The "sortDescending" property sorts the returned sessions in descending order based on the last time the sessions connected or disconnected, the sessions' "clientName" properties, or the sessions' IP addresses.

"fields": [
  {
    "sortDescending": true
  }
]
 Optional with default of false Boolean

data

.fields

.scale

 If the type is "number" or "money", the "scale" property identifies the number of places to the right of the decimal point,. integer 0 to 32

data

.fields

.type

 Identifies the type of the field. See Data types. string

"bit"

"tinyint"

"smallint"

"integer"

"bigint"

"real"

"float"

"number"

"money"

"date"

"time"

"timestamp"

"char"

"varchar"

"lvarchar"

"binary"

"varbinary"

"lvarbinary"

"json"

data

.folder

The "folder" property defines the file system folder where an item will be stored. 

Important If it is a zero-length string, the server chooses its own folder; otherwise, it uses this folder.

 string 0 to 2,048 bytes

data

.growthExtent

The "growthExtent" property specifies the number of bytes that a server uses to increase a file's size. 

  • A file is extended when adding or updating a record, which requires the file to grow larger.
  • Use a larger number to minimize the number of times a file needs to be extended.
  • Use a smaller number to minimize the amount of unused space in a file.
 integer 0 to 2147483647

data

.indexFileExtension

The "indexFileExtension" property specifies the file system extension to use for a table's index files. If omitted, it defaults to ".idx".

Note If set to a zero-length string, then newly created index files will have no extension.

 string 0 to 64 bytes

data

.indexes

.collectstats

 The “collectStats” property identifies whether usage statistics are being collected and stored. boolean

true

false

data

.indexes

.compression

 The “compression” property identifies whether the index is compressed. string

"on"

"off"

"auto"

data

.indexes

.conditionalExpression

 The “conditionalExpression” property identifies an optional conditional expression that filters which records are included in the index. It is null when there is no conditional expression. string

null

or a string containing a conditional expression.

data

.indexes

.databaseName

The "databaseName" property specifies the database that contains an object, such as a table or code package. If it is set to null or is omitted, it defaults to the default database of the JSON Action session (see "createSession" and the "defaultDatabaseName" property). 

You specify this property when you want to use a different database instead of the default. 

This property is useful because objects, such as tables and code packages, can have the same name in multiple databases. This feature allows you to create multiple environments in the same server and reuse the same JSON actions in each environment. For example, you can create "dev", "test", "stage", and "prod" databases on the same server and use the "defaultDatabaseName" or "databaseName" properties to specify the desired environment.

It is an error to set "databaseName" to the empty string "".

If no default database is specified during "createSession", the server sets the "defaultDatabaseName" to the "defaultDatabaseName" value specified in the services.json file.

 string 1 to 64 bytes

data

.indexes

.deferIndexing

 “deferindexing” identifies whether deferred indexing is enabled. A deferred index builds and updates asynchronously. This speeds up inserts, updates, and deletes, with a slight delay due to the changes being included in the index. boolean

true

false

data

.indexes

.filename

The "filename" property contains the name of the index file on the file system. 

  • When creating a file, specify a non-zero-length string to assign the file to a specific location in the file system.
  • The file name may include an absolute or relative path.
  • If the filename is omitted or is a zero-length string, the server defines its own path and name for the file.
  • If "filename" is not specified, the index will be added to the existing index file.
  • The server adds the "indexFileExtension" property to the end of the filename.
 string 0 to 2048 bytes

data

.indexes

.immutableKeys

 "immutableKeys" indicates whether a key's value can be changed. boolean

true

false

data

.indexes

.indexName

 The "indexName" property specifies the name of an index. A zero-length "indexName" is invalid. string 1 to 64 bytes

data

.indexes

.ownerName

The "ownerName" property specifies the account that owns an object, such as a table or code package. See "createSession" and the "defaultOwnerName" property for more details. 

You specify this property when you want to use a different account instead of the default. Your session's account must have the appropriate privileges to access the code package. 

This property is useful because objects, such as tables and code packages, can have the same name in the same database as long as different accounts own each object. This feature allows you to create duplicate objects for different users on the same server and reuse the same JSON actions on those objects. For example, an administrator can copy objects from a production environment to her account so she can troubleshoot an issue using the same JSON actions, JavaScript, and SQL code.

It is an error to set "ownerName" to the empty string "".

If no default owner is specified during "createSession", the server sets the "defaultOwnerName" to the "defaultOwnerName" value specified in the services.json file.

 string 1 to 64 bytes

data

.indexes

.tableName

The "tableName" property is a string containing the name of a table.

See table name in System specifications for the table naming requirements and limitations.

 string 1 to 64 bytes

data

.indexes

.unique

 The "unique" property causes the "createIndex" action to create a unique index, which requires the columns in the index to have a unique value for each record in the table, when set to true.  Boolean

true

false

data

.ownerName

 The "ownerName" property identifies the user who owns an object (see Object owner).  string 0 to 64 bytes

data

.padValue

The "padValue" property is used by the server as a byte value to pad all "char" and "binary" fields in a table when the contents of these fixed-length fields is shorter than the field size.

All fixed-length fields in a table are padded with the same pad value. For more details, see "padValue".

 integer 0 to 255

data

.path

 The "path" property identifies the path of the database folder. For more details, see "path". string 0 to 2,048 bytes

data

.primaryKeyFields

This property specifies the fields of the table’s primary key when multiple fields are combined to form the primary key.

Note The best practice is not to use the "primaryKeyFields" or "primaryKey" properties, so the "createTable" action will automatically create a primary key field named "id" with a unique index named "id_pk".

The order of fields in this property is the order of fields in the primary key index. The "fields" property contains the name and type of each field that is specified in the "primaryKeyFields" array. 

A primary key with multiple fields has an index named "pk". If you specify just one field, the index is named "<fieldname>_pk".

If only one field is used as the primary key, the "primaryKey" property defines the primary key.

Note The "primaryKeyFields" and "primaryKey" properties cannot be used together.

 

Example

"primaryKeyFields": [
  "a",
  "b",
  "c"
],
"fields": [
  {
    "name": "a",
    "type": "tinyint"
  },
  {
    "name": "b",
    "type": "smallint"
  },
  {
    "name": "c",
    "type": "integer"
  }
]
 array an array

data

.smallFile

The "smallFile" causes a table to be optimized for data files that cannot grow larger than 4 GB when set to true.

Note Small data files are faster and more efficient than huge files. They consume less disk space and less memory.

 Boolean

true

false

data

.tableFileExtension

The "tableFileExtension" property specifies the file system extension to use for a table's data files. 

Note If set to a zero-length string, then newly created data files will have no extension.

 string 0 to 64 bytes

data

.tableName

The "tableName" property is a string containing the name of a table.

See table name in System specifications for the table naming requirements and limitations.

 string 1 to 64 bytes

data

.totalRecordCount

The "totalRecordCount" property contains the total available number of records that can be returned from a query.

  • The "totalRecordCount" is set to -1, when the server does not know the total record count.
  • A very fast way to get the total number of records in a table is to call the "getRecordsByTable" method without applying a "tableFilter". This immediately returns the count without reading and counting records.
  • For most methods, the server does not calculate "totalRecordCount" because calculating it requires walking all records in the query, which may take a significant amount of time.
  • When the result is returned as a cursor, "totalRecordCount" is the total number of records that the cursor can traverse.
    • This does not apply to cursor responses.
  • When the result returns records directly, "totalRecordCount" is the total number of records that can be retrieved – not necessarily the number of records returned.
 integer

-1 to 99999999999999999999999999999999

data

.transactionModel

The "transactionModel" property defines how the server processes transactions for a table. It is case-insensitive. 

Possible values:
  • "logTransactions"
    • This persists data to data files and writes transaction changes to transaction logs. It supports commit and rollback transactions and data replication. It will not lose data during an unplanned outage. It provides the most durability and the most capabilities, but is slower than the other settings.
  • "ramTransactions"
    • This supports commit and rollback transactions in RAM while persisting data to data files. It does not use a transaction log, which makes it even faster, but an unplanned outage may lose or corrupt unwritten data.
  • "noTransactions"
    • This does not support transactions but still persists data to disk, making it even faster. However, an unplanned outage may lose or corrupt unwritten data
 string

"logTransactions"

"ramTransactions"

"noTransactions"

Was this article helpful?

Give feedback about this article

Related Articles