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)