"createIntegrationTable" (JSON Action)

{
    "result": {},
    "requestId": "1",
    "errorCode": 0,
    "errorMessage": ""
}

{
    "result": {},
    "requestId": "2",
    "debugInfo": {
        "request": {
            "api": "hub",
            "action": "createIntegrationTable",
            "params": {
                "databaseName": "faircom",
                "tableName": "test1",
                "fields": [
                    {
                        "name": "t10",
                        "type": "VARCHAR",
                        "length": 128,
                        "nullable": true
                    }
                ],
                "metadata": {},
                "retentionPeriod": 4,
                "retentionUnit": "week"
            },
            "apiVersion": "1.0",
            "requestId": "2",
            "debug": "max",
            "authToken": "replaceWithAuthTokenFromCreateSession"
        }
    },
    "errorCode": 12020,
    "errorMessage": "Not able to create integration table [test1]. Integration table name already exists.",
    "authToken": "replaceWithAuthTokenFromCreateSession"
}

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

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

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.

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

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

Only one of these values is allowed per field.

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

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

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:

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

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.

 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.

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

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

Request example

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

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

This optional property identifies a table's primary key.

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.

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

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

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

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

The "metadata" property is an optional JSON object. It exists primarily for the user interface to find integration information. By default, it is an empty JSON object.

{
  "description": "",
  "tags": [""],
  "yourOwnProperties": "usage, purpose, notes, location, etc.",
}

The "retentionPolicy" property controls how messages are persisted. This property is optional.

If not specified, the default found in the services.json file is used. Initially, it is "autoPurge".

The "retentionPeriod" property specifies how many units of data to retain. It must be an integer value from 1 to 100. It refers to the unit of time specified by the "retentionUnit" property — for example, if "retentionPeriod" is 14 and "retentionUnit" is "day", then message data is retained for 14 days. This property is optional.

When partitions are auto-purged, some data are maintained "forever" in a global index. Auto-purge does not prevent these files from growing without bounds and filling up your hard drive.

If not specified, the default found in the services.json file is used. Initially, it is 4 (weeks).

Automatically purging data is important to ensure that retained data does not consume all storage and shut down the computer. The default value of 4 weeks allows FairCom's servers to store 1 TB of messages when 200 topics send one 2K message per second.

Each time this unit cycles, FairCom purges expired messages. For example, if you want a week's worth of messages to be purged once a week, set "retentionUnit" to "week" . This property is optional.

If not specified, the default found in the services.json file is used. Initially, it is "week". 

The "tableName" property contains the name of the table in the database where the event occurred. It is a non-zero-length string.

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

A table in DBnotify is defined by "databaseName", "ownerName" and "tableName" or by "dataFilePath".

The "transformSteps" property is an array of transform step objects. 

Different actions interpret "transformSteps": [] differently.

Each transform step object contains the required "transformStepMethod" property. It also supports the following optional properties:

Each transform step method also supports unique properties that are defined below.

The "codeName" property is a required string from 1 to 64 bytes. It is the name of a code package that runs when a record is inserted into an integration table. See "codeName" for more information.

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

The "databaseName" property is an optional string from 1 to 64 bytes that 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. 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 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.

The "ownerName" property is an optional string from 1 to 64 bytes that specifies the account that owns an object, such as a table or code package. If it is set to null or is omitted, it defaults to the account that created the JSON Action session, see "createSession" and the "defaultOwnerName" property. 

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.

The "mapOfPropertiesToFields" property takes fields in the table and maps them to a field containing JSON properties. It is an array of property map objects. Each object maps a field in a table to a JSON property in another field.

The optional "dateFormat" property specifies the format of a date or a datetime embedded in a JSON string. It applies to user-provided JSON and to server-generated JSON. The server uses it when it needs to transform or convert a date or datetime embedded in a JSON string into a FairCom date or timestamp. It also uses it when it needs to convert a date or datetime field value into a date embedded in a JSON string.

It is an enumerated string with one of the following values: "ccyy.mm.dd", "mm.dd.ccyy", "mm.dd.yy", "dd.mm.ccyy", "dd.mm.yy", "ccyymmdd", "yymmdd", "iso8601", and "utc".

The default value for "dateFormat" is the "defaultDateFormat" property defined in the "createSession" or "alterSession" actions. If it is omitted there, it defaults to the value of the "defaultDateFormat" property in the <faircom>/config/services.json file. If it is not there, the FairCom server defaults it to "iso8601" because the ISO8601 date is the defacto standard in JSON.

The enumerated string defines how the server parses a date in a JSON string and how it writes a date into a JSON string. The following key explains the parts of each enumerated value:

The required "fieldName" property specifies a field in a record. Depending on the context, the server reads or writes the field's value. 

For example, in the "tableFieldsToJson" transform method, "fieldName" is the location where the transform step reads a value that it transforms.  In the "jsonToTableFields" and "jsonToDifferentTableFields" transform methods, "fieldName" is the location where the transform step writes a transformed value.

