Queueing messages for MSSQL

General
1

Hi, I have a workflow using node-red-contrib-msg-queue 1.0.2 and node-red-contrib-mssql-plus 0.10.2 with node-red v20.1.0.

image
image896Ă—296 14.4 KB

I am trying to store the message if the MSSQL connection goes down using the status block to check the MSSQL node. However the message is always stored even if the MSSQL server is available. I have tried removing the queue node and the data gets written to the database. Any ideas how to deploy this workflow to function correctly? Here is my code. Thanks.

[
    {
        "id": "61625aaf.479d84",
        "type": "inject",
        "z": "b8807c3ae6f9ae7c",
        "name": "{\"count\": 5, \"age\": 35}",
        "props": [
            {
                "p": "payload",
                "v": "{\"count\": 5, \"age\": 35}",
                "vt": "json"
            },
            {
                "p": "topic",
                "v": "",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "{\"count\": 5, \"age\": 35}",
        "payloadType": "json",
        "x": 280,
        "y": 220,
        "wires": [
            [
                "58ed799b9a02ceae"
            ]
        ]
    },
    {
        "id": "6e09980a.127878",
        "type": "MSSQL",
        "z": "b8807c3ae6f9ae7c",
        "mssqlCN": "a51e405c.10f64",
        "name": "",
        "query": "PRINT @name\n\nSELECT TOP {{{payload.count}}} * \nFROM testdb.dbo.[MyTable] WHERE Name = @name\n\nSELECT TOP {{{payload.count}}} * \nFROM testdb.dbo.[MyTable] WHERE Age = @age\n\nPRINT 'complete'",
        "outField": "payload",
        "x": 700,
        "y": 220,
        "wires": [
            [
                "babb6d0.5ae7e9"
            ]
        ]
    },
    {
        "id": "babb6d0.5ae7e9",
        "type": "debug",
        "z": "b8807c3ae6f9ae7c",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "x": 890,
        "y": 220,
        "wires": []
    },
    {
        "id": "bed23a904c8705f5",
        "type": "status",
        "z": "b8807c3ae6f9ae7c",
        "name": "",
        "scope": [
            "6e09980a.127878"
        ],
        "x": 280,
        "y": 400,
        "wires": [
            [
                "61b4684c06ffbe55"
            ]
        ]
    },
    {
        "id": "61b4684c06ffbe55",
        "type": "function",
        "z": "b8807c3ae6f9ae7c",
        "name": "Check the status message text",
        "func": "// Function node\nif (msg.status) {\n    // Check the status message text\n    if (msg.status.text === 'requesting') {\n        // If the status is \"requesting\", it means the connection is connected\n        msg.payload = \"Connected\";\n    } else if (msg.status.text === 'Error') {\n        // If the status is \"Error\", it means there is an error in the connection\n        msg.payload = \"Disconnected\";\n    } else {\n        // If the status is neither \"requesting\" nor \"Error\", handle other cases here if needed\n        msg.payload = \"Unknown Status\"; // Or provide an appropriate message\n    }\n} else {\n    // If there is no status object, handle other cases here if needed\n    msg.payload = \"No Status Information\"; // Or provide an appropriate message\n}\n\n// Pass the modified message along to the next node\nreturn msg;\n",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 530,
        "y": 400,
        "wires": [
            [
                "58ed799b9a02ceae"
            ]
        ]
    },
    {
        "id": "58ed799b9a02ceae",
        "type": "queue",
        "z": "b8807c3ae6f9ae7c",
        "name": "Message Queue",
        "connected": "Connected",
        "connectedType": "str",
        "disconnected": "Disconnected",
        "disconnectedType": "str",
        "sqlite": "C:\\sqlite\\mydatabase.db",
        "x": 520,
        "y": 220,
        "wires": [
            [
                "6e09980a.127878"
            ]
        ]
    },
    {
        "id": "a51e405c.10f64",
        "type": "MSSQL-CN",
        "tdsVersion": "7_4",
        "name": "My SQL Server",
        "server": "192.168.1.38",
        "port": "1433",
        "encyption": false,
        "database": "testdb",
        "useUTC": false,
        "connectTimeout": "15000",
        "requestTimeout": "15000",
        "cancelTimeout": "5000",
        "pool": "5",
        "credentials": {}
    }
]
2

The current version of Node-red is 3.0.2 so your v20.1.0 must refer to something else. Could it be npm?

Node-red version might be relevant: there is an issue posted (today) on node-red-contrib-msg-queue GitHub page saying "Unfortunately, this node stopped working after updrading to V3 of nodered, and can no longer be installed."

3

This flow is designed for exactly such requirements
https://flows.nodered.org/flow/05e6d61f14ef6af763ec4cfd1049ab61

4

Hi jbudd, I am on V3.0.2. I was able to install node-red-contrib-msg-queue after upgrading Python, visual basic and sqlite3 but it just seems to store messages and not pass them on.

5

Thanks Colin, this seems like this could be adapted for what I need. However I tested it and the messages just seem to queue. The status of the MSSQL node only seems to change if there is an error. If the node is connected there is no status and the "OK Value of success" never reaches the Guaranteed delivery node.
image

6

Thanks @Colin That is working now after I created a function to deliver the msg.control. Do you know where the messages get stored while they are being buffered?

7

If you look at the function node one the subflow it should be obvious. It uses the standard node red context store.

1 Like
8

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