Saving Data in DB at SQLite Function - Help!

General
1

Hi everyone. I looked almost all SQLite topic and didnt get solve my problem.
I created a flow and testing local. Connected DHT11 and 1 cable for test.

I can take sensor values and also GPIO port connected or not connected. But I didnt succeed it to save in DB at SQLİte. DB files is creating in my RPI local.

I installed SQLite but dont have experience about function code. Can some one help me about function code how should be it ?

I also adding some screen shot and my flow. Thanks for your helps.

My Function node is ;

new Date(2018, 11 , 12, 11, 04,0)
var _resultDate = d.toLocaleDateString('de-CH', options);
msg.payload = _resultDate + ' T:' + msg.payload + ' H:' + msg.humidity;
return msg;

Flow

[{"id":"b6f00e41.761ef","type":"rpi-gpio in","z":"f833ce19.4b7f2","name":"","pin":"40","intype":"tri","debounce":"25","read":false,"x":150,"y":120,"wires":[["6cf8d1c6.c57b1"]]},{"id":"6cf8d1c6.c57b1","type":"dsm","z":"f833ce19.4b7f2","name":"state right","sm_config":"{\n    \"triggerInput\": \"payload\",\n    \"currentState\": \"running\",\n    \"states\": {\n        \"stopped\": {\n            \"0\": \"running\"\n        },\n        \"running\": {\n            \"1\": \"stopped\"\n        }\n    },\n    \"methods\": {\n        \"onBeforeTransition\": \"msg.pyload = msg.payload.toString();\",\n        \"0\": \"sta.fill='green';msg.background='green';msg.label='Laser 1 Working';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 1 Error!!!';\"\n    }\n}","x":336,"y":118,"wires":[["ce065d9c.2bf7d","698821cf.9c9ad"]]},{"id":"ce065d9c.2bf7d","type":"ui_button","z":"f833ce19.4b7f2","name":"Laser 1","group":"7fe16ebd.2f6bb","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":500,"y":120,"wires":[[]]},{"id":"206a0f23.962e3","type":"sqlite","z":"f833ce19.4b7f2","mydb":"3a0f3358.0ee28c","sqlquery":"msg.topic","sql":"","name":"Sqlite","x":670,"y":80,"wires":[[]]},{"id":"698821cf.9c9ad","type":"function","z":"f833ce19.4b7f2","name":"Record","func":"","outputs":1,"noerr":0,"x":500,"y":80,"wires":[["206a0f23.962e3"]]},{"id":"1eedbd33.6bf773","type":"ui_gauge","z":"f833ce19.4b7f2","name":"Temp Gauge","group":"51d39551.b1d77c","order":0,"width":0,"height":0,"gtype":"gage","title":"Temp Gauge","label":"°C","format":"{{value}}","min":0,"max":"35","colors":["#00b500","#fb9b44","#ca3838"],"seg1":"","seg2":"","x":710,"y":260,"wires":[]},{"id":"30d30a4b.5b8a76","type":"ui_gauge","z":"f833ce19.4b7f2","name":"Humid Gauge","group":"ca64eeb9.03b0c","order":0,"width":0,"height":0,"gtype":"gage","title":"Humid Gauge","label":"%","format":"{{value}}","min":0,"max":"50","colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","x":700,"y":400,"wires":[]},{"id":"487c05d4.5fafcc","type":"ui_chart","z":"f833ce19.4b7f2","name":"Humid Graph","group":"ca64eeb9.03b0c","order":0,"width":0,"height":0,"label":"Humid Graph","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"0","ymax":"100","removeOlder":"1","removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"x":690,"y":360,"wires":[[],[]]},{"id":"3d877289.a4cd3e","type":"ui_chart","z":"f833ce19.4b7f2","name":"Temp Graph","group":"51d39551.b1d77c","order":0,"width":0,"height":0,"label":"Temp Graph","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"0","ymax":"50","removeOlder":"1","removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"x":705.0000267028809,"y":226.00000381469727,"wires":[[],[]]},{"id":"92a455b6.ad22e8","type":"function","z":"f833ce19.4b7f2","name":"Humidity","func":"msg.payload = msg.humidity;\nreturn msg;","outputs":1,"noerr":0,"x":500,"y":340,"wires":[["30d30a4b.5b8a76","487c05d4.5fafcc","7763bb14.c0cfe4"]]},{"id":"1bbe8187.919a2e","type":"function","z":"f833ce19.4b7f2","name":"Temperature","func":"msg.payload = msg.payload;\nreturn msg;","outputs":1,"noerr":0,"x":509.9999656677246,"y":298.9999933242798,"wires":[["1eedbd33.6bf773","3d877289.a4cd3e","7763bb14.c0cfe4"]]},{"id":"f20a0124.8beab","type":"rpi-dht22","z":"f833ce19.4b7f2","name":"","topic":"Isi/Nem","dht":"11","pintype":"0","pin":4,"x":300,"y":320,"wires":[["92a455b6.ad22e8","1bbe8187.919a2e"]]},{"id":"a7f66a47.d2f538","type":"rpi-gpio in","z":"f833ce19.4b7f2","name":"","pin":"7","intype":"tri","debounce":"25","read":false,"x":110,"y":340,"wires":[["f20a0124.8beab"]]},{"id":"8e763e28.d77a5","type":"inject","z":"f833ce19.4b7f2","name":"","topic":"","payload":"","payloadType":"date","repeat":"30","crontab":"","once":false,"onceDelay":0.1,"x":135.52993392944336,"y":306.3500499725342,"wires":[["f20a0124.8beab"]]},{"id":"7763bb14.c0cfe4","type":"function","z":"f833ce19.4b7f2","name":"Rec Function","func":"new Date(2018, 11 , 12, 11, 04,0)\nvar _resultDate = d.toLocaleDateString('de-CH', options);\n\n//var d = new Date(2018, 11, 24, 10, 33, 30, 0);\nmsg.payload = _resultDate + ' T:' + msg.payload + ' H:' + msg.humidity;\nreturn msg;","outputs":1,"noerr":0,"x":769.5299110412598,"y":301.350022315979,"wires":[["ac9435a0.fbc6b8","4f988ac8.e745f4"]]},{"id":"4f988ac8.e745f4","type":"e-mail","z":"f833ce19.4b7f2","server":"53.151.100.102","port":"25","secure":false,"name":"//tarik.bellikli@daimler.com","dname":"Mail","x":970,"y":340,"wires":[]},{"id":"ac9435a0.fbc6b8","type":"sqlite","z":"f833ce19.4b7f2","mydb":"3a0f3358.0ee28c","sqlquery":"msg.topic","sql":"","name":"Sql","x":970,"y":260,"wires":[[]]},{"id":"7fe16ebd.2f6bb","type":"ui_group","z":"","name":"Assembly Lines","tab":"6809cbe4.c8aa24","disp":true,"width":"6","collapse":false},{"id":"3a0f3358.0ee28c","type":"sqlitedb","z":"","db":"/var/www/html/database/LaserDB","mode":"RWC"},{"id":"51d39551.b1d77c","type":"ui_group","z":"","name":"Assembly Lines ","tab":"6809cbe4.c8aa24","disp":true,"width":"6","collapse":false},{"id":"ca64eeb9.03b0c","type":"ui_group","z":"","name":"Assembly Lines  ","tab":"6809cbe4.c8aa24","disp":true,"width":"6","collapse":false},{"id":"6809cbe4.c8aa24","type":"ui_tab","z":"","name":"Chasis Lines","icon":"dashboard"}]

