Using Microsoft SQL Server With Node-Red and MQTT

in a previous video I showed how to set up Microsoft sequel Server 2016 and configure it and create a test table and database in this table we put some test data in there just to show that it was working so now we're going to use node-red to push into tt data into the database table so to get started you're going to want to go ahead and stop node read and then you're going to do NPM install node read contribute off sequel mssql go ahead and hit enter and it will install the sequel node so I've already done that and here it is Microsoft sequel so you can go ahead and drag this guy in and let's go ahead and get started a little quicker than this let's go ahead and copy my node read flow so you can go to flows node red org and type in Microsoft sequel here's my example flow we'll just go ahead and copy all this control fee come back over here we're going to do import from clipboard there it is okay go ahead and edit the Microsoft sequel node you're going to want to set up a connection you're going to need to do a new connection give it a name since sequel server is on the same machine I just typed in localhost and then I gave it the username and password and then the database we're going to which is dev as you can see this is the database name okay we're going to update and don't put the query here because it will override any other query we want to send the query using our other nodes in the flow here so for the very basic example I basically dragged and dropped and inject onto the screen change the payload to be a string and you can put in your sequel query so we're going to select the first topic and payload from the MQTT data database let's go ahead and make sure this is deployed all right now let's go ahead and click the select button and it returned a payload and put it straight to the debug window this object is an array which you could parse and use in node-red somehow and you can see that the top record was test one two three four five okay let's go on to the next example here once again this is very simple we're just putting the entire statement in the payload we're going to insert the topic and payload we're going to the topic will be cardinal and the payload will be tombi direction so let's to insert let's go ahead and go back here and refresh this oops we want to select star from okay so you see that our record was inserted here very good alright now let's go to the next one so this update is just sending a timestamp you could really send it anything the the it's just going to trigger this function so in the function we're doing something a little more fancy the time stamp from node-red is not recognized very well from sequel server so we're getting the date and we're parsing it and putting it into the proper format for sequel server so then we're going to update and then we're going to set the timestamp to this new timestamp where the ID is 1 so it's going to just update this first record where the ID is 1 so let's go ahead and hit update and then when we refresh this guy we see there's a nice timestamp into this first record okay moving on to the next example the payload is to in the function we go ahead and get that message dot payload and then we are going to select the ID topic payload then time stamp where the ID is - so let's go ahead and click that so that shows how you can customize how that select statement works and then our last example is where we actually drag an imp utt input on to the flow let's go ahead and see what's going on here so you can see I'm connected to my local host m2t t broker on this machine this is the topic I'm subscribing to everything that has sequel test forward slash and then the wild card says anything after that so as long as it has sequel test it's going to come in here this function once again we're making a nice big time stamp and then in our insert tape statement we're getting the topic from MQTT the message payload from MQTT and then our good date/time stamp that we made up here and we're going to pass it on so in order to show this I'm going to go ahead and bring over a an mqtt test client MQTT spy is what I'm using so let's go ahead and connect so I am connecting to my local host and let's just do this public publish message here so topic I'd see I already forgot what did it need to be sequel test something so we're going to do sequel test let's do let's pretend we're putting weather data in here weather / temperature and my data is it is 82 degrees outside point four so when I publish you'll notice something happen here it's pretty quick and then we go refresh over here you can see the topic which is the full topic the payload and then the timestamp was inserted into the database let's go ahead and do a couple more weather station items so there's wind wind speed is five miles an hour and humidity is really bad it's going to be 98 percent voila we've got all our data into Microsoft sequel server so this is really really quite awesome how this works like I said before Microsoft sequel server is free certainly for small or home installations if you are a business user and you need multiple CPUs and massive databases of course you're going to want to pay for it just like you would for my sequel now how you consume this data of course you can consume it in node-red but you could also use you know sequel server reporting services or power bi look for some videos on that coming in the future thanks and have

Channel: electronhacks

Views: 50,659

Keywords: Microsoft, SQL, MSSQL, Server, Node-Red, MQTT

Id: nmeUSucRZrc

Length: 9min 52sec (592 seconds)

Published: Fri Apr 28 2017