I’m trying to learn how to get temp, humidity, and pressure readings into a SQLite database, my big hang up is the function node code, any suggestions on where I can find a tutorial,
Thanks
Welcome to the forum.
Without telling us what the function node should do it is impossible to help. Show us what the message going in looks like in a debug node and tell us exactly what it should output.
lets see if I can give an overview.
[{"id":"2beed822.5ff3d8","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"296aeedb.892322","type":"mqtt in","z":"2beed822.5ff3d8","name":"","topic":"/swa/temperature","qos":"1","datatype":"auto","broker":"a24c936.fc6627","x":170,"y":100,"wires":[["f934f9df.0f3d68","6d234b04.048054","4b03aa41.f9d284"]]},{"id":"939c5659.af85c8","type":"mqtt in","z":"2beed822.5ff3d8","name":"","topic":"/swa/humidity","qos":"1","datatype":"auto","broker":"a24c936.fc6627","x":160,"y":200,"wires":[["f934f9df.0f3d68","6d234b04.048054"]]},{"id":"e03bbfcc.00ff3","type":"mqtt in","z":"2beed822.5ff3d8","name":"","topic":"/swa/pressure","qos":"1","datatype":"auto","broker":"a24c936.fc6627","x":160,"y":300,"wires":[["f934f9df.0f3d68","6d234b04.048054"]]},{"id":"a73a9c2b.0ff6","type":"mqtt in","z":"2beed822.5ff3d8","name":"volt","topic":"/swa/volt","qos":"2","datatype":"auto","broker":"a24c936.fc6627","x":150,"y":400,"wires":[["f934f9df.0f3d68","6d234b04.048054"]]},{"id":"f934f9df.0f3d68","type":"function","z":"2beed822.5ff3d8","name":"Add Time","func":"\nmsg.payload = msg.payload + new Date().toString();\n\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":440,"y":360,"wires":[["4e7fb055.3757e"]]},{"id":"4e7fb055.3757e","type":"file","z":"2beed822.5ff3d8","name":"","filename":"/mnt/hdd/data.log","appendNewline":true,"createDir":true,"overwriteFile":"false","encoding":"none","x":670,"y":360,"wires":[]},{"id":"6d234b04.048054","type":"function","z":"2beed822.5ff3d8","name":"","func":"var sqliteTimeStamp = Math.round(Date.now() / 1000); //converting to seconds instead of milliseconds epoch;\nvar theDevice = "ESP8266";\nvar theSensor = "temp";\nvar theSQL = "INSERT INTO IOTSensors (timestamp, deviceName, sensor, reading) VALUES ";\ntheSQL = theSQL + "('" + sqliteTimeStamp + "', '" + theDevice +"', '" + theSensor + "', " + msg.payload + ");";\nmsg.topic = theSQL;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":440,"y":160,"wires":[["797df5e4.04b51c","8fc80f1a.99156"]]},{"id":"797df5e4.04b51c","type":"debug","z":"2beed822.5ff3d8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":720,"y":100,"wires":},{"id":"8fc80f1a.99156","type":"sqlite","z":"2beed822.5ff3d8","mydb":"f7816a86.b66188","sqlquery":"msg.topic","sql":"","name":"Data","x":620,"y":40,"wires":[]},{"id":"4b03aa41.f9d284","type":"debug","z":"2beed822.5ff3d8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":360,"y":60,"wires":},{"id":"a24c936.fc6627","type":"mqtt-broker","z":"","name":"BME280","broker":"localhost","port":"1883","clientid":"","usetls":false,"compatmode":false,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"1","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"f7816a86.b66188","type":"sqlitedb","z":"2beed822.5ff3d8","db":"IOT.db","mode":"RWC"}]
When posting a flow please use the </> button at the top of the forum entry window and paste it in there. this puts the flow between lines containing three backticks which stops the forum messing with it. You can edit your post and put those in so your flow will be importable.
However we don't need the flow, as I said if you don't know how to code a function all we need is the debug output showing what is going into the function and for you to tell us what you want out.
Here is whats going into the function:
11/12/2020, 6:25:40 PMnode: 4b03aa41.f9d284
/swa/temperature : msg.payload : string[5]
"65.75"
11/12/2020, 6:25:40 PMnode: 4b03aa41.f9d284
/swa/humidity : msg.payload : string[5]
"58.04"
11/12/2020, 6:25:40 PMnode: 4b03aa41.f9d284
/swa/pressure : msg.payload : string[7]
"1013.01"
11/12/2020, 6:25:40 PMnode: 4b03aa41.f9d284
/swa/volt : msg.payload : string[4]
"3.68"
what I would like is the function to manipulate the Date Time, Temp(reading), Hum(reading), Pressure(reading), and the Volt(reading)
so I can input it in a SQLite database,
Thanks for your time
Hi Jerry,
Your last posts are useful but can you also edit the post with your flow according to Colin's directions ?
because as it is now we cannot import it. (Use the pencil icon to edit and then paste the flow again)
Also i wanted to ask if you have already created the Sqlite db table and fields
[{"id":"2beed822.5ff3d8","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"296aeedb.892322","type":"mqtt in","z":"2beed822.5ff3d8","name":"","topic":"/swa/temperature","qos":"1","datatype":"auto","broker":"a24c936.fc6627","x":170,"y":100,"wires":[["f934f9df.0f3d68","6d234b04.048054","4b03aa41.f9d284"]]},{"id":"939c5659.af85c8","type":"mqtt in","z":"2beed822.5ff3d8","name":"","topic":"/swa/humidity","qos":"1","datatype":"auto","broker":"a24c936.fc6627","x":160,"y":200,"wires":[["f934f9df.0f3d68","6d234b04.048054","4b03aa41.f9d284"]]},{"id":"e03bbfcc.00ff3","type":"mqtt in","z":"2beed822.5ff3d8","name":"","topic":"/swa/pressure","qos":"1","datatype":"auto","broker":"a24c936.fc6627","x":160,"y":300,"wires":[["f934f9df.0f3d68","6d234b04.048054","4b03aa41.f9d284"]]},{"id":"a73a9c2b.0ff6","type":"mqtt in","z":"2beed822.5ff3d8","name":"volt","topic":"/swa/volt","qos":"2","datatype":"auto","broker":"a24c936.fc6627","x":150,"y":400,"wires":[["f934f9df.0f3d68","6d234b04.048054","4b03aa41.f9d284"]]},{"id":"f934f9df.0f3d68","type":"function","z":"2beed822.5ff3d8","name":"Add Time","func":"\nmsg.payload = msg.payload + new Date().toString();\n\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":440,"y":360,"wires":[["4e7fb055.3757e"]]},{"id":"4e7fb055.3757e","type":"file","z":"2beed822.5ff3d8","name":"","filename":"/mnt/hdd/data.log","appendNewline":true,"createDir":true,"overwriteFile":"false","encoding":"none","x":670,"y":360,"wires":[[]]},{"id":"6d234b04.048054","type":"function","z":"2beed822.5ff3d8","name":"","func":"var sqliteTimeStamp = Math.round(Date.now() / 1000); //converting to seconds instead of milliseconds epoch;\nvar theDevice = \"ESP8266\";\nvar theSensor = \"temp\";\nvar theSQL = \"INSERT INTO IOTSensors (timestamp, deviceName, sensor, reading) VALUES \";\ntheSQL = theSQL + \"('\" + sqliteTimeStamp + \"', '\" + theDevice +\"', '\" + theSensor + \"', \" + msg.payload + \");\";\nmsg.topic = theSQL;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":440,"y":160,"wires":[["8fc80f1a.99156"]]},{"id":"8fc80f1a.99156","type":"sqlite","z":"2beed822.5ff3d8","mydb":"f7816a86.b66188","sqlquery":"msg.topic","sql":"","name":"Data","x":620,"y":40,"wires":[[]]},{"id":"4b03aa41.f9d284","type":"debug","z":"2beed822.5ff3d8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":360,"y":60,"wires":[]},{"id":"a24c936.fc6627","type":"mqtt-broker","z":"","name":"BME280","broker":"localhost","port":"1883","clientid":"","usetls":false,"compatmode":false,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"1","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"f7816a86.b66188","type":"sqlitedb","z":"2beed822.5ff3d8","db":"IOT.db","mode":"RWC"}]
I have created the database and fields
Table would be a simple table with following fields ID, DATETIME, TEMP, HUM, PRES, VOLT
You can use a Join node to get all the values into one message. Look in the node red cookbook for the page on joining streams for an example of how to do it.
Hi Jerry,
The important thing is what Colin said .. to use a Join node.
Because the MQTT messages arrive at different times .. you need to use the Join node to wait and gather all 4 msgs and then send them to your function so the values can be simultaneously available.
After that, the Function node can store the mqtt values to JS variables in order to construct the sql query in msg.topic
// fields ID, DATETIME, TEMP, HUM, PRES, VOLT
let temp = msg.payload["/swa/temperature"]
let hum = msg.payload["/swa/hum"]
let pres = msg.payload["/swa/pres"]
let volt = msg.payload["/swa/volt"]
msg.topic = `INSERT INTO IOTSensors (DATETIME, TEMP, HUM, PRES, VOLT) VALUES (datetime('now'), ${temp}, ${hum}, ${pres}, ${volt})`;
return msg
Test Flow :
[{"id":"296aeedb.892322","type":"mqtt in","z":"2beed822.5ff3d8","name":"","topic":"/swa/temperature","qos":"1","datatype":"json","broker":"a24c936.fc6627","x":160,"y":100,"wires":[["b33b5a6b.b8873"]]},{"id":"939c5659.af85c8","type":"mqtt in","z":"2beed822.5ff3d8","name":"","topic":"/swa/humidity","qos":"1","datatype":"json","broker":"a24c936.fc6627","x":150,"y":200,"wires":[["b33b5a6b.b8873"]]},{"id":"e03bbfcc.00ff3","type":"mqtt in","z":"2beed822.5ff3d8","name":"","topic":"/swa/pressure","qos":"1","datatype":"json","broker":"a24c936.fc6627","x":150,"y":300,"wires":[["b33b5a6b.b8873"]]},{"id":"a73a9c2b.0ff6","type":"mqtt in","z":"2beed822.5ff3d8","name":"volt","topic":"/swa/volt","qos":"2","datatype":"json","broker":"a24c936.fc6627","x":130,"y":400,"wires":[["b33b5a6b.b8873"]]},{"id":"f934f9df.0f3d68","type":"function","z":"2beed822.5ff3d8","name":"Add Time","func":"\nmsg.payload = msg.payload + new Date().toString();\n\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":640,"y":480,"wires":[["4e7fb055.3757e"]]},{"id":"4e7fb055.3757e","type":"file","z":"2beed822.5ff3d8","name":"","filename":"/mnt/hdd/data.log","appendNewline":true,"createDir":true,"overwriteFile":"false","encoding":"none","x":850,"y":480,"wires":[[]]},{"id":"6d234b04.048054","type":"function","z":"2beed822.5ff3d8","name":"","func":"//var sqliteTimeStamp = Math.round(Date.now() / 1000); //converting to seconds instead of milliseconds epoch;\n\n//var theSQL = \"INSERT INTO IOTSensors (timestamp, deviceName, sensor, reading) VALUES \";\n//theSQL = theSQL + \"('\" + sqliteTimeStamp + \"', '\" + theDevice +\"', '\" + theSensor + \"', \" + msg.payload + \");\";\n\nlet theDevice = \"ESP8266\";\n\n// fields ID, DATETIME, TEMP, HUM, PRES, VOLT\nlet temp = msg.payload[\"/swa/temperature\"]\nlet hum = msg.payload[\"/swa/hum\"]\nlet pres = msg.payload[\"/swa/pres\"]\nlet volt = msg.payload[\"/swa/volt\"]\n\nmsg.topic = `INSERT INTO IOTSensors (DATETIME, TEMP, HUM, PRES, VOLT) VALUES (datetime('now'), ${temp}, ${hum}, ${pres}, ${volt})`;\n\nreturn msg","outputs":1,"noerr":0,"initialize":"","finalize":"","x":660,"y":240,"wires":[["8fc80f1a.99156","77445301.37fb74"]]},{"id":"8fc80f1a.99156","type":"sqlite","z":"2beed822.5ff3d8","mydb":"f7816a86.b66188","sqlquery":"msg.topic","sql":"","name":"Data","x":930,"y":240,"wires":[[]]},{"id":"4b03aa41.f9d284","type":"debug","z":"2beed822.5ff3d8","name":"1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":490,"y":160,"wires":[]},{"id":"b33b5a6b.b8873","type":"join","z":"2beed822.5ff3d8","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"4","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":390,"y":240,"wires":[["4b03aa41.f9d284","6d234b04.048054"]]},{"id":"eb432989.b559a","type":"mqtt out","z":"2beed822.5ff3d8","name":"","topic":"/swa/temperature","qos":"1","retain":"","broker":"a24c936.fc6627","x":470,"y":680,"wires":[]},{"id":"96aaf19a.1a16e8","type":"inject","z":"2beed822.5ff3d8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"23","payloadType":"num","x":190,"y":680,"wires":[["eb432989.b559a"]]},{"id":"e52edafc.6d4048","type":"mqtt out","z":"2beed822.5ff3d8","name":"","topic":"/swa/humidity","qos":"1","retain":"","broker":"a24c936.fc6627","x":450,"y":780,"wires":[]},{"id":"1344f036.91e1d","type":"inject","z":"2beed822.5ff3d8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"60","payloadType":"num","x":180,"y":780,"wires":[["e52edafc.6d4048"]]},{"id":"acf7ebc5.acb5b","type":"comment","z":"2beed822.5ff3d8","name":"Simulate MQTT message from device","info":"","x":350,"y":580,"wires":[]},{"id":"5b55c6e4.df491","type":"mqtt out","z":"2beed822.5ff3d8","name":"","topic":"/swa/pressure","qos":"1","retain":"","broker":"a24c936.fc6627","x":440,"y":880,"wires":[]},{"id":"370d0bb8.cdfd8c","type":"inject","z":"2beed822.5ff3d8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"2.3","payloadType":"num","x":170,"y":880,"wires":[["5b55c6e4.df491"]]},{"id":"b2f7352.09871c8","type":"mqtt out","z":"2beed822.5ff3d8","name":"","topic":"/swa/volt","qos":"1","retain":"","broker":"a24c936.fc6627","x":410,"y":980,"wires":[]},{"id":"f209598e.3f8988","type":"inject","z":"2beed822.5ff3d8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"233.12","payloadType":"str","x":160,"y":980,"wires":[["b2f7352.09871c8"]]},{"id":"77445301.37fb74","type":"debug","z":"2beed822.5ff3d8","name":"2","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":790,"y":160,"wires":[]},{"id":"a24c936.fc6627","type":"mqtt-broker","name":"BME280","broker":"localhost","port":"1883","clientid":"","usetls":false,"compatmode":false,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"1","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"f7816a86.b66188","type":"sqlitedb","z":"2beed822.5ff3d8","db":"IOT.db","mode":"RWC"}]
If you have access to what ever is sending the MQTT msgs, why not change it to combine all the readings into one msg?
OK getting close , but this is what i got:
11/14/2020, 4:19:15 PMnode: 4b03aa41.f9d284
INSERT INTO IOTSensors (DATETIME, TEMP, HUM, PRES, VOLT) VALUES (datetime('now'), 65.75, undefined, undefined, 3.68) : msg.payload : Object
{ /swa/temperature: "65.75", /swa/humidity: "58.04", /swa/pressure: "1013.01", /swa/volt: "3.68" }
11/14/2020, 4:19:15 PMnode: Data
msg : error
"Error: SQLITE_ERROR: no such column: undefined"
Do I need to call my column /swa/temperature ...........
my bad .. in the Function there were a couple of copy/paste mistakes
Two values are undefined because hum should have been humidity and pres -> pressure
let temp = msg.payload["/swa/temperature"]
let hum = msg.payload["/swa/humidity"]
let pres = msg.payload["/swa/pressure"]
let volt = msg.payload["/swa/volt"]
No .. the way you defined them is fine .. but do confirm that they have the correct field names as you mentioned in you previous post
In order to check there's a handy utility called DB Browser for SQLite
Since you are on a PI you can install it with the following directions
well I got this, but now it's not updating
← T → ID DATETIME TEMP HUM PRES VOLT
Edit Delete 1 NULL NULL NULL NULL NULL
Edit Delete 2 2020-11-15 02:06:32 65.75 58.04 1013.01 3.68
Edit Delete 3 2020-11-15 02:09:02 65.75 58.04 1013.01 3.68
I'll play around, thanks for all your help
It's Working thanks.... anyway of getting the time to Pacific coast time
Usually its good practice to save the time in UTC and then on the Select query to convert it to whatever Timezone you want. But if you going to be the only one reading the data and you want your TZ then ...
in our Insert command we have datetime('now')
try datetime('now','localtime')
