Node-RED導入SQLite提交SQL Server

Node-RED導入SQLite提交SQL Server

新建table

CREATE TABLE MEMBER( TIMESTAMP INT PRIMARY KEY NOT NULL, FIRSTNAME TEXT NOT NULL, LASTNAME TEXT NOT NULL, COUNTRY TEXT NOT NULL)

1) TIMESTAMP INT PRIMARY KEY NOT NULL,
2) FIRSTNAME TEXT NOT NULL,
3) LASTNAME TEXT NOT NULL,
4) COUNTRY    TEXT NOT NULL)


write query function
var d = new Date();
var timestamp = d.getTime();

var newMsg = {
    "payload":{
        "time":timestamp,
        "firstname": msg.payload.firstname,
        "lastname":  msg.payload.lastname,
        "country" : msg.payload.country
    },
    "topic": "INSERT INTO MEMBER VALUES ( " + timestamp + ", '" + msg.payload.firstname + "', '" + msg.payload.lastname + "', '" + msg.payload.country + "')"
}
return newMsg;



msg function

msg.topic = "SELECT * FROM MEMBER ORDER BY TIMESTAMP DESC LIMIT 100;";
return msg;

 Template HTML樣板

<table style="width:100%">
  <tr>
    <th>Index</th>
    <th>Timestamp</th>
    <th>Firstname</th>
    <th>Lastname</th>
    <th>Country</th>
  </tr>
  <tr ng-repeat="x in msg.payload | limitTo:20">
    <td>{{$index}}</td>
    <td>{{msg.payload[$index].TIMESTAMP}}</td>
    <td>{{msg.payload[$index].FIRSTNAME}}</td>
    <td>{{msg.payload[$index].LASTNAME}}</td>
    <td>{{msg.payload[$index].COUNTRY}}</td>
  </tr>
</table>

View records
SELECT * FROM MEMBER ORDER BY TIMESTAMP DESC LIMIT 100;


Flow JSON Sourcecode

[{"id":"cf55a763.b5ff38","type":"ui_form","z":"fea04978.fe55d8","name":"","label":"","group":"31745d9a.412202","order":0,"width":0,"height":0,"options":[{"label":"First Name","value":"firstname","type":"text","required":true},{"label":"Last Name","value":"lastname","type":"text","required":true},{"label":"Country","value":"country","type":"text","required":true}],"formValue":{"firstname":"","lastname":"","country":""},"payload":"","topic":"","x":110,"y":80,"wires":[["3f5f4dda.fc2022","24308878.5823e8"]]},{"id":"3f5f4dda.fc2022","type":"http request","z":"fea04978.fe55d8","name":"","method":"POST","ret":"txt","url":"localhost:1880/mysitepost","tls":"","x":310,"y":80,"wires":[[]]},{"id":"f71bc20e.2b85b","type":"sqlite","z":"fea04978.fe55d8","mydb":"77e8c1a4.6e881","sqlquery":"msg.topic","sql":"","name":"","x":520,"y":140,"wires":[["e5d3a3d1.44fea","ebe341d5.18eec"]]},{"id":"fa5d0e64.396e4","type":"inject","z":"fea04978.fe55d8","name":"create database table","topic":"CREATE TABLE MEMBER( TIMESTAMP INT PRIMARY KEY NOT NULL, FIRSTNAME TEXT NOT NULL, LASTNAME TEXT NOT NULL, COUNTRY TEXT NOT NULL)","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"x":260,"y":200,"wires":[["f71bc20e.2b85b"]]},{"id":"e5d3a3d1.44fea","type":"debug","z":"fea04978.fe55d8","name":"","active":true,"console":"false","complete":"false","x":670,"y":140,"wires":[]},{"id":"24308878.5823e8","type":"function","z":"fea04978.fe55d8","name":"write query","func":"var d = new Date();\nvar timestamp = d.getTime();\n\nvar newMsg = {\n    \"payload\":{\n        \"time\":timestamp,\n        \"firstname\": msg.payload.firstname,\n        \"lastname\":  msg.payload.lastname,\n        \"country\" : msg.payload.country\n    },\n    \"topic\": \"INSERT INTO MEMBER VALUES ( \" + timestamp + \", '\" + msg.payload.firstname + \"', '\" + msg.payload.lastname + \"', '\" + msg.payload.country + \"')\"\n}\nreturn newMsg;","outputs":1,"noerr":0,"x":310,"y":140,"wires":[["f71bc20e.2b85b"]]},{"id":"bb4ecef5.9fbc8","type":"inject","z":"fea04978.fe55d8","name":"View records","topic":"SELECT * FROM MEMBER ORDER BY TIMESTAMP DESC LIMIT 100;","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":270,"y":260,"wires":[["f71bc20e.2b85b"]]},{"id":"4c2d7ba7.5ab7e4","type":"inject","z":"fea04978.fe55d8","name":"delete","topic":"DELETE FROM MEMBER WHERE TIMESTAMP <= strftime('%s','now', '-1 hours')*1000","payload":"","payloadType":"date","repeat":"60","crontab":"","once":false,"x":120,"y":140,"wires":[["f71bc20e.2b85b"]]},{"id":"98c12adf.ad0728","type":"ui_template","z":"fea04978.fe55d8","group":"63800e4a.d5e23","name":"","order":0,"width":"10","height":"10","format":"<table style=\"width:100%\">\n  <tr>\n    <th>Index</th> \n    <th>Timestamp</th>\n    <th>Firstname</th> \n    <th>Lastname</th>\n    <th>Country</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].FIRSTNAME}}</td> \n    <td>{{msg.payload[$index].LASTNAME}}</td>\n    <td>{{msg.payload[$index].COUNTRY}}</td>\n  </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"templateScope":"local","x":940,"y":180,"wires":[[]]},{"id":"99a0029a.fed36","type":"sqlite","z":"fea04978.fe55d8","mydb":"77e8c1a4.6e881","sqlquery":"msg.topic","sql":"","name":"","x":780,"y":180,"wires":[["98c12adf.ad0728"]]},{"id":"ebe341d5.18eec","type":"function","z":"fea04978.fe55d8","name":"msg","func":"msg.topic = \"SELECT * FROM MEMBER ORDER BY TIMESTAMP DESC LIMIT 100;\";\nreturn msg;","outputs":1,"noerr":0,"x":650,"y":180,"wires":[["99a0029a.fed36"]]},{"id":"31745d9a.412202","type":"ui_group","z":"","name":"Default","tab":"1d173c13.e2dc64","disp":true,"width":"6"},{"id":"77e8c1a4.6e881","type":"sqlitedb","z":"","db":"sqlite.db","mode":"RWC"},{"id":"63800e4a.d5e23","type":"ui_group","z":"","name":"Database","tab":"1d173c13.e2dc64","disp":true,"width":"10"},{"id":"1d173c13.e2dc64","type":"ui_tab","z":"","name":"Home","icon":"dashboard"}]