Skip to content

MS SQL Flux Asset

The MS SQL Connector is a flux asset provided by Coreflux that enables seamless communication between MS SQL databases and your data hub. This guide will provide a comprehensive overview of the MS SQL Connector, including its features, benefits, installation, configuration, and usage.

Introduction

What is MS SQL?

Microsoft SQL Server (MS SQL) is a relational database management system developed by Microsoft. It is used to store and retrieve data as requested by other software applications, either on the same computer or on another computer across a network.

Features and Benefits

  • Seamless Communication: Enables seamless communication between MS SQL databases and your data hub.
  • Data Aggregation: Aggregates data from MS SQL databases.
  • Real-time Monitoring: Allows real-time monitoring of MS SQL databases.
  • Easy Integration: Easily integrate MS SQL databases with your IIoT projects.

Prerequisites

Before you install and configure the MS SQL Connector, make sure you have the following prerequisites:

  • Basic understanding of the MQTT protocol.
  • Coreflux account.
  • Coreflux v1.2.
  • Coreflux MQTT Broker asset.
  • MQTT Client (such as MQTT Explorer).

Installation

Method 1: GUI

// Instructions for GUI installation

Method 2: Command-line

// Instructions for command-line installation

Configuration

To configure the MS SQL Connector, you need to run only one command:

-assetConfigSave <guid> <configuration>
  • <guid>: A unique asset id that is generated during the installation. To get the asset guid, consult the $SYS/Coreflux/Assets topic.
  • <configuration>: The asset settings.

Parameters

MQTT Parameters

  • Port: MQTT broker port.
  • Address: MQTT broker IP Address.
  • IsAnonymous: Connect to MQTT broker as anonymous client.
  • Username: MQTT client username.
  • Password: MQTT client password.
  • EnableTLS: Enables a secure connection between broker and client.
  • EnableDebugTopic: Enables a special topic that gives feedback on the connection state.
  • DebugTopic: Define the debug topic.

MSSQL Parameters

  • ServerAddress: Address of the server with SQL DB.
  • DatabaseName: Name of DB.
  • Username: DB username.
  • Password: DB password.
  • QueryTimeoutInSeconds: Query timeout in seconds.

Tags

  • Name: Unique Tag identifier.
  • WriteDirection: Chose the origin and destination of data. The options are: 0 - ToAll, 1 - ToMQTT, and 2 - ToFirebase.
  • MQTTTriggerTopic: Unique topic that will connect with the database node specified in "MQTTFeedbackTopic" parameter.
  • MQTTFeedbackTopic: Feedback topic.
  • MQTTFeedbackQoS: Quality of service for the topic. Options are: AtMostOnce, AtLeastOnce, and ExactlyOnce.
  • MQTTFeedbackRetain: Define to retain the message or not. Options are: true or false.
  • Query: Unique database node that will connect with the topic specified in the "MQTTTopic".
  • ResultInJsonFormat: Set the method for setting data on the database node and/or on the topic. Options are: 0 - IsPostJSONWithAutoKey, 1 - IsPostValueWithAutoKey, 2 - IsPutJSon, 3 - IsPutValue, 4 - IsGetJson, 5 - IsGetValue.

Example Configuration

{
  "MQTTParameters": {
    "Port": 1883,
    "Address": "127.0.0.1",
    "IsAnonymous": true,
    "Username": "",
    "Password": "",
    "EnableTLS": true,
    "EnableDebugTopic": true,
    "DebugTopic": "test/debugtopic"
  },
  "MSSQLParameters": {
    "ServerAddress": "192.168.20.19",
    "DatabaseName": "MyDB",
    "Username": "admin",
    "Password": "pass",
    "QueryTimeoutInSeconds": 0
  },
  "Tags": [
    {
      "Name": "Test",
      "WriteDirection": 1,
      "MQTTTriggerTopic": "sqlasset/triggerTopicDirectSQL",
      "MQTTFeedbackTopic": "feedback/topic",
      "MQTTFeedbackQoS": 0,
      "MQTTFeedbackRetain": false,
      "Query": "INSERT INTO {value.json.table} (CustomerID,CustomerName, ContactName, Country) VALUES ({value.json.data.CustomerID},{value.json.data.CustomerName}, {value.json.data.ContactName}, {value.json.data.Country});",
      "ResultInJsonFormat": true
    }
  ]
}

Tag System

  • {value}: This will inject the full payload into the query string.
  • {value.json.<property>}: This will add a specific property of the JSON payload to the query.
  • {time}: This will add the current time to the query.
  • {utc}: This will add the current time in UNIX timestamp format to the query.

Example Usage

Suppose you have the following JSON payload:

