-2

Im trying to create a Dashboard connected to my Database. Everything works fine, but when i try to filter the result with a SQL Query im getting the Error :

"TypeError: Cannot read property 'payload' of undefined"

If i cut the Date-part out the output still works.

Debug_Window Picture of my nodes

  1. Date-part (Picking 2 Dates and try to show informations between these)
  2. SQL-Query + Connection + Output to Table
  3. Filter by Machine_ID (MAT4 and MAT6 are the Machine_Names)

Node-Red

Function Node throwing error:

[{"id":"a87a533e.7502c","type":"function","z":"5c6013ad.23ba3c","name":"Query_ID","func":"var machine_id = msg.machineId.payload;\nvar beginDate = msg.begindate.payload;\nvar endDate = msg.enddate.payload;\n\n\n\nmsg.topic = \"SELECT processdata.time,eqpm_machines.name AS machine_name,tags.Id AS tags_no,tags.name,processdata.value,tags.units,processdata.item_serial_no FROM processdata JOIN tags ON processdata.tag_id=tags.Id JOIN eqpm_machines ON processdata.machine_id=eqpm_machines.Id WHERE 1\";\n\nif(machine_id !== 0){\n    msg.topic = msg.topic + \" AND processdata.machine_id = \" + machine_id ;\n}\n\n\nif(beginDate !== \"\" && endDate !== \"\"){\n    msg.topic = msg.topic + \" AND processdata.time >= '\" + beginDate + \"' AND processdata.time <= '\" + endDate + \"'\" ;\n}\n\n\nif(1 == 1){\n    msg.topic = msg.topic + \";\";\n}\n\nreturn msg;","outputs":1,"noerr":0,"x":580,"y":140,"wires":[["527131e3.e2df","95da523b.7b8f"]]}]

All Nodes: 

