Introduction: UCL-IIoT-Datalogger
The original idea for this school project was to make a PLC datalogger, logging data into MySQL database and interact with it via an interface made in Node-Red. I succeeded in getting the communication working between Kepserverex’s IOT gateway, using a MQTT broker, but only to some extent. I could send one string of data to Node-Red, but if I sent multiple strings, it would sometime mix the data up, I also couldn’t send data back to Kepserverex. After getting stuck a few hours I dropped the project and went with an Arduino solution instead, as it was much easier to find tutorials and help on this project.
Step 1: Data Flow
An ESP32s collects data from a sensor and sends it to Node-Red via TCP/IP protocol MQTT. From there the data gets stored in MySQL DB. From the GUI it's possible to view a chart of the DB values and to turn on a onboard LED on the ESP32s.
Step 2: GUI Description
There are two slide buttons, one to turn on/off the onboard LED, and one to allow datalogging. Two graphical elements, a real time gauge and a chart showing a range of measurements from the DB defined by the user in the input form.
Step 3: Requirements
Hardware
1x ESP32
Software required
Node-Red with MySQL and Dashboard
· npm install node-red-node-mysql
· npm install node-red-dashboard
MySQL
Mosquitto
Arduino IDE
· ESP32 boards in Arduino IDE: follow this installation guide.
· External libraries “PubSubClient” for MQTT.
Step 4: The Code
In this section i will primarly focus on the Node-Red code.
The Arduino code is commented further down in the code section. To upload the code, you might need to hold the boot button while its connecting, after uploading press the enable button. See picture 1.
MySQL DB. I’ve created a DB called mydb and a table called mydbtable with two columns, Tid and Val, Tid has the datatype DATETIME which has this format “YYYY-MM-DD HH:MM:SS” and Val is an INT(11).
Node-Red. MQTT nodes need to be connected to the host IP (in my project it's localhost), port 1883 (standard). The topic is used in the Arduino either to subscribe or publish data, in this project “room/temperature” is the data I publish from the Arduino. See picture 2.
The data I receive from the Arduino is in a string and needs to be changed to an integer, I do this in a change node with the following input. See picture 3.
This integer can now be used in a gauge etc. I output the integer as an global variable, in a function, with this command
global.set("InputVal" , msg.payload);
Now the variable can be used in other functions later on.
The MySQL DB nodes needs to be setup accordingly to your MySQL server. See picture 4.
I had a problem where I couldn’t connect to the DB. Running this command in the SQL Query fixed it
“ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'”
The data from the Arduino is stored in the SQL DB and is executed every 5 seconds.
var TimeStamp = global.get("Timestamp");
var InputValue = global.get("InputVal")
var Logger = global.get("Logger")
if (Logger === "on") {
msg.topic = 'INSERT INTO mydbtable VALUES ('+ "'" + TimeStamp + "'" + ', ' + InputValue + ')'
return msg;
}“INSERT INTO ”tablename” VALUES(column1,column2)”
is a SQL command and it’s self-explanatory. The values need to match the datatypes defined in the MySQL DB. I had some problems with the Timestamp as Node-Red's datatype didn't match, so I had to do it myself. The Timestamp needed to be sent as a string otherwise Node-Red would read the variable as a calculation and give an error due to the space in my Timestamp. To give Timestamp the right format this code was used.
var currentDate = new Date();
var date = currentDate.getDate();
var month = currentDate.getMonth();
var year = currentDate.getFullYear();
var hours = currentDate.getHours();
var minutes = currentDate.getMinutes();
var seconds = currentDate.getSeconds();
var dateString = year + "-" + (month + 1) + "-" + date + " " + hours + "-" + minutes+ "-" + seconds;
msg.payload = dateString;
global.set("Timestamp" , msg.payload);
return msg;To make a chart of the data from the DB, the data input to the chart needs to follow the format showed in picture 5.
On the dashboard the user can set a range of data they want displayed in the chart. This is done in an input form (min and max values in the for-loop). The input to this function is all the values in the DB-table given from a SQL command msg.topic='SELECT * FROM mydbtable', where asterisk means all columns and rows. This gives a large array of data that needs to be sorted accordingly to the chart format. This was achieved with the following code.
var temp = [];<br>var temp1 = [];
var ny = {};
var min = global.get("minChart")
var max = global.get("maxChart")
var k = 0;
for (var i = min ; i < max; i++) {
temp[k] = msg.payload[i].Tid;
temp1[k] = msg.payload[i].Val;
k=k+1;
}
ny.labels = temp;
ny.series = ['Data']
ny.data = [temp1];
return {payload:[ny]};Arduino code (Download the file for raw code, some lines are corrupted here)
<p>#include <br>#include
</p><p>const char* ssid = "1234"; //Insert network ssid
const char* password = "1234"; //Network password
const char* mqtt_server = "192.168.1.40"; //IP for MQTT server</p><p>WiFiClient espClient;
PubSubClient client(espClient); </p><p>int temperatureTemp = 0; // Data variable
char cstr[5]; // Buffer for send command</p><p>void callback(String topic, byte* message, unsigned int length) { //receives the MQTT messages of the subscribed topics.
Serial.print("Message arrived on topic: ");
Serial.print(topic);
Serial.print(". Message: ");
String messageTemp;</p><p> for (int i = 0; i < length; i++) {
Serial.print((char)message[i]);
messageTemp += (char)message[i];
}
Serial.println();</p><p>
// If a message is received on the topic room/lamp, you check if the message is either on or off. Turns the LED according to the message
if (topic == "room/lamp") {
Serial.print("Changing Room lamp to ");
if (messageTemp == "on") {
digitalWrite(LED_BUILTIN, HIGH);
Serial.print("On");
}
else if (messageTemp == "off") {
digitalWrite(LED_BUILTIN, LOW);
Serial.print("Off");
}
}
Serial.println();
}</p><p>// This functions reconnects your ESP8266 to your MQTT broker
void reconnect() {
// Loop until we're reconnected
while (!client.connected()) {
Serial.print("Attempting MQTT connection...");
// Attempt to connect</p><p> if (client.connect("ESP8266Client")) {
Serial.println("connected");
// Subscribe or resubscribe to a topic
// Subscribed topics
client.subscribe("room/lamp");
} else {
Serial.print("failed, rc=");
Serial.print(client.state());
Serial.println(" try again in 5 seconds");
// Wait 5 seconds before retrying
delay(5000);
}
}
}</p><p>void setup() {</p><p> Serial.begin(115200);
pinMode(LED_BUILTIN, OUTPUT);
WiFi.begin(ssid, password);</p><p> while (WiFi.status() != WL_CONNECTED) {
delay(500);
Serial.println("Connecting to WiFi..");
}</p><p> Serial.println("Connected to the WiFi network");</p><p> client.setServer(mqtt_server, 1883);
client.setCallback(callback);</p><p>}</p><p>void loop() {</p><p> if (!client.connected()) {
reconnect();
}
if (!client.loop())
client.connect("ESP8266Client");</p><p> if (temperatureTemp >= 200) { //Limit output to 200
temperatureTemp = 0;
}
delay(1000);
temperatureTemp = temperatureTemp + 1; //Data to be logged</p><p> client.publish("room/temperature", itoa(temperatureTemp, cstr, 10) ); //Data to Node Red via MQTT, the data must be of the type CHAR therefore i convert it to a char with the itoa command. </p><p>}</p>Node-Red code (Download the file for raw code, some lines are corrupted here)
<p>[<br> {
"id": "fe9e878d.a1a2b8",
"type": "tab",
"label": "Test",
"disabled": false,
"info": ""
},
{
"id": "7708e1a3.60bd",
"type": "mqtt in",
"z": "fe9e878d.a1a2b8",
"name": "InputValue mqtt in",
"topic": "room/temperature",
"qos": "2",
"broker": "fbac67d.b391298",
"x": 350,
"y": 2440,
"wires": [
[
"11fa242e.273b5c"
]
]
},
{
"id": "35950db3.1fbf52",
"type": "mqtt out",
"z": "fe9e878d.a1a2b8",
"name": "Lamp mqtt out",
"topic": "room/lamp",
"qos": "2",
"retain": "",
"broker": "fbac67d.b391298",
"x": 520,
"y": 2560,
"wires": []
},
{
"id": "32f3b0e1.aa07b",
"type": "ui_switch",
"z": "fe9e878d.a1a2b8",
"name": "Lamp switch",
"label": "Diode",
"tooltip": "",
"group": "1dc5ca90.ab4105",
"order": 1,
"width": "1",
"height": "1",
"passthru": true,
"decouple": "false",
"topic": "room/lamp",
"style": "",
"onvalue": "on",
"onvalueType": "str",
"onicon": "",
"oncolor": "",
"offvalue": "off",
"offvalueType": "str",
"officon": "",
"offcolor": "",
"x": 310,
"y": 2560,
"wires": [
[
"35950db3.1fbf52"
]
]
},
{
"id": "11fa242e.273b5c",
"type": "change",
"z": "fe9e878d.a1a2b8",
"name": "StrToInt",
"rules": [
{
"t": "set",
"p": "payload",
"pt": "msg",
"to": "$number(payload)",
"tot": "jsonata"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 580,
"y": 2440,
"wires": [
[
"98881cba.6756c",
"718470db.6425d",
"10cb91bd.5ebf7e"
]
]
},
{
"id": "98881cba.6756c",
"type": "debug",
"z": "fe9e878d.a1a2b8",
"name": "",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"x": 930,
"y": 2480,
"wires": []
},
{
"id": "9759afb5.20e6d",
"type": "mysql",
"z": "fe9e878d.a1a2b8",
"mydb": "c55cec19.aae03",
"name": "Insert to DB",
"x": 930,
"y": 2660,
"wires": [
[]
]
},
{
"id": "c7b378a9.ea0df8",
"type": "function",
"z": "fe9e878d.a1a2b8",
"name": "sql select",
"func": "msg.topic='SELECT * FROM mydbtable'\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 500,
"y": 2860,
"wires": [
[
"1272f904.ed62e7"
]
]
},
{
"id": "3f6a31fa.97432e",
"type": "debug",
"z": "fe9e878d.a1a2b8",
"name": "SQL insert test",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "topic",
"x": 940,
"y": 2740,
"wires": []
},
{
"id": "1e4049ce.14b9a6",
"type": "function",
"z": "fe9e878d.a1a2b8",
"name": "TimeConvert",
"func": "var currentDate = new Date();\n\nvar date = currentDate.getDate();\nvar month = currentDate.getMonth();\nvar year = currentDate.getFullYear();\nvar hours = currentDate.getHours();\nvar minutes = currentDate.getMinutes();\nvar seconds = currentDate.getSeconds();\n\nvar dateString = year + \"-\" + (month + 1) + \"-\" + date + \" \" + hours + \"-\" + minutes+ \"-\" + seconds;\n\nmsg.payload = dateString;\n\nglobal.set(\"Timestamp\" , msg.payload);\n\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 650,
"y": 2780,
"wires": [
[]
]
},
{
"id": "2aee248.0666ddc",
"type": "inject",
"z": "fe9e878d.a1a2b8",
"name": "0.5 sec interval",
"topic": "",
"payload": "",
"payloadType": "num",
"repeat": "0.5",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"x": 300,
"y": 2780,
"wires": [
[
"1e4049ce.14b9a6",
"c7b378a9.ea0df8"
]
]
},
{
"id": "718470db.6425d",
"type": "function",
"z": "fe9e878d.a1a2b8",
"name": "Var InputVal",
"func": "global.set(\"InputVal\" , msg.payload);\n",
"outputs": 1,
"noerr": 0,
"x": 930,
"y": 2520,
"wires": [
[]
]
},
{
"id": "8ad9fff4.5bcfc",
"type": "function",
"z": "fe9e878d.a1a2b8",
"name": "sql Insert",
"func": "var TimeStamp = global.get(\"Timestamp\");\nvar InputValue = global.get(\"InputVal\")\nvar Logger = global.get(\"Logger\")\n\nif (Logger === \"on\") {\nmsg.topic = 'INSERT INTO mydbtable VALUES ('+ \"'\" + TimeStamp + \"'\" + ', ' + InputValue + ')'\nreturn msg;\n}\n\n\n",
"outputs": 1,
"noerr": 0,
"x": 640,
"y": 2700,
"wires": [
[
"3f6a31fa.97432e",
"9759afb5.20e6d"
]
]
},
{
"id": "1954a5c4.f95a1a",
"type": "inject",
"z": "fe9e878d.a1a2b8",
"name": "5 sec interval",
"topic": "",
"payload": "",
"payloadType": "num",
"repeat": "5",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"x": 340,
"y": 2700,
"wires": [
[
"8ad9fff4.5bcfc"
]
]
},
{
"id": "1272f904.ed62e7",
"type": "mysql",
"z": "fe9e878d.a1a2b8",
"mydb": "c55cec19.aae03",
"name": "",
"x": 650,
"y": 2860,
"wires": [
[
"e66edb0d.839668",
"67dae2b0.25382c",
"d9a70d8f.2fa93"
]
]
},
{
"id": "e66edb0d.839668",
"type": "debug",
"z": "fe9e878d.a1a2b8",
"name": "",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"x": 910,
"y": 2960,
"wires": []
},
{
"id": "4dc116e9.6f2188",
"type": "ui_switch",
"z": "fe9e878d.a1a2b8",
"name": "Logning",
"label": "Logning",
"tooltip": "",
"group": "1dc5ca90.ab4105",
"order": 2,
"width": "1",
"height": "1",
"passthru": true,
"decouple": "false",
"topic": "room/lamp",
"style": "",
"onvalue": "on",
"onvalueType": "str",
"onicon": "",
"oncolor": "",
"offvalue": "off",
"offvalueType": "str",
"officon": "",
"offcolor": "",
"x": 300,
"y": 2640,
"wires": [
[
"d6c6f581.42c638",
"f41d85ca.7a3578"
]
]
},
{
"id": "d6c6f581.42c638",
"type": "debug",
"z": "fe9e878d.a1a2b8",
"name": "",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"x": 650,
"y": 2660,
"wires": []
},
{
"id": "f41d85ca.7a3578",
"type": "function",
"z": "fe9e878d.a1a2b8",
"name": "Var Logger",
"func": "global.set(\"Logger\" , msg.payload);\n",
"outputs": 1,
"noerr": 0,
"x": 650,
"y": 2620,
"wires": [
[]
]
},
{
"id": "10cb91bd.5ebf7e",
"type": "ui_gauge",
"z": "fe9e878d.a1a2b8",
"name": "",
"group": "1dc5ca90.ab4105",
"order": 3,
"width": 0,
"height": 0,
"gtype": "gage",
"title": "Real Time Måling",
"label": "AbsoluteUnits",
"format": "{{value}}",
"min": 0,
"max": "200",
"colors": [
"#374a7d",
"#567da9",
"#2fa9b3"
],
"seg1": "",
"seg2": "",
"x": 950,
"y": 2440,
"wires": []
},
{
"id": "67dae2b0.25382c",
"type": "function",
"z": "fe9e878d.a1a2b8",
"name": "dataToChartFormat",
"func": "var temp = [];\nvar temp1 = [];\nvar ny = {};\nvar min = global.get(\"minChart\")\nvar max = global.get(\"maxChart\")\nvar k = 0;\nfor (var i = min ; i < max; i++) {\n\ntemp[k] = msg.payload[i].Tid;\ntemp1[k] = msg.payload[i].Val;\nk=k+1;\n\n}\n\nny.labels = temp;\nny.series = ['Data']\nny.data = [temp1];\nreturn {payload:[ny]};\n\n",
"outputs": 1,
"noerr": 0,
"x": 690,
"y": 3040,
"wires": [
[
"5046e579.43305c",
"c8c57070.d066"
]
]
},
{
"id": "5046e579.43305c",
"type": "debug",
"z": "fe9e878d.a1a2b8",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"x": 990,
"y": 3040,
"wires": []
},
{
"id": "c8c57070.d066",
"type": "ui_chart",
"z": "fe9e878d.a1a2b8",
"name": "",
"group": "b8b58d0d.ed85f",
"order": 5,
"width": 0,
"height": 0,
"label": "Graf fra DB",
"chartType": "line",
"legend": "false",
"xformat": "auto",
"interpolate": "linear",
"nodata": "",
"dot": false,
"ymin": "",
"ymax": "",
"removeOlder": "1",
"removeOlderPoints": "",
"removeOlderUnit": "3600",
"cutout": 0,
"useOneColor": false,
"colors": [
"#1f77b4",
"#aec7e8",
"#ff7f0e",
"#2ca02c",
"#98df8a",
"#d62728",
"#ff9896",
"#9467bd",
"#c5b0d5"
],
"useOldStyle": false,
"x": 1010,
"y": 3120,
"wires": [
[],
[]
]
},
{
"id": "d9a70d8f.2fa93",
"type": "function",
"z": "fe9e878d.a1a2b8",
"name": "",
"func": "var newMsg = { payload: 'Logninger i database : ' + msg.payload.length};\nvar newMsg1 = { payload: msg.payload.length};;\nreturn [[newMsg], newMsg1];\n",
"outputs": 2,
"noerr": 0,
"x": 870,
"y": 2860,
"wires": [
[
"ac1914ab.4e04f8",
"c7933fb3.d8727"
],
[]
]
},
{
"id": "ac1914ab.4e04f8",
"type": "debug",
"z": "fe9e878d.a1a2b8",
"name": "",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"x": 1110,
"y": 2800,
"wires": []
},
{
"id": "c7933fb3.d8727",
"type": "ui_text",
"z": "fe9e878d.a1a2b8",
"group": "b8b58d0d.ed85f",
"order": 1,
"width": 0,
"height": 0,
"name": "",
"label": "",
"format": "{{msg.payload}}",
"layout": "col-center",
"x": 1090,
"y": 2840,
"wires": []
},
{
"id": "77ce81ee.6ce8e",
"type": "ui_form",
"z": "fe9e878d.a1a2b8",
"name": "",
"label": "",
"group": "b8b58d0d.ed85f",
"order": 4,
"width": 0,
"height": 0,
"options": [
{
"label": "Indtast minimumsværdi for logningsinterval",
"value": "min",
"type": "number",
"required": true
},
{
"label": "Indtast maximumsværdi for logningsinterval",
"value": "max",
"type": "number",
"required": true
}
],
"formValue": {
"min": "",
"max": ""
},
"payload": "",
"submit": "submit",
"cancel": "cancel",
"topic": "",
"x": 910,
"y": 2900,
"wires": [
[
"bd91a03d.48e01"
]
]
},
{
"id": "bd91a03d.48e01",
"type": "function",
"z": "fe9e878d.a1a2b8",
"name": "var Min/Max",
"func": "/*\nvar msg1 = { payload: msg.payload.min};\nvar msg2 = { payload: msg.payload.max};;\n*/\n\nglobal.set(\"maxChart\" , msg.payload.max);\nglobal.set(\"minChart\" , msg.payload.min);\n\n\n//return [[msg1],msg2]\n",
"outputs": 2,
"noerr": 0,
"x": 1110,
"y": 2900,
"wires": [
[],
[]
]
},
{
"id": "fbac67d.b391298",
"type": "mqtt-broker",
"z": "",
"name": "",
"broker": "localhost",
"port": "1883",
"clientid": "",
"usetls": false,
"compatmode": true,
"keepalive": "60",
"cleansession": true,
"birthTopic": "",
"birthQos": "0",
"birthPayload": "",
"closeTopic": "",
"closeQos": "0",
"closePayload": "",
"willTopic": "",
"willQos": "0",
"willPayload": ""
},
{
"id": "1dc5ca90.ab4105",
"type": "ui_group",
"z": "",
"name": "Knapper",
"tab": "dce20cbd.e90be",
"order": 1,
"disp": false,
"width": "6",
"collapse": false
},
{
"id": "c55cec19.aae03",
"type": "MySQLdatabase",
"z": "",
"host": "127.0.0.1",
"port": "3306",
"db": "myDB",
"tz": ""
},
{
"id": "b8b58d0d.ed85f",
"type": "ui_group",
"z": "",
"name": "Visning",
"tab": "dce20cbd.e90be",
"order": 2,
"disp": false,
"width": "6",
"collapse": false
},
{
"id": "dce20cbd.e90be",
"type": "ui_tab",
"z": "",
"name": "AwesomeDashboard",
"icon": "dashboard",
"order": 1,
"disabled": false,
"hidden": false
}
]</p>
Step 5: Conclusion
I've learned a few things about IOT, that it can be complicated to communicate between different devices, but when the communication is setup it's easy to add extra things to the project.