Node-RED and WAMP. How do logging?

[Marco Sillano]

Using a Sonoff basic (tasmota) and mosqiutto, i wanna use old-plain WAMP as data logging and UI.

I start logging the tension values present in tele//STATE, and this looks working for me:

[{"id":"9ffc8804.6f1768","type":"mqtt in","z":"b5b3d741.809d98","name":"","topic":"tele/sonoff/STATE","qos":"0","broker":"265c223f.71cfee","x":150,"y":420,"wires":[["7c36efd6.b28ae"]]},{"id":"7c36efd6.b28ae","type":"function","z":"b5b3d741.809d98","name":"extracts time & Vcc","func":"//  input: \"{\"Time\":\"2018-01-10T12:00:40\",\"Uptime\":1,\"Vcc\":3.190,\"POWER\":\"OFF\",\"Wifi\":{\"AP\":1,\"SSId\":\"FRITZ!Box 3272\",\"RSSI\":100,\"APMac\":\"34:81:C4:EB:BA:5C\"}}\"\n// output: \"time=2018-01-10T12:00:40&volt=3.190\"\nvar words = msg.payload.split(\",\");\nvar xtime =  words[0].substr(9,19);\nvar volt  = words[2].split(\":\");\nmsg.payload = \"time=\"+xtime+ \"&volt=\"+volt[1];\nreturn msg;","outputs":1,"noerr":0,"x":170,"y":520,"wires":[["524433dd.514c3c"]]},{"id":"9ec8a1e0.bf6b9","type":"http request","z":"b5b3d741.809d98","name":"","method":"GET","ret":"txt","url":"","tls":"","x":530,"y":540,"wires":[["c3db831c.7a4eb"]]},{"id":"524433dd.514c3c","type":"function","z":"b5b3d741.809d98","name":"format url","func":"msg.headers = {\"content-type\" : \"application/x-www-form-urlencoded\"};\nmsg.url= \"http://localhost:85/www/testred/tension.php?\" + msg.payload;\nmsg.payload =\"\";\nreturn msg;","outputs":1,"noerr":0,"x":360,"y":520,"wires":[["9ec8a1e0.bf6b9","1fafe22e.2e1e3e"]]},{"id":"cf89768c.f25508","type":"debug","z":"b5b3d741.809d98","name":"ERROR (log)","active":true,"console":"false","complete":"payload","x":910,"y":540,"wires":[]},{"id":"c3db831c.7a4eb","type":"function","z":"b5b3d741.809d98","name":"test for ERROR","func":"if (msg.statusCode != 200) {\nmsg.payload = \"ERROR HTTP: code = \"+ msg.statusCode + \" (see https://en.wikipedia.org/wiki/List_of_HTTP_status_codes) \" + msg.payload;\nreturn msg;  \n}\nif (msg.payload != \"OK\"){\nmsg.payload = \"ERROR PHP: \" + msg.payload;\nreturn msg;  \n}\nreturn;\n\n\n\n","outputs":1,"noerr":0,"x":720,"y":540,"wires":[["cf89768c.f25508"]]},{"id":"1fafe22e.2e1e3e","type":"debug","z":"b5b3d741.809d98","name":"url (debug)","active":true,"console":"false","complete":"url","x":530,"y":480,"wires":[]},{"id":"265c223f.71cfee","type":"mqtt-broker","z":"","broker":"localhost","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"willTopic":"","willQos":"0","willPayload":"","birthTopic":"","birthQos":"0","birthPayload":""}]

The php page is very simple:  (the DB 'testred' as a table 'tensione', with fields 'ora TIMESTAMP' and 'volt DECIMAL(4.3)')

<?php
$d=dirname(__FILE__);
require_once ("$d/commonSQL.php");
  
       if(isset($_POST['time'])){        // POST/GET compatible, GET only for debug
           $_GET = $_POST;
       }
      $tx = $_GET['time'];
      $vx = $_GET['volt'];
      sql("INSERT INTO  `testred`.`tensione`  (`ora` ,`volt`) VALUES ('$tx', '$vx');");     // in commonSQ.php, does the SQL query
      echo "OK";
?>

------------------------------------------

I am new on Node-RED so my question is: this is the best way to update mySQL from Node-RED?

Best regards

m.s.

[steve rickus]

Marco, no need to use a separate PHP server to insert the data into MySQL -- node-red already has a MySQL node that does that directly from your flow...

You will need to install that node using the Palette Manager, after which the new node will appear under the Storage category on the left side. Put that in your flow in place of the Http Request node, and configure it with your database location and credentials. Then you will need to change your function to put your Sql Insert statement into msg.topic and wire it to the MySQL node. You can pass in Select, Insert, and Delete statements -- queries return the result rows as as array of json objects. Good Luck!

--

Steve

[Marco Sillano]

On Thursday, January 11, 2018 at 3:07:53 PM UTC+1, steve rickus wrote:

Marco, no need to use a separate PHP server to insert the data into MySQL -- node-red already has a MySQL node that does that directly from your flow..

Thanks Steve, i will try it... 

m.s.