Skip to main content

"alterTable"

The JSON DB API "alterTable" action modifies an existing table

Abstract

The "alterTable" action alters, changes, modifies, updates, or configures an existing table in a database. This action works for all FairCom products that include the FairCom database engine.

The "alterTable" action alters, changes, modifies, updates, or configures an existing table in a database. This action works for all FairCom products that include the FairCom database engine.

Request examples

Add fields request example

This example uses "alterTable" to define new fields of a table.

{
  "api": "db",
  "authToken": "replaceWithAuthtokenFromCreateSession",
  "action": "alterTable",
  "params": {
    "tableName": "test1",
    "addFields": [
      {
        "name": "rename_me1",
        "type": "varchar",
        "length": 10,
        "nullable": false
      },
      {
        "name": "rename_me2",
        "type": "varchar",
        "length": 10,
        "nullable": false
      },
      {
        "name": "alter_me1",
        "type": "varchar",
        "length": 10,
        "nullable": false
      },
      {
        "name": "alter_me2",
        "type": "varchar",
        "length": 10,
        "nullable": false
      },
      {
        "name": "delete_me1",
        "type": "varchar",
        "length": 10,
        "nullable": false
      },
      {
        "name": "delete_me2",
        "type": "varchar",
        "length": 10,
        "nullable": false
      }
    ]
  }
}

This example uses "alterTable" to modify existing fields of a table. Specifically, it changes the name of the "rename_me1" field, to "description" and changes the "length" value of the "alter_me1" field from 10 to 50 and the "nullable" value from false to true.

{
  "api": "db",
  "authToken": "replaceWithAuthtokenFromCreateSession",
  "action": "alterTable",
  "params": {
    "databaseName": "ctreeSQL",
    "ownerName": "admin",
    "tableName": "test1",
    "alterFields": [
      {
        "name": "rename_me1",
        "type": "varchar",
        "newName": "description"
      },
      {
        "name": "alter_me1",
        "type": "varchar",
        "length": 50,
        "nullable": true
      }
    ]
  },
  "requestId": "2"
}

This example uses "alterTable" to remove the "delete_me1" field.

{
  "requestId": "3",
  "authToken": "replaceWithAuthtokenFromCreateSession",
  "api": "db",
  "action": "alterTable",
  "params": {
    "databaseName": "ctreeSQL",
    "ownerName": "admin",
    "tableName": "test1",
    "deleteFields": [
      "delete_me1"
    ]
  }
}
{
  "api": "db",
  "apiVersion": "1.0",
  "requestId": "4",
  "authToken": "replaceWithAuthtokenFromCreateSession",
  "action": "alterTable",
  "params": {
    "databaseName": "ctreeSQL",
    "ownerName": "admin",
    "tableName": "test1",
    "transactionModel": "logTransactions"
  }
}
{
  "api": "db",
  "apiVersion": "1.0",
  "requestId": "5",
  "authToken": "replaceWithAuthtokenFromCreateSession",
  "action": "alterTable",
  "params": {
    "databaseName": "ctreeSQL",
    "ownerName": "admin",
    "tableName": "test1",
    "addFields": [
      {
        "name": "nested_json_object_or_array",
        "type": "json",
        "length": 65500
      },
      {
        "name": "boolean_byte",
        "type": "bit"
      },
      {
        "name": "signed_int8",
        "type": "tinyint"
      },
      {
        "name": "signed_int16",
        "type": "smallint"
      },
      {
        "name": "signed_int32",
        "type": "integer"
      },
      {
        "name": "signed_int64",
        "type": "bigint"
      },
      {
        "name": "iee_base2float32",
        "type": "real"
      },
      {
        "name": "iee_base2float64",
        "type": "float"
      },
      {
        "name": "signed32digits_base10_left32right0",
        "type": "number",
        "length": 32,
        "scale": 0
      },
      {
        "name": "signed32digits_base10_left0right32",
        "type": "number",
        "length": 32,
        "scale": 32
      },
      {
        "name": "signed32digits_base10_left20right12",
        "type": "number",
        "length": 32,
        "scale": 12
      },
      {
        "name": "signed32digits_base10_left30right2",
        "type": "money",
        "length": 32,
        "scale": 2
      },
      {
        "name": "signed32digits_base10_left28right4",
        "type": "money",
        "length": 32,
        "scale": 4
      },
      {
        "name": "date_yyyymmdd",
        "type": "date"
      },
      {
        "name": "time_hhmmss",
        "type": "time"
      },
      {
        "name": "datetime_yyyymmddthhmmss_nnnz",
        "type": "timestamp"
      },
      {
        "name": "fixed_string_10bytes",
        "type": "char",
        "length": 10
      },
      {
        "name": "variable_string_up_to_max65500bytes",
        "type": "varchar",
        "length": 65500
      },
      {
        "name": "variable_string_up_to_2GB",
        "type": "lvarchar"
      },
      {
        "name": "fixed_binary_10bytes",
        "type": "binary",
        "length": 10
      },
      {
        "name": "variable_binary_up_to_max65500bytes",
        "type": "varbinary",
        "length": 65500
      },
      {
        "name": "variable_binary_up_to_2GB",
        "type": "lvarbinary"
      }
    ],
    "alterFields": [
      {
        "name": "alter_me2",
        "type": "varchar",
        "length": 50,
        "nullable": true
      }
    ],
    "deleteFields": [
      "delete_me2"
    ],
    "transactionModel": "logTransactions",
    "growthExtent": 100000,
    "padValue": 0,
    "fieldDelimiterValue": 0,
    "createRecByteIndex": false,
    "tableFileExtension": ".dat",
    "indexFileExtension": ".idx"
  },
  "responseOptions": {
    "binaryFormat": "hex",
    "dataFormat": "objects",
    "numberFormat": "string"
  },
  "debug": "max"
}

