node-red-contrib-sql-prepare
v0.0.9
Published
A node which translate an array input into an sql command
Readme
node-red-contrib-sql-prepare
https://github.com/yannt23/node-red-contrib-sql-prepare
Install
Either use the Node-RED Menu - Manage Palette - Install, or run the following command in your Node-RED user directory - typically ~/.node-red
npm i node-red-contrib-sql-prepareLicense
MIT License
Copyright (c) 2021 yannt23
Usage
A node that converts array data input to sql insert
The node needs
msg.column_name set as an array of table columns,
msg.column_sort as array for data value,
msg.command to tell the node what to do,
msg.data as array for data select,
msg.column_data for update value if the update is used and
msg.database as string with the name of the Database table.
Note
The Arrays for INSERT some data can be as long as you want, it s just important, that the length of msg.column_name and msg.column_sort is the same;
The Arrays for SELECT some data can be as long as you want;
You can only update one column, the term arrays for defining the update can be as long as you need them;
You can delete all columns, which have the same statements you define them;
Example:
**INSERT into Database
|column1 |column2 |column3|
|-----------|:---------:|------:|
|5 | Text | 8 |
`msg.column_name` = [column1,column2,column3];
`msg.column_sort` = [5,"Text",8];
`msg.database` = "test";
`msg.command` = "insert";
*INSERT INTO test (column1,column2,column3) VALUES(5,"Text",8)
**SELECT hole row (*) from Database**
|column1 |column2 |column3|
|-----------|:---------:|------:|
| 5 | Text1 | 452 |
| 6 | Text2 | 24 |
| 7 | Text3 | 25 |
| 8 | Text4 | 8 |
`msg.column_name` = [];
`msg.column_sort` = ["column1"];
`msg.data` = [7];
`msg.database` = "test";
`msg.command` = "select";
*SELECT * FROM test WHERE column1=7
**SELECT only defined columns from Database
|column1 |column2 |column3|
|-----------|:---------:|------:|
| 5 | Text1 | 452 |
| 6 | Text2 | 24 |
| 7 | *Text3* | 25 |
| 8 | Text4 | 8 |
`msg.column_name` = ["column2"];
`msg.column_sort` = ["column1","column3"];
`msg.data` = [7,25];
`msg.database` = "test";
`msg.command` = "select";
*SELECT column2 FROM test WHERE column1='7' AND column3='25'
**UPDATE one value in the existing records in a table
|column1 |column2 |column3|
|-----------|:----------:|------:|
| 5 | Text1 | 452 |
| 6 | Text2 | update|
| 7 | Text3 | 25 |
| 8 | Text4 | 8 |
`msg.column_name` = ["column3"];
`msg.column_data` = [UPDATE];
`msg.column_sort` = ["column1",column2];
`msg.data` = [6,"Text2"];
`msg.database` = "test";
`msg.command` = "update";
*UPDATE test SET column3='UPDATE' WHERE column1='6' AND column2='Text2'
**DELETE existing records in a table
|column1 |column2 |column3|
|-----------|:---------:|------:|
| 5 | Text1 | 452 |
| 6 | Text2 | 125 |
| ** 7 | Text3 | 25 |
| 8 | Text4 | 8 |
`msg.column_name` = [];
`msg.column_sort` = ["column1","column2","column3"];
`msg.data` = [6,"Text2",125];
`msg.database` = "test";
`msg.command` = "delete";
*DELETE FROM test WHERE column1='6' AND column2='Text2' AND column3='125'
**Example code
`[
{
"id": "1b088f24.18cdf1",
"type": "sql-prepare",
"z": "af9b6025.dd026",
"name": "",
"x": 830,
"y": 140,
"wires": [
[
"524d4664.0f5b58"
]
]
},
{
"id": "5be245ef.14d62c",
"type": "function",
"z": "af9b6025.dd026",
"name": "SELECT only defined columns from Database",
"func": "\nmsg.column_name = [\"column2\"];\nmsg.column_sort = [\"column1\",\"column3\"];\nmsg.data = [7,25];\nmsg.database = \"name of the database\";\nmsg.command = \"select\";\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"x": 420,
"y": 120,
"wires": [
[
"1b088f24.18cdf1"
]
]
},
{
"id": "aac32ae.b2278d8",
"type": "function",
"z": "af9b6025.dd026",
"name": "INSERT into Database",
"func": "\nmsg.column_name = [\"column1\",\"column2\",\"column3\"];\nmsg.column_sort = [5,\"Text\",8];\nmsg.database = \"name of the database\";\nmsg.command = \"insert\";\n\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"x": 340,
"y": 40,
"wires": [
[
"1b088f24.18cdf1"
]
]
},
{
"id": "82ac7d.3c20b38",
"type": "function",
"z": "af9b6025.dd026",
"name": "SELECT hole row (*) from Database",
"func": "msg.column_name = [];\nmsg.column_sort = [\"column1\"];\nmsg.data = [7];\nmsg.database = \"name of the database\";\nmsg.command = \"select\";\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"x": 390,
"y": 80,
"wires": [
[
"1b088f24.18cdf1"
]
]
},
{
"id": "9507a308.8df4f",
"type": "function",
"z": "af9b6025.dd026",
"name": "UPDATE one value in the existing records in a table",
"func": "msg.column_name = [\"column3\"];\nmsg.column_data = [5];\nmsg.column_sort = [\"column1\",\"column2\"];\nmsg.data = [6,\"Text2\"];\nmsg.database = \"name of the database\";\nmsg.command = \"update\";\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"x": 430,
"y": 160,
"wires": [
[
"1b088f24.18cdf1"
]
]
},
{
"id": "86969f13.780e6",
"type": "function",
"z": "af9b6025.dd026",
"name": "DELETE existing records in a table",
"func": "msg.column_name = [];\nmsg.column_sort = [\"column1\",\"column2\",\"column3\"];\nmsg.data = [6,\"Text2\",125];\nmsg.database = \"test\";\nmsg.command = \"delete\";\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"x": 380,
"y": 200,
"wires": [
[
"1b088f24.18cdf1"
]
]
},
{
"id": "1076d0e5.c24f0f",
"type": "inject",
"z": "af9b6025.dd026",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 120,
"y": 40,
"wires": [
[
"aac32ae.b2278d8"
]
]
},
{
"id": "7f1ee4ec.ca6c0c",
"type": "inject",
"z": "af9b6025.dd026",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 120,
"y": 80,
"wires": [
[
"82ac7d.3c20b38"
]
]
},
{
"id": "5d735064.d9343",
"type": "inject",
"z": "af9b6025.dd026",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 120,
"y": 120,
"wires": [
[
"5be245ef.14d62c"
]
]
},
{
"id": "9379b362.94b3e",
"type": "inject",
"z": "af9b6025.dd026",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 120,
"y": 160,
"wires": [
[
"9507a308.8df4f"
]
]
},
{
"id": "a12a68cc.c91388",
"type": "inject",
"z": "af9b6025.dd026",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 120,
"y": 200,
"wires": [
[
"86969f13.780e6"
]
]
},
{
"id": "524d4664.0f5b58",
"type": "debug",
"z": "af9b6025.dd026",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "topic",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 1050,
"y": 140,
"wires": []
}]`