Hi there,
I'm very new to node red so apologies in advance if I make a mis-step with posting for help. My question outlined here, is how do I get two MQTT messages to input into an SQLite database as a single entry.
I have followed a number of youtube tutorials and almost everything works as expected. My current problem is...
I have an ESP32 reading temperature and humidity simultaneously, they are coming across separately via different MQTT threads for Node Red dashboard. This is working fine.
Temperature MQTT and a Humidity MQTT message are then both set to a function to change the msg.topic to Temperature and Humidity (respectively). These function nodes go into 'join' with a manual setup, setting combine each msg.payload to create a key/value using the value of msg.topic as the key. It is set to send the message after every 2 message parts. The debug from this fires once and is as follows;
30/10/2022, 19:09:13node: debug 17Humidity : msg.payload : Object
{ Temperature: 23.2, Humidity: 64.5 }
From this I take that the join is working fine as both values come across as one message. From here the join I go into a function node with the following; (Which I put together from the ChilliChump YouTube tutorial on MQTT and SQLite).
var sqliteTimeStamp = Math.round(Date.now() / 1000);
var theDevice = "Lounge";
// var theSensor = "Humidity";
var theSQL = "INSERT INTO Test1 (Date_Time, Area, Temperature, Humidity) VALUES "
theSQL = theSQL + "('" + sqliteTimeStamp + "', '" + theDevice + "', " + msg.payload.Temperature +", " + msg.payload.Humidity + " );" ;
msg.topic = theSQL;
return msg;
The debug of this function gives me this;
30/10/2022, 19:14:34node: debug 16INSERT INTO Test1 (Date_Time, Area, Temperature, Humidity) VALUES ('1667157274', 'Lounge', 23.3, 64.6 ); : msg.payload : Object
{ Temperature: 23.3, Humidity: 64.6 }
From this I take it that an INSERT into the table has both values in a single SQL insert ... "message". However when I look at the SQL Database, I'm seeing that the values are separated out into two entries.
ID Date_Time Area Temperature Humidity
455 1667155259 Lounge 22.5 NULL
456 1667155259 Lounge NULL 65.8
457 1667155269 Lounge 22.5 NULL
458 1667155269 Lounge NULL 65.8
459 1667155279 Lounge 22.5 NULL
460 1667155279 Lounge NULL 65.8
As you can hopefully see, the SQL isn't getting or perhaps treating the message as a joint 'package' and is separating out to two ID's the values that should come across as one.
I've done alot of googling and watched many tutorials. As you can hopefully see I've at least fixed the common mistakes and the data is going into the fields but not 100% as expected. What I'm hoping to achieve is a single SQL entry with both values populated to avoid NULL and duplicate entries.
As a new user I'm not sure what else I can supply to help trouble shoot this. So please do let me know if more information is uploaded. But I'm also a new user so suspect I will be limited in the number of responses and what I can upload.
Many thanks.
Josh
