Hi,
I am facing trouble in reading data from MSSQL server and displaying the same in a chart. Somehow could manage to program the "data to chart " node but not able to to make the query command work.
I am posting my flow.
Anticipating assistance, please.
[
{
"id": "0ca3c9337c5d6e21",
"type": "MSSQL",
"z": "d3a12e2fb48b81fc",
"mssqlCN": "358e71158db79bf8",
"name": "read1",
"outField": "payload",
"returnType": "0",
"throwErrors": 1,
"query": " select * from @input2 where cast ([DTE] as date) ='@input'\r\n",
"modeOpt": "queryMode",
"modeOptType": "query",
"queryOpt": "payload",
"queryOptType": "editor",
"paramsOpt": "",
"paramsOptType": "editor",
"rows": "rows",
"rowsType": "msg",
"params": [
{
"output": false,
"name": "input1",
"type": "Text",
"valueType": "global",
"value": "date",
"options": {
"nullable": true,
"primary": false,
"identity": false,
"readOnly": false
}
},
{
"output": false,
"name": "input2",
"type": "text",
"valueType": "global",
"value": "mac",
"options": {
"nullable": true,
"primary": false,
"identity": false,
"readOnly": false
}
}
],
"x": 290,
"y": 140,
"wires": [
[
"945e07068744e6eb"
]
]
},
{
"id": "5c34967ab58ddfa1",
"type": "debug",
"z": "d3a12e2fb48b81fc",
"name": "debug 4",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 720,
"y": 60,
"wires": []
},
{
"id": "4eb96b345e7a1cff",
"type": "ui_chart",
"z": "d3a12e2fb48b81fc",
"name": "",
"group": "626d77e33dc6c4ec",
"order": 1,
"width": 0,
"height": 0,
"label": "chart",
"chartType": "line",
"legend": "false",
"xformat": "HH:mm:ss",
"interpolate": "linear",
"nodata": "",
"dot": false,
"ymin": "0",
"ymax": "50",
"removeOlder": 1,
"removeOlderPoints": "",
"removeOlderUnit": "86400",
"cutout": 0,
"useOneColor": false,
"useUTC": false,
"colors": [
"#1f77b4",
"#aec7e8",
"#ff7f0e",
"#2ca02c",
"#98df8a",
"#d62728",
"#ff9896",
"#9467bd",
"#c5b0d5"
],
"outputs": 1,
"useDifferentColor": false,
"className": "",
"x": 730,
"y": 180,
"wires": [
[]
]
},
{
"id": "668a49e50f92d2af",
"type": "ui_dropdown",
"z": "d3a12e2fb48b81fc",
"name": "",
"label": "M/C",
"tooltip": "Select Meter of Reporting",
"place": "Select Machines",
"group": "626d77e33dc6c4ec",
"order": 1,
"width": 0,
"height": 0,
"passthru": true,
"multiple": false,
"options": [
{
"label": "TRIAL",
"value": "trial",
"type": "str"
},
{
"label": "tro",
"value": "login",
"type": "str"
}
],
"payload": "",
"topic": "machine",
"topicType": "str",
"className": "",
"x": 130,
"y": 400,
"wires": [
[
"f465c6ea1f5030df"
]
]
},
{
"id": "3b9d7a51551c81e2",
"type": "ui_button",
"z": "d3a12e2fb48b81fc",
"name": "",
"group": "626d77e33dc6c4ec",
"order": 3,
"width": 0,
"height": 0,
"passthru": false,
"label": "button",
"tooltip": "",
"color": "",
"bgcolor": "",
"className": "",
"icon": "",
"payload": "",
"payloadType": "str",
"topic": "topic",
"topicType": "msg",
"x": 130,
"y": 120,
"wires": [
[
"0ca3c9337c5d6e21"
]
]
},
{
"id": "f465c6ea1f5030df",
"type": "function",
"z": "d3a12e2fb48b81fc",
"name": "",
"func": "\nglobal.set(\"mac\", msg.payload)\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 360,
"y": 400,
"wires": [
[]
]
},
{
"id": "eaa37416e7f2d5d2",
"type": "function",
"z": "d3a12e2fb48b81fc",
"name": "",
"func": "\nglobal.set(\"date\", msg.payload);\n\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 360,
"y": 340,
"wires": [
[]
]
},
{
"id": "2702f9e9f5449158",
"type": "ui_date_picker",
"z": "d3a12e2fb48b81fc",
"name": "",
"label": "date",
"group": "626d77e33dc6c4ec",
"order": 3,
"width": 0,
"height": 0,
"passthru": false,
"topic": "topic",
"topicType": "msg",
"className": "",
"x": 130,
"y": 340,
"wires": [
[
"eaa37416e7f2d5d2"
]
]
},
{
"id": "945e07068744e6eb",
"type": "function",
"z": "d3a12e2fb48b81fc",
"name": "SQL-CHART",
"func": "\n\nvar series = [];\nvar data = msg.payload;\nif (!data || !Array.isArray(data) || !data.length) {\n node.warn(\"warning\");\n return null;\n \n}\n\nconst tsField = \"DTE\";\nvar _f = Object.keys(data[0]);\nvar fields = [];\nvar fi = 0;\n_f.forEach(function(elem) {\n if (elem != tsField) {\n fields.push(elem);\n series[fi++] = [];\n }\n});\n\n\nfor (let index = 0; index < msg.payload.length; index++) {\n const row = msg.payload[index];\n var t = new Date(row[tsField]).valueOf()\n var i = 0;\n for (let f = 0; f < fields.length; f++ ) {\n let field = fields[f];\n series[f].push({ \"x\": t, \"y\": row[field] })\n }\n}\n\n\nmsg.payload = [\n {\n \"series\": fields,\n \"data\": series,\n \"labels\": [\"\"]\n }\n];\n\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 490,
"y": 120,
"wires": [
[
"5c34967ab58ddfa1",
"4eb96b345e7a1cff"
]
]
},
{
"id": "358e71158db79bf8",
"type": "MSSQL-CN",
"tdsVersion": "7_4",
"name": "",
"server": "localhost",
"port": "1433",
"encyption": false,
"trustServerCertificate": false,
"database": "DATA",
"useUTC": false,
"connectTimeout": "15000",
"requestTimeout": "15000",
"cancelTimeout": "5000",
"pool": "5",
"parseJSON": false,
"enableArithAbort": true
},
{
"id": "626d77e33dc6c4ec",
"type": "ui_group",
"name": "Group 1",
"tab": "3c503526e045eeaf",
"order": 1,
"disp": true,
"width": 22
},
{
"id": "3c503526e045eeaf",
"type": "ui_tab",
"name": "Tab 28",
"icon": "dashboard",
"order": 28
}
]
I am trying to read data from a table named "trial" where the data pertains to a specific date and display the same in a chart.