Response examples

{
  "authToken": "authToken",
  "result": {
    "dataFormat": "objects",
    "data": [
      {
        "changeIdField": "changeId",
        "createRecByteIndex": false,
        "databaseName": "ctreeSQL",
        "fieldDelimiterValue": 0,
        "fields": [
          {
            "autoValue": "incrementOnInsert",
            "defaultValue": null,
            "length": null,
            "name": "id",
            "nullable": false,
            "primaryKey": 1,
            "scale": null,
            "type": "bigint"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": null,
            "name": "changeid",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "bigint"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 50,
            "name": "name",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 10,
            "name": "rename_me1",
            "nullable": false,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 10,
            "name": "rename_me2",
            "nullable": false,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 10,
            "name": "alter_me1",
            "nullable": false,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 10,
            "name": "alter_me2",
            "nullable": false,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 10,
            "name": "delete_me1",
            "nullable": false,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 10,
            "name": "delete_me2",
            "nullable": false,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          }
        ],
        "folder": ".\\ctreeSQL.dbs",
        "growthExtent": 0,
        "indexFileExtension": ".idx",
        "indexes": [
          {
            "collectStats": false,
            "compression": "off",
            "conditionalExpression": null,
            "databaseName": "ctreeSQL",
            "deferIndexing": false,
            "fields": [
              {
                "caseInsensitive": false,
                "name": "id",
                "reverseCompare": false,
                "sortDescending": false
              }
            ],
            "filename": "admin_test1.idx",
            "immutableKeys": false,
            "indexName": "id_pk",
            "indexNumber": 0,
            "ownerName": "admin",
            "tableName": "test1",
            "unique": true
          }
        ],
        "ownerName": "admin",
        "padValue": 0,
        "path": ".\\ctreeSQL.dbs\\admin_test1.dat",
        "primaryKeyFields": [
          "id"
        ],
        "smallFile": false,
        "tableFileExtension": ".dat",
        "tableName": "test1",
        "totalRecordCount": 12,
        "transactionModel": "logTransactions",
        "uid": 1181
      }
    ]
  },
  "errorCode": 0,
  "errorMessage": ""
}
{
  "authToken": "authToken",
  "result": {
    "dataFormat": "objects",
    "data": [
      {
        "changeIdField": "changeId",
        "createRecByteIndex": false,
        "databaseName": "ctreeSQL",
        "fieldDelimiterValue": 0,
        "fields": [
          {
            "autoValue": "incrementOnInsert",
            "defaultValue": null,
            "length": null,
            "name": "id",
            "nullable": false,
            "primaryKey": 1,
            "scale": null,
            "type": "bigint"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": null,
            "name": "changeid",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "bigint"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 50,
            "name": "name",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 10,
            "name": "description",
            "nullable": false,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 10,
            "name": "rename_me2",
            "nullable": false,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 50,
            "name": "alter_me1",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 10,
            "name": "alter_me2",
            "nullable": false,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 10,
            "name": "delete_me1",
            "nullable": false,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 10,
            "name": "delete_me2",
            "nullable": false,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          }
        ],
        "folder": ".\\ctreeSQL.dbs",
        "growthExtent": 0,
        "indexFileExtension": ".idx",
        "indexes": [
          {
            "collectStats": false,
            "compression": "off",
            "conditionalExpression": null,
            "databaseName": "ctreeSQL",
            "deferIndexing": false,
            "fields": [
              {
                "caseInsensitive": false,
                "name": "id",
                "reverseCompare": false,
                "sortDescending": false
              }
            ],
            "filename": "admin_test1.idx",
            "immutableKeys": false,
            "indexName": "id_pk",
            "indexNumber": 0,
            "ownerName": "admin",
            "tableName": "test1",
            "unique": true
          }
        ],
        "ownerName": "admin",
        "padValue": 0,
        "path": ".\\ctreeSQL.dbs\\admin_test1.dat",
        "primaryKeyFields": [
          "id"
        ],
        "smallFile": false,
        "tableFileExtension": ".dat",
        "tableName": "test1",
        "totalRecordCount": 12,
        "transactionModel": "logTransactions",
        "uid": 1181
      }
    ]
  },
  "requestId": "2",
  "errorCode": 0,
  "errorMessage": ""
}
{
  "authToken": "authToken",
  "result": {
    "dataFormat": "objects",
    "data": [
      {
        "changeIdField": "changeId",
        "createRecByteIndex": false,
        "databaseName": "ctreeSQL",
        "fieldDelimiterValue": 0,
        "fields": [
          {
            "autoValue": "incrementOnInsert",
            "defaultValue": null,
            "length": null,
            "name": "id",
            "nullable": false,
            "primaryKey": 1,
            "scale": null,
            "type": "bigint"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": null,
            "name": "changeid",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "bigint"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 50,
            "name": "name",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 10,
            "name": "description",
            "nullable": false,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 10,
            "name": "rename_me2",
            "nullable": false,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 50,
            "name": "alter_me1",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 10,
            "name": "alter_me2",
            "nullable": false,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 10,
            "name": "delete_me2",
            "nullable": false,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          }
        ],
        "folder": ".\\ctreeSQL.dbs",
        "growthExtent": 0,
        "indexFileExtension": ".idx",
        "indexes": [
          {
            "collectStats": false,
            "compression": "off",
            "conditionalExpression": null,
            "databaseName": "ctreeSQL",
            "deferIndexing": false,
            "fields": [
              {
                "caseInsensitive": false,
                "name": "id",
                "reverseCompare": false,
                "sortDescending": false
              }
            ],
            "filename": "admin_test1.idx",
            "immutableKeys": false,
            "indexName": "id_pk",
            "indexNumber": 0,
            "ownerName": "admin",
            "tableName": "test1",
            "unique": true
          }
        ],
        "ownerName": "admin",
        "padValue": 0,
        "path": ".\\ctreeSQL.dbs\\admin_test1.dat",
        "primaryKeyFields": [
          "id"
        ],
        "smallFile": false,
        "tableFileExtension": ".dat",
        "tableName": "test1",
        "totalRecordCount": 12,
        "transactionModel": "logTransactions",
        "uid": 1181
      }
    ]
  },
  "requestId": "3",
  "errorCode": 0,
  "errorMessage": ""
}
{
  "authToken": "authToken",
  "result": {
    "dataFormat": "objects",
    "data": [
      {
        "changeIdField": "changeId",
        "createRecByteIndex": false,
        "databaseName": "ctreeSQL",
        "fieldDelimiterValue": 0,
        "fields": [
          {
            "autoValue": "incrementOnInsert",
            "defaultValue": null,
            "length": null,
            "name": "id",
            "nullable": false,
            "primaryKey": 1,
            "scale": null,
            "type": "bigint"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": null,
            "name": "changeid",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "bigint"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 50,
            "name": "name",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 10,
            "name": "description",
            "nullable": false,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 10,
            "name": "rename_me2",
            "nullable": false,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 50,
            "name": "alter_me1",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 10,
            "name": "alter_me2",
            "nullable": false,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 10,
            "name": "delete_me2",
            "nullable": false,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          }
        ],
        "folder": ".\\ctreeSQL.dbs",
        "growthExtent": 0,
        "indexFileExtension": ".idx",
        "indexes": [
          {
            "collectStats": false,
            "compression": "off",
            "conditionalExpression": null,
            "databaseName": "ctreeSQL",
            "deferIndexing": false,
            "fields": [
              {
                "caseInsensitive": false,
                "name": "id",
                "reverseCompare": false,
                "sortDescending": false
              }
            ],
            "filename": "admin_test1.idx",
            "immutableKeys": false,
            "indexName": "id_pk",
            "indexNumber": 0,
            "ownerName": "admin",
            "tableName": "test1",
            "unique": true
          }
        ],
        "ownerName": "admin",
        "padValue": 0,
        "path": ".\\ctreeSQL.dbs\\admin_test1.dat",
        "primaryKeyFields": [
          "id"
        ],
        "smallFile": false,
        "tableFileExtension": ".dat",
        "tableName": "test1",
        "totalRecordCount": 12,
        "transactionModel": "logTransactions",
        "uid": 1181
      }
    ]
  },
  "requestId": "4",
  "errorCode": 0,
  "errorMessage": ""
}
{
  "authToken": "authToken",
  "result": {
    "dataFormat": "objects",
    "data": [
      {
        "changeIdField": "changeId",
        "createRecByteIndex": false,
        "databaseName": "ctreeSQL",
        "fieldDelimiterValue": 0,
        "fields": [
          {
            "autoValue": "incrementOnInsert",
            "defaultValue": null,
            "length": null,
            "name": "id",
            "nullable": false,
            "primaryKey": 1,
            "scale": null,
            "type": "bigint"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": null,
            "name": "changeid",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "bigint"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 50,
            "name": "name",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 10,
            "name": "description",
            "nullable": false,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 10,
            "name": "rename_me2",
            "nullable": false,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 50,
            "name": "alter_me1",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 50,
            "name": "alter_me2",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 65500,
            "name": "nested_json_object_or_array",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "json"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": null,
            "name": "boolean_byte",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "bit"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": null,
            "name": "signed_int8",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "tinyint"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": null,
            "name": "signed_int16",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "smallint"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": null,
            "name": "signed_int32",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "integer"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": null,
            "name": "signed_int64",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "bigint"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": null,
            "name": "iee_base2float32",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "real"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": null,
            "name": "iee_base2float64",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "float"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 32,
            "name": "signed32digits_base10_left32right0",
            "nullable": true,
            "primaryKey": 0,
            "scale": 0,
            "type": "number"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 32,
            "name": "signed32digits_base10_left0right32",
            "nullable": true,
            "primaryKey": 0,
            "scale": 32,
            "type": "number"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 32,
            "name": "signed32digits_base10_left20right12",
            "nullable": true,
            "primaryKey": 0,
            "scale": 12,
            "type": "number"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 32,
            "name": "signed32digits_base10_left30right2",
            "nullable": true,
            "primaryKey": 0,
            "scale": 2,
            "type": "money"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 32,
            "name": "signed32digits_base10_left28right4",
            "nullable": true,
            "primaryKey": 0,
            "scale": 4,
            "type": "money"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": null,
            "name": "date_yyyymmdd",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "date"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": null,
            "name": "time_hhmmss",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "time"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": null,
            "name": "datetime_yyyymmddthhmmss_nnnz",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "timestamp"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 10,
            "name": "fixed_string_10bytes",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "char"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 65500,
            "name": "variable_string_up_to_max65500bytes",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "varchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": null,
            "name": "variable_string_up_to_2gb",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "lvarchar"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 10,
            "name": "fixed_binary_10bytes",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "binary"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": 65500,
            "name": "variable_binary_up_to_max65500bytes",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "varbinary"
          },
          {
            "autoValue": "none",
            "defaultValue": null,
            "length": null,
            "name": "variable_binary_up_to_2gb",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "lvarbinary"
          }
        ],
        "folder": ".\\ctreeSQL.dbs",
        "growthExtent": 100000,
        "indexFileExtension": ".idx",
        "indexes": [
          {
            "collectStats": false,
            "compression": "off",
            "conditionalExpression": null,
            "databaseName": "ctreeSQL",
            "deferIndexing": false,
            "fields": [
              {
                "caseInsensitive": false,
                "name": "id",
                "reverseCompare": false,
                "sortDescending": false
              }
            ],
            "filename": "admin_test1.idx",
            "immutableKeys": false,
            "indexName": "id_pk",
            "indexNumber": 0,
            "ownerName": "admin",
            "tableName": "test1",
            "unique": true
          }
        ],
        "ownerName": "admin",
        "padValue": 0,
        "path": ".\\ctreeSQL.dbs\\admin_test1.dat",
        "primaryKeyFields": [
          "id"
        ],
        "smallFile": false,
        "tableFileExtension": ".dat",
        "tableName": "test1",
        "totalRecordCount": 12,
        "transactionModel": "logTransactions",
        "uid": 1181
      }
    ]
  },
  "requestId": "5",
  "debugInfo": {
    "request": {
      "authToken": "authToken",
      "api": "db",
      "action": "alterTable",
      "params": {
        "databaseName": "ctreeSQL",
        "ownerName": "admin",
        "tableName": "test1",
        "addFields": [
          {
            "name": "nested_json_object_or_array",
            "type": "json",
            "length": 65500
          },
          {
            "name": "boolean_byte",
            "type": "bit"
          },
          {
            "name": "signed_int8",
            "type": "tinyint"
          },
          {
            "name": "signed_int16",
            "type": "smallint"
          },
          {
            "name": "signed_int32",
            "type": "integer"
          },
          {
            "name": "signed_int64",
            "type": "bigint"
          },
          {
            "name": "iee_base2float32",
            "type": "real"
          },
          {
            "name": "iee_base2float64",
            "type": "float"
          },
          {
            "name": "signed32digits_base10_left32right0",
            "type": "number",
            "length": 32,
            "scale": 0
          },
          {
            "name": "signed32digits_base10_left0right32",
            "type": "number",
            "length": 32,
            "scale": 32
          },
          {
            "name": "signed32digits_base10_left20right12",
            "type": "number",
            "length": 32,
            "scale": 12
          },
          {
            "name": "signed32digits_base10_left30right2",
            "type": "money",
            "length": 32,
            "scale": 2
          },
          {
            "name": "signed32digits_base10_left28right4",
            "type": "money",
            "length": 32,
            "scale": 4
          },
          {
            "name": "date_yyyymmdd",
            "type": "date"
          },
          {
            "name": "time_hhmmss",
            "type": "time"
          },
          {
            "name": "datetime_yyyymmddthhmmss_nnnz",
            "type": "timestamp"
          },
          {
            "name": "fixed_string_10bytes",
            "type": "char",
            "length": 10
          },
          {
            "name": "variable_string_up_to_max65500bytes",
            "type": "varchar",
            "length": 65500
          },
          {
            "name": "variable_string_up_to_2GB",
            "type": "lvarchar"
          },
          {
            "name": "fixed_binary_10bytes",
            "type": "binary",
            "length": 10
          },
          {
            "name": "variable_binary_up_to_max65500bytes",
            "type": "varbinary",
            "length": 65500
          },
          {
            "name": "variable_binary_up_to_2GB",
            "type": "lvarbinary"
          }
        ],
        "alterFields": [
          {
            "name": "alter_me2",
            "type": "varchar",
            "length": 50,
            "nullable": true
          }
        ],
        "deleteFields": [
          "delete_me2"
        ],
        "transactionModel": "logTransactions",
        "growthExtent": 100000,
        "padValue": 0,
        "fieldDelimiterValue": 0,
        "createRecByteIndex": false,
        "tableFileExtension": ".dat",
        "indexFileExtension": ".idx"
      },
      "apiVersion": "1.0",
      "requestId": "5",
      "responseOptions": {
        "binaryFormat": "hex",
        "dataFormat": "objects",
        "numberFormat": "string"
      },
      "debug": "max"
    },
    "serverSuppliedValues": {
      "databaseName": "ctreeSQL",
      "ownerName": "admin"
    },
    "errorData": {
      "errorData": null
    },
    "warnings": []
  },
  "errorCode": 0,
  "errorMessage": ""
}

