Node-RED SQL Database Log - Modbus Logging

[Music] hi everyone today we're going to be looking at node-red sql database log now detailed information contained in the video can be found at acc automation.ca the link has been put in description below if you have not watched the other videos yet there'll be links in the description below that will start your video one it will links the rest of the videos in the series as well so node-red can easily log data to a database sql sql or structured query language can be used to communicate to a sql database databases separates analysis from the data unlike spreadsheets like excel the database is designed to hold more information than spreadsheets and allow multiple users to access the information we will be creating a sql lite sql database and sql studio will be used to design the structure of the database namely tables and we'll be installing the node-red sql node palette which will allow us to log information from our solo process temperature controller click plc and dashboard variables so let's get started and up here on my screen what you'll see is our previous what we've done with our database and we're reading it the information and displaying it in our graphic user interface which is located right here [Music] so the first thing we will do is download and install sql lite and sql lite home page is right here and sql lite is actually a a command prompt information so what you can do is basically it will open up like a dos window and we can uh type in sql commands or sql commands in order to create the database create tables etc and what we do is we can download the latest release go down to windows here and what we can do is download this sql tools here and that will give us a bundle in order for us to then utilize sql lite on our computer and there's a good tutorial right here on sql lite and how to get started using that command prompt commands and sql commands now because we're just starting out and we're not sure exactly how the sql works we can actually use sql lite studio in sqlite studio will allow us to use a graphical user interface in order to create our database or structure in order for us to then learn about it so we download this file and when we download this file what ends up happening is it creates a an extension for us so let me just call that up there's our sql let me just go back to the root of that and what you'll see here is there's my three files that were actually created when i just did my sql lite itself and my command prompt response then my studio this is what gets downloaded i unzip that which is directly right here and then when i go into sql light studio i will go down and actually execute the sqlitestudio.exe that will actually bring up sql uh light studio and now we're running studio now the first thing we do is actually we will create a database in order to do that you hit database and add database and we have two different buttons over here to the file you see the database type is actually sqlite3 then we have a plus here which will as create a new database or browse for an existing database so we can open and manipulate it so what we'll do is do it we'll do a test uh database hit this button here and we will just call it test and we'll call it test uh dot i will say test two dot db and we can call it any one of these extensions here and it will all be the same database now keep in mind this is one file that we're actually creating for all of our database and make that file can go up to oh well over 140 terabytes of information so we'll just hit save and now that's where our file is going to be stored and there's my test name and we're gonna say keep permanent keep it in configuration and then we can test this connection and it tests okay so we'll hit ok and now here is our test if we double click on that test you'll see that we have tables and views if we hit tables and then right click we can create a table now now we're getting to the structure of the database itself or we can go up to structure and hit create table either way it's going to do the same thing and what we'll do is create a table so we'll say table test is our name and then when we double click on the actual column information we can then type a column name um and our data type we'll just say it's date time and we'll hit ok we'll add another column we'll say column two and we'll call this uh an integer or a double we'll hit ok so now we have two columns in our database these can be anything that we want to actually log so if we think about it as we get those variables in from node red we can now log this information into the database and this is what the structure is going to look like so this is where you're going to actually spend a lot of time determining exactly what you want in your database and what to store so once we've created that we can hit the commit structure changes and now this is actually the command that's going to be used to actually create that so everything even in sql studio or sqlite studio will rely on using the command prompt and the sql commands in order to generate or do the information or do the work required on the sql database hit ok and we have here the table test was successful everything looks good so now what we can do is we look at the data and you can see here's my two columns and as we populate that would populate so that's my test but what we have is we actually have one already created so if we hit database and again we're going to add a database we will now add the one we already have created which is acc automation we'll open that one up and we'll test the connection which is okay and now here's our database so when we look at a double click on the acc automation you'll see our tables we have one it's called the solo and on the solo let's close that one down you'll see that we have our id which is an integer and it's a primary key i like using primary keys in my table because if any any data gets missing that primary key now disappears and it keeps on going so i know exactly if i'm losing information out of my database if i click on there you see my kill under my configuration it auto increments one so we're just going to cancel that one then we have a date time so this will be the date time in which where our verbs are going to be stored in the database then we have our present value which is set for double which allows us to put in decimal places which is what our present value is and then we have our set value which is also a double and then we have our switch which is boolean which is either on or off so that is our structure for our table if we actually look at data we can actually look at the data that we have within here and you can actually sort the columns there's the user id and you can see the information automatically gets obtained in there [Music] so that is what the structure will do all right so now that we have our structure the next thing we do is we actually will go back to our node red and we can look at inputting or downloading to our data or installing our sqlite node so if we go over here we can go to manage palette and under manage palette we can go to install and if we type in sql light you will see at this node right here is the node that we want you can see that we've already have it installed here so which is good and then so we'll just hit close there and if we didn't we'd hit install then we can look over here we can see that the node-red light pallets has two nodes and there they are right here and then we'll just close that down so we have that installed and if we look down our function or our nodes here you'll see that that node is actually under the storage area which is the sql light function right here so moving down our structure so we fizzle we had last time and what we've done is we've actually had a line here saying right value to sqlite database so what we're going to do is inject the information here so let's look at the inject and on the inject what we do is we have a every five seconds we're going to log information or variables and right now we're going to log information using the payload and then we're going to take now which is going to be the actual timestamp and we're going to use year month day hour minute and second and then we're going to account for the time zone right here so it's just going to create a variable that we can use for our timestamp or a datetimestamp [Music] then what we do is we're going to create a function [Music] and that function will now have a message and that return message will be the topic and the topic will contain sql commands that will allow us to insert into the solo table the date the present value set value and switch values and we get those values by the message payload which is my injection which is my date first then i get my present value which is divided by 10 giving us my decimal place our set value and then we get our flow switch so that's how our function is set up so that all the variables are then done so that we this will be executed into our database [Music] so let cancel there and then finally we have our sqlite node and on that node what you'll see is that we basically point to our database location which is our sqlite slash acc automation dot db [Music] and then our sql query comes from our message topic so it's every five seconds we're going to then update our database and log that information so currently right now that is actually flowing or actually executing so if we look at our sql tutorial there's also sql tutorials to help you out w3schools is an excellent place to get information and it's absolutely free and this has a great tutorial on sql and how to get information in and out of a database with practical examples that you can actually use so going back to our node right here so there's our information here that is go flowing now we go back to take a look let's just take a look at our hardware [Music] and our actual hardware what we have is we've actually substituted our regular click to our click plus so our ethernet connection is now through our wi-fi network or located right here we still have our analog pot right here that as we turn our pot you can see that we have the values going up and down so turn that back down so you can see that how that works and then we have our thermocouple right here and as we hit the thermocouple or make it you can see the temperature is rising [Music] so we have that going on and then what happens is if we call up our again our sql studio here here's my information we can actually refresh this and you can see now that our here's our new variables that we just came in the other thing we can do is actually create through this we can create a sql editor and on a sql editor what we can do is actually create a information that we can put in here and actually view the information such as our select so we can start pulling the information from our our database and see what that will look like so a lot of different options that we can have for this logging and you can see how easy it is to implement using node red so if you enjoyed this video please hit the like button below if you have any questions about the video please leave a comment below and i'll do my best to answer it if you want more information about us or you want our free ebooks on numbering systems or robust data logging please click on the link in description below to get it a new video is put out every monday so make sure you hit the subscribe button so you get more videos like this in the future remember to click the bell beside your subscription to actually receive those notifications thanks so much for watching i'll see you next time stay safe [Music] you

Channel: ACC Automation

Views: 4,001

Keywords: acc automation, iiot programming, iot industrial, iot node-red, iot tutorial, learn node-red, node red, node-red plc data log, node-red tutorial, node-red modbus, node-red sql database log, node-red sql database, node-red database, node-red data logging, sql data logging, sqlite node-red database, sqlite node-red plc, sql database node-red, iot, node-red, sql, tutorial, automation, modbus, node red sql, nodered sql, node red log, node-red sql database log - modbus logging

Id: QHjcNj1S_Mo

Length: 15min 30sec (930 seconds)

Published: Mon May 10 2021