Using Microsoft SQL Server Database with Node-Red
This Example shows how to use node-red-contrib-mssql to SELECT, UPDATE, and INSERT data into Microsoft SQL Server. Blog post and video walkthrough found at http://www.electronhacks.com/2017/04/using-microsoft-sql-server-with-node-red-and-mqtt/
[{"id":"15237651.20281a","type":"debug","z":"31ff4407.c5ea7c","name":"","active":true,"console":"false","complete":"payload","x":855,"y":159,"wires":[]},{"id":"84df0cd4.58462","type":"inject","z":"31ff4407.c5ea7c","name":"Select","topic":"","payload":"SELECT TOP (1) [Topic] ,[Payload] FROM [Dev].[dbo].[MQTTData]","payloadType":"str","repeat":"","crontab":"","once":false,"x":256.00000381469727,"y":92.99999809265137,"wires":[["eac3e9b1.b2f0e8"]]},{"id":"eac3e9b1.b2f0e8","type":"MSSQL","z":"31ff4407.c5ea7c","mssqlCN":"df8c0b88.91b0a8","name":"MSSQL","query":"","outField":"payload","x":624,"y":160,"wires":[["15237651.20281a"]]},{"id":"ca03c5dc.709628","type":"inject","z":"31ff4407.c5ea7c","name":"Insert","topic":"","payload":"INSERT INTO [Dev].[dbo].[MQTTData] (Topic, Payload) VALUES ('Cardinal', 'Tom B. Erichsen' )","payloadType":"str","repeat":"","crontab":"","once":false,"x":258,"y":144,"wires":[["eac3e9b1.b2f0e8"]]},{"id":"57a3215a.12b8d","type":"inject","z":"31ff4407.c5ea7c","name":"Update","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":255,"y":195,"wires":[["fc1afd38.1ad0f"]]},{"id":"fc1afd38.1ad0f","type":"function","z":"31ff4407.c5ea7c","name":"Function","func":"d = new Date,\ndformat = [d.getMonth()+1,\n d.getDate(),\n d.getFullYear()].join('/')+' '+\n [d.getHours(),\n d.getMinutes(),\n d.getSeconds()].join(':');\n\ndtstmp = new Date().toString();\npld = \"UPDATE [Dev].[dbo].[MQTTData] \"\npld = pld + \"Set Timestamp = '\" + dformat + \"' \"\npld = pld + \"WHERE id = 1\"\n\nmsg.payload = pld\nreturn msg;\n\n\n\n\n","outputs":1,"noerr":0,"x":395,"y":195,"wires":[["eac3e9b1.b2f0e8"]]},{"id":"35b4a3e.f64c05c","type":"inject","z":"31ff4407.c5ea7c","name":"Select","topic":"","payload":"2","payloadType":"num","repeat":"","crontab":"","once":false,"x":256,"y":251,"wires":[["1528d6ba.7db449"]]},{"id":"1528d6ba.7db449","type":"function","z":"31ff4407.c5ea7c","name":"Function","func":"pld = \"SELECT ID, Topic, Payload, Timestamp \"\npld = pld + \"FROM [Dev].[dbo].[MQTTData] \"\npld = pld + \"WHERE id = \" + msg.payload\n\nmsg.payload = pld\nreturn msg;\n\n\n\n\n","outputs":1,"noerr":0,"x":396,"y":251,"wires":[["eac3e9b1.b2f0e8"]]},{"id":"38bb7577.af1c3a","type":"mqtt in","z":"31ff4407.c5ea7c","name":"","topic":"SQLTest/#","qos":"0","broker":"712b53e5.990dfc","x":247,"y":301,"wires":[["7eafa2f8.041ebc"]]},{"id":"7eafa2f8.041ebc","type":"function","z":"31ff4407.c5ea7c","name":"Function","func":"d = new Date(),\ndformat = [d.getMonth()+1,\n d.getDate(),\n d.getFullYear()].join('/')+' '+\n [d.getHours(),\n d.getMinutes(),\n d.getSeconds()].join(':');\n\npld = \"INSERT INTO [Dev].[dbo].[MQTTData] \"\npld = pld + \"(Topic, Payload, Timestamp) \"\npld = pld + \"VALUES ('\" + msg.topic + \"', '\" + msg.payload + \"', '\" + dformat + \"')\"\n\nmsg.topic = ''\nmsg.payload = pld\nreturn msg;\n\n\n\n\n","outputs":1,"noerr":0,"x":389,"y":301,"wires":[["eac3e9b1.b2f0e8","15237651.20281a"]]},{"id":"df8c0b88.91b0a8","type":"MSSQL-CN","z":"","name":"Dev","server":"localhost","encyption":false,"database":"Dev"},{"id":"712b53e5.990dfc","type":"mqtt-broker","z":"","broker":"localhost","port":"1883","clientid":"NodeRedSQLClient","usetls":false,"compatmode":true,"keepalive":"15","cleansession":true,"willTopic":"","willQos":"0","willPayload":"","birthTopic":"","birthQos":"0","birthPayload":""}]
