I have an MQTT client on Node-RED subscribed to a client that publish temperature. Now I want to save this value into SQLite. Any solutions?
Hi and welcome to the forum.
Have you looked at/tried the examples and available nodes?
https://flows.nodered.org/?term=sqlite&num_pages=1
What have you tried so far?
Perhaps post your flow/screenshots and we can attempt to help?
Have you installed the node-red-node-SQLite node yet?
How much sql do you know?
What platform are you using?
Have you looked on the forums flows page (search for SQLite)?
What have you tried?
I tried using the inject node but the thing is I don't know how to take the output of the mqtt node.
Have you installed the node-red-node-SQLite node yet?
R. Yes
How much sql do you know?
R. I know the basics (Create tables, insert, delete, etc)
What platform are you using?
R. NodeMCU (ESP8266) to publish temperature, Raspberry Pi as a broker and Node-Red MQTT node subscribed to the NodeMCU to get the temperature.
What have you tried?
R.I tried using the inject node but the thing is I don't know how to take the output of the mqtt node.
Ricky,
If you have an mqtt-in node subscribed to the topic this will be your input. Now all you have to do is take the msg coming out of the mqtt-in node and format an sqlite insert. You can do it a couple of ways, but using the template node is an easy way because you can layout the sql on multiple lines. Here is an example:
[{"id":"8b833332.b4e368","type":"debug","z":"bc514887.098148","name":"TEMPLATE","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":670,"y":220,"wires":[]},{"id":"3a2719cb.35e3be","type":"template","z":"bc514887.098148","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO temperature (\n temperature,\n humidity,\n look,\n detail\n)\nVALUES (\n {{payload.tempc}},\n {{payload.humidity}},\n \"{{payload.weather}}\",\n \"{{payload.detail}}\"\n);","output":"str","x":400,"y":220,"wires":[["8b833332.b4e368"]]},{"id":"d3cac669.985868","type":"inject","z":"bc514887.098148","name":"","topic":"","payload":"{\"tempc\":45,\"humidity\":16.4,\"weather\":\"Clear\",\"detail\":\"Sunny all day\"}","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":130,"y":220,"wires":[["3a2719cb.35e3be"]]}]
See if this gets you going - your input to the template will be the output of mqtt-in node
2 Likes
Hi Paul,
That's a neat little idea of using a Template node.
1 Like
Yep -- much more readable that a bunch of JS string manipulations... (although I've heard that ES6 supports string templates with variable substitution -- just can't remember the syntax)
BUT, be aware that mustache substitution inside double-braces {{ ... }} may uri encode your data, which is probably NOT what you want when saving to a database. Safer to use the triple-braces instead (to avoid the encoding). And as with all SQL statements, you'll need to provide the right quotes (or not) to match the datatypes of the columns in your table.
Hi Steve,
I actually think you inroduced me to this neat trick many, many months ago.
I've used it quite a few times in various flows as it makes 'composing' the query a lot easier.
I'll take on board what you say about the use of the triple curly brackets.
Regards, David.
To do this use backticks instead of quotes and ${expression) as the value to inject. For example
msg.payload = `Some string with ${msg.payload} inserted`
then if before executing this msg.payload contained "my string" the result would be "Some string with my string inserted".
1 Like
For the sake of complete transparency and credit, @dynamicdave showed me the use of the template and said he got it from @shrickus who deserves the credit...unless he got it from someone else 
To expand on what @shrickus said about two brackets {{...}} verses three {{{..}}}:
In this example flow there is one set of data coming in the two template nodes. The top template uses double brackets {{...}} and the botom one uses three brackets {{{...}}}.
If you send in a text string like "Clear&Dry" or a string with an HTML character like "Sunny all day
Rain tomorrow"
you will see different results
This is what comes from the template with the double brackets:
Notice that the
& has been encoded to & and the <br> has been encoded to <br>
Here is the output from the three brackets:
Notice the
& and <br> have remained unchanged.
Here is the sample flow:
Bracket_Example.json (1.9 KB)
2 Likes
Paul,
I think you have posted the wrong json file.
This one appears to be lots and lots of code-snips.
oops...fixed now