"params"

The "params" property is an object that contains an action's parameters. Each action defines its own required and optional properties.

Properties summary

Table 1. "params" properties summary

Property

Description

Default

Type

Limits (inclusive)

"addFields"

adds new fields to the table and specify their properties

[]

array of objects

"alterFields"

defines how to alter a field in a table

[{"name":""}]

array of objects

"changeIdField"

specifies the field name used for change-tracking

""

string

1 to 64 characters

"createRecByteIndex"

creates a special index for quickly walking variable-length records backward when true

false

Boolean

true
false

"databaseName"

contains the name of a database

Defaults to the "defaultDatabaseName" value that is set during "createSession". If no default is set during "createSession", then "faircom" is used.

string

Minimum length: 1
Maximum length: 64

"deleteFields"

contains the names of fields to remove from a table

[]

array

Minimum length: 1
Maximum length: 64

"fieldDelimiterValue"

delimits fields internally for FairCom use

0

integer

0-255

"growthExtent"

specifies the number of bytes that a server uses to extend a file by using a larger number to minimize the number of times a file needs to be extended or using a smaller number to minimize the amount of unused space in a file

0

integer

0 - 2,147,483,647

"indexFileExtension"

specifies the file system extension (must contain the dot before the extension) to use for a table's index files

".idx"

