createIntegrationTable

JSON hub "createIntegrationTable action creates a new integration table in FairCom Edge

The "createIntegrationTable" action creates an integration table.

  • The "createIntegrationTable" action creates an integration table.
    • This means that it will have the automatically-added integration fields discussed in the Integration fields section, in addition to the fields you explicitly add yourself using the fields property.
  • If the table already exists, it returns or logs an error.
  • Integration tables are created with input and read privileges only. This enables them for audit trail purposes. Allowing updates and deletions would destroy audit integrity.

 

Request examples

Minimal

{
  "action":     "createIntegrationTable",
  "params":     {
    "tableName": "test1"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Create an integration table with a transform

This example creates a new integration table and a transform simultaneously

{
  "api": "hub",
  "action": "createIntegrationTable",
  "params": {
    "tableName": "transformExampleTable",
    "fields": [
      {
        "name":     "my_input_varchar",
        "type":     "VARCHAR",
        "length":   128,
        "nullable": true
      },
      {
        "name":     "my_output_field",
        "type":     "JSON"
      }
    ],
    "transformSteps": [
      {
        "transformStepMethod": "javascript",
        "codeName": "transformExample"
      },
      {
        "transformStepMethod": "tableFieldsToJson",
        "mapOfPropertiesToFields": [
          {
            "fieldName": "my_input_varchar",
            "recordPath": "my_output_field.myProperty"
          }
        ]
      }
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Create an integration table with one additional field

{
  "api":        "hub",
  "apiVersion": "1.0",
  "requestId":  "2",
  "action":     "createIntegrationTable",
  "params":     {
    "databaseName": "faircom",
    "tableName":    "test2",
    "fields": [
      {
        "autoValue": "none",
        "name": "name",
        "type": "varchar",
        "length": 50,
        "primaryKey": null,
        "scale": null,
        "defaultValue": null,
        "nullable": false
      }
    ],
    "metadata":        { },
    "retentionPeriod": 4,
    "retentionUnit":   "week"
  },
  "apiVersion": "1.0",
  "debug": "max",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

 

Response examples

Success

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

Table already exists failure

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

 

Properties

Request properties ("params") 

Property Description Default Type Limits (inclusive)

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

fields

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

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

Required - No default value

 array of objects

"caseInsensitive"

"name"

"reverseCompare"

"sortDescending"

"autotimestamp"

"autoValue"

"defaultValue"

"length"

"name"

"nullable"

"primaryKey"

"scale"

"type"

fields

.autoValue

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

"none"

"incrementOnInsert"

"timestampOnInsert"

"timestampOnUpdate"

"timestampOnUpdateAndInsert"

"changeid"

fields

.defaultValue

 The "defaultValue" property specifies the default value of a field. It is used when a record is inserted without specifying a value for the field. The server coerces the string value into the proper field type.  Optional with default of "" string 0 to 65,500 bytes

fields

.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

fields

.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

fields

.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

fields

.primaryKey

The "primaryKey" property identifies a table's primary key. For more details, see "primaryKey".

 Optional with default of null integer 0 to 32

fields

.scale

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

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

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

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

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

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

 

Request Example

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

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


],
 Optional with default of null integer 0 to 32

fields

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

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

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

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

.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

.fieldName

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

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

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

 

 

 

Was this article helpful?

Give feedback about this article

Related Articles