Data to SQLite help

General
1

Hello,
I am having a hard time inserting a few values into a db.
The flow looks like this;

image
image1300Ă—310 32.5 KB 

[{"id":"1f014414.ca877c","type":"sqlite","z":"f10130b.539efd","mydb":"dd149518.c13258","sqlquery":"msg.topic","sql":"","name":"DataToSQLite","x":1220,"y":180,"wires":[[]]},{"id":"9fd92e27.a46db","type":"change","z":"f10130b.539efd","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1010,"y":180,"wires":[["cb49f6fe.6bc368","1f014414.ca877c"]]},{"id":"3e8af77d.672f08","type":"join","z":"f10130b.539efd","name":"JoinToSQL","mode":"custom","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":"","joinerType":"str","accumulate":false,"timeout":"","count":"3","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":810,"y":180,"wires":[["9fd92e27.a46db"]]},{"id":"a0235603.419c98","type":"change","z":"f10130b.539efd","name":"SQL 3","rules":[{"t":"set","p":"parts.index","pt":"msg","to":"2","tot":"num"},{"t":"set","p":"payload","pt":"msg","to":");","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":630,"y":140,"wires":[["3e8af77d.672f08"]]},{"id":"d6e68690.c7ea98","type":"change","z":"f10130b.539efd","name":"SQL 2","rules":[{"t":"set","p":"parts.index","pt":"msg","to":"1","tot":"num"}],"action":"","property":"","from":"","to":"","reg":false,"x":630,"y":180,"wires":[["3e8af77d.672f08"]]},{"id":"ed91bd36.bb1c2","type":"change","z":"f10130b.539efd","name":"SQL 1","rules":[{"t":"set","p":"parts.index","pt":"msg","to":"0","tot":"num"},{"t":"set","p":"payload","pt":"msg","to":"INSERT INTO Meteo(DateTime, Temperature, Humidity, Dewpoint) VALUES(","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":630,"y":100,"wires":[["3e8af77d.672f08"]]},{"id":"13826652.d6fe7a","type":"join","z":"f10130b.539efd","name":"JoinData","mode":"custom","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":",","joinerType":"str","accumulate":false,"timeout":"","count":"4","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":460,"y":180,"wires":[["53fe10fa.8010e","d6e68690.c7ea98"]]},{"id":"a27d64b9.3b0438","type":"change","z":"f10130b.539efd","name":"Dew 4","rules":[{"t":"set","p":"parts.index","pt":"msg","to":"3","tot":"num"},{"t":"set","p":"payload","pt":"msg","to":"payload.StatusSNS.SI7021.DewPoint","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":270,"y":180,"wires":[["13826652.d6fe7a"]]},{"id":"15ccc837.197228","type":"change","z":"f10130b.539efd","name":"Hum 3","rules":[{"t":"set","p":"parts.index","pt":"msg","to":"2","tot":"num"},{"t":"set","p":"payload","pt":"msg","to":"payload.StatusSNS.SI7021.Humidity","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":270,"y":220,"wires":[["13826652.d6fe7a"]]},{"id":"15e82a12.1003b6","type":"change","z":"f10130b.539efd","name":"Tmp 2","rules":[{"t":"set","p":"parts.index","pt":"msg","to":"1","tot":"num"},{"t":"set","p":"payload","pt":"msg","to":"payload.StatusSNS.SI7021.Temperature","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":270,"y":260,"wires":[["13826652.d6fe7a"]]},{"id":"fd0501fa.46c29","type":"moment","z":"f10130b.539efd","name":"Time 1","topic":"0","input":"payload","inputType":"msg","inTz":"Europe/Bucharest","adjAmount":"0","adjType":"hours","adjDir":"add","format":"YYYY-MM-DDTHH:mm:ss","locale":"en-GB","output":"payload","outputType":"msg","outTz":"Europe/Bucharest","x":270,"y":140,"wires":[["13826652.d6fe7a","e7ac3a8f.157698"]]},{"id":"686ac101.a6d28","type":"inject","z":"f10130b.539efd","name":"Cycle 3m","props":[{"p":"payload"},{"p":"parts.index","v":"0","vt":"num"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":80,"wires":[["fd0501fa.46c29","a0235603.419c98","ed91bd36.bb1c2"]]},{"id":"e7ac3a8f.157698","type":"debug","z":"f10130b.539efd","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"auto","x":450,"y":40,"wires":[]},{"id":"53fe10fa.8010e","type":"debug","z":"f10130b.539efd","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"auto","x":630,"y":260,"wires":[]},{"id":"cb49f6fe.6bc368","type":"debug","z":"f10130b.539efd","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"topic","targetType":"msg","statusVal":"params","statusType":"auto","x":1200,"y":240,"wires":[]},{"id":"bc11f81d.d70108","type":"mqtt in","z":"f10130b.539efd","name":"Sensors","topic":"Meteo/STATUS8","qos":"2","datatype":"json","broker":"a36e2f8a.1c6a6","nl":false,"rap":false,"x":80,"y":180,"wires":[["a27d64b9.3b0438","15ccc837.197228","15e82a12.1003b6"]]},{"id":"dd149518.c13258","type":"sqlitedb","db":"/home/pi/noderedlocal/nodered.db","mode":"RW"},{"id":"a36e2f8a.1c6a6","type":"mqtt-broker","name":"","broker":"192.168.101.3","port":"1883","clientid":"","usetls":false,"compatmode":false,"keepalive":"3","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""}]

