Node-red-contrib-sql-prepare NPM

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-prepare

License

MIT License

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": []
}

node-red sql mysql sqlite

@everything-registry/sub-chunk-2314 @infinitebrahmanuniverse/nolb-node-red-contrib-s

3 years ago

3 years ago

3 years ago

3 years ago

3 years ago