Trouble reading data from MSSQL

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.

  1. You need to specify the table name - you have used @input2 - this means select data from an in-memory table.
  2. do not surround a parameter with quotes.
  3. @input should be @input1

I am getting an error as given below;

I changed the data type to "datetime". Moreover I want to prefill the table name from the dropdown list "M/C" (and the date will be fed from the date picker - "date".)

For the table name, use mustache syntax as described in the built in help.

For the date time, make sure the input parameter is a date object.

Got it to work!!...thanks

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.