Receiving data from multiple nodes and uploading it to sqlite database

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}]

Any help is highly appreciated .! :grinning:

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

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

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.

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

  1. messages DO NOT arrive at the input of a node at the same time
  2. 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...

  1. 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.
  2. 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...

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 ?

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
```

thanks for pointing it out
i have edited my post

i am a beginner and learning it

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
}

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.

image

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