使用nodered对常用数据库操作
mysql
- 安装节点
从node-red的节点管理中安装
- 查看帮助
节点帮助很重要，一定要仔细阅读。
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"
}
]
- 测试结果
mssql
- 安装节点
从node-red的节点管理中安装
- 查看帮助
节点帮助很重要，一定要仔细阅读。重要的事情第二遍
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"
}
]
- 测试结果
oracle
- 安装节点
从node-red的节点管理中安装
没有安装oracle客户端的话配置连接信息后会报DPI-1047错误，下载即时客户端解压包并加入环境变量 - 查看帮助
节点帮助很重要，一定要仔细阅读。重要的事情说三遍
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"
}
]
- 测试结果
欢迎来访
- 有问题欢迎留言或加交流qq:825121848
- 转载请注明出处
- 请小编喝茶~