string

Minimum length: 0
Maximum length: 64

"ownerName"

contains the unique name of a schema in a database

""

string

Minimum length: 1
Maximum length: 64

"padValue"

pads all "char" and "binary" fields in a table

0

integer

0 - 255

"primaryKeyFields"

specifies the fields to use for the table’s primary key

[]

array of strings

["field1", …,"fieldN"]

"tableFileExtension"

specifies the file extension (must contain the dot before the extension) that the server assigns to the table's data file on the file system

".dat"

string

Minimum length: 0
Maximum length: 64

"tableName"

contains the name of a table

Required - No default value

string

Minimum length: 1
Maximum length 64

"transactionModel"

defines how the server processes transactions for a table

"logTransactions"

string

"logTransactions"
"ramTransactions"
"noTransactions"


The "addFields" property is optional. It allows you to add new fields to the table and specify their properties.

Example

    "addFields": 
    [
      {
        "name": "myFieldName", 
        "type": "VARCHAR", 
        "length": 128,
        "scale": 0,
        "nullable": true
      }
    ]

Property summary

Table 2. "addFields" properties summary

Property

Description

Default

Type

Limits (inclusive)

"autoValue"

controls when and how the server automatically sets the field value

""

string

See enumerated values

"length"

defines the length of a field's value in a record

