Receiving data from multiple nodes and uploading it to sqlite database

abhishek · 17 December 2020 11:40

Hello All,

I am making a UI where I need to store the UUID and part number along with the timestamp into an SQLite database.
The values are taken from the user input which is numbers.
I need to take the value from multiple inputs node and then insert it into the database.

Blockquote
below is my function node:
//MY FN
var data=context.get('data')|| {value:""}; // var to get data from a node .. use context.get method
var topic=msg.topic;
var payload=msg.payload;

if (topic=="tagid")
data.tagid=msg.payload;
if (topic=="part")
data.part=msg.payload;
if (topic=="time")
data.time=msg.payload;

if (topic=="submit")
{
var newMsg ={
topic:"INSERT INTO MFS VALUES ("+ data.time + "," + data.tagid + ","+ data.part + ")"};

return newMsg;
//context.set('data',data)
//var msg1={};
//msg1.topic=newMsg;
//return msg1;

// return msg;
}
//return msg;

Blockquote
Please correct where i am wrong.
node-red running on windows 10
node-red-version : v1.2.6
nodejs version: v14.151

Thanks

[{"id":"8c629254.dc91c","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"2cc7c674.3d088a","type":"sqlite","z":"8c629254.dc91c","mydb":"e6954f6c.40b54","sqlquery":"msg.topic","sql":"","name":"random number","x":390,"y":1780,"wires":[["e4ee2f85.cfa0c"]]},{"id":"e318c216.047a1","type":"inject","z":"8c629254.dc91c","name":"create","props":[{"p":"payload"},{"p":"topic","v":"CREATE TABLE RANDOMNUM( TIMESTAMP INT PRIMARY KEY NOT NULL, VALUE INT NOT NULL, BOOL INT NOT NULL)","vt":"flow"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":1720,"wires":[["2cc7c674.3d088a"]]},{"id":"e4ee2f85.cfa0c","type":"debug","z":"8c629254.dc91c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":560,"y":1820,"wires":[]},{"id":"8d74baff.f82fe8","type":"ui_text","z":"8c629254.dc91c","group":"9f520ce.39907f","order":1,"width":0,"height":0,"name":"","label":"User Input","format":"","layout":"row-spread","x":150,"y":560,"wires":[]},{"id":"26fd3592.4d2efa","type":"ui_text","z":"8c629254.dc91c","group":"9f520ce.39907f","order":2,"width":0,"height":0,"name":"","label":"Select the tag id from the below menu","format":"","layout":"row-spread","x":190,"y":620,"wires":[]},{"id":"49af049e.05468c","type":"ui_text","z":"8c629254.dc91c","group":"9f520ce.39907f","order":4,"width":0,"height":0,"name":"","label":"Enter the part number for tag","format":"","layout":"row-spread","x":160,"y":760,"wires":[]},{"id":"96fcf123.d61fd","type":"ui_text_input","z":"8c629254.dc91c","name":"","label":"value","tooltip":"","group":"9f520ce.39907f","order":5,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"part","x":130,"y":820,"wires":[["82e51c85.b1015"]]},{"id":"9cef7490.fdf528","type":"ui_button","z":"8c629254.dc91c","name":"submit","group":"9f520ce.39907f","order":6,"width":0,"height":0,"passthru":false,"label":"Submit","tooltip":"","color":"","bgcolor":"","icon":"","payload":"1","payloadType":"str","topic":"submit","x":150,"y":900,"wires":[["82e51c85.b1015"]]},{"id":"82e51c85.b1015","type":"function","z":"8c629254.dc91c","name":"","func":"//MY FN\nvar data=context.get('data')|| {value:\"\"}; // var to get data from a node .. use context.get method\nvar topic=msg.topic;\nvar payload=msg.payload;\n\nif (topic==\"tagid\")\ndata.tagid=msg.payload;\nif (topic==\"part\")\ndata.part=msg.payload;\nif (topic==\"time\")\ndata.time=msg.payload;\n\nif (topic==\"submit\")\n{\n    var newMsg ={ \n    topic:\"INSERT OR IGNORE INTO MFS VALUES (\"+ payload + \",\" + data.tagid + \",\"+ data.part + \")\"};\n\t\n    return newMsg;\n    //context.set('data',data)\n    //var msg1={};\n    //msg1.topic=newMsg;\n    //return msg1;    \n   // return msg;\n}\n\n\n\n//return msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":460,"y":720,"wires":[["a115ec14.19375"]]},{"id":"5e23c6cd.3a2618","type":"inject","z":"8c629254.dc91c","name":"Create MFS ","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"CREATE TABLE MFS(TIMESTAMP INT PRIMARY KEY NOT NULL , UUID INT NOT NULL , PN INT NOT NULL)","payload":"","payloadType":"date","x":600,"y":620,"wires":[["a115ec14.19375"]]},{"id":"2dbd1c44.7702e4","type":"inject","z":"8c629254.dc91c","name":"insert timestamp mfs","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"1","crontab":"","once":false,"onceDelay":0.1,"topic":"time","payload":"","payloadType":"date","x":340,"y":960,"wires":[["82e51c85.b1015"]]},{"id":"a115ec14.19375","type":"sqlite","z":"8c629254.dc91c","mydb":"fd9adcff.377c4","sqlquery":"msg.topic","sql":"","name":"MFS database","x":810,"y":700,"wires":[["13febf91.7f66d"]]},{"id":"13febf91.7f66d","type":"debug","z":"8c629254.dc91c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1000,"y":660,"wires":[]},{"id":"6f31471b.8df218","type":"sqlite","z":"8c629254.dc91c","mydb":"fd9adcff.377c4","sqlquery":"msg.topic","sql":"","name":"","x":870,"y":900,"wires":[["b9f52f6b.86e5"]]},{"id":"b9f52f6b.86e5","type":"function","z":"8c629254.dc91c","name":"time conversion mfs","func":"//var payload=msg.payload;\nfor (var i=0;i<msg.payload.length;i++)\n{\n dateObj = new Date(msg.payload[i].TIMESTAMP); \n var date=dateObj.getDate();\n var month=dateObj.getMonth()+1;\n var year=dateObj.getFullYear();\n var hours=dateObj.getHours();\n var minutes=dateObj.getMinutes(); \n var seconds=dateObj.getSeconds(); \n var newtime=date+\"/\"+month+\"/\"+year;\n newtime=newtime+\"-\"+hours+\":\"+minutes+\":\"+seconds;\n msg.payload[i].TIMESTAMP=newtime\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1090,"y":1020,"wires":[["894ee61f.e6b228"]]},{"id":"894ee61f.e6b228","type":"ui_template","z":"8c629254.dc91c","group":"92c2524c.8f3b2","name":"mfs table","order":0,"width":0,"height":0,"format":"<table style=\"width:100%\">\n  <tr>\n    <th>Index</th> \n    <th>Timestamp</th>\n    <th>UUID</th> \n    <th>PN</th>\n  </tr>\n  <tr ng-repeat=\"x in msg.payload | limitTo:20\">\n    <td>{{$index}}</td>\n    <td>{{msg.payload[$index].TIMESTAMP}}</td>\n    <td>{{msg.payload[$index].UUID}}</td> \n    <td>{{msg.payload[$index].PN}}</td>\n  </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","x":1270,"y":920,"wires":[[]]},{"id":"3feb257d.c9205a","type":"inject","z":"8c629254.dc91c","name":"Select mfs","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT *FROM MFS ORDER BY TIMESTAMP DESC LIMIT 100;\t","payload":"","payloadType":"date","x":570,"y":1040,"wires":[["6f31471b.8df218"]]},{"id":"93fdc2dc.b041e","type":"ui_text_input","z":"8c629254.dc91c","name":"","label":"tagid","tooltip":"","group":"9f520ce.39907f","order":3,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"tagid","x":210,"y":700,"wires":[["82e51c85.b1015"]]},{"id":"e6954f6c.40b54","type":"sqlitedb","db":"C:\\Users\\abhiv\\Desktop\\random.db","mode":"RWC"},{"id":"9f520ce.39907f","type":"ui_group","name":"User Input","tab":"51de2de.479bad4","order":1,"disp":true,"width":"6","collapse":false},{"id":"fd9adcff.377c4","type":"sqlitedb","db":"C:\\Users\\abhiv\\Desktop\\mfsdatabase.db","mode":"RWC"},{"id":"92c2524c.8f3b2","type":"ui_group","name":"Database","tab":"51de2de.479bad4","order":3,"disp":true,"width":"6","collapse":false},{"id":"51de2de.479bad4","type":"ui_tab","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]

abhishek · 7 January 2021 15:31

Any help is highly appreciated .!

Colin · 7 January 2021 16:01

You haven't told us what isn't working.

abhishek · 7 January 2021 16:12

the function block is not working and i don't get the expected result in the database

Colin · 7 January 2021 16:22

I suggest reading the page Writing Functions from the node red docs. That will show you how to use node.warn() to debug your code to find out where it is going wrong. If you still can't get it to do what you want then show us what is going into the function node, what is coming out of it and tell us what should be coming out.

Steve-Mcl · 7 January 2021 16:58

2 fundamental things to undertand about node-red flows...

messages DO NOT arrive at the input of a node at the same time
functions nodes are fully executed from top to bottom each time a message arrives (vars and consts are re-created).

With that in mind, you cannot simply connect a bunch of UI elements into a function node and expect values to be retained.

you have a few choices to make this work. Here are 2 ways...

Store the UI values in context then when you trigger the function, retrieve the UI values from context and make a SQL UPDATE from those.
Send all UI nodes to a JOIN node to make a single KEY/VALUE object then send that to the function node.

I see from your function node you have taken the context route (I prefer the join method) but there is a problem - you NEVER do a context.set("data",data); so the data is always empty.

some additional info to assist you in your learning...

See this article in the cookbook for an example of how to join messages into one object.
Working with context : Node-RED

abhishek · 12 January 2021 13:09

Thanks for your input.
Can you please point out the mistake in the function below and whether this approach is correct or should i change it ?

//MY FN
var data=context.get('data')|| {value:""}; // var to get data from a node .. use context.get method
var topic=msg.topic;
var payload=msg.payload;

if (topic=="tagid")
data.tagid=msg.payload;
if (topic=="part")
data.part=msg.payload;
if (topic=="time")
data.time=msg.payload;

if (topic=="submit")
{
    var newMsg ={ 
    topic:"INSERT OR IGNORE INTO MFS VALUES ("+ payload + "," + data.tagid + ","+ data.part + ")"};
	context.set('data',data)
	
    return newMsg;
}

Thanks for pointing it out.

I need to take user input from dashboard. How will join method work in this case ?

Steve-Mcl · 12 January 2021 13:33

Firstly, please edit your post - the code formatting is incorrect and really difficult to tell what is what..

Use the </> code button OR surround code with three backticks

```
paste code like this between backticks
```

abhishek · 12 January 2021 14:47

thanks for pointing it out
i have edited my post

i am a beginner and learning it

Steve-Mcl · 12 January 2021 14:58

Try this...

//MY FN
var data=context.get('data')|| {value:""}; // var to get data from a node .. use context.get method
var topic=msg.topic;
var payload=msg.payload;

if (topic=="tagid") data.tagid=msg.payload;
if (topic=="part") data.part=msg.payload;
if (topic=="time") data.time=msg.payload;

context.set('data',data); //update data object in context

if (topic=="submit")
{
    if(!data.tagid) node.warn("tagid is empty");
    if(!data.part) node.warn("part is empty");
    if(!data.time) node.warn("time is empty");
    msg.topic = `INSERT OR IGNORE INTO MFS VALUES ('${msg.payload}','${data.tagid}','${data.part})'`;	

    context.set('data',{}); //clear data in context - OPTIONAL

    return msg; //send the message to next node
}

abhishek · 12 January 2021 15:40

Thanks a lot Steve-Mcl
It helped but still the values in the database is not updated. I think we have an issue with the timestamp inject node because its value is a constant i.e. 1. the values entered by the user are getting updated but not the timestamp. I just need to record the time at the values were entered in the database.

I have attached my flow with the updated function. please have a look and suggest your input.
Thanks once again.

[{"id":"8c629254.dc91c","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"2cc7c674.3d088a","type":"sqlite","z":"8c629254.dc91c","mydb":"e6954f6c.40b54","sqlquery":"msg.topic","sql":"","name":"random number","x":390,"y":1780,"wires":[["e4ee2f85.cfa0c"]]},{"id":"e318c216.047a1","type":"inject","z":"8c629254.dc91c","name":"create","props":[{"p":"payload"},{"p":"topic","v":"CREATE TABLE RANDOMNUM( TIMESTAMP INT PRIMARY KEY NOT NULL, VALUE INT NOT NULL, BOOL INT NOT NULL)","vt":"flow"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":1720,"wires":[["2cc7c674.3d088a"]]},{"id":"e4ee2f85.cfa0c","type":"debug","z":"8c629254.dc91c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":560,"y":1820,"wires":[]},{"id":"8d74baff.f82fe8","type":"ui_text","z":"8c629254.dc91c","group":"9f520ce.39907f","order":1,"width":0,"height":0,"name":"","label":"User Input","format":"","layout":"row-spread","x":150,"y":560,"wires":[]},{"id":"26fd3592.4d2efa","type":"ui_text","z":"8c629254.dc91c","group":"9f520ce.39907f","order":2,"width":0,"height":0,"name":"","label":"Select the tag id from the below menu","format":"","layout":"row-spread","x":190,"y":620,"wires":[]},{"id":"49af049e.05468c","type":"ui_text","z":"8c629254.dc91c","group":"9f520ce.39907f","order":4,"width":0,"height":0,"name":"","label":"Enter the part number for tag","format":"","layout":"row-spread","x":160,"y":760,"wires":[]},{"id":"96fcf123.d61fd","type":"ui_text_input","z":"8c629254.dc91c","name":"","label":"value","tooltip":"","group":"9f520ce.39907f","order":5,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"part","x":130,"y":820,"wires":[["82e51c85.b1015"]]},{"id":"9cef7490.fdf528","type":"ui_button","z":"8c629254.dc91c","name":"submit","group":"9f520ce.39907f","order":6,"width":0,"height":0,"passthru":false,"label":"Submit","tooltip":"","color":"","bgcolor":"","icon":"","payload":"1","payloadType":"str","topic":"submit","x":150,"y":900,"wires":[["82e51c85.b1015"]]},{"id":"82e51c85.b1015","type":"function","z":"8c629254.dc91c","name":"","func":"//MY FN\nvar data=context.get('data')|| {value:\"\"}; // var to get data from a node .. use context.get method\nvar topic=msg.topic;\nvar payload=msg.payload;\n\nif (topic==\"tagid\") data.tagid=msg.payload;\nif (topic==\"part\") data.part=msg.payload;\nif (topic==\"time\") data.time=msg.payload;\n\ncontext.set('data',data); //update data object in context\n\nif (topic==\"submit\")\n{\n    if(!data.tagid) node.warn(\"tagid is empty\");\n    if(!data.part) node.warn(\"part is empty\");\n    if(!data.time) node.warn(\"time is empty\");\n    msg.topic = `INSERT OR IGNORE INTO MFS VALUES ('${msg.payload}','${data.tagid}','${data.part}')`;\t\n\n    context.set('data',{}); //clear data in context - OPTIONAL\n\n    return msg; //send the message to next node\n}","outputs":1,"noerr":0,"initialize":"","finalize":"","x":460,"y":720,"wires":[["a115ec14.19375","54095eb5.5da04"]]},{"id":"5e23c6cd.3a2618","type":"inject","z":"8c629254.dc91c","name":"Create MFS ","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"CREATE TABLE MFS(TIMESTAMP INT PRIMARY KEY NOT NULL , UUID INT NOT NULL , PN INT NOT NULL)","payload":"","payloadType":"date","x":600,"y":620,"wires":[["a115ec14.19375"]]},{"id":"2dbd1c44.7702e4","type":"inject","z":"8c629254.dc91c","name":"insert timestamp mfs","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"1","crontab":"","once":false,"onceDelay":0.1,"topic":"time","payload":"","payloadType":"date","x":340,"y":960,"wires":[["82e51c85.b1015"]]},{"id":"a115ec14.19375","type":"sqlite","z":"8c629254.dc91c","mydb":"fd9adcff.377c4","sqlquery":"msg.topic","sql":"","name":"MFS database","x":810,"y":700,"wires":[["13febf91.7f66d"]]},{"id":"13febf91.7f66d","type":"debug","z":"8c629254.dc91c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1000,"y":660,"wires":[]},{"id":"6f31471b.8df218","type":"sqlite","z":"8c629254.dc91c","mydb":"fd9adcff.377c4","sqlquery":"msg.topic","sql":"","name":"","x":870,"y":900,"wires":[["b9f52f6b.86e5"]]},{"id":"b9f52f6b.86e5","type":"function","z":"8c629254.dc91c","name":"time conversion mfs","func":"//var payload=msg.payload;\nfor (var i=0;i<msg.payload.length;i++)\n{\n dateObj = new Date(msg.payload[i].TIMESTAMP); \n var date=dateObj.getDate();\n var month=dateObj.getMonth()+1;\n var year=dateObj.getFullYear();\n var hours=dateObj.getHours();\n var minutes=dateObj.getMinutes(); \n var seconds=dateObj.getSeconds(); \n var newtime=date+\"/\"+month+\"/\"+year;\n newtime=newtime+\"-\"+hours+\":\"+minutes+\":\"+seconds;\n msg.payload[i].TIMESTAMP=newtime\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1090,"y":1020,"wires":[["894ee61f.e6b228"]]},{"id":"894ee61f.e6b228","type":"ui_template","z":"8c629254.dc91c","group":"92c2524c.8f3b2","name":"mfs table","order":0,"width":0,"height":0,"format":"<table style=\"width:100%\">\n  <tr>\n    <th>Index</th> \n    <th>Timestamp</th>\n    <th>UUID</th> \n    <th>PN</th>\n  </tr>\n  <tr ng-repeat=\"x in msg.payload | limitTo:20\">\n    <td>{{$index}}</td>\n    <td>{{msg.payload[$index].TIMESTAMP}}</td>\n    <td>{{msg.payload[$index].UUID}}</td> \n    <td>{{msg.payload[$index].PN}}</td>\n  </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","x":1270,"y":920,"wires":[[]]},{"id":"3feb257d.c9205a","type":"inject","z":"8c629254.dc91c","name":"Select mfs","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT *FROM MFS ORDER BY TIMESTAMP DESC LIMIT 100;\t","payload":"","payloadType":"date","x":570,"y":1040,"wires":[["6f31471b.8df218"]]},{"id":"93fdc2dc.b041e","type":"ui_text_input","z":"8c629254.dc91c","name":"","label":"tagid","tooltip":"","group":"9f520ce.39907f","order":3,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"tagid","x":210,"y":700,"wires":[["82e51c85.b1015"]]},{"id":"54095eb5.5da04","type":"debug","z":"8c629254.dc91c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":700,"y":820,"wires":[]},{"id":"e6954f6c.40b54","type":"sqlitedb","db":"C:\\Users\\abhiv\\Desktop\\random.db","mode":"RWC"},{"id":"9f520ce.39907f","type":"ui_group","name":"MFS Traceability","tab":"51de2de.479bad4","order":1,"disp":true,"width":"6","collapse":false},{"id":"fd9adcff.377c4","type":"sqlitedb","db":"C:\\Users\\abhiv\\Desktop\\mfsdatabase.db","mode":"RWC"},{"id":"92c2524c.8f3b2","type":"ui_group","name":"MFS Database","tab":"51de2de.479bad4","order":3,"disp":true,"width":"6","collapse":false},{"id":"51de2de.479bad4","type":"ui_tab","name":"Continental - Mobile Fluid Solutions","icon":"dashboard","disabled":false,"hidden":false}]

The image of the debug window for reference.

zenofmud · 12 January 2021 19:30

Your button is sending a payload of the number 1, try changing it to send a timestamp in the payload.

abhishek · 1 February 2021 16:54

Thank You so much Zenofmud.

The project is up and running

system · 15 February 2021 16:55

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Save multiple inputs Sqlite - undefined General database	9	319	23 September 2022
Insert into SQlite database? General	14	4296	10 July 2020
Storing NODE-RED input into an SQLite database General function-node	2	798	7 September 2022
Insert csv. file to SQLite General	19	1128	1 April 2021
MQTT to SQLite help please Dashboard	12	735	11 April 2021

Receiving data from multiple nodes and uploading it to sqlite database

Related topics