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

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

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

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 optional "variantFormat" property specifies how the server represents variant fields in JSON. It is an enumerated string with one of the following values: "binary", "json", "string", and "variantObject".

The default value for "variantFormat" is the "defaultVariantFormat" property defined in the "createSession" or "alterSession" actions. If it is omitted there, it defaults to the value of the "defaultVariantFormat" property in the <faircom>/config/services.json file. If it is not there, the FairCom server defaults it to "json".

When "variantFormat" occurs in "mapOfPropertiesToFields", it tells the server how to encode or decode a variant value in a JSON property.

For example, including "variantFormat" in a "tableFieldsToJson" transform step controls how the server takes the value in a variant field and encodes it in a JSON property. By default, the server takes the variant value and stores it as JSON in the JSON property.

For example, including "variantFormat" in a "jsonToTableFields" or "jsonToDifferentTableFields"  transform step controls how the server decodes a value in a JSON property so it can store that value in a variant field. By default, the server takes the value and stores it as JSON in the variant field.

The "json" value causes the server to return a variant field value as JSON or to store JSON in the variant field.

The "string" value causes the server to return a variant field value as a JSON string or to store a JSON string value in the variant field.

The "binary" value causes the server to return a variant field value as a binary value in JSON or to store a binary value in the variant field.

The "variantObject" value causes the server to return a variant field value as a variant object or to store a value in a variant field as specified by the variant object, which is a standard JSON object that describes the variant value's type, encoding, and value.

{
  "schema": "jsonaction.org/schemas/variantObject",
  "value": "ewogICJrZXkiOiAidmFsdWUiCn0=",
  "valueEncoding": ["base64"],
  "type": "binary"
}

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