新增Sqlite Database
既有Node-Red 節點 新增 Sqlite Database 節點
Function : INSERT的內容
msg.topic = "INSERT INTO LED (time_led, led_status) VALUES (?,?)";
//msg.topic = "INSERT INTO LED (id, time_led, led_status) VALUES (?,?,?)";
var Today = new Date();
var yyyy = Today.getFullYear(); //年
var MM = Today.getMonth()+1; //月
var dd = Today.getDate(); //日
var h = Today.getHours(); //時
var m = Today.getMinutes(); //分
var s = Today.getSeconds(); //秒
if(MM<10)
{
MM = '0'+MM;
}
if(dd<10)
{
dd = '0'+dd;
}
if(h<10)
{
h = '0'+h;
}
if(m<10)
{
m = '0' + m;
}
if(s<10)
{
s = '0' + s;
}
var hms= yyyy + '/'+ MM + '/'+ dd + ' ' + h + ':' + m + ':' + s ;
//var id= Date.now() ;
//msg.payload = [id ,hms, msg.payload];
msg.payload = [hms, msg.payload];
//id 會由SQLite自動給定 , hms給time_led , 進來的payload給led_status
return msg;
而SQLite不用
SQLite安裝在Windows
去到SQLite下載頁麵，下載預編譯Windows的二進製文件。
將需要下載 sqlite-shell-win32-*.zip 和 sqlite-dll-win32-*.zip 壓縮文件。
創建一個文件夾C:>sqlite，並解壓上麵兩個壓縮文件，在此文件夾有：sqlite3.def, sqlite3.dll 和sqlite3.exe 文件.
添加C:>sqlite 到PATH環境變量，最後去到命令提示 sqlite3 命令，應該顯示的結果如下東西。
修改日期格式後的程式 (time_led 欄位的格式)
修改日期格式前的程式
[{"id":"51754690.ffee38","type":"mqtt in","z":"c1eadc43.582be","name":"","topic":"alex9ufo/led/led_event","qos":"1","datatype":"auto","broker":"841df58d.ee5e98","x":140,"y":80,"wires":[["94302818.ae36a8","96f2f0d8.1fbfa","d1d1e654.843568","b99597a3.65f958"]]},{"id":"94302818.ae36a8","type":"ui_text","z":"c1eadc43.582be","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"name":"","label":"MQTT Suscribe Data","format":"{{msg.payload}}","layout":"col-center","x":400,"y":80,"wires":[]},{"id":"e7f38845.851508","type":"ui_button","z":"c1eadc43.582be","name":"","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"passthru":false,"label":"LED ON","tooltip":"","color":"white","bgcolor":"","icon":"fa-circle","payload":"ON","payloadType":"str","topic":"","x":120,"y":300,"wires":[["e4961251.9311c"]]},{"id":"19bfc564.049e4b","type":"ui_button","z":"c1eadc43.582be","name":"","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"passthru":false,"label":"LED OFF","tooltip":"","color":"black","bgcolor":"","icon":"fa-circle-o","payload":"OFF","payloadType":"str","topic":"","x":120,"y":340,"wires":[["e4961251.9311c"]]},{"id":"e4961251.9311c","type":"mqtt out","z":"c1eadc43.582be","name":"","topic":"alex9ufo/led/led_event","qos":"1","retain":"false","broker":"841df58d.ee5e98","x":400,"y":340,"wires":[]},{"id":"96f2f0d8.1fbfa","type":"function","z":"c1eadc43.582be","name":"Format timestamp","func":"var date = new Date();\nvar h = date.getHours();\nvar m = date.getMinutes();\nvar s = date.getSeconds();\nif(h<10){\n h = '0'+h;\n}\nif(m<10){\n m = '0' + m;\n}\nif(s<10){\n s = '0' + s;\n}\nmsg.payload = msg.payload + ' --> Time:(' + h + ':' + m + ':' + s + ')' ;\n\nreturn msg;","outputs":1,"noerr":0,"x":150,"y":240,"wires":[["c5252b6e.bebcc8"]]},{"id":"c5252b6e.bebcc8","type":"function","z":"c1eadc43.582be","name":"Set Line API ","func":"msg.headers = {'content-type':'application/x-www-form-urlencoded','Authorization':'Bearer A4wwPNh2WqB7dlfeQyyIAwtggn1kfZSI5LkkCdia1gB'};\nmsg.payload = {\"message\":msg.payload};\nreturn msg;\n\n//oR7KdXvK1eobRr2sRRgsl4PMq23DjDlhfUs96SyUBZu","outputs":1,"noerr":0,"x":290,"y":280,"wires":[["7f86a659.2321c8"]]},{"id":"7f86a659.2321c8","type":"http request","z":"c1eadc43.582be","name":"","method":"POST","ret":"txt","paytoqs":false,"url":"https://notify-api.line.me/api/notify","tls":"","persist":false,"proxy":"","authType":"","x":440,"y":240,"wires":[["847c3e7e.3b5fc"]]},{"id":"847c3e7e.3b5fc","type":"debug","z":"c1eadc43.582be","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":550,"y":280,"wires":[]},{"id":"d1d1e654.843568","type":"debug","z":"c1eadc43.582be","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":390,"y":20,"wires":[]},{"id":"e473ddb0.8d753","type":"ui_audio","z":"c1eadc43.582be","name":"","group":"6c9116b.b62d4e8","voice":"zh-TW","always":true,"x":520,"y":120,"wires":[]},{"id":"fc1ee7eb.f3c0d8","type":"ui_button","z":"c1eadc43.582be","name":"","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"passthru":false,"label":"LED Flashing","tooltip":"","color":"blue","bgcolor":"","icon":"fa-circle-o","payload":"FLASH","payloadType":"str","topic":"","x":140,"y":380,"wires":[["e4961251.9311c"]]},{"id":"b99597a3.65f958","type":"function","z":"c1eadc43.582be","name":"","func":"var st1;\nif (msg.payload === \"ON\") {\n st1=\"LED開\"; \n} \nelse if (msg.payload === \"OFF\") {\n st1=\"LED關\";\n}\nelse if (msg.payload === \"FLASH\") {\n st1=\"LED閃爍\";\n}\nelse if (msg.payload === \"TIMER\") {\n st1=\"LED開五秒鐘\";\n}\n\nmsg.payload=st1;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":330,"y":120,"wires":[["e473ddb0.8d753","faeddd08.b340c","5b2052d1.e5ce1c"]]},{"id":"75bd5e9a.f45f5","type":"ui_button","z":"c1eadc43.582be","name":"","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"passthru":false,"label":"LED ON 5 Sec","tooltip":"","color":"purple","bgcolor":"","icon":"fa-circle-o","payload":"TIMER","payloadType":"str","topic":"","x":140,"y":420,"wires":[["e4961251.9311c"]]},{"id":"f465a5e1.e917d8","type":"mysql","z":"c1eadc43.582be","mydb":"73a92d28.0c3eb4","name":"LED STATUS","x":440,"y":200,"wires":[["272e1d81.bb3b82"]]},{"id":"faeddd08.b340c","type":"function","z":"c1eadc43.582be","name":"MySQL Function","func":"var status = msg.payload ;\nmsg.topic = \"INSERT INTO mysql_led_status (`LED_MQTT_Status`) VALUES ( '\" + status + \"');\"\nnode.status({text:msg.topic});\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":190,"y":180,"wires":[["f465a5e1.e917d8"]]},{"id":"272e1d81.bb3b82","type":"debug","z":"c1eadc43.582be","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":610,"y":200,"wires":[]},{"id":"5b2052d1.e5ce1c","type":"function","z":"c1eadc43.582be","name":"INSERT","func":"msg.topic = \"INSERT INTO LED (time_led, led_status) VALUES (?,?)\";\n//msg.topic = \"INSERT INTO LED (id, time_led, led_status) VALUES (?,?,?)\";\n\nvar date = new Date();\nvar h = date.getHours();\nvar m = date.getMinutes();\nvar s = date.getSeconds();\n\nif(h<10)\n{\n h = '0'+h;\n}\n\nif(m<10)\n{\n m = '0' + m;\n}\n\nif(s<10)\n{\n s = '0' + s;\n}\n\nvar hms= h + ':' + m + ':' + s ;\n//var id= Date.now() ;\n//msg.payload = [id ,hms, msg.payload];\nmsg.payload = [hms, msg.payload];\n\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":480,"y":160,"wires":[["258da7b9.8fe668"]]},{"id":"258da7b9.8fe668","type":"sqlite","z":"c1eadc43.582be","mydb":"19f59ce9.3edc23","sqlquery":"msg.topic","sql":"","name":"LED Status","x":630,"y":160,"wires":[["c7736a87.ce1068"]]},{"id":"c7736a87.ce1068","type":"debug","z":"c1eadc43.582be","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":630,"y":80,"wires":[]},{"id":"841df58d.ee5e98","type":"mqtt-broker","z":"","name":"","broker":"broker.mqtt-dashboard.com","port":"1883","clientid":"","usetls":false,"compatmode":false,"keepalive":"15","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"6c9116b.b62d4e8","type":"ui_group","z":"","name":"Storing IOT Data ","tab":"eeb8e179.a47a4","order":1,"disp":true,"width":"6","collapse":false},{"id":"73a92d28.0c3eb4","type":"MySQLdatabase","z":"","name":"mysql_mqtt_led","host":"127.0.0.1","port":"3306","db":"mysql_mqtt_led","tz":"GMT +8"},{"id":"19f59ce9.3edc23","type":"sqlitedb","z":"","db":"MQTT_LED.db","mode":"RWC"},{"id":"eeb8e179.a47a4","type":"ui_tab","z":"","name":"MySQL","icon":"dashboard","disabled":false,"hidden":false}]
沒有留言:
張貼留言