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