MySQL with Arduino and Node-Red

In this blog I would like to document a project that I did with my kids. The goal of the project was to learn SQL in a couple of different environments.

mysql_overview

We did most of our initial testing/learning directly on the MySQL server. Once we got everything worked out we moved to Arduino, then Node-Red.

For our MySQL testing we used alwaysdata.com which has a free low use account. They run MariaDB which looks exactly like MySQL.

 

MySQL Setup

The plan was to have the Arduino module write 3 values:

  1. randint – a random integer from 1-100
  2. saw – a saw tooth integer from 0-100
  3. sstatus – a text value of : HIGH, OKAY or LOW

sample_data

We didn’t want to generate a time stamp from Arduino, instead we wanted MySQL to generate it. Our SQL structure was:

datal_structure

The field “thetime” was defined as a timestamp with CURRENT_TIMESTAMP as the default. By doing this we only needed to INSERT the Arduino data and MySQL would add the current timestamp.

insert_test

After the Arduino Tables was created and tested we were able to start inserting data from Arduino.

 

Arduino and MySQL

There is an excellent MySQL library for Arduino, that can be added via the Arduino IDE “Manage Libraries” option.

Our Arduino code wrote the 3 values to MySQL every 10 seconds.

/*
  MySQL Connector/Arduino Example : connect by wifi
*/
#include <ESP8266WiFi.h>           // Use this for WiFi instead of Ethernet.h
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

IPAddress server_addr(111,222,111,111);  // IP of the MySQL *server* here
char user[] = "yourusername";              // MySQL user login username
char password[] = "yourpassword";        // MySQL user login password

// WiFi card example
char ssid[] = "xxxxx";         // your SSID
char pass[] = "xxxxx";     // your SSID Password

WiFiClient client;                 // Use this for WiFi instead of EthernetClient
MySQL_Connection conn(&client);
MySQL_Cursor* cursor;

int saw = 0;

void setup()
{
  Serial.begin(9600);

  // Begin WiFi section
  Serial.printf("\nConnecting to %s", ssid);
  WiFi.begin(ssid, pass);
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }

  // print out info about the connection:
  Serial.println("\nConnected to network");
  Serial.print("My IP address is: ");
  Serial.println(WiFi.localIP());

  Serial.print("Connecting to SQL...  ");
  if (conn.connect(server_addr, 3306, user, password))
    Serial.println("OK.");
  else
    Serial.println("FAILED.");
  
  // create MySQL cursor object
  cursor = new MySQL_Cursor(&conn);
}

void loop()
{
  // Sample insert query
  //
  char INSERT_SQL[] = "INSERT INTO datal (randint, saw, sstatus) VALUES ('%d','%d','%s')";
  char query[128];
  int randint = random(100);


saw++;
if (saw == 100){
  saw = 0;
}

  if (randint <= 10){
     sprintf(query, INSERT_SQL, randint, saw, "LOW");
  } else if (randint >= 90){
     sprintf(query, INSERT_SQL, randint, saw, "HI");
  } else {
     sprintf(query, INSERT_SQL, randint, saw, "OKAY");
  }
  
  Serial.println(query);

  if (conn.connected())
    cursor->execute(query);


  // Sample select query - Get the last 2 values and show them 
  // Table: arduino1 - this is written to from Node-Red 

  int arddata;
  // Initiate the query class instance
  row_values *row = NULL;
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);

  char squery[] = "SELECT thetime,arddata FROM arduino1 ORDER BY thetime DESC limit 2;";
  cur_mem->execute(squery);
  // Fetch the columns (required) but we don't use them.
  column_names *columns = cur_mem->get_columns();

  do {
    row = cur_mem->get_next_row();
    if (row != NULL) {
      //thetime = (row->values[0]);
      arddata = atoi(row->values[1]);
      Serial.print("Time: "); Serial.print(row->values[0]); Serial.print(" data: "); Serial.println(arddata);
      
    }
  } while (row != NULL);
  delete cur_mem;
  
  delay(10000);
  
}

 

MySQL Views

The table(s) that we created were nice and simple for Arduino data, but they couldn’t be used directly for any types of statistics like daily/hourly/minute averages/maxs or minimums. So we created some MySQL views.

The first things that we needed to do was create some “time” columns like HOUR and MINUTE.

CREATE VIEW v_raw as 
 SELECT HOUR(thetime) as HOUR, 
   MINUTE(thetime) as MINUTE, 
   randint, saw, sstatus from datal 
   order by thetime DESC

This SQL statement creates a view (v_raw)  that shows the hour, minute fields with data ordered from newest to oldest. The query of : SELECT  * FROM v_raw; gives:

v_raw

We then created a number of statistic views like:

CREATE VIEW v_min AS 
  SELECT HOUR(thetime) as HOUR, MINUTE(thetime) as MINUTE, 
    AVG(randint), AVG(saw) 
   from datal 
     GROUP BY HOUR,MINUTE ORDER BY thetime LIMIT 720

This gave us some minute averages:

v_min

 

Node-Red with MySQL

The component node-red-node-mysql can be added manually or via the “Manage Palettes” option.

node-red-node-mysql

For our project we wanted to show:

  • Last 100 Arduino values
  • Latest Average Minute Values
  • Latest Average Hourly Value

Then we wanted to Insert a new 0/1 value that could be read back in the Arduino. This inserted value could be used to turn on a relay connected to the the Arduino (like a start a fan, turn on a light etc.)

The logic used Dashboard buttons (to select an action).  MySQL nodes do the SELECT or INSERT command, and a Dashboard Table to show the results.

mysql_nodered_logic

The buttons are configured to pass the SQL views statement to the MySQL nodes, with the results showing in a Dashboard table node.

nr_v_min

After the logic is complete, use the Deploy button to make the logic live. The web dashboards can be viewed at: http://yourNodeRed_IP:1880/ui . Below is an example of 1) Minute Averages, and 2) Last 100 (raw values).

 

Final Comments

For this project we kept things simple, and used tables to show the results. As a next step it would be good to show the results in a trend chart. In a previous blog I looked at putting SQLite into a Node-Red chart. I would use this technique with MySQL data.

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s