Screenshot

1
11017×424 33.9 KB
2
21017×499 26.4 KB
3
3810×347 14 KB

2

So where exactly is your SQL? If you are doing an insert you must use an insert statement.

If you don't know SQL I would suggest you start off by taking a tutorial like https://www.w3schools.com/sql/

As I suggested to you 3 days ago in the other thread, you should to the 'FLOWS' section of this website and search for 'flows' (not 'nodes') that contain the word 'sqlite' and see what shows up.

3

I looked but didnt find it about what I want, maybe I mistook.

My SQL is in there, which is I showing in picture.

4

4

You clearly need to take a course on SQL. I suggest you take a step back and take the W3Schools tutorial I previously suggested. Once you complete it you should have a better handle on things.

5

If I have free time I will, but at the moment want to complete it. I need some working examples. Basically I want to save my data in SQL not more today.

6

Then go back and do the search I have told you twice before to do - go to the flows tab and type into the searchbox 'sqlite' and look at the 'flows' examples.

7

To insert data into a database you need an insert SQL statement. There does not seem to be any such statement in your posts. That is what was meant when asking where your SQL is.

8

I found 3 example in flows but this are not clear for me. Thanks.

9

Dear @Colin I created a DB manually in phpliteadmin.
But when I choose sqlite node and want to connect this DB it gives an error below.

