Fine tune a "function" node

[fritserasmus]

I am trying to create an INSERT SQL script when reading are sent to the MQTT server.
I am 99% done just sit with two " both sides of the message that I am unable to get rid of.

This is a shortened version of my code but it will suffice.

How do I get rid of the two " s

Code:

topic = msg.topic;

temp = msg.payload

// string1:
string1 = ("INSERT INTO `temperatures`(`source`, `read_date`, `create_date`, `reading`)");

// string2
string2 = (" VALUES (");

// string3
string3 = ("'boiler1','");

msg.payload = string1 + string2 + string3


return msg;

Results: Included are the two " - in front of the INSERT and after boiler1’

“INSERT INTO temperatures(source, read_date, create_date, reading) VALUES (‘boiler1’”

Thanks for helping out.

Rgds

[knolleary]

Where are you seeing the ‘extra’ double quotes?

If it is the Debug sidebar, those quotes are there to show you it is displaying a string - they are not part of the string itself.

FYI, you don’t need to wrap your strings in brackets:

var string1 = "INSERT INTO... ... ";

[Colin]

Are you sure that the additional quotes are actually there? If you think so then please export a small flow (inject -> function -> debug) that demonstrates this.

You don’t need the parentheses round the strings but I don’t think that is the cause of the problem.

[fritserasmus]

Thanks for your prompt reply, but allow me to post all detail:

Debug:

topic = msg.topic;

temp = msg.payload

string1 = "INSERT INTO temperatures(source, read_date, create_date, reading)"; // string1:

string2 = (" VALUES ("); // string2

string3 = ("'boiler1','"); // string3

d= new Date(), //string4 construct and format a date timestamp from now()

string4 =
[d.getFullYear(),
d.getMonth()+1,
d.getDate()
].join('-') + ' ' +

[d.getHours(),
d.getMinutes(),
d.getSeconds()].join(':');

string5 = ("',CURRENT_TIMESTAMP,"); // string5

string6 = (temp); // string6

string7 = (")"); //string7

msg.payload = string1 + string2 + string3 + string4 + string5 + string6 + string7 // Concatenate strings

return msg;

[Colin]

I don’t use the mariadb node, but I imagine it is similar to other db nodes in that the query has to be in the topic and not in the payload. Note that the error shows the string that you have in the topic.

[fritserasmus]

Colin,
Thank you for taking time to reply, but could you please give me some pointers what to change, as I have no idea where to change and what to do to fix this issue.
Frits

[dceejay]

2nd last line of your function … instead of msg.payload = … msg.topic =

[fritserasmus]

dceejay,

Maybe I am just do not have enough grey-matter.

After changing that to msg.topic =
the error message is worse.

OR - Is there an easier way to insert data into MySQL?

Sorry bother you again!

[knolleary]

The error is fairly clear: Table 'Temp_basic.temperatures' doesn't exist

Have you created the table in the database already? You need to do that yourself for that sql insert statement to work.

[fritserasmus]

@knolleary

temperatures

I have created the database yes, but with all my experimenting I did not have the correct table in the database I as trying to INSERT.

Thanks for the help.

Node-RED is now inserting data into the database perfectly!!!!

[fritserasmus]

So I was extremely exited to be able to INSERT records from a temperature sensor...so exited I went and bought three more Node-MCU Arduinos with sensors as I eventually want to monitor temperatures of three boilers.

The issue I now have is that I have two flows: if I connect any of the two it updates the database just fine, but as soon as I connect BOTH none works.

So I changed the topic to two completely different topics, thinking that would help, but still does not work .

(P.s.: The arduinos both open a WiFi connection and keep it open as I want to update temperature readings every 500 milliseconds - I am not sure if that MIGHT cause the issue- but I doubt it)

By the way, we call a boiler a geyser, in case someone do not understand my naming convention:
attached is a snippet of the flow.

Herewith the json:

[{"id":"163a4d52.990043","type":"mqtt in","z":"170d5f99.154f1","name":"","topic":"/18561/geyser1/temp","qos":"2","broker":"93155540.4d2f18","x":120,"y":260,"wires":[["e9b7f867.539dd8","bcb382bc.f8333"]]},{"id":"efe8311.4ce8dd","type":"debug","z":"170d5f99.154f1","name":"Time Temp","active":true,"tosidebar":true,"console":true,"tostatus":true,"complete":"payload","x":550,"y":120,"wires":},{"id":"e9b7f867.539dd8","type":"function","z":"170d5f99.154f1","name":"Add Time","func":"topic = msg.topic;\n\ntemp = msg.payload\n\n//construct and format a date timestamp from now()\nd= new Date(),\n\ndformat = \n[d.getFullYear(),\nd.getMonth()+1,\nd.getDate()\n].join('/') + ',' +\n\n[d.getHours(),\nd.getMinutes(),\nd.getSeconds()].join(':');\n\n//Replace message payload\n\nmsg.payload = dformat + "," + temp\n\nreturn msg;\n","outputs":1,"noerr":0,"x":340,"y":120,"wires":[]},{"id":"bcb382bc.f8333","type":"function","z":"170d5f99.154f1","name":"Create SQL INSERT ","func":"topic = msg.topic;\n\ntemp = msg.payload\n\nstring1 = "INSERT INTO temperatures(source, read_date, create_date, reading)"; // string1:\n\nstring2 = (" VALUES ("); // string2\n\nstring3 = ("'geyser1','"); // string3\n\nd= new Date(), //string4 construct and format a date timestamp from now()\n\nstring4 = \n[d.getFullYear(),\nd.getMonth()+1,\nd.getDate()\n].join('-') + ' ' +\n\n[d.getHours(),\nd.getMinutes(),\nd.getSeconds()].join(':');\n\nstring5 = ("',CURRENT_TIMESTAMP,"); // string5\n\nstring6 = (temp); // string6\n\nstring7 = (")"); //string7\n\nmsg.topic = string1 + string2 + string3 + string4 + string5 + string6 + string7 // Concatenate strings\n\nreturn msg;\n","outputs":1,"noerr":0,"x":360,"y":260,"wires":[["8ef6462.54138b8"]]},{"id":"8ef6462.54138b8","type":"mysql","z":"170d5f99.154f1","mydb":"b5034bb3.da6df8","name":"Geyser1","x":600,"y":280,"wires":[]},{"id":"14753a1a.d5a916","type":"mqtt in","z":"170d5f99.154f1","name":"","topic":"/18562/geyser2/temp","qos":"2","broker":"93155540.4d2f18","x":120,"y":320,"wires":[["b068cc8a.7192b","47b6f46a.1cb52c"]]},{"id":"f6440d1.00c68f","type":"debug","z":"170d5f99.154f1","name":"Time Temp","active":true,"tosidebar":true,"console":true,"tostatus":true,"complete":"payload","x":570,"y":400,"wires":},{"id":"b068cc8a.7192b","type":"function","z":"170d5f99.154f1","name":"Add Time","func":"topic = msg.topic;\n\ntemp = msg.payload\n\n//construct and format a date timestamp from now()\nd= new Date(),\n\ndformat = \n[d.getFullYear(),\nd.getMonth()+1,\nd.getDate()\n].join('/') + ',' +\n\n[d.getHours(),\nd.getMinutes(),\nd.getSeconds()].join(':');\n\n//Replace message payload\n\nmsg.payload = dformat + "," + temp\n\nreturn msg;\n","outputs":1,"noerr":0,"x":340,"y":480,"wires":[]},{"id":"47b6f46a.1cb52c","type":"function","z":"170d5f99.154f1","name":"Create SQL INSERT ","func":"topic = msg.topic;\n\ntemp = msg.payload\n\nstring1 = "INSERT INTO temperatures(source, read_date, create_date, reading)"; // string1:\n\nstring2 = (" VALUES ("); // string2\n\nstring3 = ("'geyser2','"); // string3\n\nd= new Date(), //string4 construct and format a date timestamp from now()\n\nstring4 = \n[d.getFullYear(),\nd.getMonth()+1,\nd.getDate()\n].join('-') + ' ' +\n\n[d.getHours(),\nd.getMinutes(),\nd.getSeconds()].join(':');\n\nstring5 = ("',CURRENT_TIMESTAMP,"); // string5\n\nstring6 = (temp); // string6\n\nstring7 = (")"); //string7\n\nmsg.topic = string1 + string2 + string3 + string4 + string5 + string6 + string7 // Concatenate strings\n\nreturn msg;\n","outputs":1,"noerr":0,"x":360,"y":320,"wires":[["8ef6462.54138b8"]]},{"id":"93155540.4d2f18","type":"mqtt-broker","z":"","name":"MQTTonUBUNTU","broker":"192.168.1.110","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"30","cleansession":true,"birthTopic":"","birthQos":"1","birthPayload":"","closeTopic":"","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"b5034bb3.da6df8","type":"MySQLdatabase","z":"170d5f99.154f1","host":"127.0.0.1","port":"3306","db":"Temp_Basic","tz":""}]

Any suggestions how I can fix it would be greatly appreciated.

Frits

[zenofmud]

@fritserasmus

The issue I now have is that I have two flows: if I connect any of the two it updates the database just fine, but as soon as I connect BOTH none works.

Are you getting any error messages? try adding a 'catch' node attached to a debug (complete msg object' and see what you get

[fritserasmus]

I have established that it is NOT Node-RED related, but rather the Arduino that does not connect to the WiFi when another one is already connected.

I will pursue the solution there.

[Colin]

Have you possibly set fixed ip addresses in the arduinos, with the same ip for both?

[dceejay]

or the same MQTT clientID ? - they must be unique or the second will knock the first off the network - that will then try to reconnect and knock off the second etc…

[fritserasmus]

@dceejay, that was what I had wrong.

Its now fixed, but two specific ardino's still just stop sending data.

Still trying to figure out what the issue is

`

[fritserasmus]

Just wanted to report back on the solution:

So instead of rebooting these arduinos with the temperature sensors I flashed the ESP8266's with Tasmota.
Currently I am running 4 of them and they just happily report every 5 minutes