SQL SERVER Node

General
1

Hello, I am working with SQL Server and I need to make a query using Node Red so that it brings me information from a table.
my script is the following:

select * from alc.ActivityType where ActivityTypeName = 'variable'

where it says "variable" I want to enter the values ​​that come from another node.
I downloaded node-red-contrib-mssql-plus.
I configured it correctly since when I add the script with a fixed variable it brings me the table information. But I can't find a way to add the variable coming from another node.

2

Something like this should work (Note: I don't use this Node, so may not be 100%)

WHEN Query -> Editor

select * from alc.ActivityType where ActivityTypeName = '{{{msg.filterValue}}}'

Your msg

msg.filterValue = 'Foo-Bar'

WHEN Query -> msg (lets assume topic, and Parameters option is set to payload)

Your msg

msg.topic = select * from alc.ActivityType where ActivityTypeName = '@filterValue'
msg.payload = {
   filterValue: 'Foo-Bar'
}

I think @Steve-Mcl may correct me here :innocent:

3

Somewhat

mssql-plus is a lot easier to use (in theory) and safer by default - when using it properly.

image
image759×609 23.3 KB

2 Likes
4

It didn't work for me, in the function node I have the msg:

msg.filterValue = 'intervencion\Reparación\Cañerias AG\.\PVC\90 mm. (Trabajo)'

the SQL node y set it like This:

nodo
nodo1056×780 89.9 KB

5

Set the output to driver mode.
Add a debug (set to show complete msg object)
Show me the debug output (expand all properties)
Show me your table with a manual query using that string in the where clause.

6

this is the debug

nodo
nodo1866×890 244 KB

And this is the debug whit the response of the SQL node with de script on the query editor

nodo 2
nodo 2446×618 82.7 KB

and this is the clipboard of this nodes.

[
    {
        "id": "8a7a4f463020edb6",
        "type": "MSSQL",
        "z": "4ab6a4fb7cf2f03a",
        "mssqlCN": "2b06f502f12b11a7",
        "name": "",
        "outField": "payload",
        "returnType": "1",
        "throwErrors": "0",
        "query": "select * from alc.ActivityType where ActivityTypeName = @type",
        "modeOpt": "",
        "modeOptType": "query",
        "queryOpt": "",
        "queryOptType": "editor",
        "paramsOpt": "",
        "paramsOptType": "editor",
        "rows": "rows",
        "rowsType": "msg",
        "parseMustache": false,
        "params": [
            {
                "output": false,
                "name": "type",
                "type": "VarChar",
                "valueType": "msg",
                "value": "filterValue",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            }
        ],
        "x": 820,
        "y": 1900,
        "wires": [
            [
                "516b732d290c57d9"
            ]
        ]
    },
    {
        "id": "516b732d290c57d9",
        "type": "debug",
        "z": "4ab6a4fb7cf2f03a",
        "name": "debug 456",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 1030,
        "y": 1900,
        "wires": []
    },
    {
        "id": "2b739a5e48d3eae0",
        "type": "inject",
        "z": "4ab6a4fb7cf2f03a",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 480,
        "y": 1900,
        "wires": [
            [
                "16702c357e386af0"
            ]
        ]
    },
    {
        "id": "16702c357e386af0",
        "type": "function",
        "z": "4ab6a4fb7cf2f03a",
        "name": "function 73",
        "func": "//msg.query = \"select * from alc.ActivityType where ActivityTypeName = 'intervencion\\Reparación\\Cañería AG\\.\\PVC\\90 mm. (Trabajo)'\"\n//msg.topic = \"select * from alc.ActivityType where ActivityTypeName = '@filterValue'\"\nmsg.filterValue = 'intervencion\\Reparación\\Cañería AG\\.\\PVC\\90 mm. (Trabajo)'\n\n\nreturn msg",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 650,
        "y": 1900,
        "wires": [
            [
                "8a7a4f463020edb6"
            ]
        ]
    },
    {
        "id": "2b06f502f12b11a7",
        "type": "MSSQL-CN",
        "tdsVersion": "7_4",
        "name": "",
        "server": "10.40.2.8",
        "port": "13341",
        "encyption": true,
        "trustServerCertificate": true,
        "database": "WLM",
        "useUTC": true,
        "connectTimeout": "15000",
        "requestTimeout": "15000",
        "cancelTimeout": "5000",
        "pool": "5",
        "parseJSON": false,
        "enableArithAbort": true,
        "readOnlyIntent": false
    }
]
7

You haven't shown me a manual query that returns a row containing that exact string.

8

Wait, is that your data I see?

9

ok, a second look - I know what is happening.

As you are setting the filter value in a function node AND it has \ backslashes, they are acting as escapes.

Your filter value intervencion\Reparación\Cañería AG\.\PVC\90 mm. (Trabajo) is getting converted to intervencionReparaciónCañería AG.PVC90 mm. (Trabajo)
proof: image before it even reaches the MSSQL node. A simple debug node BEFORE the MSSQL node would have highlighted this.

To escape a backslash in JavaScript string, you need to use double \\ backslash

10

Yes was that!!! Thank you so much!!!!

11

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