Skip to main content

"createTable" (JSON Action)

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.

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.

Request examples

Minimal

{
  "api": "db",
  "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,
        "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": [
          {
            "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"
}

Use the createTable JSON API action to create a new table in a FairCom database

API actionsJSON DB APIJSON Actiontable actionscreate tablecreateTable

"params"

The "params" property is an object that contains an action's request parameters as defined by a set of properties. Each action defines its own required and optional properties. See System limits for a comprehensive overview of property requirements and limitations.

Table 1. createTable "params" properties summary

Property

Description

Default

Type

Limits (inclusive)

changeIdField

(optional) specifies the field name used for tracking changes.

"changeid"

string

1 to 64 bytes

createRecByteIndex

(optional) when true, a special index is created for quickly walking variable-length records backward.

false

Boolean

true
false

databaseName

(optional) specifies the name of a database.

Defaults to the "defaultDatabaseName" value that is set during "createSession". If no default is set during "createSession", then "faircom" is used.

string

1 to 64 bytes

fieldDelimiterValue

(optional) delimits fields internally for FairCom use.

0

integer

0 to 255

fields

contains field objects that define the characteristics of each field in a table.

Required - No default value

array

fields
.autotimestamp

(optional) specifies when the server will timestamp a record.

"none"

string

"none"
"onInsert"
"onUpdate"
"onUpdateAndInsert"
fields
.autoValue

(optional) controls when and how the server automatically sets the field value.

"none"

string

"none"
"incrementOnInsert"
"timestampOnInsert"
"timestampOnUpdate"
"timestampOnUpdateAndInsert"
"changeid"
fields
.defaultValue

(optional) identifies the default value of the field.

""

string

0 to 65,500 bytes

fields
.length

(conditional) specifies the length of a field's value in a record.

Optional for fields of type "number" and "money" and defaults to 32.

Optional for fields of type "json" and defaults to 2 GB.

Required for fields of type "char", "varchar", "binary", or "varbinary". You must specify a length.

integer

"number" and "money": 1 to 32 (Optionally specifies the field's maximum total number of numeric digits.)
"char" and "binary": 1 to 65500 (Specifies the fixed-length field's length in bytes.)
"varchar" and "varbinary": 1 to 65500 (Specifies the variable-length field's maximum number of bytes.)
"json": 1 to 65500
fields
.name

identifies the name of the field.

Required - No default value

string

1 to 64 bytes

fields
.nullable

(optional) identifies whether a field can contain a NULL value.

true

Boolean

true
false
fields
.primaryKey

(optional) when > 0, identifies the ordinal position of the field within the table's primary key.

null

integer

0 to 32

fields
.scale

(conditional)specifies the number of places to the right of the decimal point.

If the field's "type" is "number"or "money", you may specify a scale.

integer

"money": 2 digits to the right of the decimal point allows 30 digits to the left.
"money": 4 digits to the right of the decimal point allows 28 digits to the left.
"number":0 to 32 digits. It must be less than or equal to "length" which defaults to 32.
fields
.type

specifies the field's data type and whether it needs length and scale.

Required - No default value

string

"bit"
"tinyint"
"smallint"
"integer"
"bigint"
"real"
"float"
"number"
"money"
"date"
"time"
"timestamp"
"char"
"varchar"
"lvarchar"
"binary"
"varbinary"
"lvarbinary"
"json"

folder

(optional) specifies the file system folder where an item will be stored.

""

string

0 to 2,048 bytes

growthExtent

(optional) specifies the number of bytes that a server uses to extend a file.

0

integer

0 to 2147483647

indexFileExtension

(optional) specifies the file system extension to use for a table's index files. (Must contain the dot before the extension.)

".idx"

string

0 to 64 bytes

ownerName

(optional) specifies the unique name of a schema in a database.

""

string

0 to 64 bytes

padValue

(optional) pads all "char" and "binary" fields in a table.

0

integer

0 to 255

primaryKeyFields

(optional) specifies the fields to use for the table’s primary key.

null

array of strings

["field1", …,"fieldN"]

smallFile

(optional) when true, optimizes a table for data files that cannot grow larger than 4 GB.

false

Boolean

true
false

tableFileExtension

(optional) specifies the file extension that the server assigns to the table's data file on the file system. (Must contain the dot before the extension.)

".dat"

string

0 to 64 bytes

tableName

specifies the name of a table.

Required - No default value

string

1 to 64 bytes

transactionId

(optional) identifies a transaction to include this action.

" "

string

0 to 255 bytes

transactionModel

(optional) specifies how the server processes transactions for a table.

"logTransactions"

string

"logTransactions"
"ramTransactions"
"noTransactions"


"autoTimeStamp"

This optional property works only with fields that have a type of "timestamp". The default value is "none", which prevents the server from populating the value of the field automatically.

The value is an official timestamp of the date and time when the server inserted or updated the associated field. The timezone is always UTC time (ZULU time). Once set, the value cannot be changed.

When the value is "onInsert", the server automatically populates the value of the field when the associated field is inserted.

When the value is "onUpdate", the server automatically populates the value of the field each time the associated field is updated.

When the value is "onUpdateOrInsert", the server automatically populates the value of the field each time the associated field is inserted or updated.

"fields": [
  {
    "name": "name",
    "type": "timestamp",
    "autoTimeStamp": "onupdate"
  }
]

"autoValue"

This optional property indicates when and how the server automatically sets the field value.

Only one of these values is allowed per field.

  • "none" indicates that the server does not automatically set the field's value.

  • "incrementOnInsert" indicates that the server automatically increments a field’s value each time the server inserts a new record. It applies to fields that are of the type of decimal or one of the integer types, such as "bigint". Only one field per table can have this attribute. The server returns an error when assigning this attribute to multiple fields. The JSON DB API automatically creates the "id" field as an "incrementOnInsert" field. If you apply this attribute to another field, it becomes the only automatically incremented field in the table. If you want that field to be the primary key, assign  "primaryKey": 1 to it.

  • "timestampOnInsert" indicates that the server automatically sets a field’s value to the current date and time of an insert. It applies only to fields with a type of "timestamp".

    Note

    If you attempt to insert a record and specify a timestamp for a field that has "autoValue" set to "timestampOnInsert", the timestamp you specified is ignored, and the current date and time are assigned to the field.

  • "timestampOnUpdate" indicates that the server automatically sets a field’s value to the current date and time of an update. This setting applies only to timestamp fields.

  • "timestampOnUpdateAndInsert" indicates that the server automatically sets a field’s value to the current date and time of an insert and an update. It applies only to fields with a type of "timestamp".

  • "changeId" indicates the server uses the field for optimistic locking. The server automatically sets the field's value to the internal transaction number used during the last update of the record. This value changes each time the server updates the record. A table may only have one change tracking field. The field type must be "bigint".

    The JSON DB API automatically creates a "changeid" field with change-tracking functionality.

    Change tracking is optional in the CTDB and ISAM APIs. The application must create a 64-bit integer field and assign change-tracking functionality to it.

Request Example

"fields": [
  {
    "name": "signed_int32",
    "type": "integer",
    "autoValue": "incrementOnInsert"
  }
]

Response Example

{
  "result": {
    "dataFormat": "objects",
    "data": [
      {
        "changeIdField": "changeId",
        "createRecByteIndex": false,
        "databaseName": "ctreeSQL",
        "fieldDelimiterValue": 0,
        "fields": [
          {
            "autoValue": "incrementOnInsert",
            "defaultValue": null,
            "length": null,
            "name": "signed_int32",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "integer"
          }
        ]
      }
    ]
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

"changeIdField"

This property's value designates the name of the field used for change-tracking functionality if you are not using the "changeId" field for change tracking.

"createTable" automatically creates the "changeId" field to hold the change tracking number used for optimistic locking. Using the "changeId" field for optimistic locking is a best practice.

However, if you use the name "changeId" for another purpose, you can use the "changeIdField" property to designate a different field as the change tracking number field.

Request example

"params": {
  "changeIdField": "changetrackingid"
  }

"createRecByteIndex"

The "createRecByteIndex" property is an optional Boolean that creates a special index for quickly walking variable-length records backward when set to true. It defaults to false.

Note

It is not needed for fixed-length records.

"params": {
  "createRecByteIndex": true
  }

"databaseName"

The "databaseName" property is an optional string that specifies the database that contains the tables. It defaults to the database name supplied at login.

Note

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

  • A zero-length "databaseName" is invalid.

  • Its length limit is from 0 to 64 bytes.

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

"params": {
  "databaseName": "mainDatabase"
  }

"debug"

The "debug" property is an optional, case-insensitive string enum that causes the server to return debug information. The default value is "none" which is equivalent to omitting this property or similar to setting it to null.

  • Possible values:

    • "none"

    • "max"

  • Setting the "debug" value to anything other than "none" causes the server to pretty-print the JSON that it generates in the response, making it easier to troubleshoot.

  • Setting to "max" causes the server to return a response that includes the maximum amount of debug information in the response, making it useful for troubleshooting live production data with minimal impact on server resources.

  • Setting to "none" causes no debug information to be returned, which is useful for production environments that are running well.

"defaultValue"

This optional property specifies the default value of a field. It defaults to "" if no value is specified. You can specify any JSON value within the documented limits of the property.

Example

"fields": [
  {
    "name": "databaseName",
    "type": "varchar",
    "defaultValue": null
  }
]

"fields"

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

Example

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

"fieldDelimiterValue"

The "fieldDelimiterValue" property is a decimal number from 0 to 255. It is optional. If omitted, it defaults to 0. The only reason to change it is for backward compatibility with legacy c-tree files.

Note

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

"params": {
  "fieldDelimiterValue": 255
}

"folder"

The "folder" property is an optional string from 0 to 2048 bytes. It defines the file system folder where an item will be stored. It defaults to an empty string.

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

"growthExtent"

The "growthExtent" property is an optional integer from 0 to 2,147,483,647. It is the number of bytes that a server uses to increase a file's size. The default value is 0.

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

"indexFileExtension"

The "indexFileExtension" property is an optional string from 0 to 64 bytes. It 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"
}

"length"

This property is an integer that specifies the length of a table field. See also Data types.

It is required to set the length of the following fixed-length data types:

  • "char" (between 1 and 65,500 bytes)

  • "binary" (between 1 and 65,500 bytes).

It is required to set the maximum length for the following variable-length data types:

  • "varchar" (between 1 and 65,500 bytes).

  • "varbinary" (between 1 and 65,500 bytes).

It is optional to set the maximum length of the "json" data type, which defaults to 2 gigabytes. You may set its maximum length between 1 and 65,500 bytes.

It is optional to set the maximum length of the "number" and "money" data types, which default to 32 numeric digits. You can change the "length" to limit the precision of the number of digits to the left of the decimal point.

  • "number" and "money" are always stored as 32 decimal digits. Using a length less than 32 does not benefit storage.

  • You may optionally use "length" to specify fewer than 32 total digits to limit the maximum number of digits in the field. For example, a length of 4 allows numbers such as 12, 123, 1234, 12.34, and 0.1234, but not 12345, 123.45, or 0.12345.

  • You must always use "scale" to set the number of decimal places to the right of the decimal point, which must be less than or equal to the length. For example, "money" with a scale of 2, defaults to 30 digits to the left of the decimal point and 2 digits to the right, and "money" with a scale of 4, defaults to 28 digits to the left of the decimal point and 4 digits to the right.

 The "length" property is ignored for other data types because they have predefined lengths. For example, "lvarchar" and "lvarbinary" always have a maximum length of 2GB.

Note

"nchar" and "nvarchar" are only supported in FairCom's special UCS-2 server edition. These field types allocate two bytes to each character. Because UCS-2 is inefficient, FairCom recommends its standard database, which supports modern, efficient, variable-length UTF-8 characters.

  • "nchar" can be between 1 and 65,500 bytes.

  • "nvarchar" can be between 1 and 65,500 bytes.

Request example

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

"fields": [
  {
    "name": "a",
    "type": "char",
    "length": 16
  },
  {
    "name": "b",
    "type": "varchar",
    "length": 65500
  },
  {
    "name": "c",
    "type": "lvarchar"
  },
  {
    "name": "d",
    "type": "binary",
    "length": 16
  },
  {
    "name": "e",
    "type": "varbinary",
    "length": 65500
  },
  {
    "name": "f",
    "type": "lvarbinary"
  },
  {
    "name": "g",
    "type": "json"
  },
  {
    "name": "h",
    "type": "json",
    "length": 65500
  },
  {
    "name": "j",
    "type": "number",
    "scale": 32
  },
  {
    "name": "k",
    "type": "number",
    "scale": 4
  },
  {
    "name": "i",
    "type": "number",
    "length": 12,
    "scale": 2
  },
  {
    "name": "l",
    "type": "money",
    "scale": 2
  },
  {
    "name": "m",
    "type": "money",
    "scale": 4
  },
  {
    "name": "n",
    "type": "money",
    "length": 12,
    "scale": 2
  }
]

"name"

The optional "name" property is a case-sensitive string from 1 to 64 bytes in length. It is a new name for an existing field in a table. A field is not renamed when "name" is zero-length or omitted..

  • A zero-length "name" is invalid.

  • A client should enforce the uniqueness of the "name".

Request example

"fields": [
  {
    "name": "field1",
    "type": "varchar",
    "newName": "company"
  }
]

"nullable"

The "nullable" property is an optional Boolean. When true, it allows a field to contain a NULL value. To require a field to have a non-null value, set "nullable" to false.

"fields": [
  {
    "name": "company",
    "type": "varchar",
    "nullable": true 
  }
]

"ownerName"

The "ownerName" property is an optional string from 1 to 64 bytes that identifies the user who owns an object (see Object owner). If it is omitted or set to "" or null, the server uses the default owner name supplied during the "createSession" action or uses the account's "username" as the owner name.

"params": {
  "ownerName": "SuperUser"
}

"padValue"

The "padValue" property is a decimal number from 0 to 255. It is optional. If omitted, it defaults to 0.

  • The server uses this decimal number 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. 

    Note

    Padding does not apply to the variable-length fields of the table, including "varchar", "lvarchar", "varbinary", and "lvarbinary".

Reasons to use fixed-length fields:

  • Fixed-length fields are an optimization for the specific use case of storing fixed-length data that matches the exact length of the field.

  • For fixed-length fields, the following use cases work well:

    • Bit flags

      A "binary" field is useful for efficiently storing a fixed set of bit flags where each bit represents a different on/off setting.

    • Legacy identifiers

      A "char" field is useful for efficiently storing legacy fixed-length identifiers, such as a VIN number, where characters in specific positions represent different subsets of data.

    • Very small strings

      A "char" field is useful for efficiently storing strings that are smaller than six characters because they do not have the overhead of requiring two extra bytes to track the length of the field.

  • A fixed-length field is more efficient than a variable-length field because it does not require extra bytes to track the length of the field:

    • The "varchar" and "varbinary" fields require two extra bytes to track the length of the field, which can be up to 64K. 

    • The "lvarchar", "lvarchar",  and "lvarbinary" fields require four extra bytes to track the length of the field, which can be up to 2GB.

Reasons for padding fixed-length fields:

  • Padding is required for fixed-length fields because data can be assigned to a field with a shorter length than the field's length.

  • Padding is useful in the following use cases:

    • The default padding value of 0 in a "char" field makes a fixed-length string behave like a variable-length string. It works because the database can treat a fixed-length string as a null-terminated string that can be smaller than the fixed width.

    • A padding value of 32 in a "char" field is the ASCII space character, which automatically left-aligns fixed-length strings.

    • The default padding value of 0 in a "binary" field works for bit flags when zero is the desired default state of a bit. Otherwise, padding in a "binary" field is rarely a good thing because it alters the binary value.

How padding works:

  • When the server assigns a value to a "char" or "binary" field (which are fixed-length field types), it puts the value in the field starting on the left and uses the pad value to fill in any remaining bytes on the right.

  • In other words, when the number of bytes in a value being assigned to a "char" or "binary" field is less than the number of bytes in the field, the server fills in the remaining bytes with the pad value.

CHAR padding behavior:

  • The default pad value of 0 causes a special padding behavior for "char" fields.

    • It causes the JSON DB API to treat fixed-length string fields as if they were variable-length strings with a maximum length.

    • In other words, the same string value going into a "char" field comes out the same, no padding is added — for example, when a table's "padValue" is set to 0 and a JSON string of "12" is inserted into a "char" field, the JSON string of "12" is returned from that field.

  • When you want the server to add padding to strings that are stored in a "char" field, set the "padValue" property to a non-zero number that is the ASCII character you want to pad the string.

    For example, set "padValue" to 32 to pad strings with the space character. When a table's "padValue" is set to 32 and a JSON string of "12" is inserted into a "char" field with a "length" of 4 bytes, the JSON string of "12 " is returned from that field.

BINARY padding behavior:

  • The server always applies padding when the number of bytes in a binary value is less than the number of bytes in the fixed-length, "binary" field. When the server adds padding to a binary value, it permanently modifies the binary value. If this is not desirable, ensure the binary value exactly matches the size of the "binary" field.

  • The JSON DB API always returns the entire stored value of a "binary" field. This binary value is encoded as a JSON array of bytes or a string containing a base64 or hexadecimal representation of the binary value. The length of the JSON string encoded in base64 or hexadecimal is always longer than the length of the binary field.

  • If a table's "padValue" is set to 0, and a binary value of 0x3132 is stored in a "binary" field with a "length" of 4 bytes, the server pads the value and stores the value of 0x31320000 into the field. When the value is returned by the JSON DB API, it is returned as 0x31320000. Thus, padding alters a binary value when the value is smaller than the field. The default padding behavior of a "binary" field is different from the default padding behavior of a "char" field because the server can assume that an ASCII NULL padding value is a string terminator, but it cannot assume the same for a binary value.

Example 1. Turning off padding

If a table's "padValue" is set to 0, the ASCII NULL character with the hexadecimal value of 0x00 is used to pad unfilled byte positions. So when the JSON string "12" is inserted into a "char" field that has a "length" of 4 bytes, the binary value of 0x31320000 is stored in the field. The value returned is a JSON string of "12" because it recognizes the ASCII NULL character as a string terminator.

In other words, the ASCII NULL character allows the server to automatically trim the length of a string that is stored in a fixed-length field before it returns the string in JSON. So, by default, the same JSON string inserted into a "char" field is always returned by the JSON DB API. This behavior is safe for UTF-8 strings, which is the string encoding used by JSON.



Example 2. Padding s a string with spaces

When a table's "padValue" is set to 32, which is hexadecimal 0x20, and a JSON string of "12" is assigned to a "char" field that has a "length" of 4 bytes, the binary value of 0x31322020 is stored in the field. When the JSON DB API retrieves the value, it returns it as a JSON string of "12  ". This is because the value 32 is the ASCII space character " ".



Example 3. Padding a string with hyphens

If a table's "padValue" is set to 54, which is hexadecimal 0x36, and a JSON string of "12" is assigned to a "char" field that has a "length" of 4 bytes, the binary value of 0x31323636 is stored in the field. When the JSON DB API retrieves the value, it returns it as a JSON string of "12--". This is because the value 54 is the ASCII character "-".



Example 4. Manually padding a string

Assigning "12**" to a "char" field that has a "length" of 4 bytes stores "12**" into the field regardless of the table's "padValue".



Example 5. Right-aligning a value in a string

Assigning " $12.34" to a "char" field that has a "length" of 8 bytes stores " $12.34" into the field regardless of the table's "padValue".



Example 6. Avoiding padding

Use the variable-length char and binary field types ("varchar", "lvarchar", "lvarchar", "varbinary", and "lvarbinary"). 

Each of these field types is stored in the table with length information, so the data returned matches the data stored.



Request example

{
  "api": "db",
  "action": "createTable",
  "params": {
    "padValue": 32
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

"primaryKey"

This optional property identifies a table's 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".

Each table created by the JSON DB API has a primary key that uniquely identifies each record.

"createTable" automatically adds the "id" field as the primary key to your table. It makes "id" an auto-increment bigint field and indexes the field with a unique index named "id_pk". Using the "id" field as the primary key is a best practice.

You can specify one or more fields to be the primary key of the table instead of the "id" field. To do so, you must add the "primaryKeyFields" property to "createTable" or use the "fields" property's "primaryKey" to specify which field(s) are in the primary key.

Note

You should not use both the  "primaryKeyFields" and "primaryKey" properties together.

If multiple fields are specified for the key, the index is named "pk" . If only one field is specified for the key, the index is named "<fieldname>_pk".

If you use the "primaryKey" property to specify multiple fields as the primary key, the assigned value from 1 to n specifies the order of the fields in the primary key. Assign "primaryKey": 1 to the first field in the primary key, "primaryKey": 2 to the second, and so forth. If you create a primary key with multiple fields, the index is named "pk". If you specify just one field, the index is named "<fieldname>_pk".

Example

"fields": [
  {
    "name": "a",
    "type": "tinyint",
    "primaryKey": 1
  },
  {
    "name": "b",
    "type": "smallint",
    "primaryKey": 2
  },
  {
    "name": "c",
    "type": "integer",
    "primaryKey": 3
  }
]

"primaryKeyFields"

This optional 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"
  }
]

"scale"

The "scale" property 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 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.

The value of "scale" must be an integer from 0 to the number of digits specified by the "length" property. The maximum length is 32, which allows the scale to have up to 32 digits. The default value of length is 32.

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.

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. 

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

"length"

"scale"

Zero with Fixed Precision

Closest Positive Number to Zero

Closest Negative Number to Zero

Furthest Positive Number from Zero

Furthest Negative Number from Zero

Furthest Positive Fractional Number from Zero

Furthest Negative Fractional Number from Zero

Miscellaneous Examples

4

0

0

1

-1

9999

-9999

N/A

N/A

1234; 12

4

1

0.0

0.1

-0.1

999.9

-999.9

0.9

-0.9

123.4; 12.0

4

2

0.00

0.01

-0.01

99.99

-99.99

0.99

-0.99

12.34; 12.00

4

3

0.000

0.001

-0.001

9.999

-9.999

0.999

-0.999

1.234; 1.200

4

4

0.0000

0.0001

-0.0001

0.9999

-0.9999

0.9999

-0.9999

0.1234; 0.1200



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
  },
  {
    "name": "i",
    "type": "number",
    "length": 12,
    "scale": 2
  },
  {
    "name": "l",
    "type": "money",
    "scale": 2
  },
  {
    "name": "m",
    "type": "money",
    "scale": 4
  },
  {
    "name": "n",
    "type": "money",
    "length": 12,
    "scale": 2
  }
]

"smallFile"

The "smallFile" is an optional Boolean. When true, a table is optimized for data files that cannot grow larger than 4 GB. It defaults to false.

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

"tableFileExtension"

The "tableFileExtension" property is an optional string from 0 to 64 bytes. It specifies the file system extension to use for a table's data files. If omitted, it defaults to ".dat".

Note

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

Example request

"params": {
  "tableName": "test1",
  "tableFileExtension": ".dat"
}

"tableName"

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

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

Example request

"params": {
  "tableName": "ctreeTable"
}

"transactionId"

The "transactionId" is an optional string that the server generates and returns during a "createTransaction" action. The generated ID represents a transaction. In requests, it defaults to an empty string.

  • When a client wants an action to be controlled by a transaction, the "transactionId" must be included in the action request.

  • A "transactionId" is valid and can be applied to multiple actions until it is either committed using "commitTransaction" or rolled back using "rollbackTransaction".

  • A zero-length string means the "transactionId" is invalid.

  • Do not assume that "transactionId" is a number embedded in a string.

"transactionModel"

The "transactionModel" property is an optional, case-insensitive, string enum. It defines how the server processes transactions for a table. The default is "logTransactions".

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

"type"

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

Request example

"fields": [
  {
    "name": "j",
    "type": "number"
  }
]

"result"

Table 3. createTable "result" properties summary

Property

Description

Type

Contents

data

is an array of objects or arrays. Each item in the array describes a record. The array is empty if no results are available.

array

The action determines its contents.

data
.indexes

is an array of objects where each object identifies the characteristics of each index in the table.

array

The content is determined by the action requested.

data
.indexes
.collectStats

identifies whether usage statistics are being collected and stored.

boolean

true
false
data
.indexes
.compression

identifies whether the index is compressed.

string

"on"
"off"
"auto"
data
.indexes
.conditionalExpression

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
.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 from including the changes in the index.

boolean

true
false
data
.indexes
.fields

is an array of objects, one for each field in the index. Each object identifies the field's attributes.

array

data
.indexes
.immutableKeys

identifies whether the key's values can be changed.

boolean

true
false

dataFormat

identifies the format of the data in the "data" property.

string

"autoDetect"
"arrays"
"objects"


"data"

The "data" property is an array of objects or arrays that contain information about the database. The "dataFormat" property specifies whether the content is arrays or objects. The initiating action defines the data contents. In results messages, when no results are available, it is empty. For path formats, see "path".

Examples

arrays
"data":
[
  ["test1", ".\\test1.dbs\\SQL_SYS", 1003]
]
objects
"data":
[
  {
    "databaseName": "test7",
    "path": ".\\test7.dbs\\SQL_SYS",
    "uid": 1015
  }
]

"dataFormat"

The "dataFormat" property is a case-insensitive string enum that defines the format of the "data" property. The default format is an array of arrays. The alternative is an array of objects. The default for "dataFormat" can be changed during a "createSession" action by assigning a different value to the "dataFormat" property in "defaultResponseOptions".

There are three different (but similar) versions of the "dataFormat" property:

Two of those versions occur in a request, and another occurs in a response. They all indicate how data is formatted.

  • "dataFormat" in the request in "responseOptions" determines how the "data" property in the response is formatted.

    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.

  • "dataFormat" in the request in the "params" object notifies the server how the "sourceData" property is formatted in the request. This version is rarely used because of the default "autoDetect" behavior.

    Possible values include:

    • "arrays"

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

    • "autoDetect"

      This is the default, and the server automatically detects the format of the data in the "sourceData" property.

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

Example response

{
  "result": {
    "dataFormat": "objects"
  }
}

A database is a set of data that is managed by a database server. A database contains data that is organized into tables, records, and fields. A database is a huge composite data type that represents a group of interconnected objects. A database allows records to be joined together for querying, searching, and processing by users, applications, and services.

A database is a security container that controls which accounts have permissions to access specific data.