1

integer

1 - 65,500

"name"

contains the name of a existing field in a table

""

string

Minimum length: 1
Maximum length: 64

"nullable"

allow a field to contain a NULL value when true

true

Boolean

true
false

"scale"

defines the number of places to the right of the decimal point

4

integer

0 - 32

"type"

defines the type of field

Required - No default value

string

"json"
"bit"
"tinyint"
"smallint"
"integer"
"bigint"
"real"
"float"
"number"
"money"
"date"
"time"
"timestamp"
"char"
"varchar"
"lvarchar"
"binary"
"varbinary"
"lvarbinary"


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

Specify only one of these values per field.

  • "none" indicates the server does not automatically set the field's value.

  • "incrementOnInsert" indicates the server automatically increments a field’s value each time the server inserts a new record. It applies to fields that are of the type of decimal or one of the integer types, such as "bigint". Only one field per table can have this attribute. The server returns an error when assigning this attribute to multiple fields. The JSON DB API automatically creates the "id" field as an "incrementOnInsert" field. If you apply this attribute to another field, it becomes the only automatically incremented field in the table. If you want that field to be the primary key, assign  "primaryKey": 1 to it.

  • "timestampOnInsert" indicates the server automatically sets a field’s value to the current date and time of an insert. It applies only to fields with a type of "timestamp".

  • "timestampOnUpdate" indicates the server automatically sets a field’s value to the current date and time of an update. It applies only to timestamp fields.

  • "timestampOnUpdateAndInsert" indicates the server automatically sets a field’s value to the current date and time of an insert and an update. It applies only to fields with a type of "timestamp".

  • "changeId" indicates the server uses the field for optimistic locking. The server automatically sets the field's value to the internal transaction number used during the last update of the record. This value changes each time the server updates the record. A table may only have one change tracking field. The field type must be "bigint".

    The JSON DB API automatically creates a "changeid" field with change-tracking functionality.

    Change tracking is optional in the CTDB and ISAM APIs. The application must create a 64-bit integer field and assign change-tracking functionality to it.

The value of this property is the name of the field used for the change-tracking functionality.

If the table already has a change-tracking field, this new field is now used for change-tracking.

The "createRecByteIndex" property is an optional Boolean that creates a special index for quickly walking variable-length records backward when set to true. It defaults to false.

Note

It is not needed for fixed-length records.

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

Note

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

Things to know:
  • A zero-length "databaseName" is invalid.

  • Its limits are from 0 to 64 bytes.

  • 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", "defaultDatabaseName" will be set to the "defaultDatabaseName" value that is specified in the services.json file.

The "deleteFields" property is an optional array of strings. Each string is the name of a field to remove from a table.

Things to know:
  • Each string is 1 to 64 bytes.

  • Removing a field destroys the data in that field.

  • A zero-length "fieldName" is invalid.

The "fieldDelimiterValue" property is a decimal number from 0 to 255. It is optional. If omitted, it defaults to 0. The only reason to change it is for backward compatibility with legacy c-tree files.

