Create a Flow to Populate a MySQL Database

Estimated reading time: 6 minutes

Use the steps provided in these instructions to create a flow to collect data and store it in a MySQL database. Note that you can access the MySQL database located on the OT Link Platform device or you can use an external database on the same network as the OT Link Platform device.

Prerequisites

Configure Nodes

Configure the following nodes to connect to the device from which data will be collected and also to establish a connection to the MySQL database, where the data will be stored.

Nodes Steps to configure the nodes

DataHub Subscribe


JSON

Configure the DataHub Subscribe node with a topic for a connected device. This node connects with the device and collects the message, which the JSON node processes to ensure that it is in the proper JSON format required for further processing.

  1. Drag the DataHub Subscribe node onto the canvas.
  2. Go to Devices > Tags and copy the raw topic for a tag associated with the connected device.
  3. Double-click the DataHub Subscribe node, paste the topic into the node, and click Done.
  4. Drag the JSON node onto the canvas, wire it to the DataHub Subscribe node, and click Save.
    No configuration is required for the JSON node.

Function

Use the function node to parse the incoming message.

  1. Drag the Function node onto the canvas and wire it to the JSON node.
  2. Double-click the Function node, enter JavaScript to parse the message payload, and click Done.

    var obj = {};
    obj.deviceID = msg.payload.deviceID;
    obj.tagName = msg.payload.tagName;
    obj.value = msg.payload.tagValue;
    msg.payload = obj;
    return msg;

Template

Use the template node to write SQL statements to insert data into the database columns.

  1. Drag the Template node onto the canvas and wire it to the Function node.
  2. Double-click the Template node.
  3. Set the property to msg.topic.
  4. Enter the SQL statement to insert data into the database, click Done, and then click Save.

    INSERT INTO test (deviceID,tagName,tagValue) VALUES 
    ('','','');  

    Note: In the above example, test represents the name of the database table, which needs to be created by the user. This table does not exist, by default. See Create a MySQL Database Table.

MySQL

Configure this node with a port and credentials to connect to the local OT Link Platform MySQL database, when using the marketplace application. When using an external MySQL database on the same network as the OT Link Platform device, configure the MySQL node with the IP address, port, and credentials for that database server.

  1. Drag the MySQL node onto the canvas and wire it to the Template node.
  2. Double-click the MySQL node, configure the following settings, and click Done, then Save.

Host: 127.0.0.1

Port: 3306

User: root

Password: <Password for the MySQL database>

Database: <Name of the database>

Debug

  1. To verify that your flow is working as designed, wire a Debug node to the MySQL node.
  2. When you activate the Debug node, view the output in the Debug tab at the bottom of the canvas.

Validate Database Updates

To check that collected values are being inserted into the MySQL database, create a basic flow.

Nodes Steps to configure nodes

Template

  1. Copy and paste the Template node used in the previous flow and wire it to the Function node.

  2. Double-click the Template node and enter the SQL statement to select the table rows.

    select * from test;
  3. Click Done and then click Save.

MySQL

  1. Copy and paste the MySQL node used in the previous flow and wire it to the Template node. This node will have the IP address, port, and credentials already configured.
  2. Click Save.

Debug

  1. To verify that your flow works as designed, wire a Debug node to the MySQL node.
  2. When you activate the Debug node, view the output in the Debug tab below the canvas.