MsgPayload into a query on nodered-sqlite

General
1

Hi everyone,
I’m Fabio and i’m using node-red for some days and i’m a newbie for sure.
It allowed me to integrate many “black boxes” easily. However i have a problem that i cannot get around:

I’m using a great node that is “node-red-node-sqlite”. I’m configuring a function mode to get the msg.topic to be send to the sqllite node( SQL Query via msg.topic). I got it easily running if i hardcode the variables into the topic string.

However i don’t get how i can send a msg (.topic / .payload) from a previous function to be inserted on the topic to be forwarded into the topic.

Below i have a forced example through an injection node that injects “teststring”.
If i put the function like this, it works:
msg.topic =“insert into sensors (sensor_ID, sensor_Name) values (1 , ‘dd’)”;
return msg;

However i don’t know how to do something similar to this to work::
msg.topic =“insert into sensors (sensor_ID, sensor_Name) values (1 ,” + msg.payload + “)”;
return msg;

I want to get the payload in the future by a mqtt local broker.
Thank you so much.

[{“id”:“cde3c4df.c0d398”,“type”:“mqtt in”,“z”:“e77eefb.b9a191”,“name”:“broker_rec_est”,“topic”:“Parque/Estado”,“qos”:“0”,“broker”:“104c82cf.cfea5d”,“x”:160,“y”:540,“wires”:[[“8530e0fc.f01b4”]]},{“id”:“c723de02.2b52b”,“type”:“catch”,“z”:“e77eefb.b9a191”,“name”:"",“scope”:null,“x”:1220,“y”:380,“wires”:[[]]},{“id”:“758d3cb8.10a584”,“type”:“file”,“z”:“e77eefb.b9a191”,“name”:"",“filename”:“C:\Users\fabio\OneDrive\Documents\Teste\teste.txt”,“appendNewline”:true,“createDir”:false,“overwriteFile”:“false”,“x”:930,“y”:500,“wires”:[]},{“id”:“256b2ec6.46e142”,“type”:“debug”,“z”:“e77eefb.b9a191”,“name”:"",“active”:false,“tosidebar”:true,“console”:false,“tostatus”:false,“complete”:“false”,“x”:610,“y”:440,“wires”:[]},{“id”:“8530e0fc.f01b4”,“type”:“function”,“z”:“e77eefb.b9a191”,“name”:“AddTime”,“func”:"\n//msg.payload = msg.payload + " - " + new Date().toString();\n\n//msg.payload = {value:msg.payload, timestamp:Date.now()};\nvar currentdate = new Date();\nvar currenthour = currentdate.getHours();\nvar currentminute = currentdate.getMinutes();\n\nmsg.payload = msg.payload + “-” + currenthour.toString() + currentminute.toString();\nreturn msg;",“outputs”:1,“noerr”:0,“x”:430,“y”:500,“wires”:[[“758d3cb8.10a584”,“256b2ec6.46e142”]]},{“id”:“6a3db9a2.fc8df8”,“type”:“sqlite”,“z”:“e77eefb.b9a191”,“mydb”:“133ff868.a05478”,“sqlquery”:“msg.topic”,“sql”:“insert into sensors (sensor_ID, sensor_Name) values({{payload_sensor_ID,{{payload.humidity}});\n”,“name”:"",“x”:630,“y”:180,“wires”:[[“dee86050.6448f”]]},{“id”:“dee86050.6448f”,“type”:“debug”,“z”:“e77eefb.b9a191”,“name”:"",“active”:true,“tosidebar”:true,“console”:false,“tostatus”:false,“complete”:“payload”,“x”:1070,“y”:180,“wires”:[]},{“id”:“accc27e9.44e5b8”,“type”:“sqlite”,“z”:“e77eefb.b9a191”,“mydb”:“133ff868.a05478”,“sqlquery”:“fixed”,“sql”:“select * from sensors;\n\n\n”,“name”:"",“x”:630,“y”:240,“wires”:[[“2e891a75.522726”]]},{“id”:“2e891a75.522726”,“type”:“debug”,“z”:“e77eefb.b9a191”,“name”:"",“active”:true,“tosidebar”:true,“console”:false,“tostatus”:false,“complete”:“false”,“x”:1070,“y”:240,“wires”:[]},{“id”:“c3410279.a6bfb”,“type”:“inject”,“z”:“e77eefb.b9a191”,“name”:"",“topic”:"",“payload”:“true”,“payloadType”:“bool”,“repeat”:"",“crontab”:"",“once”:false,“onceDelay”:0.1,“x”:170,“y”:240,“wires”:[[“accc27e9.44e5b8”]]},{“id”:“f43b2845.d8f668”,“type”:“sqlite”,“z”:“e77eefb.b9a191”,“mydb”:“133ff868.a05478”,“sqlquery”:“fixed”,“sql”:“create table sensors (sensor_ID integer, sensor_Name text);\n”,“name”:"",“x”:630,“y”:120,“wires”:[[]]},{“id”:“22589f72.051fa”,“type”:“inject”,“z”:“e77eefb.b9a191”,“name”:"",“topic”:"",“payload”:“true”,“payloadType”:“bool”,“repeat”:"",“crontab”:"",“once”:false,“onceDelay”:0.1,“x”:170,“y”:120,“wires”:[[“f43b2845.d8f668”]]},{“id”:“638d875d.2401e8”,“type”:“function”,“z”:“e77eefb.b9a191”,“name”:“insertquery”,“func”:"\n//msg.topic =“insert into sensors (sensor_ID, sensor_Name) values (1 ,” + msg.payload[0] + “)”;\nmsg.topic =“insert into sensors (sensor_ID, sensor_Name) values (1 , ‘dd’)”;\n\n// var str= “insert into sensors (sensor_ID, sensor_Name)” + " values (2," + uu +")";\n/\nvar uu =“insert into sensors (sensor_ID, sensor_Name)” + " values (2, uu)";\n\nvar myarray=[];\nvar ob = {};\n\nfor (var i=0; i<uu.length; i++){\n ob = msg.payload[i];\n myarray.push(ob);\n \n}\nmsg1=myarray;\n/\nreturn msg;",“outputs”:1,“noerr”:0,“x”:370,“y”:180,“wires”:[[“6a3db9a2.fc8df8”,“892c6e69.8d69e”]]},{“id”:“6af0cdbf.485664”,“type”:“inject”,“z”:“e77eefb.b9a191”,“name”:"",“topic”:"",“payload”:“teststring”,“payloadType”:“str”,“repeat”:"",“crontab”:"",“once”:false,“onceDelay”:0.1,“x”:180,“y”:180,“wires”:[[“638d875d.2401e8”]]},{“id”:“892c6e69.8d69e”,“type”:“debug”,“z”:“e77eefb.b9a191”,“name”:"",“active”:true,“tosidebar”:true,“console”:false,“tostatus”:false,“complete”:“false”,“x”:630,“y”:40,“wires”:[]},{“id”:“104c82cf.cfea5d”,“type”:“mqtt-broker”,“z”:"",“name”:“Broker_rasp”,“broker”:“192.168.1.85”,“port”:“1883”,“clientid”:“wemos3”,“usetls”:false,“compatmode”:true,“keepalive”:“60”,“cleansession”:true,“willTopic”:"",“willQos”:“0”,“willRetain”:“false”,“willPayload”:"",“birthTopic”:"",“birthQos”:“0”,“birthRetain”:“false”,“birthPayload”:""},{“id”:“133ff868.a05478”,“type”:“sqlitedb”,“z”:"",“db”:“C:\test\teste1”}]

2

Hi.
Got it to work by putting like this:

msg.topic =(“insert into sensors (sensor_ID, sensor_Name) values (1 ,” + “’” + msg.payload + “’” + “)”);

I’ll continue the Project and hopefully finish it so i can help all new people like me getting into this amazing tool.

Have a nice week.

3

An alternative to assembling text strings in function code is to use a simple template node – it uses “mustache syntax” to substitute variables into other text. So your example could be more simply written as:

insert into sensors (sensor_ID, sensor_Name) values (1, ’{{msg.payload}}’)

Set the template node to output plain text into the msg.topic field, and voila!

1 Like