SQLite on Node Red & Raspberry pi

[Mike Hill]

Guys,

Can someone help me please. I'm new to Node Red & I am having a problem UPDATING values in an SQlite DB. I have created a database (chcontroller) & a table (readings) within the database.  I can Update & Select values from the table (swon field) using the pi console.  I have created a simple flow to test the same operations using Node Red. Referring to the enclosed flow, the Read function works but the Write function is not working correctly. I'm just trying to update a numeric value via the Inject node & the DB write function & then query the same location with the DB read function. The update works if I actually type a numeric value into the DB write topic rather than using the newMsg.payload. Both Node Red & SQlite were downloaded 2 weeks ago.

Thanks to anyone who can put me right on this problem.

Mike Hill

[{"id":"d7e5ad22.df85e","type":"sqlite","z":"5927cbef.928cf4","mydb":"966bfd3b.9742c","name":"SQLite\\CHcontroller","x":414.1000213623047,"y":84.19999694824219,"wires":[["6c63fd8d.8ee484"]]},{"id":"15c58e82.1f5351","type":"inject","z":"5927cbef.928cf4","name":"","topic":"","payload":"20","payloadType":"num","repeat":"","crontab":"","once":false,"x":78.10000610351562,"y":29.800003051757812,"wires":[["a1f0f3ec.efd58","7a45c8e7.a2a0b8"]]},{"id":"a1f0f3ec.efd58","type":"function","z":"5927cbef.928cf4","name":"Database write","func":"\nvar newMsg={payload: msg.payload };\nnewMsg.topic=\"update readings set swon='newMsg.payload' where id=1\";\nreturn newMsg;","outputs":1,"noerr":0,"x":177.09999084472656,"y":83.19999694824219,"wires":[["d7e5ad22.df85e"]]},{"id":"233ddfe5.79cbc","type":"inject","z":"5927cbef.928cf4","name":"","topic":"","payload":"","payloadType":"str","repeat":"","crontab":"","once":false,"x":80.10000610351562,"y":181.8000030517578,"wires":[["93a1bc65.662e"]]},{"id":"93a1bc65.662e","type":"function","z":"5927cbef.928cf4","name":"Database read","func":"var newMsg={payload: msg.payload};\nnewMsg.topic=\"select swon from readings where id=1\";\nreturn newMsg;","outputs":1,"noerr":0,"x":250.10000610351562,"y":182.1999969482422,"wires":[["d7e5ad22.df85e"]]},{"id":"6c63fd8d.8ee484","type":"debug","z":"5927cbef.928cf4","name":"","active":true,"console":"false","complete":"false","x":476.1000061035156,"y":183.1999969482422,"wires":[]},{"id":"7a45c8e7.a2a0b8","type":"debug","z":"5927cbef.928cf4","name":"","active":true,"console":"false","complete":"false","x":454.1000213623047,"y":31,"wires":[]},{"id":"966bfd3b.9742c","type":"sqlitedb","z":"","db":"chcontroller.db"}]

[Mark Setrem]

Have you tried replacing

newMsg.topic="update readings set swon='newMsg.payload' where id=1";

with

newMsg.topic="update readings set swon='"+newMsg.payload+"' where id=1";

[Mike Hill]

Hi Marc,

That has worked! I must have tried  every other possibility but that one.  Thanks for taking the time to have a look - much appreciated.