Importing data from MSSQL into a table in dashboard

Dashboard
1

Hello,

Thanks in advance for your help. I have a table in MSSQL and I want to import these data into a table in my dashboard. I'm sure that query for selecting data is true but I have an error in my function for converting timestamp to date! I'm going to share my flow for simplicity to understand my case!

[
    {
        "id": "010882be62e27c36",
        "type": "tab",
        "label": "Flow 4",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "d4ecedcd76b49cf2",
        "type": "group",
        "z": "010882be62e27c36",
        "style": {
            "stroke": "#999999",
            "stroke-opacity": "1",
            "fill": "none",
            "fill-opacity": "1",
            "label": true,
            "label-position": "nw",
            "color": "#a4a4a4"
        },
        "nodes": [
            "f582c6879cde83c3",
            "e6f7c805b3694875",
            "629163d58d2b2ff6",
            "41acfbf0c664d218",
            "118fb59630c5a5ea",
            "eb48bcfb652ab2ea",
            "30412c3aea9dcc5f",
            "1868ac5e53302eb2",
            "81ec6960c2706a5c",
            "d02ac0f3a0ba2ef1",
            "2818070ffbea4010",
            "4b89b334b59d64cd",
            "fd4616e5f68ecb6a",
            "e5705cc287185d41",
            "475b1d0bd7e58541",
            "3d474e4fb542f1ae",
            "132f01ebac382d08",
            "71082a0b18ae90b4",
            "eb4d2733562c4f37"
        ],
        "x": 74,
        "y": 139,
        "w": 1232,
        "h": 262
    },
    {
        "id": "f582c6879cde83c3",
        "type": "ui_button",
        "z": "010882be62e27c36",
        "g": "d4ecedcd76b49cf2",
        "name": "",
        "group": "acc5121b3da9d3b2",
        "order": 6,
        "width": 3,
        "height": 1,
        "passthru": false,
        "label": "Import",
        "tooltip": "",
        "color": "",
        "bgcolor": "",
        "className": "",
        "icon": "",
        "payload": "1",
        "payloadType": "str",
        "topic": "topic",
        "topicType": "msg",
        "x": 150,
        "y": 300,
        "wires": [
            [
                "e6f7c805b3694875",
                "629163d58d2b2ff6"
            ]
        ]
    },
    {
        "id": "e6f7c805b3694875",
        "type": "debug",
        "z": "010882be62e27c36",
        "g": "d4ecedcd76b49cf2",
        "name": "",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 330,
        "y": 220,
        "wires": []
    },
    {
        "id": "629163d58d2b2ff6",
        "type": "function",
        "z": "010882be62e27c36",
        "g": "d4ecedcd76b49cf2",
        "name": "Get parameters (1)",
        "func": "msg = {\n    BMU:  flow.get(\"BMU\") ,\n    sDate: \"'\" + flow.get(\"sDate\")+ \"'\",\n    eDate: \"'\" + flow.get(\"eDate\")+ \"'\"\n};\n\n\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 350,
        "y": 260,
        "wires": [
            [
                "41acfbf0c664d218",
                "118fb59630c5a5ea"
            ]
        ]
    },
    {
        "id": "41acfbf0c664d218",
        "type": "debug",
        "z": "010882be62e27c36",
        "g": "d4ecedcd76b49cf2",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 530,
        "y": 220,
        "wires": []
    },
    {
        "id": "118fb59630c5a5ea",
        "type": "function",
        "z": "010882be62e27c36",
        "g": "d4ecedcd76b49cf2",
        "name": "Func 1",
        "func": "/******************************************************************/\n/*************************** Build Query **************************/\n\n// select = \" SELECT * FROM [MAXBESS_1].[dbo].[\" + msg.BMU + \"] WHERE Timestamp BETWEEN\n// + msg.sDate + \"AND\" + msg.eDate \";\n\n// select = \" SELECT * FROM [MAXBESS_1].[dbo].[\" + msg.BMU + \"] WHERE Timestamp BETWEEN \"+ msg.sDate + \" AND \" + msg.eDate ;\n\n\n// select =\"SELECT * FROM [MAXBESS_1].[dbo].[BMU2 ] WHERE Timestamp BETWEEN '2022-05-25 02:00:00' AND '2022-05-26 02:00:00'\"\n// msg.topic = select;\n// return msg;\n\n\nmsg.payload =  \" SELECT * FROM [MAXBESS_1].[dbo].[\" + msg.BMU + \"] WHERE Timestamp BETWEEN \"+ msg.sDate + \" AND \" + msg.eDate ;\n\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 530,
        "y": 260,
        "wires": [
            [
                "eb48bcfb652ab2ea",
                "eb4d2733562c4f37"
            ]
        ]
    },
    {
        "id": "eb48bcfb652ab2ea",
        "type": "debug",
        "z": "010882be62e27c36",
        "g": "d4ecedcd76b49cf2",
        "name": "",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 730,
        "y": 240,
        "wires": []
    },
    {
        "id": "30412c3aea9dcc5f",
        "type": "ui_template",
        "z": "010882be62e27c36",
        "g": "d4ecedcd76b49cf2",
        "group": "",
        "name": "Table template",
        "order": 1,
        "width": 30,
        "height": 16,
        "format": "\n        \n<table style=\"width:100%\">\n  <tr>\n    <th>Index</th> \n    <th>Timestamp</th>\n    <th>Cell_V1</th> \n    <th>Cell_V2</th>\n    <th>Cell_V3</th>\n    <th>Cell_V4</th>\n    <th>Cell_V5</th>\n    <th>Cell_V6</th>\n    <th>Cell_V7</th>\n    <th>Cell_V8</th>\n    <th>Cell_V9</th>\n    <th>Cell_V10</th>\n    <th>Cell_V11</th>\n    <th>Cell_V12</th>\n    <th>Cell_V13</th>\n    <th>Cell_V14</th>\n    <th>Cell_V15</th>\n    <th>Cell_V16</th>\n    <!--<th>Cell_T1</th>-->\n    <!--<th>Cell_T2</th>-->\n    <!--<th>Cell_T3</th>-->\n    <!--<th>Cell_T4</th>-->\n    <!--<th>Cell_T5</th>-->\n    <!--<th>Cell_T6</th>-->\n    <!--<th>Cell_T7</th>-->\n    <!--<th>Cell_T8</th>-->\n    \n  </tr>\n  <tr ng-repeat=\"x in msg.payload | limitTo:500\">\n    <td><center>{{$index}}</center></td>\n    <td><center>{{msg.payload[$index].Timestamp}}</center></td>\n    <td><center>{{msg.payload[$index].Cell_V1}}</center></td> \n    <td><center>{{msg.payload[$index].Cell_V2}}</center></td>\n    <td><center>{{msg.payload[$index].Cell_V3}}</center></td>\n    <td><center>{{msg.payload[$index].Cell_V4}}</center></td>\n    <td><center>{{msg.payload[$index].Cell_V5}}</center></td>\n    <td><center>{{msg.payload[$index].Cell_V6}}</center></td>\n    <td><center>{{msg.payload[$index].Cell_V7}}</center></td>\n    <td><center>{{msg.payload[$index].Cell_V8}}</center></td>\n    <td><center>{{msg.payload[$index].Cell_V9}}</center></td>\n    <td><center>{{msg.payload[$index].Cell_V10}}</center></td>\n    <td><center>{{msg.payload[$index].Cell_V11}}</center></td>\n    <td><center>{{msg.payload[$index].Cell_V12}}</center></td>\n    <td><center>{{msg.payload[$index].Cell_V13}}</center></td>\n    <td><center>{{msg.payload[$index].Cell_V14}}</center></td>\n    <td><center>{{msg.payload[$index].Cell_V15}}</center></td>\n    <td><center>{{msg.payload[$index].Cell_V16}}</center></td>\n    <!--<td><center>{{msg.payload[$index].Cell_T1}}</center></td>-->\n    <!--<td><center>{{msg.payload[$index].Cell_T2}}</center></td>-->\n    <!--<td><center>{{msg.payload[$index].Cell_T3}}</center></td>-->\n    <!--<td><center>{{msg.payload[$index].Cell_T4}}</center></td>-->\n    <!--<td><center>{{msg.payload[$index].Cell_T5}}</center></td>-->\n    <!--<td><center>{{msg.payload[$index].Cell_T6}}</center></td>-->\n    <!--<td><center>{{msg.payload[$index].Cell_T7}}</center></td>-->\n    <!--<td><center>{{msg.payload[$index].Cell_T8}}</center></td>-->\n  </tr>\n</table>\n",
        "storeOutMessages": true,
        "fwdInMessages": true,
        "resendOnRefresh": false,
        "templateScope": "local",
        "className": "",
        "x": 1200,
        "y": 300,
        "wires": [
            []
        ]
    },
    {
        "id": "1868ac5e53302eb2",
        "type": "function",
        "z": "010882be62e27c36",
        "g": "d4ecedcd76b49cf2",
        "name": "Date to TS",
        "func": "// for(var i = 0; i < msg.payload.length; i++){\n// //  9H = 32,400,000\n//       var dd = new Date(parseInt((new Date(msg.payload[i].date).getTime()).toFixed(0)) - 32400000);\n//     msg.payload[i].date = dd.toLocaleTimeString('it-IT');\n// }\n// return msg;\n\n\nfor(var i = 0; i < msg.payload.length; i++){\n    \n    date = new Date(msg.payload[i].Timestamp);\n    msg.payload[i].Timestamp = date.toLocaleString();\n    \n}\nreturn msg;",
        "outputs": 1,
        "noerr": 2,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 950,
        "y": 280,
        "wires": [
            [
                "2818070ffbea4010",
                "4b89b334b59d64cd"
            ]
        ]
    },
    {
        "id": "81ec6960c2706a5c",
        "type": "debug",
        "z": "010882be62e27c36",
        "g": "d4ecedcd76b49cf2",
        "name": "RAW data",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 940,
        "y": 240,
        "wires": []
    },
    {
        "id": "d02ac0f3a0ba2ef1",
        "type": "MSSQL",
        "z": "010882be62e27c36",
        "g": "d4ecedcd76b49cf2",
        "mssqlCN": "2f72498397332137",
        "name": "",
        "outField": "payload",
        "returnType": 0,
        "throwErrors": 1,
        "query": "",
        "modeOpt": "queryMode",
        "modeOptType": "query",
        "queryOpt": "payload",
        "queryOptType": "editor",
        "paramsOpt": "queryParams",
        "paramsOptType": "none",
        "rows": "rows",
        "rowsType": "msg",
        "params": [],
        "x": 740,
        "y": 280,
        "wires": [
            []
        ]
    },
    {
        "id": "2818070ffbea4010",
        "type": "debug",
        "z": "010882be62e27c36",
        "g": "d4ecedcd76b49cf2",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 1190,
        "y": 220,
        "wires": []
    },
    {
        "id": "4b89b334b59d64cd",
        "type": "ui_table",
        "z": "010882be62e27c36",
        "g": "d4ecedcd76b49cf2",
        "group": "30a5202a77f57bce",
        "name": "",
        "order": 1,
        "width": 21,
        "height": 7,
        "columns": [],
        "outputs": 0,
        "cts": false,
        "x": 1170,
        "y": 260,
        "wires": []
    },
    {
        "id": "fd4616e5f68ecb6a",
        "type": "comment",
        "z": "010882be62e27c36",
        "g": "d4ecedcd76b49cf2",
        "name": "Import data by Datetime (Cell data) ",
        "info": "",
        "x": 240,
        "y": 180,
        "wires": []
    },
    {
        "id": "e5705cc287185d41",
        "type": "function",
        "z": "010882be62e27c36",
        "g": "d4ecedcd76b49cf2",
        "name": "Get parameters(2)",
        "func": "msg = {\n    BMU:  flow.get(\"BMU\") ,\n    sDate_: \"'\" + flow.get(\"sDate_\"),\n    sTime: flow.get(\"sTime\")+ \"'\" ,\n    eDate_: \"'\" + flow.get(\"eDate_\"),\n    eTime: flow.get(\"eTime\")+ \"'\"\n};\n\n\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 350,
        "y": 340,
        "wires": [
            [
                "475b1d0bd7e58541",
                "3d474e4fb542f1ae"
            ]
        ]
    },
    {
        "id": "475b1d0bd7e58541",
        "type": "function",
        "z": "010882be62e27c36",
        "g": "d4ecedcd76b49cf2",
        "name": "Func 2",
        "func": "/******************************************************************/\n/*************************** Build Query **************************/\n\n\nmsg.payload =  \" SELECT * FROM [MAXBESS_1].[dbo].[\" + msg.BMU + \"] WHERE Timestamp BETWEEN \"+ msg.sDate_+\" \"+ msg.sTime + \" AND \" + msg.eDate_+\" \"+msg.eTime;\n\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 530,
        "y": 340,
        "wires": [
            [
                "71082a0b18ae90b4",
                "eb4d2733562c4f37"
            ]
        ]
    },
    {
        "id": "3d474e4fb542f1ae",
        "type": "debug",
        "z": "010882be62e27c36",
        "g": "d4ecedcd76b49cf2",
        "name": "",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 530,
        "y": 300,
        "wires": []
    },
    {
        "id": "132f01ebac382d08",
        "type": "ui_button",
        "z": "010882be62e27c36",
        "g": "d4ecedcd76b49cf2",
        "name": "",
        "group": "acc5121b3da9d3b2",
        "order": 19,
        "width": 3,
        "height": 1,
        "passthru": false,
        "label": "import",
        "tooltip": "",
        "color": "",
        "bgcolor": "",
        "className": "",
        "icon": "",
        "payload": "1",
        "payloadType": "str",
        "topic": "topic",
        "topicType": "msg",
        "x": 150,
        "y": 360,
        "wires": [
            [
                "e5705cc287185d41"
            ]
        ]
    },
    {
        "id": "71082a0b18ae90b4",
        "type": "debug",
        "z": "010882be62e27c36",
        "g": "d4ecedcd76b49cf2",
        "name": "",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 730,
        "y": 340,
        "wires": []
    },
    {
        "id": "eb4d2733562c4f37",
        "type": "MSSQL",
        "z": "010882be62e27c36",
        "g": "d4ecedcd76b49cf2",
        "mssqlCN": "2f72498397332137",
        "name": "",
        "outField": "payload",
        "returnType": "1",
        "throwErrors": 1,
        "query": "",
        "modeOpt": "queryMode",
        "modeOptType": "query",
        "queryOpt": "payload",
        "queryOptType": "editor",
        "paramsOpt": "",
        "paramsOptType": "editor",
        "rows": "rows",
        "rowsType": "msg",
        "params": [],
        "x": 740,
        "y": 280,
        "wires": [
            [
                "1868ac5e53302eb2"
            ]
        ]
    },
    {
        "id": "acc5121b3da9d3b2",
        "type": "ui_group",
        "name": "Control unit",
        "tab": "6149056c61e606b6",
        "order": 1,
        "disp": false,
        "width": 7,
        "collapse": false,
        "className": ""
    },
    {
        "id": "2f72498397332137",
        "type": "MSSQL-CN",
        "name": "",
        "server": "localhost",
        "port": "",
        "encyption": false,
        "trustServerCertificate": false,
        "database": "MAXBESS_1",
        "useUTC": false,
        "connectTimeout": "",
        "requestTimeout": "",
        "cancelTimeout": "",
        "pool": "",
        "parseJSON": false,
        "enableArithAbort": true
    },
    {
        "id": "30a5202a77f57bce",
        "type": "ui_group",
        "name": "Database",
        "tab": "6149056c61e606b6",
        "order": 3,
        "disp": true,
        "width": 21,
        "collapse": false,
        "className": ""
    },
    {
        "id": "6149056c61e606b6",
        "type": "ui_tab",
        "name": "Offline records",
        "icon": "dashboard",
        "order": 4,
        "disabled": false,
        "hidden": false
    }
]

