使用nodered对常用数据库操作

mysql

  • 安装节点
    从node-red的节点管理中安装
    mysqlinstall
  • 查看帮助
    节点帮助很重要,一定要仔细阅读。
    nodedesc
    msg.topic: 内容是sql脚本语句
    msg.payload: 以数组形式存储sql脚本参数或脚本返回值
    mysqlReconnectTime:重连时间可以在settings.js中设置
  • 创建流程
    示例采用四个节点,注入、设置topic、mysql、debug
[
    {
        "id": "fce31c80.e33",
        "type": "tab",
        "label": "mysqldemo",
        "disabled": false,
        "info": ""
    },
    {
        "id": "5ac7fa51.6e4d44",
        "type": "inject",
        "z": "fce31c80.e33",
        "name": "",
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "x": 110,
        "y": 100,
        "wires": [
            [
                "84b8d38.57ef73"
            ]
        ]
    },
    {
        "id": "2bbf6de4.99bcb2",
        "type": "mysql",
        "z": "fce31c80.e33",
        "mydb": "a052576c.4f3778",
        "name": "localhost",
        "x": 500,
        "y": 100,
        "wires": [
            [
                "a430906f.e73bd"
            ]
        ]
    },
    {
        "id": "a430906f.e73bd",
        "type": "debug",
        "z": "fce31c80.e33",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "x": 670,
        "y": 100,
        "wires": []
    },
    {
        "id": "84b8d38.57ef73",
        "type": "change",
        "z": "fce31c80.e33",
        "name": "设定msg.topic",
        "rules": [
            {
                "t": "set",
                "p": "topic",
                "pt": "msg",
                "to": "SELECT * FROM iot_terminal",
                "tot": "str"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 320,
        "y": 100,
        "wires": [
            [
                "2bbf6de4.99bcb2"
            ]
        ]
    },
    {
        "id": "a052576c.4f3778",
        "type": "MySQLdatabase",
        "z": "",
        "host": "localhost",
        "port": "3306",
        "db": "iot",
        "tz": "Asia/Shanghai"
    }
]
  • 测试结果
    mysqlresult

mssql

  • 安装节点
    从node-red的节点管理中安装
    mssqlinstall
  • 查看帮助
    节点帮助很重要,一定要仔细阅读。重要的事情第二遍
    nodedesc
    query中输入sql脚本执行,支持mustache(胡子)语法,包括msg、flow context、global context
    如果query为空,则把payload当作脚本执行
    多个查询将返回记录集合数组
    推荐用受影响的行数校验INSERT、UPDATE、DELETE是否成功
    msg.payload: 通常是返回结果
  • 创建流程
    示例采用四个节点,注入、mssql、debug
[
    {
        "id": "fce31c80.e33",
        "type": "tab",
        "label": "mssqldemo",
        "disabled": false,
        "info": ""
    },
    {
        "id": "5ac7fa51.6e4d44",
        "type": "inject",
        "z": "fce31c80.e33",
        "name": "",
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "x": 110,
        "y": 100,
        "wires": [
            [
                "4c7626ac.247368"
            ]
        ]
    },
    {
        "id": "a430906f.e73bd",
        "type": "debug",
        "z": "fce31c80.e33",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "x": 670,
        "y": 100,
        "wires": []
    },
    {
        "id": "4c7626ac.247368",
        "type": "MSSQL",
        "z": "fce31c80.e33",
        "mssqlCN": "d8c7fd9f.83ef2",
        "name": "localhost",
        "query": "select * from FS_TREELIST",
        "outField": "payload",
        "returnType": 0,
        "throwErrors": 1,
        "x": 420,
        "y": 100,
        "wires": [
            [
                "a430906f.e73bd"
            ]
        ]
    },
    {
        "id": "d8c7fd9f.83ef2",
        "type": "MSSQL-CN",
        "z": "",
        "tdsVersion": "7_3_B",
        "name": "jxFrontMachine",
        "server": "localhost",
        "port": "1433",
        "encyption": false,
        "database": "FrontMachine",
        "useUTC": true,
        "connectTimeout": "15000",
        "requestTimeout": "15000",
        "cancelTimeout": "5000",
        "pool": "5"
    }
]
  • 测试结果
    mssqlresult

oracle

  • 安装节点
    从node-red的节点管理中安装
    oracledbinstall
    没有安装oracle客户端的话配置连接信息后会报DPI-1047错误,下载即时客户端解压包并加入环境变量
  • 查看帮助
    节点帮助很重要,一定要仔细阅读。重要的事情说三遍
    oracledesc
    msg.payload: 包含要在查询中使用的字段的数组,数组中的第一个元素与查询中的第一个“:fieldname”参数等相对应
    msg.query: 包含SQL查询的字符串,如果不可用,将使用默认SQL
    msg.fieldMappings: 包含对象到数组字段映射的数组。将在msg.payload的内容不是数组时使用。如果不可用,将使用默认字段映射
  • 创建流程
    示例采用四个节点,注入、组装查询sql、oracledb、debug
[
    {
        "id": "fce31c80.e33",
        "type": "tab",
        "label": "oracledemo",
        "disabled": false,
        "info": ""
    },
    {
        "id": "5ac7fa51.6e4d44",
        "type": "inject",
        "z": "fce31c80.e33",
        "name": "",
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "x": 110,
        "y": 100,
        "wires": [
            [
                "aa5da6fd.5c0c98"
            ]
        ]
    },
    {
        "id": "a430906f.e73bd",
        "type": "debug",
        "z": "fce31c80.e33",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "x": 670,
        "y": 100,
        "wires": []
    },
    {
        "id": "c786068b.ce5028",
        "type": "oracledb",
        "z": "fce31c80.e33",
        "name": "localhost",
        "usequery": false,
        "query": "INSERT INTO oracleTableName\n\t(fieldName1, fieldName2, Fieldname3)\n\tVALUES (\n\t\t:valueOfValuesArrayIndex0,\n\t\t:valueOfValuesArrayIndex1,\n\t\t:valueOfValuesArrayIndex2,\n\t)",
        "usemappings": false,
        "mappings": "[\n\t\"type\"\n]",
        "server": "e82ba541.cb8538",
        "resultaction": "multi",
        "resultlimit": 100,
        "x": 440,
        "y": 100,
        "wires": [
            [
                "a430906f.e73bd"
            ]
        ]
    },
    {
        "id": "aa5da6fd.5c0c98",
        "type": "function",
        "z": "fce31c80.e33",
        "name": "querysql",
        "func": "msg.payload = {\"type\":\"SZ\"};\nmsg.query = \"select * from REL_UMP_LMP where type=:type\";\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "x": 270,
        "y": 120,
        "wires": [
            [
                "c786068b.ce5028"
            ]
        ]
    },
    {
        "id": "e82ba541.cb8538",
        "type": "oracle-server",
        "z": "",
        "host": "localhost",
        "port": "1521",
        "reconnect": true,
        "reconnecttimeout": "5000",
        "db": "ORCL"
    }
]
  • 测试结果
    oracleresult

欢迎来访

  • 有问题欢迎留言或加交流qq:825121848
  • 转载请注明出处
  • 请小编喝茶~