12.11.2018 16:12:08node: 4b233758.7dab88msg : error

"Error: SQLITE_READONLY: attempt to write a readonly database"

So first I have to solve SQLite readonliy problem. Then I need to Function node code , for saving data in SQL

10

You need to learn how to do the basic SQL commands: INSERT, UPDATE, SELECT and DELETE

go and install the flow: Database examples - insert and select using binding and after you install it and look at it and play with it, if you still don't know how to insert your data come back and show us what you have tried (include your flo0 and then ask for help.

1 Like
11

That probably means that the user running node red does not have write access to the file.

1 Like
12

I did this a few times but again will look. Thanks.

13

I solved this problem with belowing code and changed permission. Now I will try to connect db

sudo chown user file

14

I have done it I think. My problem is RPI 3 is near by me right now, and when I want to connect and discconect GPIO 40 any device, want to save this value in DB. SO what kind of way I follow your advice ?

Untitled
Untitled1089×867 72 KB

15

add a rpi gpio input node and point it at that gpio. Now you will have it's state and you can add it to an SQL insert statement.

16

Dear zenofmud I added Rpi gpio and tried this function code, but didnt worked properly. I gives an error in picture below.
Also this is my flow :slight_smile:

[{"id":"e1440866.15e6d8","type":"rpi-gpio in","z":"2127666b.508a1a","name":"","pin":"40","intype":"tri","debounce":"25","read":false,"x":290,"y":160,"wires":[["bf824466.157438"]]},{"id":"bf824466.157438","type":"dsm","z":"2127666b.508a1a","name":"state right","sm_config":"{\n    \"triggerInput\": \"payload\",\n    \"currentState\": \"running\",\n    \"states\": {\n        \"stopped\": {\n            \"0\": \"running\"\n        },\n        \"running\": {\n            \"1\": \"stopped\"\n        }\n    },\n    \"methods\": {\n        \"onBeforeTransition\": \"msg.pyload = msg.payload.toString();\",\n        \"0\": \"sta.fill='green';msg.background='green';msg.label='Laser 1 Working';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 1 Error!!!';\"\n    }\n}","x":476,"y":158,"wires":[["efa69de4.0f812"]]},{"id":"efa69de4.0f812","type":"ui_button","z":"2127666b.508a1a","name":"Laser 1","group":"60d4f788.2f0518","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":660,"y":160,"wires":[[]]},{"id":"fa3c9670.4307c8","type":"ui_gauge","z":"2127666b.508a1a","name":"Temp Gauge","group":"10353f56.f3abf1","order":0,"width":0,"height":0,"gtype":"gage","title":"Temp Gauge","label":"°C","format":"{{value}}","min":0,"max":"50","colors":["#00b500","#fb9b44","#ca3838"],"seg1":"","seg2":"","x":730,"y":340,"wires":[]},{"id":"5c607992.753da8","type":"ui_gauge","z":"2127666b.508a1a","name":"Humid Gauge","group":"ee18ca73.bf61a8","order":0,"width":0,"height":0,"gtype":"gage","title":"Humid Gauge","label":"%","format":"{{value}}","min":0,"max":"100","colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","x":740,"y":500,"wires":[]},{"id":"afe8ec98.457f8","type":"ui_chart","z":"2127666b.508a1a","name":"Humid Graph","group":"ee18ca73.bf61a8","order":0,"width":0,"height":0,"label":"Humid Graph","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"0","ymax":"100","removeOlder":"1","removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"x":736.9999809265137,"y":464.0000534057617,"wires":[[],[]]},{"id":"40b38fad.4ebf6","type":"ui_chart","z":"2127666b.508a1a","name":"Temp Graph","group":"10353f56.f3abf1","order":0,"width":0,"height":0,"label":"Temp Graph","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"0","ymax":"50","removeOlder":"1","removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"x":725.0000267028809,"y":306.00000381469727,"wires":[[],[]]},{"id":"f581843b.454128","type":"function","z":"2127666b.508a1a","name":"Humidity","func":"msg.payload = msg.humidity;\nreturn msg;","outputs":1,"noerr":0,"x":480,"y":460,"wires":[["5c607992.753da8","afe8ec98.457f8","c6ca889f.ed57c8"]]},{"id":"24051d5d.099df2","type":"function","z":"2127666b.508a1a","name":"Temperature","func":"msg.payload = msg.payload;\nreturn msg;","outputs":1,"noerr":0,"x":490,"y":420,"wires":[["fa3c9670.4307c8","40b38fad.4ebf6","c6ca889f.ed57c8"]]},{"id":"830308e6.e94f08","type":"rpi-dht22","z":"2127666b.508a1a","name":"","topic":"Temp/Humid","dht":"11","pintype":"0","pin":4,"x":306.9999580383301,"y":449.00000858306885,"wires":[["f581843b.454128","24051d5d.099df2"]]},{"id":"65103e0d.42d9b","type":"rpi-gpio in","z":"2127666b.508a1a","name":"","pin":"7","intype":"tri","debounce":"25","read":false,"x":121.99995803833008,"y":447.00000858306885,"wires":[["830308e6.e94f08"]]},{"id":"b8266054.05733","type":"inject","z":"2127666b.508a1a","name":"","topic":"","payload":"","payloadType":"date","repeat":"30","crontab":"","once":false,"onceDelay":0.1,"x":135.52993392944336,"y":406.3500499725342,"wires":[["830308e6.e94f08"]]},{"id":"f3874443.03a2f8","type":"debug","z":"2127666b.508a1a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1150,"y":360,"wires":[]},{"id":"c6ca889f.ed57c8","type":"function","z":"2127666b.508a1a","name":"Insert Statement","func":"var outputs = [] ;\n\nsql = \"INSERT INTO Status (Device,Value)\" +\n\"VALUES ('Temperature', \"+ msg.payload +\")\";\noutputs.push({topic:sql});\nsql = \"INSERT INTO Status (Device,Value)\" +\n\"VALUES ('Humidity', \"+ msg.humidity +\")\";\noutputs.push({topic:sql});\nreturn msg;","outputs":1,"noerr":0,"x":880,"y":400,"wires":[["a7903d61.7f9be","f3874443.03a2f8"]]},{"id":"a7903d61.7f9be","type":"sqlite","z":"2127666b.508a1a","mydb":"8766660e.a1b488","sqlquery":"msg.topic","sql":"","name":"RecSQL","x":1160,"y":420,"wires":[[]]},{"id":"60d4f788.2f0518","type":"ui_group","z":"","name":"Assembly Lines","tab":"3e7f6350.5b23cc","disp":true,"width":"6","collapse":false},{"id":"10353f56.f3abf1","type":"ui_group","z":"","name":"Assembly Lines ","tab":"3e7f6350.5b23cc","disp":true,"width":"6","collapse":false},{"id":"ee18ca73.bf61a8","type":"ui_group","z":"","name":"Assembly Lines  ","tab":"3e7f6350.5b23cc","disp":true,"width":"6","collapse":false},{"id":"8766660e.a1b488","type":"sqlitedb","z":"","db":"/var/www/html/database/Status","mode":"RWC"},{"id":"3e7f6350.5b23cc","type":"ui_tab","z":"","name":"Chasis Lines","icon":"dashboard"}]

var outputs = ;

sql = "INSERT INTO Status (Device,Value)" +
"VALUES ('Temperature', "+ msg.payload +")";
outputs.push({topic:sql});
sql = "INSERT INTO Status (Device,Value)" +
"VALUES ('Humidity', "+ msg.humidity +")";
outputs.push({topic:sql});
return msg;

Untitled
Untitled895×829 37.5 KB

17

When sharing code please use the ``` around the code as you have done with the flow, not blockquote.

You code is building up a outputs array, but then does nothing with it and returns msg. I assume that's isn't what you mean to do? Given it looks like you want to send two messages from the node, you'd want to end it with return [ outputs ] so that each message in the outputs array is sent.

1 Like
18

Ok, thanks for your advice. If possible can you edit my function code please ?
I just want to send data from DHT11 to Litesql db. How can I do this ?

19

Did you even try Nick's advice?

20

I gave you the one line change to make in my reply. Please at least try it for yourself.