Connect IoT - DataBase Driver#

The DataBase driver purpose is to allow the communication to a data base server (on this version only MSSQL and MYSQL is available).

It is responsible for creating the necessary SQL queries dynamically, according to the driver definition, to either execute commands or handle events.

Data Types#

These are the supported specific data types:

Handling Decimal Precision in Database#

When using the DriverDatabase to write decimal values, you may encounter an issue where the decimal part of the value is truncated, and the value is rounded to the nearest whole number. This occurs because the internal mssql Node.js module defaults to binding DECIMAL values as DECIMAL(18,0). As a result, even if the database column is defined with a precision and scale (e.g., DECIMAL(18,2)), the values are rounded.

To address this issue, use the DECIMAL type for MES and configure the Protocol Data Type as FLOAT instead of DECIMAL. This ensures the value is passed and written to the database without truncation or rounding.

Protocol Parameters#

The protocol supports the following parameters:

Event#

In order to handle events the dataBase driver creates a query that will be polling the data base (trigger query) and when a change is noticed, a different query is executed to retrieve the intended data (result query).

The trigger query and the result query are both built according to the configuration on the driver definition:

Event Property#

Event properties represent the table columns that will be retrieved as result. The association between the property and the data base column can be specified as presented below:

Event Examples#

Example 1: Subscribe to a ROW_NUMBER event

Event Configuration:

table: [Table01], argument: "Column01", pollingInterval: 1000, triggerType: "OnNewRows", trigger: "RowNumber", resultDataFormat: "AllNew", maxRowsToProcess: 2, orderByColumn: "Column01", orderByType: "DESC", processingMode: "FireAndForget", ignoreInitialValues: true

Event Properties Configuration:

name: "EventPropName", deviceId: "EventPropID", columnName: "Column02"

In this example the trigger query, which will be executed every second (pollingInterval: 1000), is the following:

Select TOP 1 ROW_NUMBER() OVER(ORDER BY Column01) as Column01 from [Table01] order by Column01 DESC

And it will retrieve all the new values for the Column02 (specified on the event property). Notice that the maxRowsToProcess is set as 2, so if the result has more than 2 rows, the event will be raised multiple times, returning only 2 rows per event. Also, this an FireAndForget event, which means that when the event is raised, it requires no reply to continue polling.

Result example:

EventPropName value:
[
    {"Column02": "Value01"}, 
  {"Column02": "Value02"}
]

Example 2: Subscribe to a COUNT event

Event Configuration:

table: [Table01], argument: "Column01", pollingInterval: 1000, triggerType: "OnNewRows", trigger: "Count", resultDataFormat: "SingleRow", orderByColumn: "Column01" , orderByType: "DESC", processingTimeout: 3000, maxRetries: 3, processingMode: "ContinueOnProcessingConfirmation", ignoreInitialValues: false

Event Properties Configuration:

name: "EventPropName01", deviceId: "EventPropID01", columnName: "Column01"

name: "EventPropName02", deviceId: "EventPropID02", columnName: "Column02"

In this example the trigger query, which will be executed every second (pollingInterval: 1000), is the following:

Select TOP 1 Count(Column01) as Column01 from [Table01]

In this case, both Column01 and Column02 values (as a SingleRow) will be sent on the event (as it is specified on the event property configuration).

After the event is raised the driver will wait for a reply as confirmation (processingMode: "ContinueOnProcessingConfirmation"). To reply to an event we need to execute a command of type ConfirmationDataProcessing. If no reply is received within the time configured (processingTimeout: 3000), the event will be sent again (up to the number defined as maxRetries). After that the event data will be lost and the polling process will start again.

Considering that the ignoreInitialValues is set as false, an event will be immediately triggered if there are values in the respective table.

Result example:

EventPropName01 value:
{"Column01": 000100}

EventPropName02 value:
{"Column02": "Value01"}

Example 3: Subscribe to a FIELD event

Event Configuration:

table: [Table01], argument: "Column01", pollingInterval: 1000, triggerType: "OnNewRows", trigger: "Field", resultDataFormat: "SingleRow", orderByColumn: "Column01", orderByType: "DESC", processingMode: "FireAndForget", ignoreInitialValues: true

Event Properties Configuration:

name: "EventPropName01", deviceId: "EventPropID01", columnName: "Column01"

name: "EventPropName02", deviceId: "EventPropID02", columnName: "Column02"

In this example the trigger query, which will be executed every second (pollingInterval: 1000), is the following:

Select TOP 1 Column02 from Table01 order by Column02 DESC

In this case, both Column01 and Column02 values (as a SingleRow) will be sent on the event (as it is specified on the event property configuration).

Result example:

EventPropName01 value:
{"Column01": "Value01"}

EventPropName02 value:
{"Column02": 20200920}

Command#

Commands are SQL queries which will be executed on a data base.

Command Parameters#

