no it not has spaces
Try this :
There was a mistake .. im re-posting
[EDIT]
[{"id":"1c37b133.3a08cf","type":"mqtt in","z":"ac171b5.1772ce8","name":"","topic":"FOB","qos":"2","datatype":"auto","broker":"db872a3f.794be8","x":90,"y":220,"wires":[["1fc7b8.0e208849","e496781.bc2b808"]]},{"id":"b9ddcd57.b180c8","type":"mqtt in","z":"ac171b5.1772ce8","name":"","topic":"TRADE ","qos":"2","datatype":"auto","broker":"db872a3f.794be8","x":90,"y":280,"wires":[[]]},{"id":"15304316.3ff535","type":"mqtt in","z":"ac171b5.1772ce8","name":"","topic":"FOB _and_TRADE","qos":"2","datatype":"auto","broker":"db872a3f.794be8","x":130,"y":340,"wires":[[]]},{"id":"86028e64.22e7e8","type":"mqtt out","z":"ac171b5.1772ce8","name":"","topic":"MATCH","qos":"","retain":"","broker":"db872a3f.794be8","x":1260,"y":280,"wires":[]},{"id":"e71b5a47.9516e8","type":"sqlite","z":"ac171b5.1772ce8","mydb":"b8fde1fc.05ebf8","sqlquery":"msg.topic","sql":"SELECT * FROM NCS WHERE Person_ID='{{payload}}';","name":"SQLITE3","x":760,"y":220,"wires":[["da5ccacb.4ade3","824e2a6d.4fa8f8"]]},{"id":"74eecdc9.74e6dc","type":"debug","z":"ac171b5.1772ce8","name":"Debug_tchange1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":430,"y":140,"wires":[]},{"id":"2c38fb5b.058cec","type":"debug","z":"ac171b5.1772ce8","name":"Debug23","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":640,"y":160,"wires":[]},{"id":"da5ccacb.4ade3","type":"switch","z":"ac171b5.1772ce8","name":"","property":"payload","propertyType":"msg","rules":[{"t":"empty"},{"t":"nempty"}],"checkall":"true","repair":false,"outputs":2,"x":850,"y":300,"wires":[["4f4c17ec.36723"],["112b3f44.6f77f9"]]},{"id":"4f4c17ec.36723","type":"change","z":"ac171b5.1772ce8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"CR_FAIL","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1040,"y":220,"wires":[["86028e64.22e7e8"]]},{"id":"112b3f44.6f77f9","type":"change","z":"ac171b5.1772ce8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"CR_SUCCESS","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1040,"y":360,"wires":[["86028e64.22e7e8","f5e645d3.d790a8"]]},{"id":"f5e645d3.d790a8","type":"debug","z":"ac171b5.1772ce8","name":"Debug2","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1140,"y":440,"wires":[]},{"id":"824e2a6d.4fa8f8","type":"debug","z":"ac171b5.1772ce8","name":"debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":870,"y":140,"wires":[]},{"id":"73ae1622.513c98","type":"function","z":"ac171b5.1772ce8","name":"","func":"let newMsg = {};\nnewMsg.topic = `SELECT Person_ID FROM NCS_DATABASE WHERE Person_ID='${msg.payload}'`;\nreturn newMsg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":530,"y":240,"wires":[["e71b5a47.9516e8","2c38fb5b.058cec"]]},{"id":"1fc7b8.0e208849","type":"debug","z":"ac171b5.1772ce8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":200,"y":160,"wires":[]},{"id":"e496781.bc2b808","type":"function","z":"ac171b5.1772ce8","name":"id no space ","func":"msg.payload = msg.payload.replace(/\\s/g, '')\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":290,"y":220,"wires":[["74eecdc9.74e6dc","73ae1622.513c98"]]},{"id":"2f27af13.5f1b38","type":"inject","z":"ac171b5.1772ce8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"a n d y","payloadType":"str","x":340,"y":360,"wires":[["e496781.bc2b808"]]},{"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"}]
Thank you sir its working for CR_SUCCESS
but if i want CR_FAIL then what change in function i need to do please tell ?
let newMsg = {};
newMsg.topic = SELECT Person_ID FROM NCS_DATABASE WHERE Person_ID='${msg.payload}';
return newMsg;
I have no idea what CR_SUCCESS and CR_FAIL are ..
you are telling us, as if we know the full aspects of your project.
we dont have the same database so i dont know the output results to help you more.
Thank you Sir .this is correct flow .thank you once again.
What you did with your switch node looks correct
.. put a debug node on both change nodes to see that its working
yes sir its working fine.
It was little difficult to understand when we dont have the same data but Im glad it worked in the end 
study the changes we made with the function nodes .. always put a complete msg debug node at every step of your flow to follow the 'flow' of data.
all the best
Sure Sir .in future if I have doubts in this flow ,please help .thank u sir in advance.
hello Sir,
I want to design a UI for door controller ,but for that i have to make door controllers as many as CAN bus i am using .please tell how i can design automatically door controllers?
Hello Shipra,
I suggest creating a new topic on the subject since this is no more related to Sqlite database so others that worked with CAN bus can more easily find it and help you.
I also recommend you post some more information on your project and share what you have already tried.
1 Like
