SQL Flows

Estimated reading time: 3 minutes

Flows supports connectivity to SQL Databases. This page details how to set up and use MySQL through OT Link Platform Marketplace and OT Link Platform Flows. Similar steps apply to other types of SQL databases, such as MSSQL.

Install MySQL through OT Link Platform Marketplace

To install MySQL on the OT Link Platform device:

  1. Go to Applications > Marketplace in the navigation panel.
  2. Search for MySQL. Click the card to begin installing the application.

  3. Enter a name for the application. Leave the other fields as their default values. Optionally, enter a description.

  4. Click Launch. Go to Applications > Overview to ensure that the MySQL app is running without any errors.

TroubleShooting:

  • Check System > Info to make sure that you have enough space on your OT Link Platform device.
  • Make sure that you do not have any network connectivity issues.
  • Click the MongoDB card, then scroll down to view the logs to view the specific error

Set Up and Query the Database

The following sections will show how to insert values into a SQL database, read the inserted values, update the values, and remove the values from the database.

Configure the MySQL Node

Before creating SQL flows, you must configure a database connection. You will only need to configure the node once to use the same configuration in all flows.

To configure MySQL nodes

  1. Drag a MySQL Node onto the Canvas.
  2. Double-click the node. Click  to edit the configuration. Enter the IP address for your SQL server. It will be 127.0.0.1 if your MySQL instance is running on the same device as OT Link Platform.
  3. Enter 3306 for the port, unless you changed it to a different value from the default one.
  4. Enter your username and password. The default values are user and OT Link Platform-s3cr3t!
  5. Enter the name of your database. The default value is sample

  6. Click Update, then click Done to save the configuration.

Query the Database

You can query SQL nodes through Template or Function nodes.

To make a flow for querying a SQL database:

  1. Connect an Inject node, a Template node, a MySQL node, and a Debug node.

  2. Double-click the Template node. Set Property to msg.topic and enter queries into the form.

    To find out which property to set on Template nodes, check the Info tab when clicking on the database node.

  3. Click Done. Save the flow.
  4. Click the Inject Node button to run queries after writing them. Results will appear in the Debug tab.

    The following queries will create a table, insert values, read the inserted values, update inserted values, and delete values:

    CREATE TABLE Example (
      id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(255)
    );
    
    INSERT INTO Example (name)
    VALUES ("Jane"),("John"),("Chris");
    

    SELECT * FROM Example;

    UPDATE Example
    SET name = "Christine"
    WHERE name = "Chris";
    

    DELETE FROM Example WHERE name = "Christine";