testIntegrationTableTransformSteps

Test new transform steps before running them

The "testIntegrationTableTransformSteps" action causes the server to test new transform steps against an integration table to verify they behave as expected.

It runs the transform steps you include in this action against the structure and records in the specified integration table. It does not run the transform steps assigned to the integration table. Thus, it is safe to use this action on a table actively collecting data because it will not affect the table's transform steps and data.

You can verify the transform steps work on the first record, last record, or on one or more records with the specified IDs. This action returns the before and after values in the response so you can compare them and verify the transform steps work as expected.

You can also verify the transform steps work on all records of an integration table by causing this action to create a test table, copy all records to it, and run the transform steps on all records in the test table. Afterwards, you can use SQL or JSON DB queries on the test table to find problematic records.

Use the "testTransformScope" property (required) to define the scope of this action:

  • "allRecords" returns immediately after starting a background thread that creates a test table, copies all records to it, and runs the transform steps on all records in that table.
  • "stop" immediately stops the background thread assigned to the test table. If there is no background thread, the action does nothing and returns an error.
  • "firstRecord" returns the first record's fields before and after being modified by the transform steps.  It runs synchronously and does not persist any changes.
  • "lastRecord" returns the last record's fields before and after being modified by the transform steps. It runs synchronously and does not persist any changes.
  • "specificRecords" returns the specified record's fields before and after being modified by the transform steps. It runs synchronously and does not persist any changes.

The "allRecords" scope has the following limitations:

  • The server runs one "allRecords" scope at a time. This applies to both the "rerunIntegrationTableTransformSteps" and "testIntegrationTableTransformSteps" actions.
  • While running transform steps on all records in an integration table, you cannot alter the structure of the table.

 

Request examples

Minimal example stops testing transform steps on all records

The following example stops a background thread that copies records to the myTable2 table and transforms them. It uses the database and owner specified in the current JSON action session (see "createSession" and "alterSession"). It returns an error if the background thread is not currently running on myTable2.

