Dynamically change the ip Server of the connection config MSSQL

General
1

I'm using MSSQL node from: node-red-contrib-mssql-plus. my solution this problem

image
image1252Ă—179 10.9 KB

the problem is that the work I have to do from the second request. Can I do this with a single request?

[
    {
        "id": "7fcde0bbca6f4af3",
        "type": "inject",
        "z": "2ccf3101d95f4197",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            },
            {
                "p": "req.params.ip",
                "v": "172.16.0.81",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 160,
        "y": 180,
        "wires": [
            [
                "992d4a30cc4e11b4"
            ]
        ]
    },
    {
        "id": "8ec06251c9fbf91e",
        "type": "MSSQL",
        "z": "2ccf3101d95f4197",
        "mssqlCN": "3c560aeb7958a44b",
        "name": "150",
        "outField": "payload",
        "returnType": 0,
        "throwErrors": 1,
        "query": "SELECT DISTINCT\r\n   CASE\r\n    WHEN isntuser = '1' THEN 'AD'\r\n     ELSE 'SQL'\r\n      END  + ' | ' +\r\n  CASE\r\n    WHEN sysadmin = '1' THEN 'sysadmin'\r\n     ELSE 'user'\r\n      END  + ' | ' +  name as ok\r\n  from master.sys.syslogins WHERE hasaccess = '1' AND name not like '#%'  ORDER BY ok",
        "modeOpt": "queryMode",
        "modeOptType": "query",
        "queryOpt": "payload",
        "queryOptType": "editor",
        "paramsOpt": "queryParams",
        "paramsOptType": "none",
        "rows": "rows",
        "rowsType": "msg",
        "params": [],
        "x": 1050,
        "y": 180,
        "wires": [
            [
                "e25689e6a986a23c"
            ]
        ]
    },
    {
        "id": "992d4a30cc4e11b4",
        "type": "http request",
        "z": "2ccf3101d95f4197",
        "name": "",
        "method": "GET",
        "ret": "txt",
        "paytoqs": "ignore",
        "url": "http://172.16.150.70/flow/2ccf3101d95f4197",
        "tls": "",
        "persist": false,
        "proxy": "",
        "authType": "",
        "senderr": false,
        "x": 390,
        "y": 180,
        "wires": [
            [
                "e903247a45354994"
            ]
        ]
    },
    {
        "id": "e903247a45354994",
        "type": "json",
        "z": "2ccf3101d95f4197",
        "name": "",
        "property": "payload",
        "action": "",
        "pretty": false,
        "x": 550,
        "y": 180,
        "wires": [
            [
                "810b9d695233924a"
            ]
        ]
    },
    {
        "id": "810b9d695233924a",
        "type": "function",
        "z": "2ccf3101d95f4197",
        "name": "edit my flow",
        "func": "msg.payload.configs[0].server = msg.req.params.ip;\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 710,
        "y": 180,
        "wires": [
            [
                "c48f3f91f4cfa3e1"
            ]
        ]
    },
    {
        "id": "c48f3f91f4cfa3e1",
        "type": "http request",
        "z": "2ccf3101d95f4197",
        "name": "",
        "method": "PUT",
        "ret": "txt",
        "paytoqs": "ignore",
        "url": "http://172.16.150.70/flow/2ccf3101d95f4197",
        "tls": "",
        "persist": false,
        "proxy": "",
        "authType": "",
        "senderr": false,
        "x": 890,
        "y": 180,
        "wires": [
            [
                "8ec06251c9fbf91e"
            ]
        ]
    },
    {
        "id": "c7b4c0aaffa769b9",
        "type": "http in",
        "z": "2ccf3101d95f4197",
        "name": "",
        "url": "/test/:ip",
        "method": "get",
        "upload": false,
        "swaggerDoc": "",
        "x": 150,
        "y": 240,
        "wires": [
            [
                "992d4a30cc4e11b4"
            ]
        ]
    },
    {
        "id": "e25689e6a986a23c",
        "type": "http response",
        "z": "2ccf3101d95f4197",
        "name": "",
        "statusCode": "",
        "headers": {},
        "x": 1190,
        "y": 180,
        "wires": []
    },
    {
        "id": "3c560aeb7958a44b",
        "type": "MSSQL-CN",
        "z": "2ccf3101d95f4197",
        "tdsVersion": "7_4",
        "name": "150.34",
        "server": "172.16.150.34",
        "port": "1433",
        "encyption": false,
        "trustServerCertificate": false,
        "database": "",
        "useUTC": false,
        "connectTimeout": "15000",
        "requestTimeout": "15000",
        "cancelTimeout": "5000",
        "pool": "5",
        "parseJSON": false,
        "enableArithAbort": true
    }
]
2

Moved to General as this isn't about a feature request for core node-red development.

1 Like
3

Hi, it is not 100% clear what you are trying to achieve.

could you provide a little context?

4

@Steve-Mc I'm trying to change the database server to the IP I set. I can't find a way to change the connectors according to my requirements. Of course, I want to keep a single MSSQL node

5

Let me ask for a little more clarification...

you want to get an IP from a HTTP Request and then use that IP to change the MSSQL-CN config source address - is that what you are trying to do?

How many SQL Servers are there involved in this overall solution?

6

I have over 50 database servers, when I call ip to run an SQL script. That is my intention.
All servers have a unique user and password. Only IP differs. It's hard to define all 50 servers and then switch. Maybe it's a simpler method?

1 Like
7

I guess I'm getting what you're asking.
You want to use the API as a variable to connect to databases. Each API variable for differrent databases and systems(Mysql, SQLServer, Postgresql...), thus you can preform different task on multimple databases.

I might want to know that too.

8

I'm afraid you will need 50 SQL configs and then use routing

You have some options to make this easier...

  1. Export 1 config, use EXCEL or something to intelligently duplicate this 49 times & then import them
  2. Using a LINK CALL you can then keep your main flow simple (and hide the 50 routes) on another page

Something like

image
image883Ă—915 84 KB

If you are using V3.0.0-beta.2 (or a future reader V3.0.0+) you can further simplify this using dynamic link call - example...

image
image899Ă—656 51.6 KB

And even better...

image
image916Ă—793 56.6 KB

1 Like
9

Thank you

I did a sublow with the databases, and I'm thinking of making an api that will automatically enter them with the IP I need.

image
image840Ă—212 13.7 KB

wow nice :heart_eyes:
image
image814Ă—122 3.82 KB

10

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