Node-RED hangs when manipulating MSSQL data

#1

I have a SQL command that uses 2 date parameters. If I only request a week or 2, everything runs fine.

I can request a month and bypass a change and split node, and it will run. If I run this code in a change node:

[
  payload[0].$join($keys(), ","),
  payload[].$join(*.$string(), ",")
]

It hangs.

Here is my code:

[{"id":"d20b2bd4.2e4158","type":"comment","z":"cbfed81f.36c5d8","name":"Raw LH35N-3000B Data Export","info":"","x":170,"y":200,"wires":[]},{"id":"1f54e700.77d3b9","type":"function","z":"cbfed81f.36c5d8","name":"SQL Function","func":"flow.set(\"msgto\", msg.payload.to);\nstDt = msg.payload.startDate;\nedDt = msg.payload.endDate;\n\npld = \"exec sp_MC_Data_export @stDt = '\"+stDt+\"', @edDt = '\"+edDt+\"'\";\npld = pld;\n\nmsg.payload = pld;\nreturn msg;\n\n\n\n\n","outputs":1,"noerr":0,"x":400,"y":240,"wires":[["3fbf7c8e.cd4bf4"]]},{"id":"4f0c8210.eaa76c","type":"split","z":"cbfed81f.36c5d8","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":850,"y":240,"wires":[["634bb8b3.650128"]]},{"id":"634bb8b3.650128","type":"file","z":"cbfed81f.36c5d8","name":"","filename":"/home/pi/RawMachineData.csv","appendNewline":true,"createDir":true,"overwriteFile":"false","x":1050,"y":240,"wires":[["d505558f.e9c3f8"]]},{"id":"6d94ccdc.242cb4","type":"function","z":"cbfed81f.36c5d8","name":"","func":"msg.to = flow.get(\"msgto\");\nmsg.payload = '';\nmsg.topic = 'RawMachineData Export'\nmsg.attachments = [{\n filename: 'RawMachineData.csv',\n path: '/home/pi/RawMachineData.csv'\n}];\nreturn msg;","outputs":1,"noerr":0,"x":1070,"y":300,"wires":[["1386ec1d.3a9e64","11b2bcfe.636f33"]]},{"id":"1386ec1d.3a9e64","type":"e-mail","z":"cbfed81f.36c5d8","server":"smtp.gmail.com","port":"465","secure":true,"name":"","dname":"","x":1190,"y":300,"wires":[]},{"id":"11b2bcfe.636f33","type":"delay","z":"cbfed81f.36c5d8","name":"","pauseType":"delay","timeout":"20","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":1200,"y":340,"wires":[["8c71575f.f351e8"]]},{"id":"343da1d8.fda39e","type":"json","z":"cbfed81f.36c5d8","name":"","property":"payload","action":"","pretty":false,"x":250,"y":240,"wires":[["1f54e700.77d3b9"]]},{"id":"cd9fb7d3.4debd8","type":"mqtt in","z":"cbfed81f.36c5d8","name":"","topic":"LH35N3000B","qos":"2","broker":"30af8e0d.e09262","x":110,"y":240,"wires":[["343da1d8.fda39e"]]},{"id":"8c71575f.f351e8","type":"file","z":"cbfed81f.36c5d8","name":"","filename":"/home/pi/RawMachineData.csv","appendNewline":true,"createDir":true,"overwriteFile":"delete","x":1430,"y":340,"wires":[[]]},{"id":"d505558f.e9c3f8","type":"trigger","z":"cbfed81f.36c5d8","op1":"","op2":"","op1type":"date","op2type":"date","duration":"250","extend":true,"units":"ms","reset":"","bytopic":"all","name":"","x":1280,"y":240,"wires":[["6d94ccdc.242cb4"]]},{"id":"c5e8d9c0.e5e228","type":"change","z":"cbfed81f.36c5d8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"[\t payload[0].$join($keys(), \",\"),\t payload[].$join(*.$string(), \",\")\t]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":700,"y":240,"wires":[["4f0c8210.eaa76c"]]},{"id":"3fbf7c8e.cd4bf4","type":"MSSQL","z":"cbfed81f.36c5d8","mssqlCN":"adec67da.9e5148","name":"Dev","query":"","outField":"payload","x":550,"y":240,"wires":[["c5e8d9c0.e5e228"]]},{"id":"30af8e0d.e09262","type":"mqtt-broker","z":"","name":"","broker":"10.0.0.2","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"adec67da.9e5148","type":"MSSQL-CN","z":"","tdsVersion":"7_4","name":"Dev","server":"10.0.0.11","port":"1433","encyption":true,"database":"Dev","useUTC":true,"connectTimeout":"15000","requestTimeout":"150000","cancelTimeout":"5000"}]

0 Likes

#2

Nevermind... This whole issue will be resolved when I get the NR Machine on our Local network.

0 Likes