The table was created like;
BEGIN;
CREATE TABLE Meteo(DateTime BLOB, Temperature NUMERIC, Humidity NUMERIC, Dewpoint NUMERIC);
COMMIT;

But I tried NUMERIC and INTEGER also for DateTime.
With YYYY-MM-DD HH:mm:ss format the error is;
image

With YYYY-MM-DDTHH:mm:ss format the error is;
image

What am I doing wrong?
Thanks in advance kindly, I am beginner everything and I am running out of hair to pull out.

Edit: fixed the link node.

2

Have you read up about date/time in sqlite3? See Datatypes In SQLite Version 3

1 Like
3

Yes, I read it although I'm not sure I understood it;
So, now, with TEXT datatype for the "DateTime" field with this format;
image
I get;
image
Also,

image
image760Ă—161 11.1 KB

Might be a typo on my side, but still no success.

4

You have no quotes around the date string in the values list.

1 Like
5

Told you I'm a beginner. Thank you!!1

1 Like
6

I am trying to query the last 10 values from the db and plot it with the dashboard chart.
Could you tell me please what I am doing wrong?

image
image732Ă—157 9.03 KB 

[{"id":"cad82fe6.7e372","type":"sqlite","z":"f10130b.539efd","mydb":"dd149518.c13258","sqlquery":"msg.topic","sql":"","name":"DataToSQLite","x":540,"y":160,"wires":[["f1ce9fcc.ca133","7a8f97ab.21cfd8"]]},{"id":"f1ce9fcc.ca133","type":"debug","z":"f10130b.539efd","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"auto","x":730,"y":160,"wires":[]},{"id":"d787d7b9.69cc38","type":"inject","z":"f10130b.539efd","name":"QuerySQL","props":[{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT DateTime, Temperature FROM Meteo ORDER BY DateTime DESC LIMIT 10","x":340,"y":160,"wires":[["cad82fe6.7e372"]]},{"id":"1a9679ac.e089e6","type":"ui_chart","z":"f10130b.539efd","name":"","group":"a67d5ecb.20989","order":8,"width":0,"height":0,"label":"chart","chartType":"line","legend":"false","xformat":"Y-M-D","interpolate":"linear","nodata":"","dot":false,"ymin":"-25","ymax":"45","removeOlder":"15","removeOlderPoints":"10","removeOlderUnit":"60","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"x":910,"y":220,"wires":[[]]},{"id":"7a8f97ab.21cfd8","type":"function","z":"f10130b.539efd","name":"","func":"var chart = [{\n    \"series\":[\"Temp\"],\n    \"data\":[{\"x\":msg.payload[0].DateTime,\"y\":msg.payload[0].Temperature},\n            {\"x\":msg.payload[1].DateTime,\"y\":msg.payload[1].Temperature},\n            {\"x\":msg.payload[2].DateTime,\"y\":msg.payload[2].Temperature},\n            {\"x\":msg.payload[3].DateTime,\"y\":msg.payload[3].Temperature},\n            {\"x\":msg.payload[4].DateTime,\"y\":msg.payload[4].Temperature},\n            {\"x\":msg.payload[5].DateTime,\"y\":msg.payload[5].Temperature},\n            {\"x\":msg.payload[6].DateTime,\"y\":msg.payload[6].Temperature},\n            {\"x\":msg.payload[7].DateTime,\"y\":msg.payload[7].Temperature},\n            {\"x\":msg.payload[8].DateTime,\"y\":msg.payload[8].Temperature},\n            {\"x\":msg.payload[9].DateTime,\"y\":msg.payload[9].Temperature},\n            {\"x\":msg.payload[10].DateTime,\"y\":msg.payload[10].Temperature}],\n    \"labels\":[\"\"]\n}];\nmsg.payload = chart;\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":740,"y":220,"wires":[["1a9679ac.e089e6"]]},{"id":"dd149518.c13258","type":"sqlitedb","db":"/home/pi/noderedlocal/nodered.db","mode":"RW"},{"id":"a67d5ecb.20989","type":"ui_group","name":"Group 1","tab":"fc457ae1.341398","order":1,"disp":false,"width":"6","collapse":false},{"id":"fc457ae1.341398","type":"ui_tab","name":"Workbench","icon":"dashboard","order":4,"disabled":false,"hidden":false}]

image

7

Your query has set a limit 10 but then your function accesses msg.payload[0] though to msg.payload[10]. That's 11 elements.

8

Thank you, the error is gone, but it is not plotting anything.
I modified the example from the chart's github documentation but apparently I misunderstood something.

9

Feed the message you are sending to the chart into a debug node and check it looks right. If you can't see what is wrong then paste it here.

10

image
image869Ă—224 13.1 KB 

[{"id":"cad82fe6.7e372","type":"sqlite","z":"f10130b.539efd","mydb":"dd149518.c13258","sqlquery":"msg.topic","sql":"","name":"DataToSQLite","x":540,"y":160,"wires":[["f1ce9fcc.ca133","7a8f97ab.21cfd8"]]},{"id":"f1ce9fcc.ca133","type":"debug","z":"f10130b.539efd","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"auto","x":730,"y":160,"wires":[]},{"id":"d787d7b9.69cc38","type":"inject","z":"f10130b.539efd","name":"QuerySQL","props":[{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT DateTime, Temperature FROM Meteo ORDER BY DateTime DESC LIMIT 10","x":340,"y":160,"wires":[["cad82fe6.7e372"]]},{"id":"1a9679ac.e089e6","type":"ui_chart","z":"f10130b.539efd","name":"","group":"a67d5ecb.20989","order":8,"width":"0","height":"0","label":"","chartType":"line","legend":"false","xformat":"\"YYY-MM-DDTHH:MM:SS\"","interpolate":"linear","nodata":"N/A","dot":false,"ymin":"-25","ymax":"45","removeOlder":"50","removeOlderPoints":"10","removeOlderUnit":"60","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"x":990,"y":160,"wires":[[]]},{"id":"7a8f97ab.21cfd8","type":"function","z":"f10130b.539efd","name":"","func":"var chart = [{\n    \"series\":[\"Temp\"],\n    \"data\":[{\"x\":msg.payload[0].DateTime,\"y\":msg.payload[0].Temperature},\n            {\"x\":msg.payload[1].DateTime,\"y\":msg.payload[1].Temperature},\n            {\"x\":msg.payload[2].DateTime,\"y\":msg.payload[2].Temperature},\n            {\"x\":msg.payload[3].DateTime,\"y\":msg.payload[3].Temperature},\n            {\"x\":msg.payload[4].DateTime,\"y\":msg.payload[4].Temperature},\n            {\"x\":msg.payload[5].DateTime,\"y\":msg.payload[5].Temperature},\n            {\"x\":msg.payload[6].DateTime,\"y\":msg.payload[6].Temperature},\n            {\"x\":msg.payload[7].DateTime,\"y\":msg.payload[7].Temperature},\n            {\"x\":msg.payload[8].DateTime,\"y\":msg.payload[8].Temperature},\n            {\"x\":msg.payload[9].DateTime,\"y\":msg.payload[9].Temperature},],\n    \"labels\":[\"\"]\n}];\nmsg.payload = chart;\n\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":740,"y":220,"wires":[["1a9679ac.e089e6","c8145226.c295a"]]},{"id":"c8145226.c295a","type":"debug","z":"f10130b.539efd","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"auto","x":910,"y":280,"wires":[]},{"id":"dd149518.c13258","type":"sqlitedb","db":"/home/pi/noderedlocal/nodered.db","mode":"RW"},{"id":"a67d5ecb.20989","type":"ui_group","name":"Group 1","tab":"fc457ae1.341398","order":1,"disp":false,"width":"6","collapse":false},{"id":"fc457ae1.341398","type":"ui_tab","name":"Workbench","icon":"dashboard","order":4,"disabled":false,"hidden":false}]

image

I'm guessing the chart does not "like" the time format on the x axis?

11

Possibly. Try new Date(msg.payload[0].DateTime).getTime()).
You really should use a loop in your function though for filling the array. Initialise the data array to an empty array and use a for loop to cycle through the input array and use push to fill the array. Then you can use msg.payload.length as the loop limit so it doesn't matter how many points there are in it.

1 Like
12

A cyclic query and plot of x values would fill and feed the chart immediately after a reboot (of the host).I'll do some more research, this is way bigger than me.
The documentation has a paragraph where it explains the feeding of the chart from another node (ie. a db) and it points to an example below, while that example contains constants. That is frustrating from a beginner's point of view if you ask me.

13

There was a missing comma at the end of the "data" array before the ]. I am changing my name to typo.

2 Likes
14

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.