Things to know:
  • Set the optional "fixedLengthRecords" property to true to optimize the file structure to support only fixed-length fields and records.

    • Each record in the table has a fixed length. This can increase the speed of data processing, but it wastes disk space when strings are highly variable.

    • Fixed-length records can never contain variable-length fields, such as VARCHAR and VARBINARY.

    • Fixed-length records do not support Hot Alter Table, which allows a table’s definition to change by rewriting a record’s structure only when it is read or updated.

    • This command returns an error if any field is not fixed length.

  • The optional "growthExtent" property is the number of bytes a file is extended when adding a record exceeds the current size of the file. The default is 0 bytes, which means a server grows the file using its default algorithm.

  • The optional "folder" property is the file system path of a folder that will contain the table’s data file. The default is the database folder.

  • The optional "tableFileExtension" property defines the file extension to add to the table’s name to create the name of the table’s data file. The default is ".dat". Meaning a table named "table1" with an extension of ".tmp" and a folder of "/temp" stores its data in a data file named "/temp/table1.tmp".

  • The optional "indexFileExtension" property defines the file extension to add to all index files created on this table. default is ".idx". Meaning that an index named "index1" with an extension of ".idx" and a folder of "/data" stores the index content in an index file named "/data/index1.idx".

  • Set the optional "smallFile" property to true when you want to optimize for data files smaller than 4 GB. The default is false.

    • Small files cannot grow larger than 4 GB and larger data files are fairly common.

    • To change from a small file to a large file requires a complete file rebuild.

    • A huge file can grow to over 16 million terabytes. This ensures the file can handle any size of data.

    • A small file saves index space and increases performance. 

    • A small file requires four-bytes to reference the position of each record in a file.

    • A huge file requires eight bytes.

    • A small file saves space on disk and in RAM because each index entry for a small file is four bytes smaller than the same entry for a huge file. This space savings allows for more index entries to be cached and retrieved in a single IO. This increases the speed of inserts, updates, deletes, and index traversals.

  • The optional "compression" property defines the type of compression to apply to the table:

    • none (0 in NAV ENUM) -  No compression (default)

    • rle (100 in NAV ENUM) - Runtime Length Encoding reduces size when the data contains the following repeated characters:

      • space

      • 0

      • binary 0x00 (NULL)

    • zlib  (200 in NAV ENUM) - This algorithm is slow but becomes efficient when it compresses large amounts of data. For small records (< 200 bytes), the size of the compressed data can be larger than the original.

  • The optional "createRecByteIndex" property creates the special purpose RECBYT index when it is true. The default is false.

    • When false, add the NORECBYT table create option.

    • The RECBYT index enables backward physical traversal of variable-length records. Because it is an extra index, it slows inserts, updates, and deletes. Unless you need to walk variable-length records backward, disable the creation of this index.

  • The required "transactionModel" property assigns one transaction model to the table.

    The property must contain one of the following symbols or be omitted:

    • "logTransactions"

      This is the default. The table supports multi-statement ACID transactions and transaction logs. This is the most capable and durable table type. There is very little risk of data loss because committed data is first written to the transaction logs. The table provides atomicity, isolation, consistency, durability, and point-in-time recoverability. Data can be backed up while the database is running with complete data consistency and point-in-time recoverability. Data can be replicated to any other FairCom database.

      Use this for excellent durability, full transactional features, data replication, and good performance.

      • "ctTRNLOG"

      • 0 in a NAV ENUM

      • (Default)

    • "ramTransactions"

      The table supports transactions in RAM without transaction logs. Transactions are processed in memory instead of using transaction logs. Making "ramTransactions" about 10x faster than "logTransactions".  There is a risk of data loss between the time data is committed in memory and when the OS flushes the changes to disk. The table provides atomicity, isolation, and consistency. Data can be backed up while the database is running with complete data consistency but without point-in-time recoverability. Data cannot be replicated

      Use this when you need faster performance with transaction processing, but do not need data replication and are willing to potentially lose a few records.

      • ctPREIMG

      • 100 in a NAV ENUM

    • "noTransactions"

      The table does not support transactions and transaction logs. Data is processed at the fastest speeds. There is a risk of data loss between the time data is written and when the OS flushes it to disk. Data can be backed up while the database is running, but without index consistency and point-in-time recoverability. If data is written to the table during a backup, restoring the backup requires rebuilding the table’s indexes. There is no atomicity. There is some isolation using locks. Data cannot be replicated.

      Use this when you want the fastest possible performance, and do not need the benefits of transaction processing and data replication, and are also willing to potentially lose a few records.

      • CTCREATE_NORMAL

      • 200 in a NAV ENUM

  • In "responseOptions", specifying "dataFormat":"default" or omitting it, is the same as specifying "dataFormat": "objects".

The "growthExtent" property is an optional integer from 0 to 2,147,483,647. It is the number of bytes that a server uses to extend a file. The default value is 0.

Things to know:
  • A file is extended when adding or updating a record requires a file to grow larger.

  • Use a larger number to minimize the number of times a file needs to be extended.

  • Use a smaller number to minimize the amount of unused space in a file.

The "indexFileExtension" property is an optional string from 0 to 64 bytes. It specifies the file system extension to use for a table's index files. If omitted, it defaults to ".idx".

Note

If set to a zero-length string, then newly created index files will have no extension.

The "ownerName" property is an optional string from 1 to 64 bytes that specifies the account that owns an object.

Things to know:
  • The "ownerName" property is optional and has a dynamic default value.

  • If the "ownerName" property is omitted or set to null, the server uses the value of the "defaultOwnerName" property supplied during the "createSession" action.

  • If the "defaultOwnerName" property is not defined, the server uses the "admin" as the owner name.

  • The owner of an object has administrative rights over that object.

  • The "ownerName" property is a namespace for an object. You can think of it as a container of objects.

    The "ownerName" allows users to use any name for the objects they create — for example, a QA engineer may copy tables into their owner space to run a set of tests.

    It is common for a user to create their own copies of objects from other accounts for testing, troubleshooting, and fixing data. The copied objects can retain the same name because the "ownerName" distinguishes between them.

  • The fully qualified name of an object is the "databaseName", "ownerName", and the object's name, such as "tableName" meaning a FairCom server may contain many tables with the name "mytable" as long as each one is in a different database or in a different owner space.

    For example, an organization often creates different databases for different phases of the development lifecycle, such as dev, test, stage, ua, and prod. Each of these databases contains the same set of objects with the same names. Applications leave the "databaseName" out of their JSON actions and use the "defaultDatabaseName" property to specify the target database.

  • Queries and scripts are often written without specifying "databaseName" and/or "ownerName", allowing queries and scripts to work properly when run in different databases or in different schemas.

