Hi, My sqlite put this error ""Error: SQLITE_ERROR: no such table: NCS".
Why?
/////DATABASE???////
sqlite> .tables
NCS NCS_DATABASE
Where do you see this error?
what node are you using?
did you put the full path to the database in the configuration of the node?
i am using sqlite node
How to put full path to database in configuration of node Sir .Please tell
What have you got configured fir the database property in the node? show a screen shot
and what tables have you defined in that database?
sqlite> SELECT * FROM NCS;
1| | | |04222436| |04222536| | |
1| | | |04222437| |04222537| | |
sqlite> .tables
NCS NCS_DATABASE
sqlite>
So what is the sqlite statement you used in your flow that generated the error?
I havent used any flow Sir
Well why are you asking about this on the Node-RED forum?
Sir actually I am using SQLITE3 in node red because I am taking a FOB ID through mqtt and going to match that with my already stored Sqlite database ,for that I am using that flow in Node Red
You showed me the settings you user to configure the sqlite node so what is the sql statement you are passing to that node?
Please attach a export of your flow
Sure Sir.
Here is the flow
[{"id":"a0a22bbe.4dd42","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"4ee90eec.77d9f","type":"mqtt in","z":"a0a22bbe.4dd42","name":"","topic":"FOB","qos":"2","datatype":"auto","broker":"3d22a2f.7bec8de","x":130,"y":140,"wires":[["d9dae4cd.dd58b8"]]},{"id":"d5e82719.0d70b8","type":"mqtt in","z":"a0a22bbe.4dd42","name":"","topic":"TRADE ","qos":"2","datatype":"auto","broker":"3d22a2f.7bec8de","x":130,"y":200,"wires":[[]]},{"id":"53b5c860.6ecf","type":"mqtt in","z":"a0a22bbe.4dd42","name":"","topic":"FOB _and_TRADE","qos":"2","datatype":"auto","broker":"3d22a2f.7bec8de","x":170,"y":260,"wires":[[]]},{"id":"b855f07b.77b14","type":"mqtt out","z":"a0a22bbe.4dd42","name":"","topic":"MATCH","qos":"","retain":"","broker":"3d22a2f.7bec8de","x":1300,"y":200,"wires":[]},{"id":"92b43cac.dee5e","type":"sqlite","z":"a0a22bbe.4dd42","mydb":"1a9ea8ca.835eef","sqlquery":"msg.topic","sql":"SELECT * FROM NCS WHERE Person_ID='{{payload}}';","name":"SQLITE3","x":800,"y":140,"wires":[["8bcbf413.dbee18","914a2434.2aeb98"]]},{"id":"d9dae4cd.dd58b8","type":"change","z":"a0a22bbe.4dd42","name":"","rules":[{"t":"set","p":"id","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":140,"wires":[["84343c5c.a4473","48d9502d.46ce78"]]},{"id":"84343c5c.a4473","type":"debug","z":"a0a22bbe.4dd42","name":"Debug_tchange1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":450,"y":80,"wires":[]},{"id":"48d9502d.46ce78","type":"template","z":"a0a22bbe.4dd42","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT Person_ID FROM NCS \nWHERE Person_ID='{{payload}}';","output":"str","x":560,"y":140,"wires":[["f8041486.46d2d","92b43cac.dee5e"]]},{"id":"f8041486.46d2d","type":"debug","z":"a0a22bbe.4dd42","name":"Debug23","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":680,"y":80,"wires":[]},{"id":"8bcbf413.dbee18","type":"switch","z":"a0a22bbe.4dd42","name":"","property":"payload","propertyType":"msg","rules":[{"t":"empty"},{"t":"nempty"}],"checkall":"true","repair":false,"outputs":2,"x":890,"y":220,"wires":[["15b45169.1dd22f"],["3e0f30eb.c40078"]]},{"id":"15b45169.1dd22f","type":"change","z":"a0a22bbe.4dd42","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"CR_FAIL","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1080,"y":140,"wires":[["b855f07b.77b14"]]},{"id":"3e0f30eb.c40078","type":"change","z":"a0a22bbe.4dd42","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"CR_SUCCESS","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1080,"y":280,"wires":[["b855f07b.77b14","bee6eb72.e9f828"]]},{"id":"bee6eb72.e9f828","type":"debug","z":"a0a22bbe.4dd42","name":"Debug2","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1180,"y":360,"wires":[]},{"id":"914a2434.2aeb98","type":"debug","z":"a0a22bbe.4dd42","name":"debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":910,"y":60,"wires":[]},{"id":"3d22a2f.7bec8de","type":"mqtt-broker","z":"","name":"","broker":"tcp://mqtt.eclipse.org","port":"1883","clientid":"NCS_Client03","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"1a9ea8ca.835eef","type":"sqlitedb","z":"","db":"/var/tmp/NCS.db","mode":"RWC"}]
[Remember to use the </> option when inserting flows so they can be imported - I have fixed your post]
Are you sure that the database exists on the machine that is running Node-RED? and that it exists in that location `/var/tmp/NCS.db'?
Thank you for fixing that first.I will remember that in future.
Sir I am not sure please tell how to fill that sqlite edit node.
If NR is running on a couputer other than the one you are sitting in front of, you need to SSH into that machine so you can go to the /var/tmp directory and see if the file NCS.db resides there.
if it does, then you should start sqlite3 and check to see if the NCS table exists in it.
Sir Node red is running on my machine same machine where database is there
What is the OS of tyour machine?
Also how did you install node red? Are you using Docker?
Looking at your flow .. so your database in called NCS and also your table is called NCS ??
If the above information is correct try this flow :
[{"id":"fc0817f2.eb8d88","type":"mqtt in","z":"71a01b62.49e8f4","name":"","topic":"FOB","qos":"2","datatype":"auto","broker":"db872a3f.794be8","x":130,"y":140,"wires":[["fc9a9295.550d4","751d3139.371318"]]},{"id":"4423ad05.f3097c","type":"mqtt in","z":"71a01b62.49e8f4","name":"","topic":"TRADE ","qos":"2","datatype":"auto","broker":"db872a3f.794be8","x":130,"y":200,"wires":[[]]},{"id":"4fbb15d2.3417b4","type":"mqtt in","z":"71a01b62.49e8f4","name":"","topic":"FOB _and_TRADE","qos":"2","datatype":"auto","broker":"db872a3f.794be8","x":170,"y":260,"wires":[[]]},{"id":"8b23402b.f70838","type":"mqtt out","z":"71a01b62.49e8f4","name":"","topic":"MATCH","qos":"","retain":"","broker":"db872a3f.794be8","x":1300,"y":200,"wires":[]},{"id":"940db604.f95","type":"sqlite","z":"71a01b62.49e8f4","mydb":"b8fde1fc.05ebf8","sqlquery":"msg.topic","sql":"SELECT * FROM NCS WHERE Person_ID='{{payload}}';","name":"SQLITE3","x":800,"y":140,"wires":[["9db9f0d4.7ca018","968baba6.9696b"]]},{"id":"fc9a9295.550d4","type":"change","z":"71a01b62.49e8f4","name":"","rules":[{"t":"set","p":"id","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":140,"wires":[["691f9f1c.710b58","bde3e26b.8bc518"]]},{"id":"691f9f1c.710b58","type":"debug","z":"71a01b62.49e8f4","name":"Debug_tchange1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":450,"y":80,"wires":[]},{"id":"3a16c607.9f476a","type":"debug","z":"71a01b62.49e8f4","name":"Debug23","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":680,"y":80,"wires":[]},{"id":"9db9f0d4.7ca018","type":"switch","z":"71a01b62.49e8f4","name":"","property":"payload","propertyType":"msg","rules":[{"t":"empty"},{"t":"nempty"}],"checkall":"true","repair":false,"outputs":2,"x":890,"y":220,"wires":[["8aa15a33.6b4bf8"],["620b6a11.bf0c44"]]},{"id":"8aa15a33.6b4bf8","type":"change","z":"71a01b62.49e8f4","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"CR_FAIL","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1080,"y":140,"wires":[["8b23402b.f70838"]]},{"id":"620b6a11.bf0c44","type":"change","z":"71a01b62.49e8f4","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"CR_SUCCESS","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1080,"y":280,"wires":[["8b23402b.f70838","ffb44bbd.548d2"]]},{"id":"ffb44bbd.548d2","type":"debug","z":"71a01b62.49e8f4","name":"Debug2","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1180,"y":360,"wires":[]},{"id":"968baba6.9696b","type":"debug","z":"71a01b62.49e8f4","name":"debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":910,"y":60,"wires":[]},{"id":"c762a68f.5d75a8","type":"inject","z":"71a01b62.49e8f4","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"andy","payloadType":"str","x":430,"y":240,"wires":[["bde3e26b.8bc518"]]},{"id":"bde3e26b.8bc518","type":"function","z":"71a01b62.49e8f4","name":"","func":"let newMsg = {};\nnewMsg.topic = `SELECT Person_ID FROM NCS WHERE Person_ID='${msg.payload}'`;\nreturn newMsg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":570,"y":160,"wires":[["940db604.f95","3a16c607.9f476a"]]},{"id":"751d3139.371318","type":"debug","z":"71a01b62.49e8f4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":240,"y":80,"wires":[]},{"id":"68eb018b.df0f58","type":"template","z":"71a01b62.49e8f4","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT Person_ID FROM NCS \nWHERE Person_ID='{{payload}}';","output":"str","x":600,"y":380,"wires":[[]]},{"id":"db872a3f.794be8","type":"mqtt-broker","z":"","name":"","broker":"tcp://mqtt.eclipse.org","port":"1883","clientid":"NCS_Client03","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"b8fde1fc.05ebf8","type":"sqlitedb","z":"","db":"/var/tmp/NCS.db","mode":"RWC"}]
I replaced your template node with a function node because your template node was outputing this
The enter (change of line) you had may have caused the problem
