# Data to SQLite help

**URL:** <https://discourse.nodered.org/t/data-to-sqlite-help/44414>\
**Category:** General\
**Created:** [19 April 2021 18:17 UTC](https://discourse.nodered.org/t/data-to-sqlite-help/44414 "2021-04-19T18:17:14Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![biros](https://avatars.discourse-cdn.com/v4/letter/b/e47c2d/32.png) [@biros](https://discourse.nodered.org/u/biros)\
**Post date:** [19 April 2021 18:17 UTC](https://discourse.nodered.org/t/data-to-sqlite-help/44414/1 "2021-04-19T18:17:15Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/d/1d1de2fe002903c318b99e1f28066d6752e1b0fc.png)

```auto
[{"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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/d/cd74da1de9e640fde6b08eb161307564204356ef.png)

With YYYY-MM-DDTHH:mm:ss format the error is;  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/9/096e47d230d1200f1ad3ab885b980a3e9e8efded.png)

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.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [19 April 2021 18:28 UTC](https://discourse.nodered.org/t/data-to-sqlite-help/44414/2 "2021-04-19T18:28:15Z")

</div>

Have you read up about date/time in sqlite3? See [Datatypes In SQLite Version 3](https://www.sqlite.org/datatype3.html#date_and_time_datatype)

---

<div class="post-metadata">

**Author:** ![biros](https://avatars.discourse-cdn.com/v4/letter/b/e47c2d/32.png) [@biros](https://discourse.nodered.org/u/biros)\
**Post date:** [19 April 2021 18:39 UTC](https://discourse.nodered.org/t/data-to-sqlite-help/44414/3 "2021-04-19T18:39:05Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/b/6b5816ec8b5c06976f2db615dd94fdd00a0400ed.png)  
I get;  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/1/311f103b6ca21a53f99bb580d01821cbe54caaa7.png)  
Also,

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/0/80dd25ced97b2470603f45100f37c64addc649d8.png)  
Might be a typo on my side, but still no success.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [19 April 2021 19:57 UTC](https://discourse.nodered.org/t/data-to-sqlite-help/44414/4 "2021-04-19T19:57:46Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![biros](https://avatars.discourse-cdn.com/v4/letter/b/e47c2d/32.png) [@biros](https://discourse.nodered.org/u/biros)\
**Post date:** [19 April 2021 20:00 UTC](https://discourse.nodered.org/t/data-to-sqlite-help/44414/5 "2021-04-19T20:00:58Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![biros](https://avatars.discourse-cdn.com/v4/letter/b/e47c2d/32.png) [@biros](https://discourse.nodered.org/u/biros)\
**Post date:** [20 April 2021 15:30 UTC](https://discourse.nodered.org/t/data-to-sqlite-help/44414/6 "2021-04-20T15:30:33Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/6/862c14a6afc4ae27016f1d542969d144a7b8b4a8.png)

```auto
[{"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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/6/a66702ed93d2e1e8e28e5e890de151e2674a6b51.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [20 April 2021 15:44 UTC](https://discourse.nodered.org/t/data-to-sqlite-help/44414/7 "2021-04-20T15:44:04Z")

</div>

> [@biros](#):
>
> `LIMIT 10`

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

---

<div class="post-metadata">

**Author:** ![biros](https://avatars.discourse-cdn.com/v4/letter/b/e47c2d/32.png) [@biros](https://discourse.nodered.org/u/biros)\
**Post date:** [20 April 2021 15:58 UTC](https://discourse.nodered.org/t/data-to-sqlite-help/44414/8 "2021-04-20T15:58:24Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [20 April 2021 16:03 UTC](https://discourse.nodered.org/t/data-to-sqlite-help/44414/9 "2021-04-20T16:03:36Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![biros](https://avatars.discourse-cdn.com/v4/letter/b/e47c2d/32.png) [@biros](https://discourse.nodered.org/u/biros)\
**Post date:** [20 April 2021 16:07 UTC](https://discourse.nodered.org/t/data-to-sqlite-help/44414/10 "2021-04-20T16:07:04Z")

</div>

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/7/e7ad7de7511fb080f6a1d678c68aff6f452f6c21.png)

```auto
[{"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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/f/1f8829edb753f97e3d82b85ae6b7e93a4ffa96c9.png)

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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [20 April 2021 16:21 UTC](https://discourse.nodered.org/t/data-to-sqlite-help/44414/11 "2021-04-20T16:21:29Z")

</div>

> [@biros](#):
>
> I'm guessing the chart does not "like" the time format on the x axis

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.

---

<div class="post-metadata">

**Author:** ![biros](https://avatars.discourse-cdn.com/v4/letter/b/e47c2d/32.png) [@biros](https://discourse.nodered.org/u/biros)\
**Post date:** [20 April 2021 16:52 UTC](https://discourse.nodered.org/t/data-to-sqlite-help/44414/12 "2021-04-20T16:52:32Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![biros](https://avatars.discourse-cdn.com/v4/letter/b/e47c2d/32.png) [@biros](https://discourse.nodered.org/u/biros)\
**Post date:** [20 April 2021 17:44 UTC](https://discourse.nodered.org/t/data-to-sqlite-help/44414/13 "2021-04-20T17:44:18Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [4 May 2021 17:44 UTC](https://discourse.nodered.org/t/data-to-sqlite-help/44414/14 "2021-05-04T17:44:39Z")

</div>

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