The "padValue" property is a decimal number from 0 to 255. It is optional. If omitted, it defaults to 0.

Things to know:
  • The server uses this decimal number as a byte value to pad all "char" and "binary" fields in a table when the contents of these fixed-length fields is shorter than the field size.

  • All fixed-length fields in a table are padded with the same pad value. 

    Note

    Padding does not apply to the variable-length fields of the table, including "varchar", "lvarchar", "varbinary", and "lvarbinary".

Reasons to use fixed-length fields:
  • Fixed-length fields are an optimization for the specific use case of storing fixed-length data that matches the exact length of the field.

  • For fixed-length fields, the following use cases work well:

    • Bit flags

      A "binary" field is useful for efficiently storing a fixed set of bit flags where each bit represents a different on/off setting.

    • Legacy identifiers

      A "char" field is useful for efficiently storing legacy fixed-length identifiers, such as a VIN number where characters in specific positions represent different subsets of data.

    • Very small strings

      A "char" field is useful for efficiently storing strings that are smaller than six characters because they do not have the overhead of requiring two extra bytes to track the length of the field.

  • A fixed-length field is more efficient than a variable-length field because it does not require extra bytes to track the length of the field:

    • The "varchar" and "varbinary" fields require two extra bytes to track the length of the field, which can be up to 64K. 

    • The "lvarchar", "lvarchar",  and "lvarbinary" fields require four extra bytes to track the length of the field, which can be up to 2GB.

Reasons padding is needed for fixed-length fields:
  • Padding is required for fixed-length fields because it is possible to assign data to a field that has a smaller length than the length of the field.

  • Padding is useful in the following use cases:

    • The default padding value of 0 in a "char" field makes a fixed-length string behave like a variable-length string. It works because the database can treat a fixed-length string as a null-terminated string that can be smaller than the fixed width.

    • A padding value of 32 in a "char" field is the ASCII space character, which automatically left-aligns fixed-length strings.

    • The default padding value of 0 in a "binary" field works for bit flags when zero is the desired default state of a bit. Otherwise, padding in a "binary" field is rarely a good thing because it alters the binary value.

How padding works:
  • When the server assigns a value to a "char" or "binary" field (which are fixed-length field types), it puts the value in the field starting on the left and uses the pad value to fill in any remaining bytes on the right.

  • In other words, when the number of bytes in a value being assigned to a "char" or "binary" field is less than the number of bytes in the field, the server fills in the remaining bytes with the pad value.

CHAR padding behavior:
  • The default pad value of 0 causes a special padding behavior for "char" fields.

    • It causes the JSON DB API to treat fixed-length string fields as if they were variable-length strings with a maximum length.

    • In other words, the same string value going into a "char" field comes out the same, no padding is added — for example, when a table's "padValue" is set to 0 and a JSON string of "12" is inserted into a "char" field, the JSON string of "12" is returned from that field.

  • When you want the server to add padding to strings that are stored in a "char" field, set the "padValue" property to a non-zero number that is the ASCII character you want to pad the string.

    For example, set "padValue" to 32 to pad strings with the space character. When a table's "padValue" is set to 32 and a JSON string of "12" is inserted into a "char" field with a "length" of 4 bytes, the JSON string of "12 " is returned from that field.

BINARY padding behavior:
  • The server always applies padding when the number of bytes in a binary value is less than the number of bytes in the fixed length, "binary" field. When the server adds padding to a binary value, it permanently modifies the binary value. If this is not desirable, ensure the binary value exactly matches the size of the "binary" field.

  • The JSON DB API always returns the entire stored value of a "binary" field. This binary value is encoded as a JSON string containing a base64 or hexadecimal representation of the binary value. The length of the JSON string encoded in base64 or hexadecimal is always longer than the length of the binary field.

  • If a table's "padValue" is set to 0, and a binary value of 0x3132 is stored in a "binary" field with a "length" of 4 bytes, the server pads the value and stores the value of 0x31320000 into the field. When the value is returned by the JSON DB API, it is returned as 0x31320000. Thus, padding alters a binary value when the value is smaller than the field. The default padding behavior of a "binary" field is different from the default padding behavior of a "char" field because the server can assume that an ASCII NULL padding value is a string terminator, but it cannot assume the same for a binary value.

Example 1. Turning off padding

If a table's "padValue" is set to 0, the ASCII NULL character with the hexadecimal value of 0x00 is used to pad unfilled byte positions. So when the JSON string "12" is inserted into a "char" field that has a "length" of 4 bytes, the binary value of 0x31320000 is stored in the field. When the JSON DB API retrieves the value, it returns it as JSON string of "12" because it recognizes the ASCII NULL character as a string terminator.

In other words, the ASCII NULL character allows the server to automatically trim the length of a string that is stored in a fixed-length field before it returns the string in JSON. So, by default, the same JSON string inserted into a "char" field is always returned by the JSON DB API. This behavior is safe for UTF-8 strings, which is the string encoding used by JSON.



Example 2. Padding s a string with spaces

When a table's "padValue" is set to 32, which is hexadecimal 0x20, and a JSON string of "12" is assigned to a "char" field that has a "length" of 4 bytes, the binary value of 0x31322020 is stored in the field. When the JSON DB API retrieves the value, it returns it as a JSON string of "12  ". This is because the value 32 is the ASCII space character " ".



Example 3. Padding a string with hyphens

