Skip to main content

Data change message in FairCom DB Notify

JSON format of data change messages

Abstract

JSON message format for the messages sent by FairCom DB Notify to FairCom MQ.

This topic defines the JSON format for the data change message that FairCom DB Notify sends to FairCom MQ. This message contains the before and after data for each data change event.

Example

The following JSON document is sent to an MQTT topic for each trigger event. FairCom DB Notify sends one message for each operation, which can be an insert, update, or delete event. The message is designed to minimize its size for maximum throughput when transmitting over the network and when parsing.

{
  "operation": "insert | update | delete | error",
  "transactionTimestamp": "2022-03-04T08:40:38.256Z",
  "transactionID": "322551223",
  "serverName": "nameOfFairComServer",
  "databaseName": "someDatabaseName",
  "ownerName": "admin",
  "tableName": "someTableName",
  "fields": [
    {
      "fieldName": "fieldName",
      "beforeValue": "field value before an update or delete",
      "afterValue": "field value after an update or insert",
      "pk":0,
      "changed":false
    }
  ],
  "error": {
    "code": 0,
    "message": "",
    "data": {
    }
  }
}

Things to know

  • Unless the "fixedOutput" property in a Table Configuration file is true, the JSON notification message omits unnecessary properties to improve network and parsing throughput.

  • Omitted properties vary with the type of operation it follows.

    • The "beforeValue" property is omitted on an insert record operation.

    • The "afterValue" property is omitted on a delete record operation.

    • The "changed" property is omitted if there are no "beforeValue" and "afterValue" properties.

Property summary

Table 1. Notification message properties summary

Property

Description

Type

Limits (inclusive)

"operation"

contains the name of the event that triggered the sending of the notification

enum string

One of the following:

"insert"
"update"
"delete"
"error"
"notificationPluginStartup"

"transactionTimestamp"

contains the timestamp of when the operation occurred

string

YYYY-MM-DDTHH:MM:SS.MMMZ

"serverName"

contains the name of the Faircom server where the event occurred, such as "FAIRCOMS".

string

Minimum length: 1
Maximum length: 64

"databaseName"

contains the name of the database where the event occurred.

string

Minimum length: 1
Maximum length: 64

"ownerName"

contains the name of the owner of the database table in which the event occurred.

string

Minimum length: 1
Maximum length: 64

"tableName"

contains the name of the database table in which the event occurred.

string

Minimum length: 1
Maximum length: 64

"fields"

contains the field's values

array

Minimum: 0
Maximum: 2,500

"error"

contains error information

Omitted when there is no error



The "operation" property is a string that contains the name of the event that triggered the sending of an operation document.

Things to know:
  • Events include:

    Note

    Additional events may be added in the future.

    • "insert"

    • "update"

    • "delete"

    • "error"

    • "notificationPluginStartup"

  • If an error occurs, the operation will be set to "error" and the error properties will be set to the appropriate values. The following are examples of errors that may occur:

    • "Cannot generate some notifications because unable to open table <tablename> as configured by notification table configuration file: <filename>."

    • "Cannot generate some notifications because the source table is invalid in the notification table configuration file: <filename>. This prevents the record callbacks from being set."

    • "Cannot generate some notifications because cannot connect to the source database as configured in notification table configuration file: <filename>."

    • "Cannot generate some notifications because cannot connect to the FairCom MQ target database because of <error reason>."

    • "Cannot generate some notifications because cannot create or update topic table in FairCom MQ because of <error reason>."

    • "Not able to open table [<tableName>] - Can't open table"

    • "Not able to connect to the local database [<dbname>] - Can't connect to database"

    • "Not able to compile filters for table [<tableName>] from database [<dbname>] - CndxCompile failed"

  • All errors are sent to the FairComServer/NotificationPlugin/Errors/ topic.

    The JSON for these errors includes the following properties that identify the context of the error.

    {
      "operation": "notificationPluginStartup",
      "transactionTimestamp": "2022-03-04T08:40:38.256Z",
      "serverName": "nameOfFairComServer",
      "databaseName": "includedIfApplicableToError",
      "ownerName": "includedIfApplicableToError",
      "tableName": "includedIfApplicableToError",
      "error": {
        "code": 0,
        "message": "",
        "data": {
        }
      }
    }
    

    Note

    Certain errors can only be logged to CTSTATUS. These include errors that prevent FairCom DB Notify from connecting to an MQTT broker. Thus, it is important to look at CTSTATUS regularly when setting up FairCom DB Notify.

