MSSQL Flow Sample
MSSQL sample flow. Contains a template node and a mysql node.
[{"id":"8eaf13c3.e28e5","type":"MSSQL","z":"1f61380c.f5fe88","mssqlCN":"8589b19c.e1096","name":"MSSQL","outField":"payload","returnType":0,"throwErrors":1,"query":"SELECT TOP 5 * \nFROM test.dbo.[MyTable]","modeOpt":"","modeOptType":"query","queryOpt":"topic","queryOptType":"msg","paramsOpt":"","paramsOptType":"none","params":[],"x":440,"y":300,"wires":[["89346aeb.071388"]]},{"id":"72d324f5.3465dc","type":"inject","z":"1f61380c.f5fe88","name":"COUNT","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":110,"y":300,"wires":[["cf2ea311.6d7ac"]]},{"id":"89346aeb.071388","type":"debug","z":"1f61380c.f5fe88","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload[0]","targetType":"msg","statusVal":"","statusType":"auto","x":620,"y":300,"wires":[]},{"id":"f1aa7214.a8d94","type":"MSSQL","z":"1f61380c.f5fe88","mssqlCN":"8589b19c.e1096","name":"MSSQL","outField":"payload","returnType":0,"throwErrors":1,"query":"INSERT INTO \"test\".\"dbo\".\"MyTable\" (\"Age\", \"Name\") VALUES ('55', 'KKK');","modeOpt":"","modeOptType":"query","queryOpt":"topic","queryOptType":"msg","paramsOpt":"","paramsOptType":"none","params":[],"x":440,"y":360,"wires":[["921d5b82.09b968","3d6345e2.a873fa"]]},{"id":"a37aebb8.262388","type":"inject","z":"1f61380c.f5fe88","name":"SELECT","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"1","crontab":"","once":true,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":360,"wires":[["4ec12ca9.3558d4"]]},{"id":"921d5b82.09b968","type":"debug","z":"1f61380c.f5fe88","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":610,"y":360,"wires":[]},{"id":"d5ad8fc9.bea14","type":"comment","z":"1f61380c.f5fe88","name":"MSSQL Sample","info":"\nhttps://docs.microsoft.com/ko-kr/sql","x":120,"y":60,"wires":[]},{"id":"cf2ea311.6d7ac","type":"function","z":"1f61380c.f5fe88","name":"COUNT","func":"msg.topic = \"SELECT COUNT(*) FROM test.dbo.[access]\";\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":280,"y":300,"wires":[["8eaf13c3.e28e5"]]},{"id":"4ec12ca9.3558d4","type":"function","z":"1f61380c.f5fe88","name":"SELECT","func":"msg = {\n\"topic\" : \"SELECT TOP 5 * FROM test.dbo.[access] ORDER BY no DESC;\"\n};\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":280,"y":360,"wires":[["f1aa7214.a8d94"]]},{"id":"8350e405.fb05c8","type":"ui_template","z":"1f61380c.f5fe88","group":"e938150d.e55d68","name":"","order":1,"width":"6","height":"4","format":"\n \n<table style=\"width:100%\">\n <tr>\n <th>Index</th> \n <th>Time</th>\n <th>Room</th> \n <th>ID</th>\n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:10\">\n <td><center>{{$index}}</center></td>\n <td><center>{{msg.payload[$index].date}}</center></td>\n <td><center>{{msg.payload[$index].room}}</center></td> \n <td><center>{{msg.payload[$index].id}}</center></td>\n </tr>\n</table>\n","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","x":620,"y":420,"wires":[[]]},{"id":"3d6345e2.a873fa","type":"function","z":"1f61380c.f5fe88","name":"Date to TS","func":"for(var i = 0; i < msg.payload.length; i++){\n// 9H = 32,400,000\n var dd = new Date(parseInt((new Date(msg.payload[i].date).getTime()).toFixed(0)) - 32400000);\n msg.payload[i].date = dd.toLocaleTimeString('it-IT');\n}\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":450,"y":420,"wires":[["8350e405.fb05c8"]]},{"id":"a0f06a3d.e63928","type":"inject","z":"1f61380c.f5fe88","name":"INSERT","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":120,"wires":[["a8a09c6a.bf5a"]]},{"id":"a8a09c6a.bf5a","type":"function","z":"1f61380c.f5fe88","name":"INSERT","func":"\nif (Math.random() < 0.5) { room = 503;}else{ room = 602; }\nif (Math.random() < 0.5) { id = 9909;}else{ id = 1893; }\n\ntopic = \"INSERT INTO test.dbo.[access] (room, id) VALUES ('\";\ntopic = topic + room +\"', '\";\ntopic = topic + id + \"');\";\n\nmsg.topic = topic;\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":280,"y":120,"wires":[["6ae1e60.fdda818"]]},{"id":"9e0cdd2d.1a521","type":"debug","z":"1f61380c.f5fe88","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":610,"y":120,"wires":[]},{"id":"6ae1e60.fdda818","type":"MSSQL","z":"1f61380c.f5fe88","mssqlCN":"8589b19c.e1096","name":"MSSQL","outField":"payload","returnType":0,"throwErrors":1,"query":"SELECT TOP 5 * \nFROM test.dbo.[MyTable]","modeOpt":"","modeOptType":"query","queryOpt":"topic","queryOptType":"msg","paramsOpt":"","paramsOptType":"none","rows":"","rowsType":"msg","params":[{"output":false,"name":"param1","type":"int","valueType":"num","value":"0","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"param2","type":"int","valueType":"num","value":"0","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":440,"y":120,"wires":[["9e0cdd2d.1a521"]]},{"id":"edaf20ea.2db0c","type":"inject","z":"1f61380c.f5fe88","name":"UPDATE","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":180,"wires":[["2f7b0cab.37b864"]]},{"id":"97e788fa.35a008","type":"debug","z":"1f61380c.f5fe88","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":610,"y":180,"wires":[]},{"id":"2f7b0cab.37b864","type":"function","z":"1f61380c.f5fe88","name":"UPDATE","func":"topic = \"UPDATE test.dbo.[access] SET room='602' \";\ntopic = topic + \"where no in (select top 1 no from access \";\ntopic = topic + \"ORDER BY no DESC);\";\n\nmsg.topic = topic;\n\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"msg = {\n\"topic\" : \"INSERT INTO test.dbo.[access] (room, id) VALUES ('503', '1893');\"\n};\n\nreturn msg;\n","finalize":"","x":280,"y":180,"wires":[["82366675.d34658"]]},{"id":"82366675.d34658","type":"MSSQL","z":"1f61380c.f5fe88","mssqlCN":"8589b19c.e1096","name":"MSSQL","outField":"payload","returnType":0,"throwErrors":1,"query":"SELECT TOP 5 * \nFROM test.dbo.[MyTable]","modeOpt":"","modeOptType":"query","queryOpt":"topic","queryOptType":"msg","paramsOpt":"","paramsOptType":"none","params":[],"x":440,"y":180,"wires":[["97e788fa.35a008"]]},{"id":"2f100105.8b6aae","type":"inject","z":"1f61380c.f5fe88","name":"DELETE","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":240,"wires":[["1de99585.137e5a"]]},{"id":"f0e148bb.9e1158","type":"debug","z":"1f61380c.f5fe88","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":610,"y":240,"wires":[]},{"id":"1de99585.137e5a","type":"function","z":"1f61380c.f5fe88","name":"DELETE","func":"topic = \"DELETE test.dbo.[access] \";\ntopic = topic + \"where no in (select top 1 no from access ORDER BY no DESC);\";\nmsg.topic = topic;\n\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"msg = {\n\"topic\" : \"INSERT INTO test.dbo.[access] (room, id) VALUES ('503', '1893');\"\n};\n\nreturn msg;\n","finalize":"","x":280,"y":240,"wires":[["af8fbc85.5e9eb"]]},{"id":"af8fbc85.5e9eb","type":"MSSQL","z":"1f61380c.f5fe88","mssqlCN":"8589b19c.e1096","name":"MSSQL","outField":"payload","returnType":0,"throwErrors":1,"query":"SELECT TOP 5 * \nFROM test.dbo.[MyTable]","modeOpt":"","modeOptType":"query","queryOpt":"topic","queryOptType":"msg","paramsOpt":"","paramsOptType":"none","params":[],"x":440,"y":240,"wires":[["f0e148bb.9e1158"]]},{"id":"8589b19c.e1096","type":"MSSQL-CN","z":"","tdsVersion":"7_4","name":"MSSQL","server":"127.0.0.1","port":"1433","encyption":true,"trustServerCertificate":true,"database":"test","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false,"enableArithAbort":true},{"id":"e938150d.e55d68","type":"ui_group","name":"[ Access Data of Class Room ]","tab":"862fafd5.b64a9","order":1,"disp":true,"width":"6","collapse":false},{"id":"862fafd5.b64a9","type":"ui_tab","name":"Main Tab","icon":"dashboard","order":1}]
