alterIntegrationTable

JSON hub "alterIntegrationTable" action modifies settings for existing tables that are safe to modify

The "alterIntegrationTable" action alters table settings that are safe to modify.

  • The settings that are safe to modify include:
    • Renaming a table
    • Adding fields
    • Increasing field size
    • Changing table retention policy
    • Changing table metadata
    • Changing a table's transformation pipeline
  • As you refine your integration processes, you may want to rename an integration table to better label the data it holds.
    • You can use the "alterIntegrationTable" action with the "newTableName" property to rename an integration table.
    • You can also use the "tableName" property of the "configureTopic" action to rename an integration table that is automatically created by an MQTT message. This is easy because you can rename the integration table using its MQTT topic.
  • This action cannot shrink the size of fields because this destroys data.
  • This action cannot rename fields because it breaks compatibility with bridges between services (integrations / configurations).

 

Request examples

Change retention policy of the "test1" integration table

Prerequisites: You must first create the "test1" integration table using the "createIntegrationTable" action.

{
  "api": "hub",
  "action": "alterIntegrationTable",
  "params": {
    "databaseName": "faircom",
    "tableName": "test1",
    "rebuildTable": true,
    
    "retentionPolicy": "autoPurge",
    "retentionPeriod": 7,
    "retentionUnit": "day"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Add user-defined fields to the "test1" integration table

Prerequisites: You must first create the "test1" integration table using the "createIntegrationTable" action.

{
  "api": "hub",
  "action": "alterIntegrationTable",
  "params": {
    "databaseName": "faircom",
    "tableName": "test1",
    "addFields": [
      {
        "name": "t1",
        "type": "json"
      },
      {
        "name": "t2",
        "type": "varchar",
        "length": 128,
        "nullable": true
      },
      {
        "name": "temperature",
        "type": "double"
      }
    ],
    "rebuildTable": true
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Modify user-defined fields in the "test1" integration table

Prerequisites: You must first run the previous "alterIntegrationTable" example to add fields to the "test1" integration table.

{
  "api": "hub",
  "action": "alterIntegrationTable",
  "params": {
    "databaseName": "faircom",
    "tableName": "test1",
    "alterFields": [
      {
        "name": "t2",
        "newName": "x1",
        "type": "varchar",
        "length": 150
      }
    ],
    "rebuildTable": true
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Delete user-defined fields in the "test1" integration table

Prerequisites: You must first modify the "test1" integration table using the previous "alterIntegrationTable" example.

{
  "api": "hub",
  "action": "alterIntegrationTable",
  "params": {
    "databaseName": "faircom",
    "tableName": "test1",
    "deleteFields": [
      "x1"
    ],
    "rebuildTable": true
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Modify the transform steps of the "test1" integration table

This example changes the transform steps assigned to the "test1" integration table.

{
  "api": "hub",
  "action": "alterIntegrationTable",
  "params": {
    "tableName": "test1",
    "logTransformOverwrites": false,
    "transformSteps": [
      {
        "transformStepMethod": "jsonToTableFields",
        "mapOfPropertiesToFields": [
          {
            "recordPath": "source_payload.temperature",
            "fieldName": "temperature"
          }
        ]
      },
      {
        "transformStepMethod": "tableFieldsToJson",
        "mapOfPropertiesToFields": [
          {
            "fieldName": "temperature",
            "recordPath": "t1.temperature"
          }
        ]
      }
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Change the metadata of the "test1" integration table

This example changes the metadata assigned to the "test1" integration table.

{
  "api": "hub",
  "action": "alterIntegrationTable",
  "params": {
    "databaseName": "faircom",
    "tableName": "test1",
    
    "metadata": {
      "description": "This can be any description you want.",
      "tags": [
        "minimal",
        "example",
        "alterIntegrationTable"
      ],
      "yourOwnProperties": "Minimal example, showing basic settings, metadata is very flexible, You can put anything here"
    }
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Maximal

The example below shows all possible properties for each type of transform step.

{
  "api": "hub",
  "action": "testIntegrationTableTransformSteps",
  "params": {
    "databaseName": "faircom",
    "ownerName": "admin",
    "tableName": "myTable1",
    "newTableName": "myTable2",
    "logTransformOverwrites": true,
    "disableTransformSteps": false,
    
    "transformSteps": [
      {
        "transformStepName": "step1",
        "transformStepMethod": "javascript",
        "transformStepService": "v8TransformService",
        "codeName": "convertFahrenheitToCelsius",
        "ownerName": "admin",
        "databaseName": "faircom"
      },


      {
        "transformStepName": "step2",
        "transformStepMethod": "tableFieldsToJson",
        "mapOfPropertiesToFields": [
          {
            "fieldName": "my_source_field",
            "recordPath": "my_output_field.myProperty",
            "binaryFormat": "byteArray",
            "numberFormat": "string",
            "variantFormat": "json",
            "dateFormat": "mm.dd.ccyy",
            "timeFormat": "hh.mm.am/pm"
          }
        ]
      },


      {
        "transformStepName": "step3",
        "transformStepMethod": "jsonToTableFields",
        "mapOfPropertiesToFields": [
          {
            "recordPath": "my_source_field.myProperty",
            "fieldName": "my_target_field",
            "binaryFormat": "base64"
          }
        ]
      },




      {
        "transformStepName": "step4",
        "transformStepMethod": "jsonToDifferentTableFields",
        "targetTableName": "myTargetIntegrationTable",
        "targetOwnerName": "admin",
        "targetDatabaseName": "faircom",
        "mapOfPropertiesToFields": [
          {
            "recordPath": "my_source_field.myProperty",
            "fieldName": "my_target_field",
            "binaryFormat": "hex"
          }
        ]
      }


    ]


  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

 

Response examples

Success

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

Wrong table name failure

{
    "result": {},
    "requestId": "5",
    "errorCode": 100,
    "errorMessage": "Not able to find integration table by name [test3/admin/faircom].",
    "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Wrong transform name failure

{
    "result": {},
    "requestId": "5",
    "errorCode": 100,
    "errorMessage": "Transform pipeline [firstCreateMe] was not found.",
    "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Changing retention policy, period, or unit without setting "rebuildTable" to true

{
  "authToken": "replaceWithAuthTokenFromCreateSession",
  "result": {},
  "requestId": "00000001",
  "debugInfo": {
    "request": {
      "authToken": "replaceWithAuthTokenFromCreateSession",
      "api": "hub",
      "action": "alterIntegrationTable",
      "params": {
        "databaseName": "faircom",
        "tableName": "test1",
        "rebuildTable": false,
        "retentionPolicy": "doNotPersist",
        "retentionPeriod": 1,
        "retentionUnit": "day"
      },
      "requestId": "00000001",
      "debug": "max"
    }
  },
  "errorCode": 12006,
  "errorMessage": "Before you can change the data retention policy, period, or unit, you must use "alterIntegrationTable" and add "rebuildTable": true to the request. WARNING: rebuilding a table disrupts data collection while it rewrites all records."
}
 
 

 

Properties

Request properties ("params")

Property Description Default Type Limits (inclusive)

addFields

The "addFields" property allows you to add new fields to a table and specify their properties. Each object in the array defines the properties of a field being added.

"addFields": [
  {
    "autoValue": "none",
    "name": "field1",
    "type": "varchar",
    "length": 50,
    "scale": null,
    "defaultValue": null,
    "nullable": false,
    "primaryKey":1
  }
]
 Optional with default of [] array of objects

"autoValue"

"defaultValue"

"length"

"name"

"newName"

"newPosition"

"nullable"

"scale"

"type"

addFields

.length

The "length" property specifies the length of a table field. For more details, see “length”.

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
  }


],
 Optional with default of null integer 1 to 65500

addFields

.name

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

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

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

 

 Required - No default value string 1 to 64 bytes

addFields

.nullable

The "nullable" property allows a field to contain a NULL value when true. To require a field to have a non-null value, set "nullable" to false.

"fields": [
  {
    "name": "company",
    "type": "varchar",
    "nullable": true 
  }
]
 Optional with default of true Boolean

true

false

addFields

.type

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

 

Request example

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

"json"

"bit"

"tinyint"

"smallint"

"integer"

"bigint"

"real"

"float"

"number"

"money"

"date"

"time"

"timestamp"

"char"

"varchar"

"lvarchar"

"binary"

"varbinary"

"lvarbinary"

alterFields

The "alterFields" property allows you to modify properties of existing fields in a table. Each object in the array defines the modifications to the named field.

In this example, the field named "field1" is being modified.

 

Example

"alterFields": [
  {
    "autoValue": "none",
    "name": "field1",
    "type": "varchar",
    "newName": "address",
    "newPosition": 5,
    "length": 50,
    "scale": null,
    "defaultValue": null,
    "nullable": false
  }
]
 Optional with default of [{"name":""}] array of objects "autoValue"
"name"
"type"
"newName"
"newPosition"
"length"
"scale"
"defaultValue"
"nullable"

alterFields

.length

The "length" property specifies the length of a table field. For more details, see “length”.

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
  }


],
 Optional with default of null integer 1 to 65500

alterFields

.name

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

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

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

 

 Required - No default value string 1 to 64 bytes

alterFields

.newName

The "newName" property assigns a new label name to an existing label or table. It defaults to null, which means the action does not change the label's or table's name. This property is available in the "alterLabel" and "changeLabels" actions, as well as the table actions.

 

 Optional with default of null string 1 to 64 bytes

alterFields

.type

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

 

Request example

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

"json"

"bit"

"tinyint"

"smallint"

"integer"

"bigint"

"real"

"float"

"number"

"money"

"date"

"time"

"timestamp"

"char"

"varchar"

"lvarchar"

"binary"

"varbinary"

"lvarbinary"

databaseName

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

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

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

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

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

 Defaults to the session's "defaultDatabaseName" property string 1 to 64 bytes

deleteFields

The "deleteFields" property specifies the names of fields to remove from a table.

  • Removing a field destroys the data in that field.
  • A zero-length "fieldName" is invalid.
 Optional with default of [] array 1 to 64 bytes

disableTransformSteps

The "disableTransformSteps" property determines if the transform steps are active without removing them from the integration table. When true, it prevents the server from running an integration table's transform steps on newly inserted records. When false, the transform steps run normally. It is useful to quickly stop a broken transform process.

 Optional with default of false Boolelan

true

false

logTransformOverwrites

The "logTransformOverwrites" property specifies whether or not the server will add a log entry to the log field when a transform step overwrites a field. When true and a record is inserted into the integration table, the server adds log entries to the log field when a transform step overwrites a field that already contains a value. When true, it protects fields, such as source_payload, from being overwritten accidentally. 

You typically set "logTransformOverwrites" to true when testing transform steps. Once they are working as expected, you can set it to false.

If multiple "recordPath" properties write to the same JSON properties in a "tableFieldsToJson" transform, the server will return an error because "recordPath" cannot overwrite. Multiple occurrences of the "recordPath" property may reference the same property as long as the property is in different fields/tables.

Additionally, non-JavaScript transform steps cannot overwrite protected fields or the source_payload field. The only exception is that a single "tableFieldsToJson" transform step may write to the source_payload field.

You may create transform steps to take the value of one JSON property and store it in multiple fields as long as no previous transform steps have already put values in these fields. Conversely, you may take the value of one field and store it in multiple JSON properties.

Tip Ensure JavaScript transform steps do not overwrite fields updated by other transform steps. 

Do not use the "tableFieldsToJson" transform method to overwrite the source_payload field; instead, use "tableFieldsToJson" to write to a different field and use the "configureTopic" action with the "outputPayloadField" property to configure the MQTT topic to deliver that field's value to subscribers. 

Ensure SQL, JSON DB, and other APIs set the value of the source_payload field and do not set field values that transform steps update.

 Optional with default of false Boolean

true

false

metadata

 The "metadata" property contains user-defined properties that add keywords and tags about the code package. The server indexes this field with a full-text index so you can search for any word or phrase to find code packages. Optional with default of {} object 0 or more key/value pairs

newTableName

The "newTableName" property specifies a new name for the table. Use the "newTableName" property to rename a table. 

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

"params": {
  "tableName": "old_table_name"
  "newTableName": "new_table_name"
}
 Optional with default of null string 1 to 64 bytes

ownerName

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

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

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

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

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

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

rebuildTable

The "rebuildTable" property causes the server to stop accepting read, insert, and update requests and rebuilds the table when true. Rebuilding a table creates new table files and writes existing records to the new files.

Warning Rebuilding a table interrupts data collection until the rebuilt operation finishes. The rebuild time is proportional to the number of table records.

Tip You may want to rebuild the table in the following cases: 

  • To apply a transform to previously inserted or updated records. 
  • To change the table's retention policy, period, or unit. 
  • To repair a data file corrupted by a storage failure.
 Optional with default of false Boolean

true

false

retentionPeriod

The "retentionPeriod" property specifies how many units of data to retain. It refers to the unit of time specified by the "retentionUnit" property. For more details, see "retentionPeriod".

Optional with default of 30

 integer 1 to 100

retentionPolicy

The "retentionPolicy" property controls how messages are persisted. 

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

 

retentionPolicy values:
  • "autoPurge"
    • This is the default. It is automatically applied when a new topic is created. It is preferred because it allows FairCom's servers to automatically remove messages that are older than the retention time. This helps ensure message data does not consume all storage space. It also minimizes storage costs and speeds up data access. The server partitions a table into multiple files so it can efficiently delete expired files.
  • "neverPurge"
    • This stores messages on disk and never removes them. This is useful when you need the entire history of the message stream. If message velocity is high, this can consume all storage space and cause an outage. The server creates a non-partitioned table, which is slightly faster than a partitioned table because it stores all records in one file.

Optional with default of "autoPurge"

 string

"autoPurge"

"neverPurge"

retentionUnit

The "retentionUnit" property specifies a unit of time that the server will use to purge 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". 

  • This property is used in concert with "retentionPeriod" to determine retention time.
  • "retentionUnit" values:
    • "minute"
    • "hour"
    • "day"
    • "week"
    • "month"
    • "year"
    • "forever"

Note 

  • For best performance, set the "retentionUnit" to a value that keeps "retentionPeriod" between 5 and 30. 
  • When you set "retentionUnit" property to "forever" the server will not purge messages. This setting is the same as setting "retentionPolicy" to "neverPurge".
  • The "retentionUnit" and "retentionPeriod" properties are used only when the "retentionPolicy" is set to "autoPurge".

Optional with default of "day"

string

"minute"

"hour"

"day"

"week"

"month"

"year"

tableName

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

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

 

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

transformSteps

 The "transformSteps" property specifies an array of transform objects. Required - No default value array of objects

0 or more objects containing 1 or more of the following properties:

"codeName"

"databaseName"

"ownerName"

"mapOfPropertiesToFields"

"targetDatabaseName"
"targetOwnerName"

"targetTableName"

"transformStepMethod"

"transformStepName"

"transformStepService"

transformSteps

.codeName

The "codeName" property contains the user-defined name for the code package.

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

The package's unique identifier is the combination of "databaseName", "ownerName", and "codeName". See the "databaseName" and "ownerName" properties for more information.

 Required - No default value string 1 to 64 bytes

transformSteps

.databaseName

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

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

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

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

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

 Defaults to the session's "defaultDatabaseName" property string 1 to 64 bytes

transformSteps

.mapOfPropertiesToFields

The "mapOfPropertiesToFields" property takes fields in the table and maps them to a field containing JSON properties. Each object maps a field in a table to a JSON property in another field.

  • Required properties
    • "recordPath"
    • "fieldName"
  • Optional properties
    • "binaryFormat"
    • "numberFormat"
    • "variantFormat"
    • "dateFormat"
    • "timeFormat"
 Optional with default of [] array

"binaryFormat"

"dateFormat"

"fieldName"

"numberFormat"

"recordPath"

"timeFormat"

"variantFormat"

transformSteps

mapOfPropertiesToFields

.binaryFormat

 The "binaryFormat" property designates the format of binary values embedded in JSON strings. For more details, see "binaryFormat".  Optional with default of "hex" string One of the following: "base64", "hex", or "byteArray".

transformSteps

mapOfPropertiesToFields

.dateFormat

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

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:

  • ccyy is a four-digit year (0000-9999).
  • yy is a two-digit year (00-99).
  • mm is a two-digit month (01-12).
  • dd is a two-digit day of the month (01-31).
  • hh is a two-digit hour (00-23).
  • . represents one character that can be one of the following values: /, ., or -.
  • "utc", "iso8601", and "ccyy.mm.dd" are the same.
UTC datetime format

Coordinated Universal Time (UTC) is an industry-standard for dates and times that uses the ISO8601 datetime format: "ccyy-mm-ddThh:mi:ss.fffz" or "ccyy-mm-ddThh:mi:ss.fff±hh:mi":

  • Variable parts
    • ccyy is a four-digit year.
    • mm is a two-digit month (01-12).
    • dd is a two-digit day of the month (01-31).
    • hh is a two-digit hour (00-23).
    • T is the letter T or the space character. It is required only when the time is present.
    • mi is a two-digit minute (00-59).
    • ss is a two-digit second (00-59).
    • fff is an optional fraction of a second with up to three digits of precision.
    • ± is an optional + or - character representing a positive or negative timezone offset. It is required only when a timezone offset is present.
  • Constant parts
    • - separates the year, month, and day.
    • : separates the hour, minute, and second.
    • . separates the second from a fraction of a second. It is required when the fraction is present.
    • Z is optional and represents the UTC timezone (also called Zulu time), which represents the timezone offset of +00:00.
  • Examples
    • 2025-07-11T16:18:33Z
    • 2025-07-11T16:18:33+00:00
    • 2025-07-11 16:18:33.9-07:00
 Optional with default of "iso8601" string enum

"ccyy.mm.dd"

"mm.dd.ccyy"

"mm.dd.yy"

"dd.mm.ccyy"

"dd.mm.yy"

"ccyymmdd"

"yymmdd"

"iso8601"

"utc"

transformSteps

mapOfPropertiesToFields

.fieldName

 The "fieldName" property specifies the name of a field in a table. Required - No default value string 1 to 64 bytes

transformSteps

mapOfPropertiesToFields

.numberFormat

The "numberFormat" property defines the format of JSON numbers. For more details, see "numberFormat". 

 Optional with default of "number" string

"number"

"string"

transformSteps

mapOfPropertiesToFields

.recordPath

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. For more details, see "recordPath".

 

 Required - No default value string 0 to 256 bytes

transformSteps

mapOfPropertiesToFields

.timeFormat

The "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. For more details, see "timeFormat".

 Optional with default of "hh.mm.am/pm" string

"hh.mm.am/pm"

"hh.mm.ss.am/pm"

"hh.mm.ss"

20:15:30

20.15&30

20H15M30S

"hh.mm"

"hhmm"

transformSteps

mapOfPropertiesToFields

.variantFormat

 The "variantFormat" property tells the server how to interpret the variant data included in a JSON Action request. For more details, see "variantFormat". Optional with default of "json" string

"json"

"binary"

"string"

"variantObject"

transformSteps

.ownerName

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

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

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

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

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

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

transformSteps

.targetDatabaseName

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

"databaseName" for more information.

 Optional with default of "" string 1 to 64 bytes

transformSteps

.targetOwnerName

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

 Optional with default of "" string 1 to 64 bytes

transformSteps

.targetTableName

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

Note The fully qualified name of a table includes the database, owner, and table names.

 Optional with default of "" string 1 to 64 bytes

transformSteps

.transformStepMethod

 The "transformStepMethod" property 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. For more details, see "transformStepMethod". Required - No default value string enum

"javascript"

"jsonToDifferentTableFields"

"jsonToTableFields"

"tableFieldsToJson"

transformSteps

.transformStepName

The "transformStepName" property assigns a name to each transform step.

 Optional with default of "" string 1 to 64 bytes

transformSteps

.transformStepService

The "transformStepService" property 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
  }
],

Note If the "transformStepMethod" property is set to "javascript", the "transformStepService" property must be set to "v8TransformService" or to a user-defined transform service name associated with the "v8transformservice.dll" service library.

 

 Optional with default of "" string 1 to 64 bytes

 

 

Was this article helpful?

Give feedback about this article

Related Articles