The "transactionTimestamp" property is a string that contains the name of the event that triggered the sending of the notification such as the UTC date time in full-length ISO 8601 format.

Things to know:
  • It is typically close to the timestamp of the transaction.

  • On a busy system, the notification event may be delayed.

    Tip

    When the exact timing of a transaction is important, it is best to use a timestamp field in the record.

  • It has the format of YYYY-MM-DDTHH:MM:SS.MMMZ.

    • YYYY represents a four digit year — for example, 2023.

    • MM (the first set) represents a two-digit month from 01 to 12.

    • DD represents a two-digit day from 01 to 3.

    • T is always the character "T" that separates the date from the time.

    • HH represents a two-digit hour from 00 to 23.

    • MM (the second set) represents a two-digit minute from 00 to 59.

    • SS represents a two-digit second from 00 to 59.

    • MMM represents a three-digit millisecond from 000 to 999.

    • Z is always the character "Z" that signifies that the time is Greenwich Zulu time, which is the UTC time zone.

The "fields" property contains the data of the event. It is an array of objects. Each object contains pieces of information about a field, such as field name and value.

Table 2. "fields" properties summary

Property

Description

Type

Range (inclusive)

"fieldName"

contains the name of a field in the table.

string

1 - 64 chars

"beforeValue"

contains the field value before the event occurred.

null,boolean, string

string: 0 - 65000 chars, null, true, or false

"afterValue"

contains the field value after the event occurred.

null,boolean, string

string: 0 - 65000 chars, null, true, or false

"pk"

contains the field number on the primary key. Zero means it is not a field from the primary key.

int16

0 - 65,535

"changed"

informs if the field value has changed with the event.

boolean

true or false



The "beforeValue" property contains the field value before the event has occurred, such as before a record was modified or deleted. A null value in JSON represents a NULL value in the database.

Numbers are represented as strings to avoid potential rounding and precision problems.

Unless the "fixedOutput" property is true, this property is omitted on an insert operation to minimize document size.

The "afterValue" property contains the field value after the event has occurred, such as after a record was modified or inserted.

Numbers are represented as strings to avoid potential rounding and precision problems. 

A null value in JSON represents a NULL value in the database.

Unless the "fixedOutput" property is true, this property is omitted on a delete operation to minimize document size.

The "pk" property contains the field number on the primary key. It is set to an integer value greater than 0. All fields that are not part of the primary key are set to 0.

The first field in a primary key is set to 1, the second is set to 2, and so forth.

Depending on the "primaryKey" settings, some field objects have a "pk" property, and others do not.

The "changed" property indicates if the field value was changed with the event. 

When the tagChanges feature is enabled, the server adds to each DBNotify message a "changed" property in the appropriate "fields" object. 

The "changed" property value is set to true when the value in a field has changed, otherwise it is set to false. Depending on the "tagChanges" settings, some field objects have a "changed" property and others do not.

Table 3. "error" properties summary

Property

Description

Type

Range

(inclusive)

"code"

contains an error number. It is 0 when there is no error.

int32

-2147483648 - 2147483647

"message"

contains a human-friendly message that describes the error. When there is no error message, it is an empty string.

string

0 - 511 chars

"data"

contains data about the error. Each error message may provide a different set of data properties. When there is no data, it is an empty object.

object

Zero or more properties



Example of a notification message

{
  "operation":"update",
  "transactionTimestamp":"2023-09-26T12:37:25.490Z",
  "serverName":"FAIRCOMS",
  "databaseName":"ctreeSQL",
  "ownerName":"admin",
  "tableName":"custmast",
  "fields":[
    {
      "fieldName":"changeid",
      "beforeValue":"11735",
      "afterValue":"11939",
      "pk":0,
      "changed":true
    },
    {
      "fieldName":"cm_custnumb",
      "beforeValue":"1001",
      "afterValue":"1001",
      "pk":1,
      "changed":false
    },
    {
      "fieldName":"cm_custzipc",
      "beforeValue":"61434    ",
      "afterValue":"61434    ",
      "pk":0,
      "changed":false
    },
    {
      "fieldName":"cm_custcity",
      "beforeValue":"Harford",
      "afterValue":"Columbia",
      "pk":0,
      "changed":true
    }
  ]
}