SQL Publisher Configuration

The publisher is used to report datasets (values or alarms) to the database.

Name	Description	Data Type
ConnectionType	Type of the SQL connection. mysql: My SQL or Maria DB connection mssql: MS SQL server azuresql: Azure SQL DataBase	System.String
Connection	Database connection string. All connection parameters are separated with ";"	System.String

Example connection string for MySQL:

Server=192.168.1.100;SslMode=none;Database=humanos.datalogger;Uid=humanos;Pwd=ExamplePassword;

Example connection string Azure SQL DataBase:

Server=mycompany.database.windows.net;Encrypt=True;TrustServerCertificate=True;Database=humanos.datalogger;Uid=humanos;Pwd=ExamplePassword;

Database Setup

It is important that the table is created with the correct fields in the database. The name of the table must match the name of the DataSet configuration.

Example Table name must be dbo.datastream1 with configured columns Id, TimeStamp, Value and State

Payload Scripting

The Sql data logger does not support any scripting.

Examples

Example reporting alarms into the table Alarming of a MariaDB.

{
  "Disabled": false,
  "Publishers": [
    {
      "Id": "590b2187-2515-4f64-9623-041367022127",
      "Name": "TestDataLogger11",
      "ConnectionType": "mysql",
      "Connection": "Server=10.196.24.12;SslMode=none;Database=humanos.test.datalogger;Uid=humanos;Pwd=humanos;",
      "DataSets": [
        {
          "Name": "Alarming",
          "Type": "AlarmEvent",
          "NodeFilter": "",
          "Fields": [
            {
              "Name": "EventId",
              "DataType": "System.Int32",
              "Query": "item.EventId"
            },
            {
              "Name": "Active",
              "DataType": "System.Boolean",
              "Query": "item.Active"
            },
            {
              "Name": "AlarmType",
              "DataType": "System.String",
              "Query": "item.AlarmType"
            },
            {
              "Name": "ConditionName",
              "DataType": "System.String",
              "Query": "item.ConditionName"
            },
            {
              "Name": "Message",
              "DataType": "System.String",
              "Query": "item.Message"
            },
            {
              "Name": "SourceName",
              "DataType": "System.String",
              "Query": "item.SourceName"
            },
            {
              "Name": "Severity",
              "DataType": "System.Int32",
              "Query": "item.Severity"
            },
            {
              "Name": "Occurrence",
              "DataType": "System.DateTime",
              "Query": "item.Occurrence"
            }
          ]
        }
      ],
      "ServiceRule": {
        "BindingRuleId": "58ff1a88-36f2-4495-837b-f2ee09dcdc3f",
        "UnbindingRuleId": "8b0b94d2-e4a8-4d31-8fd8-0eac3c5d7af0",
        "Devices": [],
        "Drivers": [],
        "Properties": {}
      }
    }
  ]
}

Example reporting data items into the table dbo.datalogger1 of a Azure SQL database.

{
  "Id": "c2dfadab-32f9-4352-8aaa-c36b4417a7d2",
  "Publishers": [
    {
      "Id": "d5ccda0f-0b30-402f-8f0f-7cd9951e8cc7",
      "Name": "DataLogger",
      "ConnectionType": "azuresql",
      "Connection": "Server=mycompany.database.windows.net;Encrypt=True;TrustServerCertificate=True;Database=humanos.datalogger;Uid=humanos;Pwd=ExamplePassword;",
      "DataSets": [
        {
          "Name": "dbo.datalogger1",
          "Fields": [
            {
              "Name": "Id",
              "DataType": "System.Guid",
              "Query": "item.GlobalId",
              "Tag": "test",
              "Properties": {}
            },
            {
              "Name": "TimeStamp",
              "DataType": "System.DateTime",
              "Query": "item.TimeStamp",
              "Tag": "",
              "Properties": {}
            },
            {
              "Name": "Value",
              "DataType": "System.Double",
              "Query": "item.Value",
              "Tag": "",
              "Properties": {}
            },
            {
              "Name": "State",
              "DataType": "System.Int32",
              "Query": "item.DataState",
              "Tag": "",
              "Properties": {}
            },
            {
              "Id": "6059f99d-dbce-4515-ba53-5187be8ce502",
              "Name": "Name",
              "DataType": "System.String",
              "Query": "item.Node.Name",
              "Tag": "",
              "Properties": {}
            }
          ],
          "NodeFilter": "node.hasProperty<bool>(\"EnableMqtt\", true)",
          "Tag": "test",
          "Type": "DataNode",
          "Properties": {}
        }
      ],
      "ServiceRule": {
        "BindingRuleId": "58ff1a88-36f2-4495-837b-f2ee09dcdc3f",
        "UnbindingRuleId": "8b0b94d2-e4a8-4d31-8fd8-0eac3c5d7af0",
        "Devices": [],
        "Drivers": [],
        "Properties": {}
      }
    }
  ]
}