Dashboard-2-sqlserverDatabase-crud

Hi Guys im trying to replicate the crud of sqlite https://flows.nodered.org/flow/ca8b64f1465f7246781315fdff6234c4 but with sql server db, this is the payload that i receive in the log,

,
and this is my flow
flows.json (27.2 KB)

i have problems, with the template,

Tagging @Cyprien here as he built the sqlite version

Hi Joe, im not good in vuejs, i have the same payload like in sqlite, but in the template, i think that have the problem,

I will assist at a later time (maybe tomorrow or friday). At the moment, I am busy with the day job I'm afraid.

Please share your latest flow.

PS, please remember what I said in DM and use the latest version of @Cyprien flow as a base for this - it has the sqlstring stuff in the functions to guard against SQL injection hacks.

flows (1).json (22.0 KB), hi Steve this is my last flow, i can see the table, and i can insert in the db, but i have the problem, when refresh the page, the table dissapears, and i need to make a change in the flow and full deplow for select the table, and then appears the table.

As promised, here is a demo of @Cyprien CRUD for SQLite re-written for MSSQL

chrome_nC6RBamLKT

Demo Flow

[{"id":"aabcc1826b8526ab","type":"subflow","name":"MSSQL-CRUD","info":"Provides CRUD operations for a SQLITE database\r\n\r\n### Dependancies\r\n* node-red-node-sqlite\r\n* @flowfuse/node-red-dashboard\r\n\r\n### Inputs\r\n\r\n: *tableName* (string)  :  (optional) Override the set table name.\r\n\r\n### Details\r\n\r\nSimple place a CRUD node on your flow, select a database and a page group\r\nand enter the name of the table.\r\n\r\nAt runtime, the selected table can be adjusted bt sending a message\r\nwith a different table name e.g:\r\n\r\n```\r\nmsg.tableName = 'table2'\r\n```\r\n","category":"dashboard 2","in":[{"x":60,"y":60,"wires":[{"id":"74c62b39145e7768"}]}],"out":[{"x":1020,"y":540,"wires":[{"id":"e26ebb252ef3653b","port":0}]},{"x":680,"y":700,"wires":[{"id":"f2dbce3b36de49c8","port":0}]}],"env":[{"name":"group","type":"ui-group","value":"","ui":{"icon":"font-awesome/fa-object-group","label":{"fr":"Group","en-US":"UI Group"},"type":"conf-types"}},{"name":"database","type":"MSSQL-CN","value":"","ui":{"icon":"font-awesome/fa-database","label":{"fr":"sqlite DB","en-US":"Database"},"type":"conf-types"}},{"name":"tableName","type":"str","value":"table1","ui":{"icon":"font-awesome/fa-table","label":{"fr":"Table Name","en-US":"Table"},"type":"input","opts":{"types":["str","env"]}}}],"meta":{"module":"dasboard-2-sqlite-crud","type":"dasboard-2-sqlite-crud","version":"0.2","author":"Cyprien","desc":"A Subflow for CRUD operations on SQL tables","keywords":"sqlite dashboard-2.0","license":"MIT"},"color":"#3FADB5","inputLabels":["Table name"],"icon":"font-awesome/fa-database"},{"id":"36c9f76a7f3ae6b5","type":"ui-template","z":"aabcc1826b8526ab","group":"${group}","page":"","ui":"","name":"MSSQL Table","order":0,"width":0,"height":0,"head":"","format":"<template>\n    <v-card flat>\n    <template v-slot:text>\n        <v-text-field \n            v-model=\"search\" \n            label=\"Search\" \n            prepend-inner-icon=\"mdi-magnify\" \n            variant=\"outlined\" \n            hide-details\n            single-line>\n        </v-text-field>\n    </template>\n    <v-data-table :search=\"search\" :headers=\"headers\" :items=\"items\" :sort-by=\"[{ key: sortBy, order: 'asc' }]\">\n        <template v-slot:top>\n            <v-toolbar flat>\n                <v-toolbar-title>{{ tableName || 'MSSQL CRUD' }}</v-toolbar-title>\n                <v-divider class=\"mx-4\" inset vertical></v-divider>\n                <v-spacer></v-spacer>\n                <v-dialog v-model=\"dialog\" max-width=\"500px\">\n                    <template v-slot:activator=\"{ props }\">\n                        <v-btn class=\"mb-2\" color=\"primary\" dark v-bind=\"props\">\n                            New Item\n                        </v-btn>\n                    </template>\n                    <v-card>\n                        <v-card-title>\n                            <span class=\"text-h5\">{{ formTitle }}</span>\n                        </v-card-title>\n\n                        <v-card-text>\n                            <v-container>\n                                <v-row>\n                                    <v-col v-for=\"header in editableHeaders\" :key=\"header.key\" cols=\"12\" md=\"4\" sm=\"6\">\n                                        <v-text-field v-model=\"editedItem[header.key]\" :label=\"header.title\">\n                                        </v-text-field>\n                                    </v-col>\n                                </v-row>\n                            </v-container>\n                        </v-card-text>\n\n                        <v-card-actions>\n                            <v-spacer></v-spacer>\n                            <v-btn color=\"blue-darken-1\" variant=\"text\" @click=\"close\">\n                                Cancel\n                            </v-btn>\n                            <v-btn color=\"blue-darken-1\" variant=\"text\" @click=\"save\">\n                                Save\n                            </v-btn>\n                        </v-card-actions>\n                    </v-card>\n                </v-dialog>\n                <v-dialog v-model=\"dialogDelete\" max-width=\"500px\">\n                    <v-card>\n                        <v-card-title class=\"text-h5\">Are you sure you want to delete this item?</v-card-title>\n                        <v-card-actions>\n                            <v-spacer></v-spacer>\n                            <v-btn color=\"blue-darken-1\" variant=\"text\" @click=\"closeDelete\">Cancel</v-btn>\n                            <v-btn color=\"blue-darken-1\" variant=\"text\" @click=\"deleteItemConfirm\">OK</v-btn>\n                            <v-spacer></v-spacer>\n                        </v-card-actions>\n                    </v-card>\n                </v-dialog>\n            </v-toolbar>\n        </template>\n        <template v-slot:item.actions=\"{ item }\">\n            <v-icon class=\"me-2\" size=\"small\" @click=\"editItem(item)\">\n                mdi-pencil\n            </v-icon>\n            <v-icon size=\"small\" @click=\"deleteItem(item)\">\n                mdi-delete\n            </v-icon>\n        </template>\n        <template v-slot:no-data>\n            <v-btn color=\"primary\" @click=\"initialize\">\n                Reset\n            </v-btn>\n        </template>\n    </v-data-table>\n    </v-card>\n</template>\n\n<script>\n    export default {\n    data() {\n        return {\n            search: '',\n            dialog: false,\n            dialogDelete: false,\n            headers: [],\n            editableHeaders: [],\n            items: [],\n            editedIndex: -1,\n            editedItem: {},\n            defaultItem: {},\n            sortBy: '',\n            pkColumn: null,\n            tableName: '',\n            tableInfo: [],\n            initialItems: []\n        }\n    },\n    computed: {\n        formTitle() {\n            return this.editedIndex === -1 ? 'New Item' : 'Edit Item'\n        },\n    },\n    watch: {\n        dialog(val) {\n            val || this.close()\n        },\n        dialogDelete(val) {\n            val || this.closeDelete()\n        },\n        // Is it better to use $socket ?\n        msg: function () {\n            console.log(this.msg.payload)\n            this.initialize()\n        },\n    },\n    created() {\n        this.initialize()\n    },\n    methods: {\n        initialize() {\n            this.tableName = this.msg.tableName\n            this.tableInfo = this.msg.payload.pragma\n            this.items = this.msg.payload.select\n            this.pkColumn = this.tableInfo.find(column => column.pk === 1)\n            \n            this.headers = this.tableInfo.map(column => ({\n                title: column.name,\n                key: column.name,\n                sortable: true,\n                isPk: column.pk === 1\n            }))\n            this.headers.push({ title: 'Actions', key: 'actions', sortable: false })\n\n            this.editableHeaders = this.headers.filter(header => !header.isPk && header.key !== 'actions')\n\n            this.defaultItem = this.tableInfo.reduce((acc, column) => {\n                if (!column.pk) {\n                    acc[column.name] = null\n                }\n                return acc\n            }, {})\n\n            this.editedItem = { ...this.defaultItem }\n            //this.items = [...this.initialItems]\n\n            // Set the default sort column (first non-PK column)\n            this.sortBy = this.headers.find(header => !header.isPk && header.key !== 'actions')?.key || ''\n        },\n        editItem(item) {\n            this.editedIndex = this.items.indexOf(item)\n            this.editedItem = { ...this.defaultItem }\n            for (let key in this.defaultItem) {\n                this.editedItem[key] = item[key]\n            }\n            this.dialog = true\n        },\n        deleteItem(item) {\n            this.editedIndex = this.items.indexOf(item)\n            this.editedItem = { ...item }\n            this.dialogDelete = true\n        },\n        deleteItemConfirm() {\n            console.log(this.editedItem)\n            const payload = {\n                topic: \"DELETE\",\n                tableName: this.tableName,\n                payload: {\n                    id: this.editedItem.id\n                }\n            };\n            this.send(payload)\n            this.items.splice(this.editedIndex, 1)\n\n            this.closeDelete()\n        },\n        close() {\n            this.dialog = false\n            this.$nextTick(() => {\n                this.editedItem = { ...this.defaultItem }\n                this.editedIndex = -1\n            })\n        },\n        closeDelete() {\n            this.dialogDelete = false\n            this.$nextTick(() => {\n                this.editedItem = { ...this.defaultItem }\n                this.editedIndex = -1\n            })\n        },\n        // mounted() {\n        //     this.$socket.on('msg-input:' + this.id, function(msg) {\n        //         // do stuff with the message\n        //         console.log('message received: ' + msg.payload)\n        //     })\n        // },\n        save() {\n            if (this.editedIndex > -1) {\n                // Update existing item\n                const updatedItem = { ...this.items[this.editedIndex] }\n                for (let key in this.editedItem) {\n                    updatedItem[key] = this.editedItem[key]\n                }\n                Object.assign(this.items[this.editedIndex], updatedItem)\n                const payload = {\n                    topic: \"UDPATE\",\n                    tableName: this.tableName,\n                    payload: {\n                        item: updatedItem\n                    }\n                };\n                this.send(payload)\n            } else {\n                // Create new item\n                const newItem = { ...this.editedItem }\n                if (this.pkColumn) {\n                    // Generate a temporary ID for the new item\n                    // In a real application, this would be handled by the backend\n                    newItem[this.pkColumn.name] = Math.max(0, ...this.items.map(item => item[this.pkColumn.name])) + 1\n                }\n                this.items.push(newItem)\n                const payload = {\n                    topic: \"INSERT\",\n                    tableName: this.tableName,\n                    payload: {\n                        item: newItem\n                    }\n                };\n                this.send(payload)\n            }\n            this.close()\n        },\n    },\n}\n</script>","storeOutMessages":true,"passthru":true,"resendOnRefresh":true,"templateScope":"local","className":"","x":300,"y":540,"wires":[["9cb600dc1c760da7"]]},{"id":"9cb600dc1c760da7","type":"switch","z":"aabcc1826b8526ab","name":"","property":"topic","propertyType":"msg","rules":[{"t":"eq","v":"DELETE","vt":"str"},{"t":"eq","v":"UDPATE","vt":"str"},{"t":"eq","v":"INSERT","vt":"str"}],"checkall":"true","repair":false,"outputs":3,"x":490,"y":540,"wires":[["a6f57055859dc572"],["08e04b7725b3dcc2"],["6ea3c00df1a78442"]]},{"id":"6ea3c00df1a78442","type":"function","z":"aabcc1826b8526ab","name":"INSERT","func":"node.warn({ payload: msg.payload });\nconst data = RED.util.cloneMessage(msg.payload.item);\nconst tableName = msg.tableName\n\n// Extract the ID\nconst id = data.id\ndelete data.id\n\n// Build columns, data placeholders and values array\nconst entries = Object.entries(data)\nconst columns = []\nconst values = []\nconst keys = Object.keys(data)\nfor (let index = 0; index < keys.length; index++) {\n    const key = keys[index]\n    columns.push('??') // sanitize\n    values.push(data[key])\n}\nconst columnsNames = tsqlstring.format(columns.join(', '),keys) \nconst placeHolders = values.map(() => '?').join(', ')\n\n// Construct query using tsqlstring.format to avoid SQLi\nmsg.query = tsqlstring.format(`INSERT INTO ?? (${columnsNames}) \\n VALUES (${placeHolders})`, [tableName, ...values, id])\nmsg.insertQuery = msg.query\nreturn msg\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[{"var":"tsqlstring","module":"tsqlstring"}],"x":660,"y":580,"wires":[["80b6e275b832aa32"]]},{"id":"a6f57055859dc572","type":"function","z":"aabcc1826b8526ab","name":"DELETE","func":"const tableName = msg.tableName\nmsg.query = tsqlstring.format(`DELETE FROM ?? WHERE id = ?`, [tableName, msg.payload.id])\nmsg.deleteQuery = msg.query\nreturn msg\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[{"var":"tsqlstring","module":"tsqlstring"}],"x":660,"y":500,"wires":[["80b6e275b832aa32"]]},{"id":"08e04b7725b3dcc2","type":"function","z":"aabcc1826b8526ab","name":"UPDATE","func":"const data = RED.util.cloneMessage(msg.payload.item)\nconst tableName = msg.tableName\n\n// Extract the ID\nconst id = data.id\ndelete data.id\n\n// Build columns, data placeholders and values array\nconst entries = Object.entries(data)\nconst sets = []\nconst values = []\nconst parts = []\nconst keys = Object.keys(data)\nfor (let index = 0; index < keys.length; index++) {\n    const key = keys[index]\n    parts.push(tsqlstring.format(`?? = ?`, [key, data[key]]))\n}\nconst setWhat = parts.join(', ')\n\n// Construct query\nmsg.query = tsqlstring.format(`UPDATE ?? SET ${setWhat} WHERE id = ?`, [tableName, ...values, id])\nmsg.updateQuery = msg.query\nreturn msg\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[{"var":"tsqlstring","module":"tsqlstring"}],"x":660,"y":540,"wires":[["80b6e275b832aa32"]]},{"id":"0bc1b70f3b5314e8","type":"link in","z":"aabcc1826b8526ab","name":"link in 1","links":["fe79ed82583fe781"],"x":385,"y":140,"wires":[["36fbf7094b2ada86"]]},{"id":"fe79ed82583fe781","type":"link out","z":"aabcc1826b8526ab","name":"link out 1","mode":"link","links":["0bc1b70f3b5314e8"],"x":1015,"y":500,"wires":[]},{"id":"74c62b39145e7768","type":"switch","z":"aabcc1826b8526ab","name":"has msg.tableName?","property":"tableName","propertyType":"msg","rules":[{"t":"nempty"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":220,"y":40,"wires":[["18d9e09a8d9ccf46"],["47e385f7c0fc866d"]]},{"id":"47e385f7c0fc866d","type":"change","z":"aabcc1826b8526ab","name":"use env tableName","rules":[{"t":"set","p":"tableName","pt":"msg","to":"tableName","tot":"env"}],"action":"","property":"","from":"","to":"","reg":false,"x":230,"y":100,"wires":[["18d9e09a8d9ccf46"]]},{"id":"36fbf7094b2ada86","type":"change","z":"aabcc1826b8526ab","name":"","rules":[{"t":"set","p":"tableName","pt":"msg","to":"tableName","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":530,"y":100,"wires":[["096ff2bbdaf4399d"]]},{"id":"18d9e09a8d9ccf46","type":"change","z":"aabcc1826b8526ab","name":"","rules":[{"t":"set","p":"tableName","pt":"flow","to":"tableName","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":530,"y":40,"wires":[["36fbf7094b2ada86"]]},{"id":"096ff2bbdaf4399d","type":"MSSQL","z":"aabcc1826b8526ab","mssqlCN":"4e85bde58681c32a","name":"get identities","outField":"identities","returnType":0,"throwErrors":1,"query":"SELECT \r\n    sys.objects.name AS TABLE_NAME, \r\n    sys.columns.name AS COLUMN_NAME\r\nFROM sys.columns JOIN sys.objects \r\n    ON sys.columns.object_id=sys.objects.object_id\r\nWHERE \r\n    sys.columns.is_identity=1\r\n    AND\r\n    sys.objects.type in (N'U')","modeOpt":"queryMode","modeOptType":"query","queryOpt":"payload","queryOptType":"editor","paramsOpt":"queryParams","paramsOptType":"none","rows":"rows","rowsType":"msg","parseMustache":true,"params":[],"x":190,"y":260,"wires":[["3b13c88ffc3bb01e"]]},{"id":"3b13c88ffc3bb01e","type":"MSSQL","z":"aabcc1826b8526ab","mssqlCN":"4e85bde58681c32a","name":"get primaries","outField":"primaries","returnType":0,"throwErrors":1,"query":"SELECT  i.name AS INDEX_NAME,\r\n        OBJECT_NAME(ic.OBJECT_ID) AS TABLE_NAME,\r\n        COL_NAME(ic.OBJECT_ID,ic.column_id) AS COLUMN_NAME\r\nFROM    sys.indexes AS i INNER JOIN \r\n        sys.index_columns AS ic ON  i.OBJECT_ID = ic.OBJECT_ID\r\n                                AND i.index_id = ic.index_id\r\nWHERE   i.is_primary_key = 1","modeOpt":"queryMode","modeOptType":"query","queryOpt":"payload","queryOptType":"editor","paramsOpt":"queryParams","paramsOptType":"none","rows":"rows","rowsType":"msg","parseMustache":true,"params":[],"x":390,"y":260,"wires":[["6d0bb115eafaf441"]]},{"id":"6d0bb115eafaf441","type":"function","z":"aabcc1826b8526ab","name":"prepare columns sql","func":"let sql = `\nSELECT\n  ORDINAL_POSITION as cid,\n  COLUMN_NAME as [name], \n  DATA_TYPE AS type,\n  case when IS_NULLABLE = 'YES' then 0 else 1 end AS notnull,\n  COLUMN_DEFAULT as dflt_value,\n  *\n  FROM INFORMATION_SCHEMA.COLUMNS\n  WHERE TABLE_NAME = ?\n`\n\nmsg.columnSelect = tsqlstring.format(sql, [msg.tableName])\nmsg.action = 'pragma';\nreturn msg;\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[{"var":"tsqlstring","module":"tsqlstring"}],"x":600,"y":260,"wires":[["7dec8d6bb13118bd"]]},{"id":"7dec8d6bb13118bd","type":"MSSQL","z":"aabcc1826b8526ab","mssqlCN":"4e85bde58681c32a","name":"get columns","outField":"columns","returnType":0,"throwErrors":1,"query":"SELECT  i.name AS INDEX_NAME,\r\n        OBJECT_NAME(ic.OBJECT_ID) AS TABLE_NAME,\r\n        COL_NAME(ic.OBJECT_ID,ic.column_id) AS COLUMN_NAME\r\nFROM    sys.indexes AS i INNER JOIN \r\n        sys.index_columns AS ic ON  i.OBJECT_ID = ic.OBJECT_ID\r\n                                AND i.index_id = ic.index_id\r\nWHERE   i.is_primary_key = 1","modeOpt":"queryMode","modeOptType":"query","queryOpt":"columnSelect","queryOptType":"msg","paramsOpt":"queryParams","paramsOptType":"none","rows":"rows","rowsType":"msg","parseMustache":false,"params":[],"x":810,"y":260,"wires":[["a0e99b62f29489d1"]]},{"id":"a0e99b62f29489d1","type":"function","z":"aabcc1826b8526ab","name":"update pk/identiry","func":"// loop each column, find matching entries in identities and primaries and update\nconst columns = msg.columns || []\nconst primaries = msg.primaries || []\nconst identities = msg.identities || []\n\nfor (let index = 0; index < columns.length; index++) {\n    const column = columns[index];\n    const primary = primaries.find(e => e.TABLE_NAME === msg.tableName && e.COLUMN_NAME === column.name)\n    const identity = identities.find(e => e.TABLE_NAME === msg.tableName && e.COLUMN_NAME === column.name)\n    if (primary) {\n        column.primary = 1\n    }\n    if (identity) {\n        column.identitiy = 1\n    }\n    if (column.primary && column.identitiy) {\n        column.pk = 1\n    }\n}\nmsg.payload = {\n    pragma: columns\n}\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":210,"y":400,"wires":[["16c8642501a5240c"]]},{"id":"16c8642501a5240c","type":"function","z":"aabcc1826b8526ab","name":"prepare SELECT","func":"msg.topic = tsqlstring.format('SELECT TOP 100 * FROM ??', [msg.tableName])\nmsg.action = 'select'\nreturn msg\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[{"var":"tsqlstring","module":"tsqlstring"}],"x":430,"y":400,"wires":[["f7bc781e87da9f8b"]]},{"id":"f7bc781e87da9f8b","type":"MSSQL","z":"aabcc1826b8526ab","mssqlCN":"4e85bde58681c32a","name":"get data","outField":"payload.select","returnType":0,"throwErrors":1,"query":"SELECT  i.name AS INDEX_NAME,\r\n        OBJECT_NAME(ic.OBJECT_ID) AS TABLE_NAME,\r\n        COL_NAME(ic.OBJECT_ID,ic.column_id) AS COLUMN_NAME\r\nFROM    sys.indexes AS i INNER JOIN \r\n        sys.index_columns AS ic ON  i.OBJECT_ID = ic.OBJECT_ID\r\n                                AND i.index_id = ic.index_id\r\nWHERE   i.is_primary_key = 1","modeOpt":"queryMode","modeOptType":"query","queryOpt":"topic","queryOptType":"msg","paramsOpt":"queryParams","paramsOptType":"none","rows":"rows","rowsType":"msg","parseMustache":false,"params":[],"x":620,"y":400,"wires":[["36c9f76a7f3ae6b5"]]},{"id":"e26ebb252ef3653b","type":"MSSQL","z":"aabcc1826b8526ab","mssqlCN":"4e85bde58681c32a","name":"query","outField":"payload.select","returnType":0,"throwErrors":1,"query":"SELECT  i.name AS INDEX_NAME,\r\n        OBJECT_NAME(ic.OBJECT_ID) AS TABLE_NAME,\r\n        COL_NAME(ic.OBJECT_ID,ic.column_id) AS COLUMN_NAME\r\nFROM    sys.indexes AS i INNER JOIN \r\n        sys.index_columns AS ic ON  i.OBJECT_ID = ic.OBJECT_ID\r\n                                AND i.index_id = ic.index_id\r\nWHERE   i.is_primary_key = 1","modeOpt":"queryMode","modeOptType":"query","queryOpt":"query","queryOptType":"msg","paramsOpt":"queryParams","paramsOptType":"none","rows":"rows","rowsType":"msg","parseMustache":false,"params":[],"x":850,"y":540,"wires":[["fe79ed82583fe781"]]},{"id":"f2dbce3b36de49c8","type":"catch","z":"aabcc1826b8526ab","name":"","scope":null,"uncaught":false,"x":550,"y":700,"wires":[[]]},{"id":"80b6e275b832aa32","type":"junction","z":"aabcc1826b8526ab","x":760,"y":540,"wires":[["e26ebb252ef3653b"]]},{"id":"4e85bde58681c32a","type":"MSSQL-CN","tdsVersion":"7_4","name":"","server":"172.29.225.148","port":"1433","encyption":true,"trustServerCertificate":true,"database":"db1","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false,"enableArithAbort":true,"readOnlyIntent":false},{"id":"2ff1080d58630657","type":"subflow:aabcc1826b8526ab","z":"953fa035e1275ec2","name":"","env":[{"name":"group","value":"d8d5ce268004b131","type":"conf-type"},{"name":"database","value":"4e85bde58681c32a","type":"conf-type"},{"name":"tableName","value":"Users","type":"str"},{"name":"sqlite","value":"","type":"str"}],"x":1080,"y":400,"wires":[[],["52fe8e3bc1fb9e87"]]},{"id":"1d859d277138d343","type":"ui-dropdown","z":"953fa035e1275ec2","group":"d8d5ce268004b131","name":"","label":"Select a table:","tooltip":"","order":3,"width":"12","height":"1","passthru":true,"multiple":false,"chips":false,"clearable":false,"options":[{"label":"PalletWeights","value":"PalletWeights","type":"str"},{"label":"Users","value":"Users","type":"str"}],"payload":"","topic":"topic","topicType":"msg","className":"","x":640,"y":400,"wires":[["40d1249d3861acd8","1e110b1f7eabd002"]]},{"id":"6a2258d443142ffe","type":"ui-control","z":"953fa035e1275ec2","name":"","ui":"22ea43815413e748","events":"connect","x":340,"y":480,"wires":[["1b895505162f7e45"]]},{"id":"1b895505162f7e45","type":"change","z":"953fa035e1275ec2","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"mssql_table","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":550,"y":480,"wires":[["1d859d277138d343"]]},{"id":"40d1249d3861acd8","type":"change","z":"953fa035e1275ec2","name":"","rules":[{"t":"set","p":"mssql_table","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":800,"y":480,"wires":[[]]},{"id":"1e110b1f7eabd002","type":"change","z":"953fa035e1275ec2","name":"","rules":[{"t":"set","p":"tableName","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":870,"y":400,"wires":[["2ff1080d58630657"]]},{"id":"52fe8e3bc1fb9e87","type":"debug","z":"953fa035e1275ec2","name":"error","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1160,"y":480,"wires":[]},{"id":"d8d5ce268004b131","type":"ui-group","name":"MSSQL Crud Demo","page":"1525cc4810388af6","width":"12","height":"1","order":-1,"showTitle":true,"className":"","visible":"true","disabled":"false"},{"id":"22ea43815413e748","type":"ui-base","name":"base","path":"/dashboard","includeClientData":true,"acceptsClientConfig":["ui-notification","ui-control"],"showPathInSidebar":false,"navigationStyle":"default","titleBarStyle":"default"},{"id":"1525cc4810388af6","type":"ui-page","name":"MSSQL Crud Demo","ui":"22ea43815413e748","path":"/mssql","icon":"home","layout":"grid","theme":"c2ff5ba1f92a0f0e","order":-1,"className":"","visible":"true","disabled":"false"},{"id":"c2ff5ba1f92a0f0e","type":"ui-theme","name":"Default","colors":{"surface":"#ffffff","primary":"#0094ce","bgPage":"#eeeeee","groupBg":"#ffffff","groupOutline":"#cccccc"},"sizes":{"pagePadding":"12px","groupGap":"12px","groupBorderRadius":"4px","widgetGap":"12px"}}]

NOTES:

  • I have made no attempt to extend upon what was achieved in the original taken from here
    • data types are not parsed
    • input fields are not mapped to proper controls (like time or date widgets)
  • Swapped out the sqlstring for the tsqlstring NPM module for MS T-SQL escaping and injection hack prevention