node-red 读取OPCUA数据存储到SQLServer中

sunly_8081

已于 2024-07-05 15:16:23 修改

阅读量834

点赞数 17

分类专栏： Node-red 文章标签： sqlserver 数据库

于 2024-07-05 15:07:15 首次发布

本文链接：https://blog.csdn.net/sunly_8081/article/details/140207669

版权

Node-red 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

整体流程如下图所示

OPCUAtoSQLServer
首先，您需要安装node-red-contrib-opcua和node-red-contrib-mssql-plus节点，以便使用OPC UA Client获取OPC变量值，连接SQLServer。
在这里插入图片描述

然后，您可以使用Inject节点（OPC采集）作为触发器，用于定期采集OPC变量值。

接下来，通过Function节点进行数据预处理。把需要需要采集的OPC UA变量放到这里，格式如图所示。
根据OPC UA服务器配置OPC UA Client 节点。
在这里插入图片描述
把获得的OPC UA 的值传送到 function（数据处理flow）中，把变量和值，成对的存放到flow中的数组变量中。（下边这段代码根据别人的代码，由GPT生成。）

var myvalue = msg.payload; // 获取消息的 payload
var ss = JSON.stringify(msg.topic.nodeId); // 将 msg.topic.nodeId 转换为字符串
// node.warn(msg);
ss = ss.replace(/^"|"$/g, ''); // 移除字符串开头和结尾的双引号
// node.warn(ss);
// 确保替换后的字符串是有效的
var nodearr = ss.split('='); // 以 '=' 分割字符串
// node.warn(nodearr);

// var tagid = nodearr[nodearr.length - 1].slice(0, -1).replace(/#/, '_').replace(/\.+/g, '_'); // 获取最后一个元素，去掉最后一个字符，并替换 '#' 为 '_'
var tagid = nodearr[nodearr.length - 1].replace(/[^a-zA-Z0-9_\u4e00-\u9fa5]/g, '_').replace(/_+/g, '_'); // // 获取最后一个元素，替换非法字符为下划线，并将连续的下划线替换为单个下划线

// node.warn(tagid);

flow.set(tagid, myvalue); // 将 tagid 和 myvalue 存储在 flow 范围内
var keys = flow.keys(); // 获取所有存储的 keys
// node.warn("num:" + keys.length);
// for (let i = 0; i < keys.length; i++) { // 遍历所有 keys
//     var key = keys[i];
//     var value = flow.get(key); // 获取每个 key 对应的值
//     // 可以在这里添加对 key 和 value 的处理逻辑
//     // node.warn("key:" + key + ", value: " + value);
// }

msg.payload = tagid; // 将 msg.payload 设置为 tagid
return msg; // 返回 msg

在上述代码中，我们将获取到的OPC变量值存储到flow中。
下边是把flow中存储的数组发送给SQLServer。
在这里插入图片描述
这是一个周期性触发的节点。

使用function节点拼接出SQL语句，这个拼接SQL语句的function是GPT写的，代码如下：

// 初始化 SQL 语句字符串
var sql = "";

// 获取流程上下文中所有的 key
var keys = flow.keys();
// node.warn("num:" + keys.length); // 输出 key 的数量用于调试
node.warn(keys);
// 定义替换非法字符的正则表达式，只保留字母、数字、汉字和下划线
var regex = /[^a-zA-Z0-9_\u4e00-\u9fa5]/g;

// 遍历所有的 key，并输出其对应的 value
for (var i = 0; i < keys.length; i++) {
    var key = keys[i]; // 获取当前的 key
    var value = flow.get(key); // 获取当前 key 对应的 value
    // node.warn("Key：" + key + ", Value: " + value); // 输出 key 和对应的 value 用于调试

    // 替换非法字符
    var sanitizedKey = key.replace(regex, '_');
    // node.warn(sanitizedKey);
    // 判断数据类型并选择适当的数据类型
    var valueType = typeof value;
    var columnType = "";
    var valueStr = "";

    if (valueType === "string") {
        columnType = "NVARCHAR(MAX)";
        valueStr = "'" + value + "'";
    } else if (valueType === "number") {
        if (Number.isInteger(value)) {
            columnType = "INT";
        } else {
            columnType = "FLOAT";
        }
        valueStr = value;
    } else if (valueType === "boolean") {
        columnType = "BIT";
        valueStr = value ? 1 : 0;
    } else {
        // 如果数据类型不支持，则跳过该键值对
        continue;
    }
    // node.warn(columnType);

    // 构建创建表的 SQL 语句，确保表存在并且结构正确
    var createTableSQL =
        "IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='" + "scada_" +  sanitizedKey + "' AND xtype='U') " + "BEGIN " +
        "CREATE TABLE scada_" + sanitizedKey + " (" +
        "id INT IDENTITY(1,1) PRIMARY KEY, " +
        "value " + columnType + " NOT NULL, " +
        "datetime DATETIME NOT NULL); " +
        "END; ";

    // // 生成当前时间的 SQL 语句
    // var datetime = new Date().toISOString().slice(0, 19).replace('T', ' ');
    // 获取当前北京时间
    var datetime = new Date().toLocaleString("en-US", { timeZone: "Asia/Shanghai", hour12: false }).replace(',', '');

    // 构建插入 SQL Server 数据库的 SQL 语句
    var insertSQL = "INSERT INTO scada_" + sanitizedKey + " (value, datetime) VALUES (" + valueStr + ", '" + datetime + "'); ";

    // 拼接创建表和插入数据的 SQL 语句
    sql += createTableSQL + insertSQL;
}

