`"deleteRecords"` (JSON Action)

JSON DB `"deleteRecords"` action deletes records from a table using its primary key

The "deleteRecords" action deletes records from a database table using its primary key, such as the id field.

Essential information

The action returns the values from the deleted records in case you want to insert them again, view them, or verify the correct records are deleted.
If the specified records do not exist, the action returns success because this is the desired end result.
The "totalRecordCount" property in the response is the number of records that are deleted by this action.
A table must have a primary key to delete records.
- Tables created by the "createTable" action, automatically have an auto increment "id" field indexed as primary key.
- Tables created by other APIs may not have an "id" field; instead, they may have a primary key index that includes one or more fields in the table.
- The "deleteRecords" action provides an optional "primaryKeys" property that can retrieve records using any primary key index.
- The "primaryKeys" and "ids" properties are mutually exclusive.
Use the "primaryKeys" property to delete records from a table that uses a natural primary key instead of the identity field.
- The outer array allows you to delete multiple records.
- The inner array defines the field values of each primary key field. It must be an array because primary keys may contain multiple fields.

Request examples

Minimal

{
  "api": "db",
  "action": "deleteRecords",
  "params": {
    "tableName": "test1",
    "ids": [
      1,
      2
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Delete one record using a multi-field primary key

{
  "api": "db",
  "action": "deleteRecords",
  "params":
  {
    "tableName": "pk_example",
    "primaryKeys": 
    [ 
      [
        {
          "fieldName": "first_name",
          "value": "Sam"
        },
        {
          "fieldName": "last_name",
          "value": "I-am"
        }
      ]
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Delete multiple records using a multi-field primary key

This example deletes two records. The outer array identifies each record. The inner array specifies the field values of each record’s primary key: "first_name" and "last_name".

{
  "api": "db",
  "action": "deleteRecords",
  "params":
  {
    "tableName": "pk_example",
    "primaryKeys": 
    [ 
      [
        {
          "fieldName": "first_name",
          "value": "Sam"
        },
        {
          "fieldName": "last_name",
          "value": "I-am"
        }
      ],
      [
        {
          "fieldName": "first_name",
          "value": "The Cat"
        },
        {
          "fieldName": "last_name",
          "value": "in the Hat"
        }
      ]
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Maximal

{
  "api": "db",
  "apiVersion": "1.0",
  "requestId": "4",
  "action": "deleteRecords",
  "params": {
    "databaseName": "ctreeSQL",
    "ownerName": "admin",
    "tableName": "test1",
    "ids": [
      1,
      2
    ]
  },
  "responseOptions": {
    "binaryFormat": "hex",
    "dataFormat": "objects",
    "numberFormat": "string",
    "includeFields": [],
    "excludeFields": []
  },
  "debug": "max",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Response examples

Note

If the target object does not exist, the response returns success, and the debug property contains a warning that the object was not found.

Minimal

{
  "result": {
    "dataFormat": "objects",
    "binaryFormat": "hex",
    "fields": [
      {
        "name": "id",
        "type": "bigint",
        "length": null,
        "scale": null,
        "defaultValue": null,
        "nullable": false,
        "primaryKey": 1,
        "autoValue": "incrementOnInsert"
      },
      {
        "name": "changeId",
        "type": "bigint",
        "length": null,
        "scale": null,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "changeId"
      },
      {
        "name": "name",
        "type": "varchar",
        "length": 50,
        "scale": null,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "none"
      }
    ],
    "data": [],
    "primaryKeyFields": [
      "id"
    ],
    "changeIdField": "changeId",
    "totalRecordCount": 0
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Maximal

{
  "result": {
      "dataFormat": "objects",
      "binaryFormat": "hexadecimal",
      "fields": [
          {
              "name": "id",
              "type": "bigint",
              "length": null,
              "scale": null,
              "autoTimestamp": "none",
              "defaultValue": null,
              "nullable": false
          },
          {
              "name": "changeId",
              "type": "bigint",
              "length": null,
              "scale": null,
              "autoTimestamp": "none",
              "defaultValue": null,
              "nullable": true
          },
          {
              "name": "name",
              "type": "varchar",
              "length": 50,
              "scale": null,
              "autoTimestamp": "none",
              "defaultValue": null,
              "nullable": true
          }
      ],
      "data": [
          {
              "changeId": 22788,
              "id": 2,
              "name": "jane"
          }
      ],
      "totalRecordCount": 1
  },
  "requestId": "4",
  "debugInfo": {
      "request": {
          "api": "db",
          "action": "deleteRecords",
          "params": {
              "databaseName": "ctreeSQL",
              "ownerName": "admin",
              "tableName": "test1",
              "ids": [
                  2
              ]
          },
          "apiVersion": "1.0",
          "requestId": "4",
          "responseOptions": {},
          "debug": "max",
          "authToken": "replaceWithAuthTokenFromCreateSession"
      },
      "serverSuppliedValues": {
          "databaseName": "ctreeSQL",
          "ownerName": "admin"
      },
      "errorData": {
          "errorData": null
      },
      "warnings": []
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

The JSON Action JSON DB API "deleteRecords" action deletes records from a database table.

API actionsJSON DB APIJSON Actiondelete recordsdeleteRecords

`"params"`

The "params" property is an object that contains an action's request parameters as defined by a set of properties. Each action defines its own required and optional properties. See System limits for a comprehensive overview of property requirements and limitations.

Table 1. deleteRecords "params" properties summary

Property	Description	Default	Type	Limits (inclusive)
databaseName	(optional) specifies 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	1 to 64 bytes
ids	(optional) specifies a unique identifier for a row in a table. It is mutually exclusive with `"primaryKeys"`.	`"null"` It is required when `"primaryKeys"` is omitted.	array
ownerName	(optional) specifies the unique name of a schema in a database.	`""`	string	1 to 64 bytes
primaryKeys	(conditional) specifies values of the fields used to form a multi-field primary key. It is mutually exclusive with `"ids"`.	`"null"` It is required when `"ids"` is omitted.	array of arrays
tableName	specifies the name of a table	Required - No default value	string	1 to 64 bytes

`"databaseName"`

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.

A zero-length "databaseName" is invalid.
Its length limit is 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", "databaseName" will be set to the "defaultDatabaseName" value that is specified in the services.json file.

"params": {
  "databaseName": "mainDatabase"
  }

`"ids"`

The "ids" property is an array. Each identifier in the array uniquely specifies a table row, indicating which records the action affects. Its default value is "null" but 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.

`"ownerName"`

The "ownerName" property is an optional string from 1 to 64 bytes that identifies the user who owns an object (see Object owner). If it is omitted or set to "" or null, the server uses the default owner name supplied during the "createSession" action or uses the account's "username" as the owner name.

"params": {
  "ownerName": "SuperUser"
}

`"primaryKeys"`

The "primaryKeys" property is an array of arrays containing key-value pairs. The default value is ""null" ,but it is required if the "ids" property is omitted. It is used to specify search criteria and to show the results found.

"primaryKeys" is best used if tables were created with primary keys composed of multiple fields. If primary keys are composed of a single field, it is best to use "ids".
Note
Tables created using JSON DB API actions cannot create primary keys composed of multiple fields.
A table must have a primary key defined in order to use "primaryKeys"
The "primaryKeys" property is mutually exclusive with the "ids" property, meaning it is required when "ids" is omitted, or an error is returned if both have values.
The "primaryKeys" property is an array of arrays
- The outer array contains one or more primary key definitions, enabling the server to retrieve multiple records simultaneously.
- Each inner array is a primary key definition that specifies the values the server needs to retrieve one matching record.
- A primary key definition consists of one or more objects where each object is a field-value pair that uses the following structure ({ "fieldName": "someField","value": "someValue" }).

Example

If your table uses the "first_name" and "last_name" fields as the primary key, the following "primaryKeys" property will retrieve two records.

Note

If your table does not have a primary key, its records cannot be retrieved, updated or deleted using the "getRecordsByIds", "updateRecords" and "deleteRecords" actions. Other getRecords actions can query its records.

   "primaryKeys": 
    [ 
      [
        { "fieldName": "first_name", "value": "Sam" },
        { "fieldName": "last_name",  "value": "I-am" }
      ],
      [
        { "fieldName": "first_name", "value": "The Cat" },
        { "fieldName": "last_name",  "value": "in the Hat" }
      ]
    ]

`"tableName"`

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

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

Example request

"params": {
  "tableName": "ctreeTable"
}

`"result"`

Table 2. "result" properties summary

Property

Description

Type

Limits (inclusive)

binaryFormat

specifies how binary values are returned

string

One of the following: "base64", "hex", or "byteArray".

data

is an array of objects or arrays. Each item in the array describes a record. The array is empty if no results are available.

array

The action determines its contents.

dataFormat

identifies the format of the data in the "data" property.

string

"autoDetect"

"arrays"

"objects"

fields

is an array of objects, one for each field. Each object contains the field's characteristics.

array

totalRecordCount

specifies an integer, the server sets its value to the number of records returned by a query

integer

-1 to 99999999999999999999999999999999

`"binaryFormat"`

The "binaryFormat" property designates the formatof binary values embedded in JSON strings. The default value for "binaryFormat" is the "defaultBinaryFormat" defined in the "createSession" or "alterSession" actions. If it is omitted there, it defaults to the value of the "defaultBinaryFormat" property in the <faircom>/config/services.json file. If it is not there, the FairCom server defaults it to "hex". Prior to version 13.0.4, the server defaulted it to "base64".

Note

Typically, response options apply only to the server’s response, but the "binaryFormat" property applies to both the request and the response.

The "binaryFormat" property may occur inside "params", "responseOptions", "defaultResponseOptions" , "result", and "mapOfPropertiesToFields".
- It occurs in "params" when actions create or change values.
- It occurs in "responseOptions" when actions return values.
When "binaryFormat" occurs in "params" it specifies how the sender represents binary values.
For example, when "binaryFormat" is set to "hex", the FairCom server expects the binary values of fields and keys to be represented in strings with hexadecimal format.
When "binaryFormat" occurs in "responseOptions" or "defaultResponseOptions" it specifies how the FairCom server should represent binary values in responses.
For example, when "binaryFormat" is set to "hex", the FairCom server represents binary values in strings with hexadecimal format.
When "binaryFormat" occurs in "result", it signifies how binary values are represented.
For example, when "binaryFormat" is set to "base64", the FairCom server represents binary values in the response in base64 format.
When "binaryFormat" occurs in "mapOfPropertiesToFields", it tells the server how to encode or decode the binary value in a JSON property.
For example, including "binaryFormat" in a "tableFieldsToJson" transform step controls how the server takes a raw binary field value and encodes it as a JSON property.
For example, including "binaryFormat" in a "jsonToTableFields" or "jsonToDifferentTableFields" transform step controls how the server decodes a binary value in a JSON property so it can store the raw binary value in a field.
The following are the possible values for each format.
- "base64"
  When the server reads and writes from a binary field, it represents the binary value as a base64 string.
  - Base64 is harder for people to read and convert to binary.
  - Base64 creates the smallest payload for the most efficient data transmission in JSON.
  - "base64" strings contain the characters 0 - 9 , A - Z, a - z, +, /, and =.
- "hex"
  When the server reads and writes from a binary field, it represents the binary value as a hexadecimal string.
  - Hexadecimal is easier for people to read and convert to binary.
  - Hexadecimal creates a 30% larger payload than "base64", which makes it less efficient for data transmission.
  - Hexadecimal strings contain the characters 0 - 9 and A - F.
- "byteArray"
  When the server reads and writes from a binary field, it represents the binary value as an array of bytes.
  - An array of bytes is easiest for a program to manipulate.
  - An array of bytes creates a larger payload than "base64" and "hex", which makes it less efficient for data transmission.
  - An array of bytes returns a JSON array containing one integer number between 0 and 255 for each byte in the binary value:
    "aBinaryField": [ 255, 0, 255 ]

Create a "binary_test" table

This example creates a table containing one binary field named "bin" with a fixed length of 5 bytes.

{
  "api": "db",
  "action": "createTable",
  "params": {
    "tableName": "binary_test",
    "fields": [
      {
        "name": "bin",
        "type": "binary",
        "length": 5
      }
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Insert a record into the "binary_test" table using an array of bytes format

This example inserts a record with the ASCII characters "123" in the "bin" field. The value of "bin" is represented as an array of bytes.

{
  "api": "db",
  "action": "insertRecords",
  "params": {
    "tableName": "binary_test",
    "dataFormat": "objects",
    "binaryFormat": "byteArray",
    "sourceData": [
      {
        "bin": [49,50,51]
      }
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Insert a record into the "binary_test" table using hexadecimal format

This example inserts a record with the ASCII characters "123" in the "bin" field. The value of "bin" is represented as a string in hexadecimal format.

{
  "api": "db",
  "action": "insertRecords",
  "params": {
    "tableName": "binary_test",
    "dataFormat": "objects",
    "binaryFormat": "hex",
    "sourceData": [
      {
        "bin": "313233"
      }
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Insert a record into the "binary_test" table using base64 format

This example inserts a record with the ASCII characters "123" in the "bin" field. The value of "bin" is represented as a string in base64 format.

{
  "api": "db",
  "action": "insertRecords",
  "params": {
    "tableName": "binary_test",
    "dataFormat": "objects",
    "binaryFormat": "base64",
    "sourceData": [
      {
        "bin": "MTIz"
      }
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Retrieve a record with "binaryFormat" as an array of bytes

This example requests the first record in the "binary_test" table with the value of "bin" represented as an array of bytes.

{
  "api": "db",
  "action": "getRecordsByTable",
  "params": {
    "tableName": "binary_test",
    "maxRecords": 1
  },
  "responseOptions": {
    "binaryFormat": "byteArray",
    "dataFormat": "objects",
    "numberFormat": "number"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Note

Our examples insert only 3 bytes into "bin". Because the "bin" field has a fixed length of 5 bytes, the server pads unused bytes with 0x00 and stores the result. When a record is retrieved, the server returns all 5 bytes.

{
  "result": {
    "dataFormat": "objects",
    "binaryFormat": "byteArray",
    "fields": [
      { "name": "id",       "type": "bigint", "length": null, "scale": null, "autoTimestamp": "none", "defaultValue": null, "nullable": false, "primaryKey": 1 },
      { "name": "changeId", "type": "bigint", "length": null, "scale": null, "autoTimestamp": "none", "defaultValue": null, "nullable": true,  "primaryKey": 0 },
      { "name": "bin",      "type": "binary", "length": 5,    "scale": null, "autoTimestamp": "none", "defaultValue": null, "nullable": true,  "primaryKey": 0 }
    ],
    "data": [
      {
        "bin": [49,50,51,0,0],
        "changeId": 50217,
        "id": 1
      }
    ],
    "moreRecords": true,
    "requestedRecordCount": 1,
    "returnedRecordCount": 1,
    "totalRecordCount": 3
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Retrieve a record with "binaryFormat" as hexadecimal

This example requests the first record in the "binary_test" table with the value of "bin" represented as a hexadecimal string.

{
  "api": "db",
  "action": "getRecordsByTable",
  "params": {
    "tableName": "binary_test",
    "maxRecords": 1
  },
  "responseOptions": {
    "binaryFormat": "hex",
    "dataFormat": "objects",
    "numberFormat": "number"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

{
  "result": {
    "dataFormat": "objects",
    "binaryFormat": "byteArray",
    "fields": [
      { "name": "id",       "type": "bigint", "length": null, "scale": null, "autoTimestamp": "none", "defaultValue": null, "nullable": false, "primaryKey": 1 },
      { "name": "changeId", "type": "bigint", "length": null, "scale": null, "autoTimestamp": "none", "defaultValue": null, "nullable": true,  "primaryKey": 0 },
      { "name": "bin",      "type": "binary", "length": 5,    "scale": null, "autoTimestamp": "none", "defaultValue": null, "nullable": true,  "primaryKey": 0 }
    ],
    "data": [
      {
        "bin": "3132330000",
        "changeId": 50217,
        "id": 1
      }
    ],
    "moreRecords": true,
    "requestedRecordCount": 1,
    "returnedRecordCount": 1,
    "totalRecordCount": 3
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Retrieve a record with "binaryFormat" as base64

This example requests the first record in the "binary_test" table with the value of "bin" represented as a base64 string.

{
  "api": "db",
  "action": "getRecordsByTable",
  "params": {
    "tableName": "binary_test",
    "maxRecords": 1
  },
  "responseOptions": {
    "binaryFormat": "base64",
    "dataFormat": "objects",
    "numberFormat": "number"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

{
  "result": {
    "dataFormat": "objects",
    "binaryFormat": "byteArray",
    "fields": [
      { "name": "id",       "type": "bigint", "length": null, "scale": null, "autoTimestamp": "none", "defaultValue": null, "nullable": false, "primaryKey": 1 },
      { "name": "changeId", "type": "bigint", "length": null, "scale": null, "autoTimestamp": "none", "defaultValue": null, "nullable": true,  "primaryKey": 0 },
      { "name": "bin",      "type": "binary", "length": 5,    "scale": null, "autoTimestamp": "none", "defaultValue": null, "nullable": true,  "primaryKey": 0 }
    ],
    "data": [
      {
        "bin": "MTIzAAA=",
        "changeId": 50217,
        "id": 1
      }
    ],
    "moreRecords": true,
    "requestedRecordCount": 1,
    "returnedRecordCount": 1,
    "totalRecordCount": 3
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

`"data"`

The "data" property is an array of objects or arrays that contain information about the database. The "dataFormat" property specifies whether the content is arrays or objects. The initiating action defines the data contents. In results messages, when no results are available, it is empty. For path formats, see "path".

Examples

arrays

"data":
[
  ["test1", ".\\test1.dbs\\SQL_SYS", 1003]
]

objects

"data":
[
  {
    "databaseName": "test7",
    "path": ".\\test7.dbs\\SQL_SYS",
    "uid": 1015
  }
]

`"dataFormat"`

The "dataFormat" property is a case-insensitive string enum that defines the format of 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 in "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 the server automatically detects 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.

Example response

{
  "result": {
    "dataFormat": "objects"
  }
}

`"fields"`

The "fields" property is an array of objects. It is required when creating a table. Each object in the array defines a field by specifying its properties.

Example

"fields": [
  {
    "autoValue": "none",
    "name": "name",
    "type": "varchar",
    "length": 50,
    "scale": null,
    "defaultValue": null,
    "nullable": false
  }
]

`totalRecordCount`

The "totalRecordCount" property contains the total available number of records that can be returned from a query.

The "totalRecordCount" is set to -1, when the server does not know the total record count.
A very fast way to get the total number of records in a table is to call the "getRecordsByTable" method without applying a "tableFilter". This immediately returns the count without reading and counting records.
For most methods, the server does not calculate "totalRecordCount" because calculating it requires walking all records in the query, which may take a significant amount of time.
When the result is returned as a cursor, "totalRecordCount" is the total number of records that the cursor can traverse.
Note
This does not apply to cursor responses.
When the result returns records directly, "totalRecordCount" is the total number of records that can be retrieved – not necessarily the number of records returned.

"deleteRecords" (JSON Action)

JSON DB "deleteRecords" action deletes records from a table using its primary key

Essential information

Request examples

Minimal

Delete one record using a multi-field primary key

Delete multiple records using a multi-field primary key

Maximal

Response examples

Note

Minimal

Maximal

The JSON Action JSON DB API "deleteRecords" action deletes records from a database table.

"params"

"databaseName"

Note

"ids"

Tip

"ownerName"

"primaryKeys"

Note

Example

Note

"tableName"

"result"

"binaryFormat"

Note

Note

"data"

Examples

arrays

objects

"dataFormat"

"fields"

Example

totalRecordCount

Note

`"deleteRecords"` (JSON Action)

JSON DB `"deleteRecords"` action deletes records from a table using its primary key

`"params"`

`"databaseName"`

`"ids"`

`"ownerName"`

`"primaryKeys"`

`"tableName"`

`"result"`

`"binaryFormat"`

`"data"`

`"dataFormat"`

`"fields"`

`totalRecordCount`