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/
-
-
Save jhottell/393641f5cdd1d3a6c59bd232dfcee08e to your computer and use it in GitHub Desktop.
Using Microsoft SQL Server Database with Node-Red
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
[{"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":""}] |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I am trying to get this to work. If I use encryption, I get nothing. If I dont, node-red crashes every time I press the "Select" Injector.
Tested my select string on the sql server (no probs). Server credentials are OK. Not sure about the domain though. Is it required?
SQL server version is 8.0.760.
Any ideas that might help me?