[{"id":"70cb9a7a.7d32f4","type":"subflow","name":"Dropdown_for_MachineID","info":"","in":[{"x":200,"y":180,"wires":[{"id":"5698dce4.98ed94"}]}],"out":[{"x":900,"y":320,"wires":[{"id":"ab37a9a2.6d2648","port":0}]}]},{"id":"785c286a.4073e8","type":"mysql","z":"70cb9a7a.7d32f4","mydb":"6f90c35b.00d35c","name":"","x":530,"y":200,"wires":[["4b472402.9312dc","525cddf2.159384"]]},{"id":"3ad59ab4.36a996","type":"inject","z":"70cb9a7a.7d32f4","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":220,"y":280,"wires":[["5698dce4.98ed94"]]},{"id":"5698dce4.98ed94","type":"function","z":"70cb9a7a.7d32f4","name":"Query","func":"var id = msg.payload.id;\nvar machine_id = msg.payload.machine_id;\nvar tag_id = msg.payload.tag_id;\n\nmsg.topic = \"SELECT processdata.Id,processdata.time,eqpm_machines.Id AS machine_id ,eqpm_machines.name AS machine_name,tags.Id AS tags_no,tags.name,processdata.value,tags.units,processdata.item_serial_no FROM processdata JOIN tags ON processdata.tag_id=tags.Id JOIN eqpm_machines ON processdata.machine_id=eqpm_machines.Id GROUP BY machine_id HAVING 1\";\n\n\n/*\nif(machine_id !== \"\"){\n    msg.topic = msg.topic + \" AND processdata.machine_id =\" + machine_id ;\n}\n\nif(tag_id !== \"\"){\n    msg.topic = msg.topic + \" AND processdata.tag_id=  \" + tag_id ;\n}\n\n\nif(1 == 1){\n //   msg.topic = msg.topic + \"AND INNER JOIN \";\n    msg.topic = msg.topic + \";\";\n}\n*/\n\nreturn msg;","outputs":1,"noerr":0,"x":410,"y":200,"wires":[["785c286a.4073e8"]]},{"id":"ab37a9a2.6d2648","type":"function","z":"70cb9a7a.7d32f4","name":"","func":"msg.options = msg.payload;\nreturn msg;\n\n","outputs":1,"noerr":0,"x":710,"y":320,"wires":[[]]},{"id":"525cddf2.159384","type":"change","z":"70cb9a7a.7d32f4","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.{machine_name:machine_id}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":600,"y":420,"wires":[["9e25a7e5.868ff8","ab37a9a2.6d2648"]]},{"id":"9e25a7e5.868ff8","type":"debug","z":"70cb9a7a.7d32f4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":810,"y":420,"wires":[]},{"id":"4b472402.9312dc","type":"debug","z":"70cb9a7a.7d32f4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":710,"y":160,"wires":[]},{"id":"de4f6679.aaa758","type":"tab","label":"Flow 4","disabled":false,"info":""},{"id":"77006751.545558","type":"mysql","z":"de4f6679.aaa758","mydb":"6f90c35b.00d35c","name":"Datenbank","x":1050,"y":380,"wires":[["6f674497.02751c","d2ca02c6.d422e","e222ef82.8faae"]]},{"id":"2f8e5fc5.9e2de","type":"function","z":"de4f6679.aaa758","name":"Query_ID","func":"var machine_id = msg.machineId.payload;\nvar beginn_date = msg.payload.begindate.payload;\nvar end_date = msg.payload.enddate.payload;\n\nmsg.topic = \"SELECT processdata.time,eqpm_machines.name AS machine_name,tags.Id AS tags_no,tags.name,processdata.value,tags.units,processdata.item_serial_no FROM processdata JOIN tags ON processdata.tag_id=tags.Id JOIN eqpm_machines ON processdata.machine_id=eqpm_machines.Id WHERE 1\";\n\nif(machine_id !== \"\" && beginn_date !== \"\" && end_date !==\"\"){\n    msg.topic = msg.topic + \" AND processdata.machine_id =\" + machine_id + \" AND processdata.time >= \" + beginn_date + \" AND processdata.time <= \" + end_date;\n}\n\nif(1 == 1){\n //   msg.topic = msg.topic + \"AND INNER JOIN \";\n    msg.topic = msg.topic + \";\";\n}\n\nreturn msg;","outputs":1,"noerr":0,"x":840,"y":380,"wires":[["77006751.545558","1478fccf.a8a3d3"]]},{"id":"d2ca02c6.d422e","type":"json","z":"de4f6679.aaa758","name":"","property":"payload","action":"","pretty":false,"x":1250,"y":380,"wires":[["23afecf2.d72d14"]]},{"id":"6f674497.02751c","type":"debug","z":"de4f6679.aaa758","name":"DATABASE","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1050,"y":340,"wires":[]},{"id":"1478fccf.a8a3d3","type":"debug","z":"de4f6679.aaa758","name":"QUERY_ID","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":850,"y":340,"wires":[]},{"id":"e222ef82.8faae","type":"ui_template","z":"de4f6679.aaa758","group":"2efc921b.eeb7de","name":"Output_Table","order":1,"width":"18","height":"11","format":"<style>\n {\n    font-family: \"Trebuchet MS\", Arial, Helvetica, sans-serif;\n    border-collapse: collapse;\n    width: 100%;\n    postion: solid;\n}\n\ntd, th {\n    border: 1px solid #ddd;\n    padding: 8px;\n}\n\ntr:nth-child(even){background-color: #f2f2f2;}\n\ntr:hover {background-color: #ddd;}\n\nth {\n    padding-top: 12px;\n    padding-bottom: 12px;\n    text-align: left;\n    background-color: #2a71d4;\n    color: white;\n}\n</style>\n\n<table>\n    <tr>\n        <th>Time</th>\n        <th>Machine</th>\n        <th>Tag_ID</th>\n        <th>Value</th>\n        <th>Unit</th>\n        <th>Item_Serial_No</th>\n    </tr>\n  <tr ng-repeat=\"payload in msg.payload\">\n    <td>{{ payload.time }}</td>\n    <td>{{ payload.machine_name }} </td>\n    <td>{{ payload.name }} </td>\n    <td>{{ payload.value }} </td>\n    <td>{{ payload.units}}</td>\n    <td>{{ payload.item_serial_no }} </td>\n  </tr>\n</table>","storeOutMessages":false,"fwdInMessages":true,"templateScope":"local","x":1250,"y":420,"wires":[[]]},{"id":"23afecf2.d72d14","type":"debug","z":"de4f6679.aaa758","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1250,"y":340,"wires":[]},{"id":"c6044e3.2372bb","type":"inject","z":"de4f6679.aaa758","name":"","topic":"","payload":"Started!","payloadType":"str","repeat":"","crontab":"","once":true,"onceDelay":"","x":220,"y":240,"wires":[["452ea09d.3dff1"]]},{"id":"1036ce2b.9809f2","type":"ui_button","z":"de4f6679.aaa758","name":"Button_ID_All","group":"9e579e53.ca3d","order":0,"width":"6","height":"1","passthru":false,"label":"Show all","color":"","bgcolor":"","icon":"","payload":" ","payloadType":"str","topic":"Button_Reset","x":440,"y":440,"wires":[["2f8e5fc5.9e2de"]]},{"id":"452ea09d.3dff1","type":"delay","z":"de4f6679.aaa758","name":"","pauseType":"delay","timeout":"500","timeoutUnits":"milliseconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":230,"y":440,"wires":[["1036ce2b.9809f2","64318e66.b5cad"]]},{"id":"64318e66.b5cad","type":"subflow:70cb9a7a.7d32f4","z":"de4f6679.aaa758","name":"","x":370,"y":380,"wires":[["be714c06.a52f1"]]},{"id":"be714c06.a52f1","type":"ui_dropdown","z":"de4f6679.aaa758","name":"","label":"","place":"Select option","group":"9e579e53.ca3d","order":0,"width":0,"height":0,"passthru":true,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"","x":580,"y":380,"wires":[["2f8e5fc5.9e2de","52a3f69.79b2e08"]]},{"id":"52a3f69.79b2e08","type":"function","z":"de4f6679.aaa758","name":"SET_FLOW","func":"flow.set(\"machineId\",msg.payload);\nreturn msg;\n","outputs":1,"noerr":0,"x":610,"y":140,"wires":[["e0e22ca1.2bcbd"]]},{"id":"d858418c.b956d","type":"debug","z":"de4f6679.aaa758","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":770,"y":160,"wires":[]},{"id":"e0e22ca1.2bcbd","type":"function","z":"de4f6679.aaa758","name":"GET_FLOW","func":"var from_input = msg._fromInput;\n\n    var machineId = {payload: flow.get(\"machineId\")};\n    msg.machineId = machineId;\n\nreturn msg;","outputs":1,"noerr":0,"x":610,"y":180,"wires":[["d858418c.b956d","2f8e5fc5.9e2de"]]},{"id":"7838fe98.cc06a","type":"ui_template","z":"de4f6679.aaa758","group":"5b074627.dbdca8","name":"StartDate","order":0,"width":"6","height":"4","format":"<!DOCTYPE html>\n<html lang=\"en\">\n<head>\n  <title>Bootstrap Example</title>\n  <meta charset=\"utf-8\">\n  <meta name=\"viewport\" content=\"width=device-width, initial-scale=1\">\n  <link rel=\"stylesheet\" href=\"https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css\">\n  <script src=\"https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js\"></script>\n  <script src=\"https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js\"></script>\n\n</head>\n<body>\n    \n    <form action=\"/ui\" method=\"post\">\n        <div id=\"date\">Begin:</div>\n        <input type=\"datetime-local\" name=\"begindate\" id=\"bdate\" value=\"2013-10-24T20:36:00\" step=\"1\">\n        <br>\n        <div id=\"date\">to End:</div>\n        <input type=\"datetime-local\" name=\"enddate\" id=\"edate\" value=\"2013-10-24T20:36:00\" step=\"1\">\n        <br><br>\n        <input type=\"submit\" id=\"submit\">\n      </form>\n      \n      <div id=\"divdiv\">L</div>\n\n<script src=\"./index.js\"></script>\n\n</body>\n</html>\n","storeOutMessages":true,"fwdInMessages":true,"templateScope":"local","x":300,"y":660,"wires":[["3898d89c.e7fbb8"]]},{"id":"ff37db30.247b08","type":"ui_template","z":"de4f6679.aaa758","group":"4f219b41.f1f344","name":"JS","order":0,"width":0,"height":0,"format":"<script src=\"https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js\"></script>\n<script>\n\n\"use strict\";\n\n        \n\n});\n\n\n    \n\n   function Rep(whenDate, index, replacement,elementID) {\n        var str = whenDate;\n        str = setCharAt(str,index,replacement);\n        document.getElementById(elementID).innerText = \"Time: \" + str;\n\n}\n\n    function setCharAt(str,index,chr) {\n        if(index > str.length-1) return str;\n        return str.substr(0,index) + chr + str.substr(index+1);\n}\n\n\ndocument.getElementById(\"submit\").addEventListener(\"click\", function(){\n\n    //event.preventDefault();\n\n    let beginDate = document.getElementById(\"bdate\").value;\n    let endDate = document.getElementById(\"edate\").value;\n\n\tRep(beginDate, 10, \" \",\"begindatetimetext\");\n    Rep(endDate, 10, \" \",\"enddatetimetext\");\n\n});\n//JQuery\n\n $(document).ready(function(){\n     \n    let dropdown__change = document.getElementById(\"select_value_label_0\");\n     \n    $(\"#submit\").click(function(){\n        $.post(\"/ui\",\n        {\n          dropdown__status: \n        },\n    });\n});\n\n\n\n</script>","storeOutMessages":true,"fwdInMessages":true,"templateScope":"local","x":290,"y":620,"wires":[[]]},{"id":"3898d89c.e7fbb8","type":"http response","z":"de4f6679.aaa758","name":"","statusCode":"","headers":{},"x":430,"y":660,"wires":[]},{"id":"b1d800ef.d4a16","type":"http in","z":"de4f6679.aaa758","name":"","url":"/ui","method":"post","upload":false,"swaggerDoc":"","x":300,"y":700,"wires":[["88ad979d.dac068"]]},{"id":"88ad979d.dac068","type":"function","z":"de4f6679.aaa758","name":"REMOVE_T_FROM_DATE","func":"    msg.payload.begindate = {payload:msg.payload.begindate.replace(/T/g, \" \")};\n    msg.payload.enddate = {payload:msg.payload.enddate.replace(/T/g, \" \")};\n    return msg;\n    ","outputs":1,"noerr":0,"x":500,"y":700,"wires":[["ff18db4a.19e958"]]},{"id":"ff18db4a.19e958","type":"function","z":"de4f6679.aaa758","name":"SET_FLOW","func":"flow.set(\"begindate\",msg.payload.begindate);\nflow.set(\"enddate\",msg.payload.enddate);\n\nreturn msg;\n","outputs":1,"noerr":0,"x":650,"y":660,"wires":[["e0eb0b34.d41488"]]},{"id":"e0eb0b34.d41488","type":"function","z":"de4f6679.aaa758","name":"GET_FLOW","func":"msg.begindate = flow.get(\"begindate\")\nmsg.enddate = flow.get(\"enddate\")\nreturn msg;","outputs":1,"noerr":0,"x":670,"y":620,"wires":[["79bd9f2f.3fffa","2f8e5fc5.9e2de"]]},{"id":"79bd9f2f.3fffa","type":"debug","z":"de4f6679.aaa758","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":890,"y":600,"wires":[]},{"id":"6f90c35b.00d35c","type":"MySQLdatabase","z":"","host":"localhost","port":"","db":"pda","tz":""},{"id":"2efc921b.eeb7de","type":"ui_group","z":"de4f6679.aaa758","name":"Output_Table","tab":"f5df333.e4490d","order":4,"disp":true,"width":"18","collapse":false},{"id":"9e579e53.ca3d","type":"ui_group","z":"","name":"Button","tab":"f5df333.e4490d","disp":true,"width":"6","collapse":false},{"id":"5b074627.dbdca8","type":"ui_group","z":"","name":"Date","tab":"f5df333.e4490d","disp":true,"width":"6","collapse":false},{"id":"4f219b41.f1f344","type":"ui_group","z":"5c6013ad.23ba3c","name":"Output_Table","tab":"e4025f2e.5474c","order":4,"disp":true,"width":"18","collapse":false},{"id":"f5df333.e4490d","type":"ui_tab","z":"de4f6679.aaa758","name":"Attendorn","icon":"dashboard","order":1},{"id":"e4025f2e.5474c","type":"ui_tab","z":"5c6013ad.23ba3c","name":"Attendorn","icon":"dashboard","order":1}]
Oliwer Krzyszczyk
  • 1
  • 1
  • 2
  • The debug output is too small to see -- which node is throwing the error? If it is a `function` node, please update your post to include the content of that one function. It would also help to see a picture of just that function node's input msg, expanded a few levels to show the structure, in the debug sidebar. – SteveR Jun 30 '18 at 13:16
  • Query_ID is throwing this Error. Updated it – Oliwer Krzyszczyk Jul 02 '18 at 05:28

1 Answers1

1

The error is inside the "REMOVE_T_FROM_DATE" function:

msg.payload.begindate = {payload:msg.payload.begindate.replace(/T/g, " ")};
msg.payload.enddate = {payload:msg.payload.enddate.replace(/T/g, " ")};
return msg;

Your incoming msg.payload is already an object, with begindate and enddate fields. In the first two lines of your code, you are replacing those two string with another javascript object with its own payload (that's what the {payload: xxx} syntax means... create a new object with a payload field value of xxx)

Instead, the right-side of the equals sign should return a new string representing the data string without the "T" in between the date and time:

msg.payload.begindate = msg.payload.begindate.replace(/T/g, " ");
msg.payload.enddate = msg.payload.enddate.replace(/T/g, " ");
return msg;

This also means that the downstream "Query_Id" function needs to be changed to get its data from different fields of the incoming msg object:

var machine_id = msg.payload.machineId;
var begin_date = msg.payload.begindate;
var end_date = msg.payload.enddate;

FYI -- All this being said, there are other (simpler) ways to do the same thing without writing any code. I like to use the node-red-contrib-moment node to format all my dates and times. Or, if you don't want to install another 3rd party node, you could use the core change node to replace "T" with a " " space.

One other suggestion is to remove your SET_FLOW/GET_FLOW function pairs, and use a change node to "Set:" flow. begindate "to" msg. begindate which copies the begindate from the incoming msg to flow context, without changing the msg. This means that you do not have to retrieve the flow context value right after setting it.

SteveR
  • 1,015
  • 8
  • 12