{
  "action": "testIntegrationTableTransformSteps",
  "params": {
    "testTableName": "myTable2",
    "testTransformScope": "stop"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Example testing transform steps on specified records

The following example tests new transform steps on records 1 and 6 in myTable1. It returns the before and after image of each record without persisting any changes.

{
  "api": "hub",
  "action": "testIntegrationTableTransformSteps",
  "params": {
    "tableName": "myTable1",
    "testTransformScope": "specificRecords",
    "ids": [
      1,
      3
    ],
    "transformSteps": [
      {
        "transformStepMethod": "javascript",
        "codeName": "convertFahrenheitToCelsius"
      }
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Example testing the transform steps on the last inserted record

The following example tests new transform steps on the most recently inserted record in myTable1. It returns the before and after image of the record without persisting the changes.

{
  "api": "hub",
  "action": "testIntegrationTableTransformSteps",
  "params": {
    "tableName": "myTable1",
    "testTransformScope": "lastRecord",
    "transformSteps": [
      {
        "transformStepMethod": "javascript",
        "codeName": "convertFahrenheitToCelsius"
      }
    ]


  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Example testing the transform steps on the first inserted record

The following example tests new transformation steps on the earliest inserted record in myTable1 that has not been purged by the retention policy. It returns the before and after image of the record without persisting the changes.

{
  "api": "hub",
  "action": "testIntegrationTableTransformSteps",
  "params": {
    "tableName": "myTable1",
    "testTransformScope": "firstRecord",
    "transformSteps": [
      {
        "transformStepMethod": "javascript",
        "codeName": "convertFahrenheitToCelsius"
      }
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Example copying records to a test table and transforming them

The following example copies the records of myTable1 to myTable2 and runs the transform steps on all the records in myTable2. It uses fully qualified table names that specify the database and owner of each table.

{
  "api": "hub",
  "action": "testIntegrationTableTransformSteps",
  "params": {


    "databaseName": "myDb1",
    "ownerName": "myOwner1",
    "tableName": "myTable1",


    "testDatabaseName": "myDb1",
    "testOwnerName": "myOwner1",
    "testTableName": "myTable2",


    "testTransformScope": "allRecords",
    "transformSteps": [
      {
        "transformStepMethod": "javascript",
        "codeName": "convertFahrenheitToCelsius"
      }
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

 

Response examples

Abbreviated response example for "testTransformScope": "allRecords"

To see the results of the action, you must query the test table created by this action.

{
  "result": {
    "testTransformScope": "allRecords",


    "databaseName": "myDb1",
    "ownerName": "myOwner1",
    "tableName": "myTable1",


    "testDatabaseName": "myDb1",
    "testOwnerName": "myOwner1",
    "testTableName": "myTable2"
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Abbreviated response example for "testTransformScope": "firstRecord"

{
  "result": {
    "testTransformScope": "firstRecord",


    "databaseName": "myDb1",
    "ownerName": "myOwner1",
    "tableName": "myTable1",


    "data": 
    [
      {


        "recordBeforeBeingTransformed": 
        {
          "id": "1",
          "changeId": "1291366",
          "create_ts": "2025-06-24T17:39:03.412",
          "source_payload": {
            "t1": 21,
            "t2": 54
          },
          "temperature": null
        },


        "recordAfterBeingTransformed": 
        {
          "id": "1",
          "changeId": "1291366",
          "create_ts": "2025-06-24T17:39:03.412",
          "source_payload": {
            "t1": 21,
            "t2": 54
          },
          "temperature": 21.54
        }
      }
    ]
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Abbreviated response example for "testTransformScope": "lastRecord"

{
  "result": {
    "testTransformScope": "lastRecord",


    "databaseName": "myDb1",
    "ownerName": "myOwner1",
    "tableName": "myTable1",


    "data": 
    [
      {
        "recordBeforeBeingTransformed": 
        {
          "id": "948",
          "changeId": "1291366",
          "create_ts": "2025-06-24T17:39:03.412",
          "source_payload": {
            "t1": 23,
            "t2": 7
          },
          "temperature": null
        },
        "recordAfterBeingTransformed": 
        {
          "id": "948",
          "changeId": "1291366",
          "create_ts": "2025-06-24T17:39:03.412",
          "source_payload": {
            "t1": 23,
            "t2": 7
          },
          "temperature": 23.7
        }
      }
    ]
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Abbreviated response example for "testTransformScope": "specifiedRecords"

{
  "result": {
    "testTransformScope": "specifiedRecords",


    "databaseName": "myDb1",
    "ownerName": "myOwner1",
    "tableName": "myTable1",


    "data": 
    [
      {
        "recordBeforeBeingTransformed": 
        {
          "id": "1",
          "changeId": "1291366",
          "create_ts": "2025-06-24T17:39:03.412",
          "source_payload": {
            "t1": 21,
            "t2": 54
          },
          "temperature": null
        },
        "recordAfterBeingTransformed": 
        {
          "id": "1",
          "changeId": "1291366",
          "create_ts": "2025-06-24T17:39:03.412",
          "source_payload": {
            "t1": 21,
            "t2": 54
          },
          "temperature": 21.54
        }
      },
      
      {
        "recordBeforeBeingTransformed": 
        {
          "id": "3",
          "changeId": "1291547",
          "create_ts": "2025-06-24T17:40:41.002",
          "source_payload": {
            "t1": 21,
            "t2": 61
          },
          "temperature": null
        },
        "recordAfterBeingTransformed": 
        {
          "id": "3",
          "changeId": "1291547",
          "create_ts": "2025-06-24T17:40:41.002",
          "source_payload": {
            "t1": 21,
            "t2": 61
          },
          "temperature": 21.61
        }
      }


    ]
  },
  "errorCode": 0,
  "errorMessage": "",
  "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

ids

The "ids" property specifies 0 or more ids. Each identifier in the array uniquely specifies a table row, indicating which records the action affects. It is required if the "primaryKeys" property is "null" or not specified.

  • The "ids" property is mutually exclusive with the "primaryKeys" property meaning it is required when "primaryKeys" is omitted or an error is returned if both have values.
  • It is typically an array of integers ("ids": [1,3,5]).
  • It can be an array of an array of strings ("ids": ["9555444333222111","9555444333222112", "9555444333222113"]).
    • A string "id" supports numbers larger than 9,007,199,254,740,991.
    • This is the largest number supported by many programming languages and JSON parser implementations that use IEEE double-precision floats to hold numbers.
  • It can be the primary key value of another field in the table making it useful when your table is created by another API, such as SQL, that allows any field in the table to be the primary key.
    • If your table does not have an "id" field but uses a "vin" field as the primary key, you can use vin values to look up records ("ids": [ "4Y1SL65848Z411439", "1HGBH41JXMN109186" ]).
  • If your table uses more than one field as the primary key, you must use the "primaryKeys" property to look up records.

Tip The "getRecordsByIds" action uses a primary key index to look up records. A primary key index must be a unique, non-null index without conditional filtering. For best performance and maximum simplicity, create tables using the JSON DB API because it automatically creates an auto increment "id" field that is indexed as a primary key.

Optional with default of "null".

Required when "primaryKeys" is omitted

 array 0 or more ids

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

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

testDatabaseName

The "testDatabaseName" property specifies the database of the test table where this action tests the transform steps.

For more information, see the "createSession" action and the "defaultDatabaseName" property.

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

 Optional with default of the "defaultDatabaseName" defined in the "createSession" action. string 1 to 64 bytes

testOwnerName

The "testOwnerName" property specifies the owner account of the test table where this action tests the transform steps.

For more information, see the "createSession" action and the "defaultOwnerName" property.

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

 Optional with default of the "defaultOwnerName" defined in the "createSession" action. string 1 to 64 bytes

testTableName

The "testTableName" property specifies the name of the test table where this action tests the transform steps.

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

 Required - No default value string 1 to 64 bytes

testTransformScope

The "testTransformScope" property defines the behavior of this action. For more details, see "testTransformScope".

 Required - No default value string enum

"allRecords"

"stop"

"firstRecord"

"lastRecord"

"specificRecords"

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

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

 

Response properties ("result")

Property Description Type Limits (inclusive)

data

The "data" property contains a response message. Its contents are defined by the action. It is an empty array when no results are available. The following is an example of the data property from a code package action.

  "result": {
    "data": [
      {
        "codeId": 6,
        "databaseName": "faircom",
        "ownerName": "admin",
        "codeName": "convertAndCategorizeTemperature",
        "codeVersion": 1,
        "clonedCodeId": 1,
        "codeStatus": "active",
        "codeLanguage": "javascript",
        "serviceName": "javascript",
        "codeType": "module",
        "description": "optional new description",
        "metadata": {},
        "createdBy": "ADMIN",
        "createdOn": "2025-08-25T21:48:38.109",
        "updatedBy": "ADMIN",
        "updatedOn": "2025-08-25T21:48:38.109",
        "comment": "Cloned from convertTemperature",
        "codeFormat": "utf8"
      },
    ]
 array of objects The action determines its contents.

data

.recordBeforeBeingTransformed

The "recordBeforeBeingTransformed" property contains an object with one property for each field in the record. Each property value is from the record before it was modified by the transform steps.

 object 0 or more properties

databaseName

The "databaseName" property specifies the database that contains the tables. 

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

  • If the "databaseName" property is omitted or set to null, the server will use the default database name specified at login.
  • If no default database is specified during "createSession", "databaseName" will be set to the "defaultDatabaseName" value that is specified in the services.json file.
  • This property's value is case insensitive. 
 string 1 to 64 bytes

ownerName

 The "ownerName" property identifies the user who owns an object (see Object owner).  string 0 to 64 bytes

tableName

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

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

 string 1 to 64 bytes

testDatabaseName

The "testDatabaseName" property specifies the database for the test table used by this action to test the transform steps.  

When set to null or omitted, the server uses the default "databaseName" defined in the "createSession" action. For more information, see the "createSession" action and the "defaultDatabaseName" property.

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

 string 1 to 64 bytes

testOwnerName

The "testOwnerName" property specifies the owner account for the test table used to test the transform steps.  

When set to null or omitted, the server uses the default "ownerName" defined in the "createSession" action. For more information, see the "createSession" action and the "defaultOwnerName" property.

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

 string 1 to 64 bytes

testTableName

The "testTableName" property specifies the name of the test table used to test the transform steps.

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

 

 string 1 to 64 bytes

testTransformScope

 The "testTransformScope" property is the same value specified in the call to the "testIntegrationTableTransformSteps" to the action. string enum

"allRecords"

"stop"

"firstRecord"

"lastRecord"

"specificRecords"

 

 

Was this article helpful?

Give feedback about this article

Related Articles