// node.warn(sql); // 输出构建的 SQL 语句用于调试

// sql 为生成的插入 SQL Server 数据库的 SQL 语句
msg.payload = sql; // 将 SQL 语句设置为 msg.payload
return msg; // 返回 msg

最后，把拼接的SQL语句发送到SQLServer数据库。
在这里插入图片描述
这是就能在数据库中看到已经创建了新的表，表名是变量地址，每个变量一个表，表中已经有了插入的数据。
最后一行是清理所有flow中的变量，这段代码由GPT生成。

// 清除所有 flow 范围内的变量
var keys = flow.keys(); // 获取所有存储的 keys
for (var i = 0; i < keys.length; i++) { // 遍历所有 keys
    flow.set(keys[i], undefined); // 将每个 key 对应的值设置为 undefined
}

// 验证是否清除成功
var clearedKeys = flow.keys(); // 再次获取所有存储的 keys
if (clearedKeys.length === 0) {
    node.warn("Flow context successfully cleared."); // 输出成功信息
    msg.payload = "Flow context successfully cleared."; // 设置 msg.payload 为成功信息
} else {
    node.warn("Failed to clear flow context. Remaining keys: " + clearedKeys); // 输出失败信息和剩余的 keys
    msg.payload = "Failed to clear flow context. Remaining keys: " + clearedKeys; // 设置 msg.payload 为失败信息和剩余的 keys
}

return msg; // 返回 msg

下边是Node-red流程导出的JSON，可以直接导入。