If a table's "padValue" is set to 54, which is hexadecimal 0x36, and a JSON string of "12" is assigned to a "char" field that has a "length" of 4 bytes, the binary value of 0x31323636 is stored in the field. When the JSON DB API retrieves the value, it returns it as a JSON string of "12--". This is because the value 54 is the ASCII character "-".



Example 4. Manually padding a string

Assigning "12**" to a "char" field that has a "length" of 4 bytes, stores "12**" into the field regardless of the table's "padValue".



Example 5. Right-aligning a value in a string

Assigning " $12.34" to a "char" field that has a "length" of 8 bytes, stores " $12.34" into the field  regardless of the table's "padValue".



Example 6. Avoiding padding

Use the variable-length char and binary field types ("varchar", "lvarchar", "lvarchar", "varbinary", and "lvarbinary"). 

Each of these field types are stored in the table with length information, so the data returned matches the data stored.



This property specifies the fields to use for the table’s primary key.

The "primaryKeyFields" property creates a unique "primary key" index on these fields.
The index is named "field1_fieldN_pk" where field1 through fieldN are the names of the specified fields.
When the table already has a primary key index, altering a table with this property may create an additional "primary key index".
The user can use "deleteIndexes" to remove extra indexes.

The "tableName" property is an optional string that contains the name of an integration table or MQTT topic that holds the topic's messages. It defaults to an empty string.

Things to know:
  • You can use a topic name for an MQTT table because FairCom automatically generates a table name for each topic.

  • A "tableName" cannot begin with a number.

  • It refers to the name of the integration table that stores a topic’s messages and is used to rename that table or assign a new topic to an existing integration table.

  • MQTT automatically creates an integration table for each new topic it receives. Thus, when a message is sent to a topic, FairCom Edge automatically creates a table to hold it.

  • 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 "tableName" property of the "configureTopic" action to rename an integration table.

  • The "alterIntegrationTable" action can also be used to rename an integration table, but it is less convenient because you must know the original name of the integration table.

The "transactionModel" property is an optional, case-insensitive, string enum. It defines how the server processes transactions for a table. The default is "logTransactions".

Possible values:
  • "logTransactions"

    This persists data to data files and writes transaction changes to transaction logs. It supports commit and rollback transactions and data replication. It will not lose data during an unplanned outage. It provides the most durability and the most capabilities but is slower than the other settings.

  • "ramTransactions"

    This supports commit and rollback transactions in RAM while persisting data to data files. It does not use a transaction log, which makes it even faster, but an unplanned outage may lose or corrupt unwritten data.

  • "noTransactions"

    This does not support transactions but still persists data to disk. This makes it even faster, but an unplanned outage may lose or corrupt unwritten data.

"result"

Property summary

Table 3. "result" property summaries

Property

Description

Type

Limits (inclusive)

"data"

contains an array or object that the server returns, such as records returned by a query

Note

It is an empty array when there are no results available.

array

Its contents are determined by the action

"dataFormat"

defines the format of the data in the "data" property

string

"autoDetect"
"arrays"
"objects"


The "dataFormat" property is an optional, case-insensitive string enum that defines the format of the response in the "data" property. The default format is an array of arrays. The alternative is an array of objects. The default for "dataFormat" can be changed during a "createSession" action by assigning a different value to the "dataFormat" property in "defaultResponseOptions".

There are three different (but similar) versions of the "dataFormat" property:

Two of those versions occur in a request and another occurs in a response. They all indicate how data is formatted.

  • "dataFormat" in the request inside "responseOptions" determines how the "data" property in the response is formatted.

    Possible values include:

    • "arrays"

      This is the default and causes the server to return results as an array of arrays, which is the most efficient.

    • "objects"

      This returns results as an array of objects. This is less efficient but is simpler to generate, read, and troubleshoot.

  • "dataFormat" in the request in the "params" object notifies the server how the "sourceData" property is formatted in the request. This version is rarely used because of the default "autoDetect" behavior.

    Possible values include:

    • "arrays"

      This causes the server to return results as an array of arrays, which is the most efficient.

    • "objects"

      This returns results as an array of objects. This is less efficient but is simpler to generate, read, and troubleshoot.

    • "autoDetect"

      This is the default and causes the server to automatically detect the format of the data in the "sourceData" property.

  • "dataFormat" in the response shows the client how the server formatted the "data" property.

    Possible values include:

    • "arrays"

      This is the default and causes the server to return results as an array of arrays, which is the most efficient.

    • "objects"

      This returns results as an array of objects. This is less efficient but is simpler to generate, read, and troubleshoot.

Troubleshooting errors

You cannot modify a table while it is in use. When a table has no activity for a default of 2 seconds, it will be closed so you can modify it.

  • When a table is in use, the following actions return an error, such as -8 or 4012:

    • "alterTable"

    • "rebuildTables"

    • "deleteTables"

    • "createIndex"

    • "deleteIndexes"

    • "rebuildIndexes"

    • "runSqlStatements"

  • A table is in use when any account is performing one or more of the following actions with the table:

    • "insertRecords"

    • "updateRecords"

    • "deleteRecords"

    • "truncateRecords"

    • "getRecords..."

    • Has open cursors on the table.

    • Has open transactions on the table (such "createTransaction").

  • Modify the SQL_IDLE_WAKE startup configuration setting to change the number of seconds the server waits before closing a table. A larger number keeps the table open longer for better performance. A smaller number allows you to modify the table sooner.

  • Modify the SQL_IDLE_WAKE startup configuration setting to change the number of tables the server keeps open after they are no longer being actively used. A larger number caches more tables and improves performance. A smaller number allows you to modify tables sooner.