Hello everybody. I'm new to Node Red.
I'm about to replace a JS module that has been running on my NAS for some time, with Node Red. It is about writing the content of an MQTT message into a MariaDB 5. Everything works fine but I get the above error message from the DB node, although the data is written correctly to the DB
Message payload example: "22.0,22.4,16.2"
Output of the DB node after an order: {"fieldCount":0,"affectedRows":1,"insertId":0,"serverStatus":2,"warningCount":0,"message":"","protocol41":true,"changedRows":0}
Code in the Function-Node:
if(msg.topic == 'Heizung/InnenAussen'){
var splited = msg.payload.split(",");
if (splited.length == 3){ // Damit wird absturz vermieden wenn unten drei value gefragt sind aber nur 2 gesendet
var setRoom = splited[0];
var actRoom = splited[1];
var actOut = splited[2];
msg.topic = "INSERT INTO `Heizung1` (`tempSet`,`tempAct`,`tempOut`) VALUES ("+setRoom+", "+actRoom+", "+actOut+")";
}
}
return msg;
Maybe something with the backticks or quotes or apostrophes?
Thank You for your Help.
I imagine the error is coming from a different message, rather than the one that shows the good output. I notice that you have two db nodes, could the error be coming from the other one.
Add debug nodes after the function nodes, showing what you are passing to the sql node and also show us the full error message.
Hi Steve-Mci
With every write job, I get the following messages in the debug window.
I get this messages from both DB nodes (Maria DB 1 and Maria DB 2)
And what do you see going into the db nodes?
Make sure you fully expand the debug output. Also set them to Show Complete Message, then it is easier to see the data.
And suddenly it makes sense. Your function node checks for msg.topic to match an mqtt topic and if so changes topic and payload to a database query. But what if it doesn’t match the exact topic? Look at the last line of your function node: return msg. So in that cases the query used for the database is your mqtt topic. Put a debug node before the function node set to full message, and see what exactly goes in your function nodes.
Coming out of your mqtt node I suggest putting down a switch to route where the msgis going to prevent innenausen to go to the vorlauf and the other way around.
if(msg.topic == 'Heizung/InnenAussen'){
var splited = msg.payload.split(",");
if (splited.length == 3){ // Damit wird absturz vermieden wenn unten drei value gefragt sind aber nur 2 gesendet
var setRoom = splited[0];
var actRoom = splited[1];
var actOut = splited[2];
msg.topic = "INSERT INTO `Heizung1` (`tempSet`,`tempAct`,`tempOut`) VALUES ("+setRoom+", "+actRoom+", "+actOut+")";
return msg;
}
}
return null;
If instead you switch your routing logic from the function nodes to a switch node(s), then keep the changing of the topic in a function or change node and retry I think it might work out. But for actual database handling Steve is the best to answer. I’m just here to spot these small things
You could try the function steve posted just above this message, or keep the if/else logic in switch nodes prior to the function node.
I wonder, are there any configuration differences between your 2 database nodes and how they’re set up? Or could it perhaps be simplified into going into the same db node?
There are two tables in the same database.
The whole thing could possibly be solved in one function with both queries in it.
The MQTT subscription is: " Heizung/# ", so two topics arrive at the output of the MQTT node. The heating sends them immediately one after the other.
It was clearer for me this way. Is there an advantage if I combine them?
You should be able to send both function node outputs to the same db node, unless the configuration of the two db nodes is different. It will do one query then the other.
if(msg.topic == 'Heizung/InnenAussen'){
var splited = msg.payload.split(",");
if (splited.length == 3){ // Damit wird absturz vermieden wenn unten drei value gefragt sind aber nur 2 gesendet
var setRoom = splited[0];
var actRoom = splited[1];
var actOut = splited[2];
msg.topic = "INSERT INTO `Heizung1` (`tempSet`,`tempAct`,`tempOut`) VALUES ("+setRoom+", "+actRoom+", "+actOut+")";
return msg;
}
}
if(msg.topic == 'Heizung/Vorlauf'){
var splited = msg.payload.split(",");
if (splited.length == 3){ // Damit wird absturz vermieden wenn unten drei value gefragt sind aber nur 2 gesendet
var setVL = splited[0];
var actVL = splited[1];
var actSpeicher = splited[2];
msg.topic = "INSERT INTO `Heizung2` (`vorlaufSoll`,`vorlaufIst`,`speicherIst`) VALUES ("+setVL+", "+actVL+", "+actSpeicher+")";
return msg;
}
}
return null;