[
    {
        "id": "2ff79274839a2769",
        "type": "inject",
        "z": "0d611e41d43fa0d1",
        "name": "OPC采集",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "1",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 130,
        "y": 60,
        "wires": [
            [
                "97045ced4ff50d65"
            ]
        ]
    },
    {
        "id": "97045ced4ff50d65",
        "type": "function",
        "z": "0d611e41d43fa0d1",
        "name": "OPC预处理",
        "func": "msg.topic = \"readmultiple\";\nmsg.payload = [\n    \"ns=2;s=OPC._System._WriteOptimizationDutyCycle\",\n\n    ];\nreturn msg;",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 310,
        "y": 60,
        "wires": [
            [
                "d52d24fb48f4bc6c"
            ]
        ]
    },
    {
        "id": "d52d24fb48f4bc6c",
        "type": "OpcUa-Client",
        "z": "0d611e41d43fa0d1",
        "endpoint": "bfd4fb1d3827c759",
        "action": "readmultiple",
        "deadbandtype": "a",
        "deadbandvalue": 1,
        "time": 10,
        "timeUnit": "s",
        "certificate": "n",
        "localfile": "",
        "localkeyfile": "",
        "securitymode": "None",
        "securitypolicy": "None",
        "useTransport": false,
        "maxChunkCount": 1,
        "maxMessageSize": 8192,
        "receiveBufferSize": 8192,
        "sendBufferSize": 8192,
        "name": "",
        "x": 500,
        "y": 60,
        "wires": [
            [
                "7349fa2b6154dce2"
            ],
            []
        ]
    },
    {
        "id": "7349fa2b6154dce2",
        "type": "function",
        "z": "0d611e41d43fa0d1",
        "name": "数据存储flow",
        "func": "var myvalue = msg.payload; // 获取消息的 payload\nvar ss = JSON.stringify(msg.topic.nodeId); // 将 msg.topic.nodeId 转换为字符串\n// node.warn(msg);\nss = ss.replace(/^\"|\"$/g, ''); // 移除字符串开头和结尾的双引号\n// node.warn(ss);\n// 确保替换后的字符串是有效的\nvar nodearr = ss.split('='); // 以 '=' 分割字符串\n// node.warn(nodearr);\n\n// var tagid = nodearr[nodearr.length - 1].slice(0, -1).replace(/#/, '_').replace(/\\.+/g, '_'); // 获取最后一个元素，去掉最后一个字符，并替换 '#' 为 '_'\nvar tagid = nodearr[nodearr.length - 1].replace(/[^a-zA-Z0-9_\\u4e00-\\u9fa5]/g, '_').replace(/_+/g, '_'); // // 获取最后一个元素，替换非法字符为下划线，并将连续的下划线替换为单个下划线\n\n// node.warn(tagid);\n\nflow.set(tagid, myvalue); // 将 tagid 和 myvalue 存储在 flow 范围内\nvar keys = flow.keys(); // 获取所有存储的 keys\n// node.warn(\"num:\" + keys.length);\n// for (let i = 0; i < keys.length; i++) { // 遍历所有 keys\n//     var key = keys[i];\n//     var value = flow.get(key); // 获取每个 key 对应的值\n//     // 可以在这里添加对 key 和 value 的处理逻辑\n//     // node.warn(\"key:\" + key + \", value: \" + value);\n// }\n\nmsg.payload = tagid; // 将 msg.payload 设置为 tagid\nreturn msg; // 返回 msg\n",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 690,
        "y": 60,
        "wires": [
            []
        ]
    },
    {
        "id": "6a8c15570f23af35",
        "type": "inject",
        "z": "0d611e41d43fa0d1",
        "name": "clear flow",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 120,
        "y": 180,
        "wires": [
            [
                "0e64427425525cc6"
            ]
        ]
    },
    {
        "id": "0e64427425525cc6",
        "type": "function",
        "z": "0d611e41d43fa0d1",
        "name": "清除所有 flow 范围内的变量",
        "func": "// //遍万所有的 key, 删除所有\n// var keys = Object.keys(context.global);\n// for (var i=0; i<keys.length; i++)\n// {\n//     delete context.global[keys[i]];\n// } \n// return msg;\n\n\n// // 获取所有全局变量的 key\n// var keys = global.keys();\n\n// // 遍历所有的 key，并删除对应的变量\n// for (var i = 0; i < keys.length; i++) {\n//     global.set(keys[i], undefined);\n// }\n\n// return msg;\n\n\n// 清除所有 flow 范围内的变量\nvar keys = flow.keys(); // 获取所有存储的 keys\nfor (var i = 0; i < keys.length; i++) { // 遍历所有 keys\n    flow.set(keys[i], undefined); // 将每个 key 对应的值设置为 undefined\n}\n\n// 验证是否清除成功\nvar clearedKeys = flow.keys(); // 再次获取所有存储的 keys\nif (clearedKeys.length === 0) {\n    node.warn(\"Flow context successfully cleared.\"); // 输出成功信息\n    msg.payload = \"Flow context successfully cleared.\"; // 设置 msg.payload 为成功信息\n} else {\n    node.warn(\"Failed to clear flow context. Remaining keys: \" + clearedKeys); // 输出失败信息和剩余的 keys\n    msg.payload = \"Failed to clear flow context. Remaining keys: \" + clearedKeys; // 设置 msg.payload 为失败信息和剩余的 keys\n}\n\nreturn msg; // 返回 msg\n\n",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 340,
        "y": 180,
        "wires": [
            []
        ]
    },
    {
        "id": "7866e9175ff383ac",
        "type": "function",
        "z": "0d611e41d43fa0d1",
        "name": "数据上传",
        "func": "// 初始化 SQL 语句字符串\nvar sql = \"\";\n\n// 获取流程上下文中所有的 key\nvar keys = flow.keys();\n// node.warn(\"num:\" + keys.length); // 输出 key 的数量用于调试\n// node.warn(keys);\n// 定义替换非法字符的正则表达式，只保留字母、数字、汉字和下划线\nvar regex = /[^a-zA-Z0-9_\\u4e00-\\u9fa5]/g;\n\n// 遍历所有的 key，并输出其对应的 value\nfor (var i = 0; i < keys.length; i++) {\n    var key = keys[i]; // 获取当前的 key\n    var value = flow.get(key); // 获取当前 key 对应的 value\n    // node.warn(\"Key：\" + key + \", Value: \" + value); // 输出 key 和对应的 value 用于调试\n\n    // 替换非法字符\n    var sanitizedKey = key.replace(regex, '_');\n    // node.warn(sanitizedKey);\n    // 判断数据类型并选择适当的数据类型\n    var valueType = typeof value;\n    var columnType = \"\";\n    var valueStr = \"\";\n\n    if (valueType === \"string\") {\n        columnType = \"NVARCHAR(MAX)\";\n        valueStr = \"'\" + value + \"'\";\n    } else if (valueType === \"number\") {\n        if (Number.isInteger(value)) {\n            columnType = \"INT\";\n        } else {\n            columnType = \"FLOAT\";\n        }\n        valueStr = value;\n    } else if (valueType === \"boolean\") {\n        columnType = \"BIT\";\n        valueStr = value ? 1 : 0;\n    } else {\n        // 如果数据类型不支持，则跳过该键值对\n        continue;\n    }\n    // node.warn(columnType);\n\n    // 构建创建表的 SQL 语句，确保表存在并且结构正确\n    var createTableSQL =\n        \"IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='\" + \"scada_\" +  sanitizedKey + \"' AND xtype='U') \" + \"BEGIN \" +\n        \"CREATE TABLE scada_\" + sanitizedKey + \" (\" +\n        \"id INT IDENTITY(1,1) PRIMARY KEY, \" +\n        \"value \" + columnType + \" NOT NULL, \" +\n        \"datetime DATETIME NOT NULL); \" +\n        \"END; \";\n\n    // // 生成当前时间的 SQL 语句\n    // var datetime = new Date().toISOString().slice(0, 19).replace('T', ' ');\n    // 获取当前北京时间\n    var datetime = new Date().toLocaleString(\"en-US\", { timeZone: \"Asia/Shanghai\", hour12: false }).replace(',', '');\n\n    // 构建插入 SQL Server 数据库的 SQL 语句\n    var insertSQL = \"INSERT INTO scada_\" + sanitizedKey + \" (value, datetime) VALUES (\" + valueStr + \", '\" + datetime + \"'); \";\n\n    // 拼接创建表和插入数据的 SQL 语句\n    sql += createTableSQL + insertSQL;\n}\n\n// node.warn(sql); // 输出构建的 SQL 语句用于调试\n\n// sql 为生成的插入 SQL Server 数据库的 SQL 语句\nmsg.payload = sql; // 将 SQL 语句设置为 msg.payload\nreturn msg; // 返回 msg\n",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 300,
        "y": 120,
        "wires": [
            [
                "fc465fae77e43b94"
            ]
        ]
    },
    {
        "id": "459e05f8b3fc6e67",
        "type": "inject",
        "z": "0d611e41d43fa0d1",
        "name": "数据上传",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "1",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 130,
        "y": 120,
        "wires": [
            [
                "7866e9175ff383ac"
            ]
        ]
    },
    {
        "id": "fc465fae77e43b94",
        "type": "MSSQL",
        "z": "0d611e41d43fa0d1",
        "mssqlCN": "9dc94b6947cb78c9",
        "name": "",
        "outField": "payload",
        "returnType": 0,
        "throwErrors": 1,
        "query": "",
        "modeOpt": "queryMode",
        "modeOptType": "query",
        "queryOpt": "payload",
        "queryOptType": "editor",
        "paramsOpt": "queryParams",
        "paramsOptType": "none",
        "rows": "rows",
        "rowsType": "msg",
        "parseMustache": true,
        "params": [],
        "x": 500,
        "y": 120,
        "wires": [
            []
        ]
    },
    {
        "id": "bfd4fb1d3827c759",
        "type": "OpcUa-Endpoint",
        "endpoint": "opc.tcp://SJ-KEPSer-01:49320",
        "secpol": "None",
        "secmode": "None",
        "none": true,
        "login": false,
        "usercert": false,
        "usercertificate": "",
        "userprivatekey": ""
    },
    {
        "id": "9dc94b6947cb78c9",
        "type": "MSSQL-CN",
        "tdsVersion": "7_4",
        "name": "SQL_TEST",
        "server": "127.0.0.1",
        "port": "1433",
        "encyption": true,
        "trustServerCertificate": true,
        "database": "",
        "useUTC": true,
        "connectTimeout": "15000",
        "requestTimeout": "15000",
        "cancelTimeout": "5000",
        "pool": "5",
        "parseJSON": false,
        "enableArithAbort": true,
        "readOnlyIntent": false
    }
]