Skip to main content

Data change message in FairCom DB Notify

JSON format of data change messages

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

For each trigger event, a JSON document is sent to an MQTT topic. 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.

If the data change message recordFormat is "fields", it will contain a list of fields. If the recordFormat is "buffer", it will contain a "recordBuffer" instead. See Configure tables for details about DB Notify table configuration.

Essential information

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

Message example for recordFormat "fields"

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

Message example for recordFormat "buffer"

{
  "operation": "update",
  "transactionTimestamp": "2024-10-17T02:56:07.032Z",
  "serverName": "FAIRCOMS",
  "databaseName": "ctreeSQL",
  "ownerName": "admin",
  "tableName": "custmast",
  "recordBuffer": {
    "beforeValue": "gAAAAAAAAAADAAAAAAAAADEwMDI3MzY3NyAgICBHQTEMAEpvc2h1YSBCcm93bg4ANDM1NiBDYW1icmlkZ2UHAEF0bGFudGE=",
    "afterValue": "gAAAAAAAAAADAAAAAAAAADEwMDI3MzY3NyAgICBHQTEEAFRlc3QOADQzNTYgQ2FtYnJpZGdlBwBBdGxhbnRh"
  }
}

Properties summary

Table 1. DB Notify message properties summary

Property

Description

Type

Limits (inclusive)

databaseName

contains the name of the database where the event occurred.

string

1 to 64 characters

error

contains error information

object

Omitted when there is no error

error
.code

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

int32

-2147483648 to 2147483647

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

error
.message

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

string

0 to 511 characters

fields

contains an array with field values. This property is presented when recordFormat is "fields".

array of objects

Minimum: 0
Maximum: 2,500
fields
.afterValue

contains the field value after the event occurred.

null,boolean, string

null: NULL
boolean: true | false
string: 0 to 65,000 chars
fields
.beforeValue

contains the field value before the event occurred.

null,boolean, string

null: NULL
boolean: true | false
string: 0 to 65,000 chars
fields
.changed

informs whether the field value has changed with the event. Value true means that the field value has changed.

boolean

true | false

fields
.fieldName

contains the name of a field in the table.

string

1 to 64 characters

fields
.pk

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

int16

0 to 65535

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"

ownerName

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

string

1 to 64 characters

serverName

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

string

1 to 64 characters

tableName

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

string

1 to 64 characters

transactionTimestamp

contains the timestamp of when the operation occurred

string

YYYY-MM-DDTHH:MM:SS.MMMZ



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

How FairCom DB Notify communicates data changesConfiguration for FairCom DB NotifyJSON message format for FairCom DB NotifyFairCom DB Notify ReferenceFairCom MQ

"fields"."afterValue"

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.

"fields"."beforeValue"

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.

"fields"."changed"

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.

"fields"."pk"

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.

"operation"

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

Essential information

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

"transactionTimestamp"

The "transactionTimestamp" property contains a UTC date time in full-length ISO 8601 format. It is the timestamp when the notification event was generated.

Essential information

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