The "numberFormat" property is an optional, case-insensitive string enum. It defines the format of JSON numbers. The default value for "numberFormat" is the "defaultNumberFormat" defined in the "createSession" or "alterSession" actions. If it is omitted there, it defaults to the value of the "defaultNumberFormat" property in the <faircom>/config/services.json file.

When "numberFormat" occurs in "mapOfPropertiesToFields", it tells the server how to encode or decode a number assigned to a JSON property.

For example, including "numberFormat" in a "tableFieldsToJson" transform step controls if the server encodes a number in a JSON property as a number or a number embedded in a string.

Example request

{
  "action": "someAction",
  "responseOptions":
  {
    "numberFormat": "string"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

The "recordPath" property specifies the location in a record where the server reads or writes a JSON value. It specifies a field name followed by an optional JSONPath. Depending on the context, it refers to a value the server reads or writes. 

For example, in the "tableFieldsToJson" transform method, "recordPath" is the location where the transform step writes a transformed value.  In the "jsonToTableFields" and "jsonToDifferentTableFields" transform methods, "recordPath" is the location where the transform step reads the value that it transforms.

Several FairCom APIs, such as the DB and Transform APIs, treat each record in each table as a JSON document. The top-level fields in the record are treated as top-level properties in the JSON document. In other words, you can think of each record as a JSON document, and you can use the "recordPath" property to refer to any field in the record and any JSON property or array item within a JSON field.

When the server writes a value to a "recordPath", it looks for the specified field and JSONPath and does the following.

When the server reads a value from a "recordPath", it looks for the specified field and JSONPath and does the following.

{
  "humidity": 
  [
    {
      "temperature": 20.1,
      "pressure": 1003
    }
  ]
}

{
  "id": 1,
  "source_payload": 
  {
    "humidity": 
    [
      {
        "temperature": 20.1,
        "pressure": 1003
      }
    ]
  },
  "create_ts": "2025-07-07T14:14:02.012"
}

{
  "humidity": 
  [
    {
      "temperature": 20.1,
      "pressure": 1003
    }
  ]
}

[
  {
    "temperature": 20.1,
    "pressure": 1003
  }
]

{
  "temperature": 20.1,
  "pressure": 1003
}

The optional "timeFormat" property specifies the format of a time or a datetime embedded in a JSON string. The server uses it when it needs to transform or convert a time or datetime embedded in a JSON string into a FairCom time or timestamp. It also uses it when it needs to convert a time or datetime field value into a time embedded in a JSON string.

It is an enumerated string with one of the following values: "hh.mm.ss.ttt", "hh.mm.am/pm", "hh.mm.ss.am/pm", "hh.mm.ss", "hh.mm", "hhmm", "iso8601", and "utc".

The default value for "timeFormat" is the "defaultTimeFormat" property defined in the "createSession" or "alterSession" actions. If it is omitted there, it defaults to the value of the "defaultTimeFormat" property in the <faircom>/config/services.json file. If it is not there, the FairCom server defaults it to "iso8601" because the ISO8601 date is the defacto standard in JSON.

The enumerated string defines how the server parses a time in a JSON string. The following key explains the parts of each enumerated value:

The "variantFormat" property tells the server how to format the values of variant fields in its response to your request.

The "variantFormat" property has one of the following values: "binary", "json", "string", and "variantObject". It tells the server how to store and return values stored in variant fields. The default value is "json".

The "targetDatabaseName" property is the optional name of the database that contains the target table. See

"databaseName" for more information.

The "targetOwnerName" property is the optional name of the account that owns the target table. See "ownerName" for more information.

The required "targetTableName" property specifies the name of the target table that has its transform steps replaced by this action.

The "transformStepMethod" property is a required string that specifies the type of transform, such as the "javascript" transform method that runs JavaScript to change the table's data or the "jsonToTableFields" transform method that extracts values from properties in a JSON field and stores them in other fields.

The value of the "transformStepMethod" affects the value of the "transformService" property. The following table defines the possible values of the "transformService" property when combined with the "transformStepMethod". Notice that some transform step methods are built into the server and do not require you to specify the "transformService".

The "transformStepName" property is an optional string that assigns a name to each transform step.

The "transformStepService" property is an optional string that specifies the name of a transform service, which is the user-defined name for a library (.dll, .so, .dylib) that implements the transform step method.

This property allows you to register your own transform libraries or use an older version of a FairCom library for backward compatibility.

Transform services are defined in the services.json file under the "transformServices" section. You can add and remove transform libraries to this list, such as your own transform libraries or specific versions of FairCom's transform libraries. Use the "serviceName" property to give each transform library a unique transform service name and use the "serviceLibrary" property to specify the filename of the library that implements the transform step method. On Windows the library filename ends with .dll. On Linux it ends with .so. On MacOS it ends with .dylib. Lastly, you must enable the transform service by setting "enabled" to true.

Example "transformServices" section in the services.json file.

"transformServices": [
  {
    "serviceName": "v8TransformService",
    "serviceLibrary": "v8transformservice.dll",
    "enabled": true
  }
],