Node-REDに飛来したデータをMySQLへ格納します
#1. テーブルを用意する
- 下記のような属性で表を用意します
CREATE TABLE TABLE06 (
MOJI_DATA VARCHAR(100)
);
mysql> show tables;
+----------------+
| Tables_in_db01 |
+----------------+
| TABLE01 |
| TABLE02 |
| TABLE03 |
| TABLE05 |
| TABLE06 |
| TABLE170908 |
| TABLE255 |
+----------------+
7 rows in set (0.00 sec)
mysql> desc table06;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| MOJI_DATA | varchar(100) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
#2. Node-REDのフローを用意します
- 下記のようなフローでNode-REDへ飛来したデータをMySQLの表へ書き込みます
MySQLの表へ書き込み
[{"id":"ba774ab5.980a18","type":"debug","z":"5f6d487f.119e6","name":"","active":true,"console":"false","complete":"payload.moji_param","x":390,"y":340,"wires":[]},{"id":"20e3127f.bbdfae","type":"debug","z":"5f6d487f.119e6","name":"","active":true,"console":"false","complete":"false","x":350,"y":300,"wires":[]},{"id":"f33a13f2.ca9a9","type":"mysql","z":"5f6d487f.119e6","mydb":"4d59b06c.961958","name":"","x":530,"y":260,"wires":[["3d75f04d.9a26d8"]]},{"id":"3d75f04d.9a26d8","type":"debug","z":"5f6d487f.119e6","name":"","active":true,"console":"false","complete":"false","x":730,"y":260,"wires":[]},{"id":"4e754c6c.a925b4","type":"function","z":"5f6d487f.119e6","name":"入力値の取り出し","func":"//msg.moji = msg.payload.moji_data;\n//msg.suji = msg.payload.suji_data;\n\nmsg.topic = \"insert into table06 (MOJI_DATA) values (\";\nmsg.topic += \"\\u0027\";\nmsg.topic += msg.payload.moji_param;\nmsg.topic += \"\\u0027\";\nmsg.topic += \");\";\n//msg.topic = \"insert into table06 (moji_data, suji_data) values ('おはよう', 123)\";\nreturn msg;\n","outputs":1,"noerr":0,"x":370,"y":260,"wires":[["f33a13f2.ca9a9"]]},{"id":"e6d43f90.5fed08","type":"template","z":"5f6d487f.119e6","name":"test16.html 入力用","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<html>\n <head>\n <style type=\"text/css\">\n {{{payload.css}}}\n </style>\n </head>\n <body style=\"background-color: #EAE5E3\">\n <div id='basic'>\n <div id='a1'>\n <h1>\n 文字列を入出力するサンプル\n </h1>\n </div>\n <div id='b1'>\n これは文字列を入出力するサンプルです\n </div>\n <div id='c1'>\n <b>何か文字列を入力します</b>\n <form action=\"http://localhost:1880/test17\" method=\"get\">\n <textarea name=\"moji_param\" cols=20 rows=1 style=\"font-size:24pt;\">あいうえおw</textarea>\n <button type=\"submit\" name=\"submit\" value=\"送信\">\n <font size=\"2\">表示要求を</font><font size=\"5\" color=\"red\">送信！</font>\n </button>\n\n </div>\n </div>\n</body>\n</html>\n","x":510,"y":100,"wires":[["1c024074.c8d81","e91e9562.e58248"]]},{"id":"c4ba4a69.ac14a","type":"template","z":"5f6d487f.119e6","name":"css","field":"payload.css","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"#basic {\n font-family: meiryo;\n}\n#a1 {\n background-color: #98d98e;\n padding: 20px;\n height: 100px;\n}\n#b1 {\n background-color: #e4de8a;\n padding: 20px;\n height: 100px;\n text-align: center;\n font-size: 18pt\n}\n#c1 {\n background-color: lightskyblue;\n padding: 20px;\n height: 100px;\n text-align: left;\n font-size: 18pt\n}\n","x":330,"y":100,"wires":[["e6d43f90.5fed08"]]},{"id":"a90af1a0.c9c8e8","type":"http in","z":"5f6d487f.119e6","name":"","url":"/test16","method":"get","upload":false,"swaggerDoc":"","x":150,"y":100,"wires":[["c4ba4a69.ac14a"]]},{"id":"1c024074.c8d81","type":"http response","z":"5f6d487f.119e6","name":"","x":710,"y":100,"wires":[]},{"id":"626b139c.e4d1e4","type":"comment","z":"5f6d487f.119e6","name":"文字列を入力するための画面を返す","info":"","x":220,"y":60,"wires":[]},{"id":"dfb772a.a7a031","type":"template","z":"5f6d487f.119e6","name":"css","field":"payload.css","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"#basic {\n font-family: meiryo;\n}\n#a1 {\n background-color: #98d98e;\n padding: 20px;\n height: 100px;\n}\n#b1 {\n background-color: #e4de8a;\n padding: 20px;\n height: 100px;\n text-align: center;\n font-size: 18pt\n}\n#c1 {\n background-color: lightskyblue;\n padding: 20px;\n height: 100px;\n text-align: left;\n font-size: 18pt\n}\n","x":330,"y":220,"wires":[["bf7e5f3c.9945c8"]]},{"id":"bead9119.47bf08","type":"http in","z":"5f6d487f.119e6","name":"","url":"/test17","method":"get","swaggerDoc":"","x":150,"y":220,"wires":[["dfb772a.a7a031","4e754c6c.a925b4","20e3127f.bbdfae","ba774ab5.980a18"]]},{"id":"1e3ad2b3.c5b15d","type":"http response","z":"5f6d487f.119e6","name":"","x":710,"y":220,"wires":[]},{"id":"e91e9562.e58248","type":"debug","z":"5f6d487f.119e6","name":"","active":false,"console":"false","complete":"false","x":730,"y":60,"wires":[]},{"id":"eaaec5d2.685a8","type":"comment","z":"5f6d487f.119e6","name":"送付された文字列をDBへ格納","info":"","x":200,"y":180,"wires":[]},{"id":"bf7e5f3c.9945c8","type":"template","z":"5f6d487f.119e6","name":"test17.html 出力用","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<html>\n <head>\n <style type=\"text/css\">\n {{{payload.css}}}\n </style>\n </head>\n <body style=\"background-color: #EAE5E3\">\n <div id='basic'>\n <div id='a1'>\n <h1>\n 文字列を入出力するサンプル\n </h1>\n </div>\n <div id='b1'>\n 送付された文字列は『<b><font color=\"blue\">{{payload.moji_param}}』</font></b>でした。\n </div>\n <div id='c1'>\n <b>何か文字列を入力します</b>\n <form action=\"http://localhost:1880/test17\" method=\"get\">\n <textarea name=\"msg\" cols=20 rows=1 style=\"font-size:24pt;\">あいうえおw</textarea>\n <button type=\"submit\" name=\"submit\" value=\"送信\">\n <font size=\"2\">表示要求を</font><font size=\"5\" color=\"red\">送信！</font>\n </button>\n\n </div>\n </div>\n</body>\n</html>\n","x":530,"y":220,"wires":[["1e3ad2b3.c5b15d"]]},{"id":"4d59b06c.961958","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"DB01","tz":""}]
#3. 入力用画面へアクセスし、文字列を送付し、データベースへ書き込まれることを確認します
- ブラウザを開き
localhost:1880/test16へアクセスします
- 適当な文字列を指定して送付します
#4. 書き込まれたことを確認します
mysql> select * from table06;
+------------------+
| moji_data |
+------------------+
| あいうえおw |
| かきくけこ |
+------------------+
2 rows in set (0.00 sec)