MSSQL in a function

Can anyone figure out the problem here

var endDate = flow.get("endDate") || false;
    //var endTime = flow.get("endTime") || false;

    // check if date range is correct
    if (startDate && endDate && new Date(endDate) >= new Date(startDate))
    {
        msg.topic = "SELECT * FROM [Dev].[dbo].[MQTTData] WHERE Timestamp BETWEEN  'startDate'  AND  'endDate' ";
        return msg;
    } else 

where is startDate?

tryonemoretime

[
    {
        "id": "2f8bfd2a84ecd1ae",
        "type": "tab",
        "label": "Flow 8",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "ddf7bdc1ff21ce85",
        "type": "inject",
        "z": "2f8bfd2a84ecd1ae",
        "name": "query",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "query",
        "payload": "",
        "payloadType": "date",
        "x": 170,
        "y": 360,
        "wires": [
            [
                "5c1e10ae15dfe082",
                "a51468253ed967fb"
            ]
        ]
    },
    {
        "id": "c548e007839fafe8",
        "type": "debug",
        "z": "2f8bfd2a84ecd1ae",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 1050,
        "y": 360,
        "wires": []
    },
    {
        "id": "eac240528b89c9a5",
        "type": "MSSQL",
        "z": "2f8bfd2a84ecd1ae",
        "mssqlCN": "df8c0b88.91b0a8",
        "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": 880,
        "y": 360,
        "wires": [
            [
                "c548e007839fafe8",
                "b36ee57218125cf6"
            ]
        ]
    },
    {
        "id": "5c1e10ae15dfe082",
        "type": "change",
        "z": "2f8bfd2a84ecd1ae",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "topic",
                "pt": "msg",
                "to": "download",
                "tot": "str"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 310,
        "y": 460,
        "wires": [
            [
                "a51468253ed967fb"
            ]
        ]
    },
    {
        "id": "9a3d637577bf2de2",
        "type": "change",
        "z": "2f8bfd2a84ecd1ae",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "startDate",
                "pt": "flow",
                "to": "$moment($$.payload).format(\"YYYY-MM-DD HH:mm:ss\")",
                "tot": "jsonata"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 670,
        "y": 100,
        "wires": [
            [
                "b26398ef742a467e",
                "a51468253ed967fb"
            ]
        ]
    },
    {
        "id": "3f08de5283efc210",
        "type": "change",
        "z": "2f8bfd2a84ecd1ae",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "endDate",
                "pt": "flow",
                "to": "$moment($$.payload).format(\"YYYY-MM-DD HH:mm:ss\")",
                "tot": "jsonata"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 670,
        "y": 160,
        "wires": [
            [
                "17d6e9f449142773",
                "a51468253ed967fb"
            ]
        ]
    },
    {
        "id": "b26398ef742a467e",
        "type": "debug",
        "z": "2f8bfd2a84ecd1ae",
        "name": "debug 21",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "payload",
        "statusType": "auto",
        "x": 860,
        "y": 100,
        "wires": []
    },
    {
        "id": "17d6e9f449142773",
        "type": "debug",
        "z": "2f8bfd2a84ecd1ae",
        "name": "debug 22",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "payload",
        "statusType": "auto",
        "x": 860,
        "y": 160,
        "wires": []
    },
    {
        "id": "8b58a03ce3f4a568",
        "type": "moment",
        "z": "2f8bfd2a84ecd1ae",
        "name": "",
        "topic": "",
        "input": "payload",
        "inputType": "msg",
        "inTz": "Europe/Copenhagen",
        "adjAmount": "1",
        "adjType": "hours",
        "adjDir": "subtract",
        "format": "YYYY-MM-DD HH:mm:ss",
        "locale": "dk",
        "output": "payload",
        "outputType": "msg",
        "outTz": "Europe/Copenhagen",
        "x": 420,
        "y": 160,
        "wires": [
            [
                "3f08de5283efc210"
            ]
        ]
    },
    {
        "id": "c6077467d4fb818a",
        "type": "moment",
        "z": "2f8bfd2a84ecd1ae",
        "name": "",
        "topic": "",
        "input": "payload",
        "inputType": "msg",
        "inTz": "Europe/Copenhagen",
        "adjAmount": "1",
        "adjType": "hours",
        "adjDir": "subtract",
        "format": "YYYY-MM-DD HH:mm:ss",
        "locale": "dk",
        "output": "payload",
        "outputType": "msg",
        "outTz": "Europe/Copenhagen",
        "x": 420,
        "y": 100,
        "wires": [
            [
                "9a3d637577bf2de2"
            ]
        ]
    },
    {
        "id": "f8b2b11cb7b65ea5",
        "type": "inject",
        "z": "2f8bfd2a84ecd1ae",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 220,
        "y": 100,
        "wires": [
            [
                "c6077467d4fb818a"
            ]
        ]
    },
    {
        "id": "6c9e2c0d8381d677",
        "type": "inject",
        "z": "2f8bfd2a84ecd1ae",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 220,
        "y": 160,
        "wires": [
            [
                "8b58a03ce3f4a568"
            ]
        ]
    },
    {
        "id": "9a7533924fe3e985",
        "type": "ui_text_input",
        "z": "2f8bfd2a84ecd1ae",
        "name": "",
        "label": "Start Time",
        "tooltip": "",
        "group": "e44f634b20faeabb",
        "order": 6,
        "width": 6,
        "height": 2,
        "passthru": true,
        "mode": "datetime-local",
        "delay": "0",
        "topic": "topic",
        "sendOnBlur": true,
        "className": "",
        "topicType": "msg",
        "x": 170,
        "y": 220,
        "wires": [
            [
                "c6077467d4fb818a",
                "841e01466cb98a6c"
            ]
        ]
    },
    {
        "id": "2d7bf953862cd85b",
        "type": "ui_text_input",
        "z": "2f8bfd2a84ecd1ae",
        "name": "",
        "label": "End Time",
        "tooltip": "",
        "group": "e44f634b20faeabb",
        "order": 8,
        "width": 6,
        "height": 2,
        "passthru": true,
        "mode": "datetime-local",
        "delay": "0",
        "topic": "endTime",
        "sendOnBlur": true,
        "className": "",
        "topicType": "msg",
        "x": 160,
        "y": 280,
        "wires": [
            [
                "8b58a03ce3f4a568",
                "5356411ef9be38e2"
            ]
        ]
    },
    {
        "id": "841e01466cb98a6c",
        "type": "debug",
        "z": "2f8bfd2a84ecd1ae",
        "name": "debug 23",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "payload",
        "statusType": "auto",
        "x": 400,
        "y": 220,
        "wires": []
    },
    {
        "id": "5356411ef9be38e2",
        "type": "debug",
        "z": "2f8bfd2a84ecd1ae",
        "name": "debug 24",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "payload",
        "statusType": "auto",
        "x": 400,
        "y": 280,
        "wires": []
    },
    {
        "id": "a51468253ed967fb",
        "type": "function",
        "z": "2f8bfd2a84ecd1ae",
        "name": "My SQL Database Request",
        "func": "// only execute query if download button has been clicked\nif(msg.topic == \"download\" || msg.topic == \"query\")\n{\n    var startDate = flow.get(\"startDate\") || false;\n    //var startTime = flow.get(\"startTime\") || false;\n    \n    var endDate = flow.get(\"endDate\") || false;\n    //var endTime = flow.get(\"endTime\") || false;\n\n    // check if date range is correct\n    if (startDate && endDate && new Date(endDate) >= new Date(startDate))\n    {\n        msg.topic = \"SELECT * FROM [Dev].[dbo].[MQTTData] WHERE Timestamp BETWEEN ('\" + startDate + \"',  'AND' , '\" + endDate + \"')\"\n        return msg;\n    } else \n    {\n        node.warn(\"end date is before start date. or context not set\");\n    }\n\n}\n",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 520,
        "y": 360,
        "wires": [
            [
                "2786afb55dc8df9b",
                "eac240528b89c9a5"
            ]
        ]
    },
    {
        "id": "b36ee57218125cf6",
        "type": "ui_table",
        "z": "2f8bfd2a84ecd1ae",
        "group": "e44f634b20faeabb",
        "name": "",
        "order": 6,
        "width": 0,
        "height": 0,
        "columns": [],
        "outputs": 0,
        "cts": false,
        "x": 1070,
        "y": 400,
        "wires": []
    },
    {
        "id": "2786afb55dc8df9b",
        "type": "debug",
        "z": "2f8bfd2a84ecd1ae",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 750,
        "y": 460,
        "wires": []
    },
    {
        "id": "3522418d20bd200c",
        "type": "inject",
        "z": "2f8bfd2a84ecd1ae",
        "name": "query",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "query",
        "payload": "SELECT * FROM [Dev].[dbo].[MQTTData] WHERE Timestamp BETWEEN '2023-01-01 13:51:00' AND '2023-01-18 18:51:00'",
        "payloadType": "str",
        "x": 290,
        "y": 600,
        "wires": [
            [
                "eac240528b89c9a5"
            ]
        ]
    },
    {
        "id": "df8c0b88.91b0a8",
        "type": "MSSQL-CN",
        "name": "Dev",
        "server": "P202-LAERER",
        "port": "1433",
        "encyption": false,
        "trustServerCertificate": false,
        "database": "dev",
        "useUTC": false,
        "connectTimeout": "",
        "requestTimeout": "",
        "cancelTimeout": "",
        "pool": "",
        "parseJSON": false,
        "enableArithAbort": true
    },
    {
        "id": "e44f634b20faeabb",
        "type": "ui_group",
        "name": "Siemens",
        "tab": "c781e4a970e06e4d",
        "order": 1,
        "disp": true,
        "width": "12",
        "collapse": false,
        "className": ""
    },
    {
        "id": "c781e4a970e06e4d",
        "type": "ui_tab",
        "name": "Home",
        "icon": "dashboard",
        "disabled": false,
        "hidden": false
    }
]