A command parameter represents an inputs to a command and can be configured according to the following information:

Remarks/ Behavior#

• If the command type is Select and no parameterValue is defined, all the columns will be retrieved.

• For the command type Select it is possible to use functions (like COUNT(), ROW_NUMBER()), MAX(), etc.). In that case it is necessary to set the function on the columnName for a specific parameterValue.

  • Example: If we set the columnName to Count(column01) as Quantity, the query would be Select Count(column01) as Quantity From table01

• ParameterValue has a default value which will be used when no other value is provided to the command.

Command Examples#

Example 1: Execute a Select Command

Command Configuration:

type: "Select" , argument: "[Table01]", operation: "None", resultType: "ArrayOfResults"

Command Parameters Configuration:

name: "ParamValue", type: "ParameterValue", columnName: "Column01", operation: "None"

name: "ParamQuery", type: "ParameterQuery", columnName: "Column02", operation: "Equal"

When the command is executed, a value for the parameterQuery must be provided (10 in this example), and the query that will be executed is presented below:

Select Column01 From [Table01] Where Column02 = 10

As the command resultType is defined as ArrayOfResults, the result of the query will be returned as an array:

[
    {"Column01":"Value01"},
    {"Column01":"Value02"},
    {"Column01":"Value03"}
]

Example 2: Execute an Update Command

Command Configuration:

type: "Update" , argument: "[Table01]", operation: "AND", resultType: "SingleRow"

Command Parameters Configuration:

name: "ParamValue01", type: "ParameterValue", columnName: "Column01", operation: "None"

name: "ParamQuery01", type: "ParameterQuery", columnName: "Column01", operation: "Equal"

name: "ParamQuery02", type: "ParameterQuery", columnName: "Column02", operation: "GreaterThanOrEqualTo"

In this case we would need to provide all the 3 parameters upon the command execution. Assuming the values as ParamValue01 = false, ParamQuery01 = true, ParamQuery02 = 20, we would have the following query to be executed:

Update [Table01] Set Column01 = false Where Column01 = true AND Column02 >= 20

Driver Extensions#

This protocol driver provides a set of extension that allow the customization to perform actions that are independent of the Driver Definitions associated. This means you can, using customization, execute a command or even register/listen to an event without needing to describe them.

• This is particularly useful when accessing equipment data that is shared/too complex to access using the regular mechanisms. Also useful when needing to access data whose naming convention may vary from device to device.
• To use these extensions you can either implement your own task or use the supplied Tasks Send Notification to Driver and Subscribe in Driver.

Objects#

The objects are structures that are passed as parameters.

Event#

Property#

RegisterHandler#

UnregisterHandler#

Methods#

Register Event#

Notifies the protocol driver to raise the occurrences of a specific event when it occurs.

void connect.iot.driver.database.registerEvent(data: RegisterHandler)

![note] The events will be published to the listeners connect.iot.driver.database.event appended with the name of the event. Example: connect.iot.driver.database.event.CustomEvent

Example#

{
    type:"connect.iot.driver.database.registerEvent",
    content: {
        name: "CustomEvent", 
        isEnabled: true,
        extendedData: {
         table: tableName,
         argument: "ColumnName",
         pollingInterval: 1000,
         triggerType: "OnNewRows",
         trigger: "RowNumber",
         resultDataFormat: "AllNew",
      processingTimeout: 3000,
         maxRetries: 3,
         maxRowsToProcess: 2,
         orderByColumn: "ColumnName",
         orderByType: "DESC",
         processingMode: "FireAndForget",
         ignoreInitialValues: true,
         rawResultQuery: ""
     },
        properties: {
            name: "PropertyName",
            deviceId: "PropertyID",
            dataType: "String",
            deviceType: "NVARCHAR",
            extendedData: { 
                columnName: "ColumnName"
            }
        }
    }
}

Unregister Event#

Notifies the protocol driver to stop raising the occurrences of a specific event when they occur.

void connect.iot.driver.database.unregisterEvent(data: UnregisterHandler)

Example#

{
  type:"connect.iot.driver.database.registerEvent",
    content: {
        name: "CustomEvent", 
    }
}

Send Command#

Sends a command to the driver to be executed

void connect.iot.driver.database.executeCommand(data: ExecuteCommandHandler)

Example#

{
    type: "connect.iot.driver.database.executeCommand",
    content: {
        command: {
            name: "SelectCustomCommand",
            deviceId: "SelectCustomCommandID",
            extendedData: { 
                type: "Select",
                argument: "Table01",
                operation: "None",
                resultType: "ArrayOfResults"
            },
            parameters: [ 
                {
                    name: "ParamName", deviceId: "ParamID", dataType: "String", deviceType: "NVARCHAR",      extendedData: { type: "ParameterValue", columnName: "ColumnName", operation: "None" }
                }
            ]
        },
        parameters: { "ParamName": "Value"}
    }
}