{
  "table": "Customers",
  "data": {
    "CustomerID": "ALFKI",
    "CustomerName": "Alfreds Futterkiste",
    "ContactName": "Maria Anders",
    "Country": "Germany"
  }
}

And you want to create a query to insert this data into a database. You can use the tag system to inject the payload values into the query string like this:

INSERT INTO {value.json.table} (CustomerID, CustomerName, ContactName, Country) VALUES ({value.json.data.CustomerID}, {value.json.data.CustomerName}, {value.json.data.ContactName}, {value.json.data.Country});

This will result in the following query:

INSERT INTO Customers (CustomerID, CustomerName, ContactName, Country) VALUES ('ALFKI', 'Alfreds Futterkiste', 'Maria Anders', 'Germany');

You can also use the {time} and {utc} tags to add the current time to the query. For example:

INSERT INTO Logs (Timestamp, UTC, Message) VALUES ({time}, {utc}, 'New data inserted');

This will result in a query like this:

INSERT INTO Logs (Timestamp, UTC, Message) VALUES ('2023-08-29T21:06:07.1234567Z', 1630269967, 'New data inserted');

Running the Asset

To run the MS SQL Connector, publish the “-R” (run) command with the asset guid to $SYS/Coreflux/Cloud/Command topic:

-R <guid>

And the asset is running!

Usage

Using the MQTT client, it is now possible to visualize the data.

Sure, you can modify the "Next Steps" section to include a mention about using Coreflux for Machine Learning (ML) as follows:


Next Steps

Now that the asset is configured, and data is flowing, you have several options for what to do next:

  • Data Storage: Store the data in the cloud using the Firebase asset.
  • Create Triggers: Create a trigger when a certain value is reached using the Flux Documentation.
  • Machine Learning: Use the data flowing through Coreflux to build and deploy Machine Learning models. Coreflux can be integrated with various ML tools and platforms to help you analyze the data, extract insights, and make predictions.

FAQ

General Questions

What is the MS SQL Connector Asset?

The MS SQL Connector Asset is a flux asset provided by Coreflux that enables seamless communication between MS SQL databases and your data hub. It allows you to aggregate data from MS SQL databases, monitor them in real-time, and easily integrate them with your IIoT projects.

What are the prerequisites for using the MS SQL Connector Asset?

Before you install and configure the MS SQL Connector, make sure you have the following prerequisites: - Basic understanding of the MQTT protocol. - Coreflux account. - Coreflux v1.2. - Coreflux MQTT Broker asset. - MQTT Client (such as MQTT Explorer).

How do I install the MS SQL Connector Asset?

The MS SQL Connector Asset can be installed via GUI or command-line. Detailed installation instructions can be found in the Installation section of the MS SQL Connector documentation.

Configuration Questions

How do I configure the MS SQL Connector Asset?

To configure the MS SQL Connector, you need to run only one command:

-assetConfigSave <guid> <configuration>
The <guid> is a unique asset id that is generated during the installation. To get the asset guid, consult the $SYS/Coreflux/Assets topic. The <configuration> parameter is where you are able to set the asset settings. Detailed configuration instructions and an example configuration can be found in the Configuration section of the MS SQL Connector documentation.

What are the parameters in the configuration?

The configuration consists of three main sections: MQTTParameters, MSSQLParameters, and Tags. - MQTTParameters are used to configure the connection between the client and the MQTT broker. - MSSQLParameters are used to configure the connection between the MQTT Broker and SQL server. - Tags is an array of "Tag", each containing parameters for a unique database node and MQTT topic connection. A detailed explanation of all parameters can be found in the Glossary of Parameters page.

Usage Questions

How do I run the MS SQL Connector Asset?

To run the MS SQL Connector, publish the “-R” (run) command with the asset guid to $SYS/Coreflux/Cloud/Command topic:

-R <guid>
And the asset is running!

How can I visualize the data?

Using an MQTT client, it is possible to visualize the data. Detailed usage instructions can be found in the Usage section of the MS SQL Connector documentation.

How can I use the tag system?

The tag system allows you to customize the query string by injecting specific values into it. Here are some examples: - {value}: This will inject the full payload into the query string. - {value.json.<property>}: This will add a specific property of the JSON payload to the query. - {time}: This will add the current time to the query. - {utc}: This will add the current time in UNIX timestamp format to the query.

Troubleshooting Questions

The MS SQL Connector Asset is not running, what should I do?

Make sure that you have correctly installed and configured the MS SQL Connector Asset. Check the $SYS/Coreflux/Assets topic to confirm that the asset guid is correct. If the problem persists, join our Discord community for help.

I am not receiving any data, what should I do?

Make sure that the MS SQL Connector Asset is running and that the MQTT client is correctly configured. Check the DebugTopic specified in the MQTTParameters section of the configuration for any connection state feedback. If the problem persists, join our Discord community for help.