image

Are the values there
add more node.warn()'s
e.g.

// only execute query if download button has been clicked
if(msg.topic == "download" || msg.topic == "query")
{
    node.warn(msg.topic)
    var startDate = flow.get("startDate") || false;
    //var startTime = flow.get("startTime") || false;
    node.warn(startDate)
    var endDate = flow.get("endDate") || false;
    //var endTime = flow.get("endTime") || false;
    node.warn(endDate)
    // check if date range is correct
    if (startDate && endDate && new Date(endDate) >= new Date(startDate))
    {
        msg.topic = "SELECT * FROM [Dev].[dbo].[MQTTData] WHERE Timestamp BETWEEN ('" + startDate + "',  'AND' , '" + endDate + "')"
        return msg;
    } else 
    {
        node.warn("end date is before start date. or context not set");
    }

}

And what is the out put of
new Date(endDate) and new Date(startDate)

Used youre flow

this is the result

"The input property was NOT a recognisable date. Output will be a blank string"

image

And from MSSQL a clip look very similar i think it is the syntax of the SQL statement

Feed it into a debug node set to show complete message and see what the query is.

Does the mssql node use the topic for the query? Which node are you using?

When i copy the debug sql statement to my sql and execute it it works :frowning:

im using ms-sql plus

Firstly, you are setting msg.topic but have the SQL node set to use the internal SQL editor.

Secondly, you should use parameters for this. Especially for dates & times as getting the correct format date string is often difficult.

The better way to do a request is enter the SQL in the mssql node & use parameters

Example..

SELECT * FROM [Dev].[dbo].[MQTTData] 
WHERE 
Timestamp BETWEEN (@startDate  AND @endDate)

where @startDate & @endDate are entered in the "parameters" section & mapped to the correct property in the msg and set for the intended type.

Here is a previous working example

Thx for help
I can see now that it is your flow i'm working on :slight_smile:
i change msg.topic = "SELECT * FROM fuzzy WHERE datetime BETWEEN '"
to msg.payload = "SELECT * FROM fuzzy WHERE datetime BETWEEN '"

and now it all work without any change in the SQL thx for the input and the original flo
Reg
Peter

Doing it that way leaves your system open to attack by SQL injection, so don't do that if your node-red is accessible outside your local network. In fact you should get into the habit of doing it the safer way whether it is accessible or not.

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