Storing NODE-RED input into an SQLite database

[fadzfiras]

I have been given a task to store a set of data into an SQLite database. I have to code the function node (format data) so the SQLite node is able to store my data. The 'database' node accepts SQL Query via msg.topic.

Here is the flow I have constructed

Below is the code for my 'input data' function node.

Would greatly appreciate any help regarding the 'format data' node.

[zenofmud]

You would be better off using a template node to format the query. Here is a sample flow showing you how you could do it. NOTE: you will have to define the path to the database in the sqlite node.

[{"id":"7acc20b7.b27b9","type":"tab","label":"SQLite test","disabled":false,"info":""},{"id":"a06bd52c.105218","type":"sqlite","z":"7acc20b7.b27b9","mydb":"3594a0ec.6329a8","sqlquery":"msg.topic","sql":"","name":"testdb","x":690,"y":320,"wires":[["9289f4ef.b44a9"]]},{"id":"6b5a0680.fcd06","type":"inject","z":"7acc20b7.b27b9","name":"create table","repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payload":"","payloadType":"date","x":150,"y":260,"wires":[["1a35bc39.05bdd4"]]},{"id":"51107643.82d748","type":"inject","z":"7acc20b7.b27b9","name":"insert","repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payload":"","payloadType":"date","x":130,"y":320,"wires":[["b65516ba.aeb3e"]]},{"id":"a4ece8df.0e597","type":"inject","z":"7acc20b7.b27b9","name":"select","repeat":"","crontab":"","once":false,"onceDelay":"","topic":"select * from greenhouse","payload":"","payloadType":"date","x":250,"y":200,"wires":[["a06bd52c.105218"]]},{"id":"1a35bc39.05bdd4","type":"template","z":"7acc20b7.b27b9","name":"Create","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"CREATE TABLE greenhouse (\n  id          INTEGER PRIMARY KEY AUTOINCREMENT, \n  temperature NUMERIC, \n  humidity    NUMERIC, \n  datetime    DATE, \n  device TEXT\n)","output":"str","x":370,"y":260,"wires":[["a06bd52c.105218"]]},{"id":"b65516ba.aeb3e","type":"template","z":"7acc20b7.b27b9","name":"Insert manual","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO greenhouse (\n  temperature, \n  humidity, \n  datetime, \n  device\n  ) \nVALUES (\n  22.4, \n  48, \n  date('now'),\n  \"room3\"\n  )","output":"str","x":400,"y":320,"wires":[["a06bd52c.105218"]]},{"id":"c1f2f118.befeb8","type":"inject","z":"7acc20b7.b27b9","name":"insert","repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payload":"","payloadType":"date","x":130,"y":380,"wires":[["27490a6f.de7636"]]},{"id":"67e31919.5f33c8","type":"template","z":"7acc20b7.b27b9","name":"Insert manual","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO greenhouse (\n  temperature, \n  humidity, \n  datetime, \n  device\n  ) \nVALUES (\n  {{payload.temperature}}, \n  {{payload.humidity}},\n  {{payload.datetime}},\n  \"{{payload.device}}\"\n  )","output":"str","x":540,"y":380,"wires":[["a06bd52c.105218","dbbade4d.e358d8"]]},{"id":"27490a6f.de7636","type":"change","z":"7acc20b7.b27b9","name":"","rules":[{"t":"delete","p":"payload","pt":"msg"},{"t":"set","p":"date","pt":"msg","to":"","tot":"date"},{"t":"set","p":"payload.temperature","pt":"msg","to":"72.3","tot":"num"},{"t":"set","p":"payload.humidity","pt":"msg","to":" 64","tot":"num"},{"t":"set","p":"payload.datetime","pt":"msg","to":"","tot":"date"},{"t":"set","p":"payload.device","pt":"msg","to":"room1","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":340,"y":380,"wires":[["67e31919.5f33c8"]]},{"id":"9289f4ef.b44a9","type":"debug","z":"7acc20b7.b27b9","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":850,"y":320,"wires":[]},{"id":"dbbade4d.e358d8","type":"debug","z":"7acc20b7.b27b9","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":750,"y":420,"wires":[]},{"id":"30750c40.20fb2c","type":"comment","z":"7acc20b7.b27b9","name":"SQLite Test flow","info":"","x":540,"y":140,"wires":[]},{"id":"3594a0ec.6329a8","type":"sqlitedb","db":"testdb"}]