I would appreciate it if you help me to find the error.

2

This the flow I am using

image
image1265×299 30.2 KB

and I see an error in block Date to TS but I don't know why toLocaleString is not acitve!

image

3

I'm guessing that you have not defined the variable date Try let date before the for block

4

Thanks for your reply, I tried it before but it didn't work. Now, I did what you adviced and again I have nothing in table!

image
image1717×834 83.4 KB

image
image1782×879 24.4 KB

5

Look at your picture, You can see the data array is not directly inside of payload. It is nested in another property. Therefore, your loop is doing nothing.

Have you tried putting the resulting array into msg.payload before sending function?

Tip: Use the tools node-red provides to ensure you are using the correct path to the data...

There’s a great page in the docs (Working with messages : Node-RED) that will explain how to use the debug panel to find the right path to any data item.

Pay particular attention to the part about the buttons that appear under your mouse pointer when you over hover a debug message property in the sidebar.

BX00Cy7yHi

6

Hi, Thanks for your reply. Actually I used this stucture before to show data in table but everything is messed up after I used windows system restore and then I reinstalled node-red and imported the flows. I'm reading data directly from db(MSSQL) and Honestly I don't know how to fix this :face_with_thermometer:

image
image1070×489 21.5 KB

7

Now, I changed the path . .

image
image862×309 12.1 KB

and date coverter block is working! but still in table I have nothing.

image
image1700×754 74.5 KB

8

Hello friends, Finally I found the problem and I'd like to share my experince with you maybe it helps you later to save your time!

The problem come up from MSSQL- PLUS configuration ! I tried to simulate only one single element of the array in another flow and I saw data pass through table but in original flow no! then I noticed
target output in msg.payload is empty!

image

and I noticed msg.payload has many subfolders which seems starnge, Then I checked the sql-plus properties and I set output type in original output and solved!

image
image578×788 15.7 KB

result :

image
image1783×883 50.8 KB

9

Glad you have things up and running once more.

More technically correct to say "msg.payload had multiple properties". NOTE: It is designed like that to permit the return of multiple queries and to access things like the count of rows affected, output parameter values, SQL Server system error messages etc, etc.

TBH, I mean no disrespect but that is a terrible way to do SQL. You risk SQL injection hacks. You should use the UI to make parameters.

Here are some examples that should help...

  1. Logging data in to MSSQL - #13 by Steve-Mcl
  2. Extracting a value from an MS-SQL database - #6 by Steve-Mcl