Advanced Node-RED & MySQL tips and techniques

a lot of no red projects are built around the fact that you need to move information from one data source to another data source it might be a user interface or one of the most commonly used and most useful data sources a database in this video in particular i'll be taking a look at the mysql database nodes for node-red and how to use them to insert data into a database as well as read it back in a previous video i went over how to install this node package how to get the basic configuration done and how to insert just one piece of data from one data source like a button press that only inserts when the button is pressed or a temperature gauge which is logged over time in this video instead of using just one piece of data i'll be collecting multiple pieces of data and i'll do this in two different ways and then show you guys how to read it back so let's get started [Music] firstly i will be using the same developer database that i used last time and the same local server but this would work for a cloud server or something else on premise as well instead of using this management table within that database however we're going to be entering purchases in this convenience store example i will be reading in a fuel level and then simulating fuel purchases and i'll also be reading in different product information and storing that in the same c store table instead of starting with this fuel level we'll actually be starting with just really basic information we want to put in a product name a product quantity a pro product price and then finally we'll have a customer name this will all be triggered by button press and i'm going to be using the node-red dashboard user interface to do all of this you can install the node-red dashboard ui yourself and we've also got a video that goes over that and we'll go into a more advanced table node that we can also install later on in the video so the first thing we need to do is understand our database so we know exactly what we need to collect let's do a quick insert statement now i'll start by just dragging in an inject node and then i'll push and hold left control and left click on my workspace so that i can quickly search for and add in a mysql node this has already been configured with that developer database that i mentioned earlier so i can just click done there and wire this in now i just need to make a request to get that list of tables so i'll come over to my timestamp and i'll inject that question into the topic since if i select the developer nodes and come over to the help tab you can see that message.topic must hold the query so let's go ahead and set our message.topic to be show tables i'll end this with a semicolon as with all transact sql commands you'll see that more throughout the video now i just need to read this back so i'll also drag in a debug node and i do need to be watching message.payload since the result is returned in message.payload and message.topic again holds that input command that select statement so with that done i'll just hit deploy inject and we can see here my two tables i have my c store table and i have my management table so let's take a deeper look at the c store i'll again come over to my inject and i'll switch my command to instead describe c-store you can see that i've put my commands in all caps so that i can differentiate them from table names and column names like i am here with c-store this isn't necessary but it can help make your commands a little bit more readable especially if you have multiple people working on this same project with that changed i'll click done deploy and we'll inject once more now we can see i get a description of all of the different columns in that table i'll scroll down so that we can see all of it and we start with our id this just identifies the entry in the table and will actually auto increment so the first entry will be entry 1 entry 2 and cell 1. because it's automatic i don't actually need to include this so i'll collapse it the others however i will need to inject i have an integer that's going to be my date timestamp i also have the product name and these are characters so i will need to specify that it's a string with single quotes whereas quantity is a float a floating point number so i just put that raw data in without any quotes finally i also have the price which is another float and will end with another string this varchar or variable character customer so here are the five different pieces of data i'm going to put in four of which i'll be able to fully control with my user interface so let's put each of these interface objects in i'll scroll down to the bottom so we can see what i have available to me now i will be generating the date automatically within code but i will be entering the product quantity price and customer a little bit more manually so let's skip over the date for now and start with the product because this is a text input we want to put in the name of the product i'll drag in a text input node here you can see right away i already have a fuel group and a other store items group that i already created if you don't know how these groups are created or what they look like go ahead and watch our previous video that goes over a more basic introduction to the node-red dashboard for now i'll just select this other store items group and i do want to change the size so that it's a bit more wide i want it to be a size of 8 but no longer next i'll give this a label and that label will be the product name and we'll just handle it as normal text input now this is going to come out on message.payload and we can see that under the help tab here any messages sent as message.payload so we'll just click done and now we need to save that message.payload in order to do that i'm going to be using a change node and will save the variables within this flow of node red so to do that i'll press and hold left control and left click on the workspace to quickly drop in this change node and i'll wire it in here though instead of setting message.payload i'll select this dropdown and choose flow flow context means that anywhere in this flow if i refer to for example flow.product i'll get whatever was outputted most recently by this node i could also save it globally so that i could also read it from the other tabs for example the management the c store or even other tabs it's global to the whole project i'll just use the flow right here this flow.product is going to hold the message.payload so now i'm setting this new flow variable and i'm saving the message.payload inside of it so i'll just click done and that's my first one already done web saved it let's move on to the next bit so i'll select the debug pane and we'll see that my next one after product is the quantity you can see that this quantity is actually going to be a float and the reason it's a float not an integer is you could for example buy half a gallon of fuel which is going to be another product that we'll look at so we do need to make sure this is a floating point number let's drag that in right away i will use a numeric scroller for this because when it comes to just buying an item it can be an integer but when we go to store it it must be allowed to be a float so now that i've dragged in the numeric input let's change that as well instead of the label just being numeric we'll label it as quantity now all we have to do is leave that input as the input and let's say they'll be able to buy up to 100 different products that's all of that handled except for the size which all set to be four the only other change we will need to make here is instead of it being the fuel checkout preview group we'll put it in the other store items group so that they're all in the right place so with that done i'll click done and we'll do the same trick here we did with the change node i can even select it press control c and then press ctrl v to get a copy and wire that in but instead of overriding the flow.product that we're already getting from the product name i want to be setting it separately so i'll be setting it to flow.quantity and that'll be able to save it without overwriting my current product with both of those set up let's test this out to make sure it's all working as expected i'll click deploy and i'll come over to this little dashboard tab up in the top right here if you don't have this it may be because you haven't installed the dashboard ui package you'll need to do that first so i'll select the dashboard there and select this little box with the arrow coming out of it to open my dashboard in a new tab you can see that brought me to rio dev that's my host name slash node red slash ui with a trailing slash there and then the rest of it is just this particular page that's rendered so now we can see here i have the quantity of products and i can increase or decrease that however i want and i can also enter a product name like snacks and i'll be able to check out with snacks in a moment so when it comes to entering the price i want to be using this product name style of field entry so that i can type in the exact number of cents that the product costs whether there's the quantity only goes up and down by those single increments so back in node red i do want to bring in another text input field but we will need to modify the value that comes out of it to make sure we save it as a number and not as a text input so i'll start by double clicking it make sure we're in the other store items group and i'll change the size to something like six so now i just need to set the label and in this case we are bringing in that price right there so we'll just enter a price and we'll see we're in text input mode so now i do want to output this value and we will copy and paste that flow change node as well when i wire this in i will need to make some other changes before i deploy though instead of just setting flow.quantity to be this message.payload which is currently a string that holds a number we want to convert that value into an actual number data type now there's a lot of ways we can turn message.payload from a string data type that's coming out of this text input field into an actual number data type we could bring in a function node or one of many other nodes but there's actually a really nifty way to do that directly in the change node here instead of simply just putting in message.payload directly i'll put message.payload into a jsonata expression this lets me do simple functions on different values and modify it before it actually gets saved to flow.quantity or in this case it'll be float up price so now that i've selected that jsonata expression value i'll select these three dots over on the right to make sure i'm doing this correctly we'll see here i have the ability to insert different functions and test it to make sure it's working so instead of just using message.payload i actually want to convert that to a number so if i bring down all the different functions i have here we see i do have one just called number so i'll select that you can see that i just put the number symbol then number and then with parentheses around the argument that i want to turn into a number so if i type in the dollar sign and then number and then i just put in the number and that in this case is message.payload and it'll turn whatever i put in as message.payload into a number type let's head over to the test tab to check that out here we can see hello world isn't going to count as a valid number but if i for example had five dollars and 22 cents we can see there my output is as expected you can see it's 5.22 and it's orange instead of green you'll see if i don't have this function here and it's just message.payload we'll see we still get the quotes around it and it's still in green so we do need to have that number function in there so that's a really easy way to convert string values into number values because we do actually have to insert a number as our price into the database so that it matches the column type so with that done i'll just click done and we'll hit done one more time so there we go we can now save a product name whenever that gets put in save a quantity as a integer number and then save a price as a floating point number that gets converted from that string we're all set to go now we want to add in the rest of these pieces of data and figure out exactly when we want to go through with this checkout process so to do that why don't we just add a simple checkout button to this user interface there's a really nice button gadget at the top here we can just drag right in and then we'll use this to trigger a function node that is going to get the current date of the time that the button is pressed and then also we'll be able to insert a customer name in there as well so let's get started first i'll double click my button and put that on the other store items and make it a little bit smaller let's say only two items wide now we can give this a button name like checkout and we'll see down here at the bottom i could also specify what payload i'm going to send but this doesn't actually need to send any particular piece of information once it gets clicked it'll insert some message into the flow and then once that message sets off the function nodes we'll be able to do the rest from there so now i'll click done and we'll first use a function node that i'll just drop in really quickly here to build together an object to make sure all of these properties are coming in the way that i expect them once we've got that then we can come back and insert them into the database so i'll wire this checkout button into my function node and double click it let's start here right at the top with the date the date is a column that's actually being stored as an integer it's not a varchar it's not a string value so you're not going to see something like tuesday may the 4th you just see a really long number and that's going to be the representation of the time in unix time or the epoch so i'll first get the time and i'll do that by doing time underscore ms because i am going to be getting in milliseconds i'll set that equal to a new date object and that'll be the date right now of what the button has been pressed at and i'll do get time you'll see though that this returns it in milliseconds and my database wants to store them in seconds so that's a pretty easy conversion though because time and milliseconds can just be divided by 1000 to get seconds i should be good to go but i do want to make sure it's saved as an integer not as a floating point value so if we have a number that falls between 0 and 1000 at the end there we're going to get a decimal so let's drop that off with another little function here this one is just math.round and the result of that division now we can just save this as our date and we'll be good to go so i'll need to be able to reference this date later though so i'll be putting all of this inside of a message object so that's just message equal to then we'll put on some brackets on either side here and there we go we have a nice message object that we can return at the end here do make sure that you have a colon in between to separate your property title with the actual property value next i'll work down the list and we'll get the product so the product is going to be that flow.product but you don't refer to it as flow.product within this context you have to go and get it from outside of this node so what you do is flow.get and then hand in the name of what you would like to go get so flow.get and then quotes around the word product so now i have that second property and let's just keep moving down the list next we have the quantity and that'll be very similar it'll be dot get quote quantity and then finally we just need to get the price so i'll copy that line and just paste it down below and swap that out so price is flow.price now the next one on my list is going to be the customer and you'll notice that both the customer and the product are actually var cars and these var cars are treated a little bit differently in different database languages for example if i say the word product quote-unquote i could be referring to as this column product or i could be referring to the value quote product end quote so in order to help my sql differentiate whether i'm referring to the column or an actual value i just put additional quotes in so instead of it just being quote rio dev which is going to be the customer i'm going to use in this place i won't just use the literal quote rio devon quote i'll hand in extra little tick characters those single quotes inside the double quotes that you can see there and these single quotes will be handed into mysql and it will then recognize it as a string value rather than something like a command or a title of something like a column or a row so now that i know that i have that fixed let's make sure we also put those same ticks around the product at the top here so instead of trying to put them around it like this which is going to turn that whole thing into a little string i'll actually put quotes around those single ticks and add them to the front and back of the string so here you can see now this whole product value is going to be a single tick then whatever we store in flow.product and then a final single tick at the end there and you'll be able to see that in the debug in just a moment so we can see now we have message.date message.product and so on all the way down we'll be able to see these values in the return message in the debug pane in just a second so i'll give the node a name something like gather data and click done now i can just drop in a new debug node but we'll note that i don't have message.payload properties anymore they're all in different singular message.something properties so i'll need to make sure i select this dropdown to output the complete message object so i'll select that click done and wire it in now we know we're all set to go i'll go ahead and hit deploy and we'll come back over to our dashboard here we can see where i've got our price here say three dollars and 20 cents we've got our quantity let's say we get three of them and we've got our snacks that's the product we're going to check out with so i'll go ahead and select checkout and we'll see back in node-red over in the debug pane i can just drop down this object and there we go we've got our date right there that's the timestamp that we were looking for we've got our product and you can see it has got that single tick there then it says snacks then it has another single tick and that whole thing is one value quantity is in fact a integer number price is a decimal and then we also have those varchar ticks around our customer name as well this message id is just used for tracking messages in node-red we can safely ignore it so we know all of our properties are here successfully it's time to turn them into an insert statement and see them in the database to do this i will be using another function node that i'll just drop in right here this one is actually going to create the insert statement this is going to be a lot easier to do now that i've already gathered the data into one nice object so we'll start by just creating an insert string and we'll set that equal to insert into the name of the table that's c store and we'll be putting in certain columns so let's specify which columns those are so those will be the the date column the product column the quantity column the price column and finally the customer column and then each of those columns will have one value associated with it and that's going to be one row so this insert statement will affect one row each time so now let's add on the string continue it on the next line here so that we can put in what values we're writing put parentheses around that and end with a semicolon as well there we want to make sure we have the semicolon both inside the quotes to let mysql know that we're finishing our statement we also need to have a semicolon on the outside of the quotes to let javascript know that we're finished defining the string that's why you'll see those both there so now let's specify the values now these are going to be the same date product quantity price and customer that we have up here they're not going to be these string values like date it's going to be these message properties you can see down here i can make sure i get it exactly by hovering over it and selecting this button on the left here you'll see that says copy path and when i click that it copies the path to my clipboard so now i can just insert quotes here and paste that in and you'll see it is at date specifically it's at message.date that's not included as part of the path copy so then i'll just put pluses in and we'll take this message.date this number and we'll put it in the middle of this string here i could reference more complex objects here for example i could have message.payload index0.temperature or something like that and that's why it comes in really handy to be able to just copy the path there because mine are just directly off the message tree it's pretty easy to just come in here put quotes around something add in a plus symbol and put message dot in there we'll put the pluses on either side and do the same for the next couple so there we go that should be our whole insert statement right there now as you may recall from earlier when we did the subscribe c-store transact request we do have to send it on message.topic so we'll set message.topic to be our insert statement the main thing is it doesn't really matter where this string comes from whether you read it indirectly whether you build it in a function node or whatever else once you have that insert statement string just set it on message.topic and you should be good to go so now we gather our data and we can create the insert statement and then finally put it into our database i'll just drop in another mysql node and wired in i will of course be using that same developer database and we're good to go so now i'll go ahead and click done up in the top right here and we'll see that the database has connected before i dive in and actually insert some data into this database let's have a look and make sure it's empty through the database command line i've got that open here we can see here i have a few databases dev my sql test as well as some different schemas so i first have to make sure that i'm using that dev database now that i know i'm using dev i can say show tables just like i did before and we'll see there we go there's our c store which is the one we're going to be checking and the management we used in a previous example now for example i can describe c store and you'll see we get the exact same description we did before the id that's the primary key that we have auto incrementing then we have the date product quantity and the other properties and columns that you saw earlier now i can do a select everything from this database table so i'll select star from c store star is just a wild card that says get everything that matches everything so when i click enter we'll see i got an empty set because this table hasn't been used before so let's insert some data and make sure it's working so now from node red i can insert something like snacks and drinks and other purchases but i want to make sure that the insert statement is working so i'll also be dropping a debug node over here in the top right and that'll be looking at the insert statement directly i know my data gathering is working so with that done i'll click deploy and we'll test this out from the dashboard let's start by going ahead and doing a checkout with these three snacks for three dollars and twenty cents each i'll click checkout and that should have done an insert statement let's say we want two drinks to go with that i can just come over here type in drinks set a different price say 2 dollars and 40 cents and i'll do a check out of that as well now i should have two rows affected with two very different sets of data now before i trust that this actually worked i want to go back to node-red and check out my debug pane in fact we can actually see here i've made some kind of error here now this might seem suddenly really scary because what have i done to the database but it's okay if you don't insert a valid statement mysql will simply reject it and not do anything with it you can write out some statements that are correctly written that could damage some data by either changing or removing things that you didn't intend to but if you do some kind of syntax mistake with the transact sql most of the time you're not going to break anything so don't panic too much of course you should discuss with your database administration your it team and other people in your place of business before using this in any kind of production environment anyway so make sure you test things out before you put them out there because things like this happen all the time let's find out exactly what went wrong you can see right here this red text right here is my insert statement this is because this is what's representing my message.topic and you can see that it does have my insert into and everything looks pretty good you can see i have my timestamp in there as an integer number i have my quote snacks end quote but then here that third field is showing up as undefined let's see what went wrong there if i bring open my code here we can see we've got the message date message.product but ah it's my message.quantity that's wrong here we can see here i accidentally missed the t when i was initially typing this out and it said quante e and it just needs to have that extra t in there so that it does say quantity so i'll fix that there and here and we should be good to go so i'll go ahead and click done we'll clear up the debug pane and go ahead and click deploy see we've now reconnected to the database let's go back to the dashboard and try to check out once more i'll also go back and add that quantity three snags we'll backspace this and turn the price into three dollars and twenty cents and relabel this as snacks now i can check out again and i should have two rows this time let's go back to node-red and here we go i've inserted two values the reason we're seeing undefined is because we're looking at the message.payload we don't care about that we just care about the topic that gets included whenever we output that to the debug pane so now i should be able to go back do a select statement on the database and i'll see them right there let's test that out i'll do one more select and there we go so we can see that we've got our drinks and then one second later we've got our snacks checked out with the quantities of two and three the price of two dollars forty cents and three dollars and twenty cents and they're both associated with the same customer although we could have changed that as well by just adding another text field in our user interface so it looks like that's working great we're able to create a flow variable that holds the product name another one that holds the quantity another one that holds the price and then whenever we click a checkout button that exists in the user interface we gather up all of that data into one single nicely formatted object that it can include some other pieces like the current time or even a customer string then we finally build that together into a nice simple insert statement that we put into the developer database and we know it's all working this is a nice usable flow in my next example i'm not going to rely as heavily on the user interface i'm going to be using physical inputs and rather than saving them to flow variables and batching them together i'll get one piece of data after the other and then end up with an object that i can then turn into an insert statement so the values i'll be using are fuel level that comes from this potentiometer knob here as well as this push button which will represent my fuel checkout i'll be inserting this fuel as a fuel product into the same product database this c store that i've been using for the rest of the example and then once i've gone through all that we'll show how to select it back and display it in that ui page so let's get started by just cleaning this up really quickly and figuring out what data we need so with our flow nicely rearranged here our debuggle cleaned up and debug nodes disabled we're ready to look at the next example let's start by figuring out how to read in this push button i'll be doing that with a groov i o input node and you can see more about these nodes in a previous video we've done but the main thing is is that whenever i press the button that will trigger this input so this could be any other node you would like as well so i'll double click it and you'll see i already have a groov io device configured here this local host which is my rio learning center i'll select that and i do want to get a digital state change that button press so let's figure out the module index and channel index really quick i'll head over to my groov manage home and select my i o channels this is my only module here for the groov rio the module index of zero you can see that up in the top left there in the url and we can see i want to grab this bottom button this digital input on input one so if i select that we can see right now it is off when i press it down it momentarily goes on and when i release it gets turned off again so this is the value we want to be reading in right here on index one so back in node red i'll put in my module index of zero and we'll put in my channel index of 1 and we'll call this read checkout button now i'll click done and we'll test this out so let's just drop in a debug node really quickly wire that together and hit deploy now you can see i get a message right away here i get a false and you can see that's because i have this send initial value so if i were to deploy while it's being held down you would also see a true message let's press it now we'll get the value true and then when i release we get the value false now i only want to do one checkout every time the button is pressed so i actually want to reject all of these false messages i want to filter those out entirely so before i move further down into my flow it's important that i drop in at switch node here so i can either grab one from the gadget palette on the left or i can just control click on this wire that'll bring out this drop down here and i can just select the switch node and it wires it in automatically for me just a nice way to save some time i'll select the switch node and we are checking message.payload for a certain value but we don't want to see if it's equal to some string we want to see if it is true if it is true it goes output 1 otherwise it just goes away so now with that done i can click deploy and we'll see i don't get that initial false message it's being rejected by this switch node we'll just label it so we can keep track of what it does is true so we know that's working every time i press the button we'll get that true message but when i release it we don't get the false one more true but again no false so we're good to go let's clear that up and deploy once more so now that we know our checkout is true we want to grab that fuel level that potentiometer now i could have a fuel level input like i do up here where i'm displaying the fuel level it just scans it for any updates whenever it gets updated it gets rounded down into an integer and displayed in the fuel level you can see that in the dashboard here 144 gallons and then the data just streams out as i turn it up there we go it's gone up to 443 and when i turn it back down it decreases further so we can just have this data streaming in but we only want to fetch that value when we're checking out so instead of using an input node we instead use a groov i o read node this requires a message input before it will trigger a read we'll need to wire this in having a little trouble dropping it on the wire and there we go so now this groov i o read is also going to communicate with the local host device but instead of getting a digital channel we're getting an analog channel now we just need to find the module index and the channel index of this so i'll go up into groove manage and we can see it's on analog input number two again on that same module zero so i'll go back into node-red enter module 0 and channel 2. now i am going to change this value from a different property other than message.payload because i'm going to need to get other pieces of data like the current price per gallon of fuel i'll need to write that into some property and if i write it into message.payload i'll be overwriting this fuel level so i'll need to save the fuel level to a different property to preserve it as i continue going through this flow this is a little bit similar to how we saved it to a flow variable but instead of it being accessible to other nodes in the flow it's just accessible to nodes further down this flow chain so we'll see how that looks in a second don't worry if it was a bit confusing it'll all become clear soon so i'll call this message.fuel and we'll call this node get fuel level so now we know how much fuel we just need to know the price so we'll do that next so i'll click done and now we need to get the fuel price i'll keep this simple for the rest of the example and just drop in a numerical input here this numerical input is going to be our fuel price so you'll just simply enter a dollar value and that'll come in and let us know exactly how much we're getting so we'll set that to a maximum of say 100 and we will put that in the fuel checkout window with a simple width of say 4. so now with all of that set we'll go ahead and click done we will need to make some other changes in a moment here but i want you to understand why so we'll come back to this the next thing i need to do is make sure all the data is there now you can see here when the value changes it sends a message.payload and i can't change this no matter what i do it will always overwrite message.payload it's hardcoded into this node that's why i couldn't save my fuel level to message.payload i have to save it to message.fuel so that it doesn't get overwritten that's what's happening there now i can check to make sure all my values are coming through to the end here when i check the complete message object so that i can see that message.fuel and that message.payload here so let's test this out i'll go ahead and click deploy and we'll see that i shouldn't get a value right away but if i come up into the dashboard and change the fuel price to say one dollar i do get a message the reason for this is this fuel price will send an update every time the value in that field changes we don't want this we don't want to do a checkout every single time the fuel price changes we only want to do a checkout when the button is pressed so we'll need to write some code in a second to handle that that's why this method of using one flow to get one piece of data after the other all attached to the same message payload can sometimes be a little bit tricky it's sometimes easier to set them to flow properties that you can then reference all at once in a separate node but we'll end up seeing at the end of this that it is possible to do it either way so let's figure out first how we make sure that we're only getting an input when the button is pressed if i press the button we'll see the difference in the message payload so we can see right away i have a payload of 0 not a payload of 1 and my message.fuel did make it through so if we get a fuel price but we don't know how much fuel we want to reject that message so that's going to be our first part of code let's just control and left click on this for wire so that we can drop in a function node so what we want to do is check to make sure this fuel property even exists by the time we've gotten this far in the flow so to do that we'll be checking what's called the type of that message.fuel if the type of that message.fuel is quote undefined end quote that means it was never included in the object payload so i'll put that in parentheses and check if that is true and if that is true we just want to return nothing return null this essentially stops the code in its tracks no more of the code will run and no message will be returned it just returns nothingness it just stops so now we can do the rest of our code knowing that we're only going to get this far when we have a fuel level and we have a payload what's important to note here though is that the payload has become zero and we can see that if we deploy and come up into the dashboard we can set some fuel price that may be say five dollars per gallon but whenever i press this button it drops the fuel price down to zero automatically this is a big problem and we'll see where that's coming from so let's see what's happening here we can see that we're starting by reading the checkout button then only if it's true will we get the fuel level and save that to message.fuel and then we enter into the fuel price so what are we carrying with us when we enter fuel price let's check that out by making a copy of a debug node and wiring that into this part of the flow i'll switch over to my debug pane and click deploy so we can see now when i press the button and release it we can see here we've got a payload of true coming out so we have a payload of the boolean true that does include our fuel level but that's going into this fuel price if i select fuel price and then go to the help tab we can see here that any input message.payload will be converted to a number and then if the conversion fails the minimum number will be taken as the default because our minimum fuel price here you can see is zero when we get an input of true that can't be converted to a number by this node so it just defaults to 0 and then that becomes our forced fuel price so because we're writing in a message.payload into this node it's actually changing the value that then gets outputted this is really easy to work around though because we don't care about this message.payload anymore we know it's true we know we're doing a checkout so before we even get into this node let's just delete it so we'll drag these out of the way and we'll drop in a change node right here this change node instead of setting message.payload we'll just delete it so now let's go through this flow one more time first we're reading the checkout button if that's true we'll collect the fuel level and save that to its own message property then we delete the payload that we picked up earlier and go ahead and grab the fuel price which now is not going to be damaged by any incoming information then we'll go into the function node make sure that we do in fact have our fuel level and we'll see it all right here in this final debug node let's clear up the debug pane deploy and test this out i'll go over to the dashboard and set some fuel price say three dollars and we'll see as soon as i press the button and release it our fuel price has not changed and if i go back into node-red we can see we haven't gotten any errors and we do still have our payload of three that's going to be our fuel price you can see that's unchanged right there under the node so now we successfully have our fuel quantity and our fuel payload that's our fuel price now it's finally time for us to create our insert statement let's move these over and start editing this function node a little bit more we'll start the same place we did with the last example by getting the current time in milliseconds and that's going to be our unix timestamp that we're going to then convert into seconds and save as our date so i'll set that timestamp equal to a new date object that's for the current time right now that that button is pressed and we'll do get time to get the time in milliseconds then to get the time in seconds which will be our date property we'll do time underscore milliseconds and divide it by one thousand again because this field is an integer we will use need to use the math library to round that down to just an integer number i won't be saving it to a message property this time although you could i'll just be creating this whole insert statement right here in this one node next i need to set what my product name is in this case that'll be the string quote fuel and quote and of course because this is a varchar i will need to put those single ticks inside those quotes so that my sequel knows how to interpret it the next thing is going to be the quantity and that's the amount of fuel i'm getting so that'll be the message.fuel that i read in earlier and i also need to know the price and that will be the current message.payload you can see that's my price there that three dollars on my message.payload finally i'll set the customer name and that will be quote rio hyphen dev end quote and again with a semicolon after that and now we can put all this into the insert statement so i'll do the same thing i did last time where i say insert equals quote insert into c store then we'll specify the columns that we're inserting into then we'll add on a new string create a new line and we'll determine exactly which values we're writing again i will need a semicolon inside the quotes and that's what gets sent to my sql to let it know that the statement is over and we'll also have a semicolon on the outside to let javascript know that this string here that we're constructing is over the two of them both need to be there it's very important so now inserting into my c store i will have my date product name my quantity let's make sure we spell that correctly and the price as well as the customer so these are of course the names of the columns i'm putting in and the values aren't going to be the strings of those columns i need to actually make sure these are individual message properties so i'll put quotes around date and i don't need to refer to it as message.date since it's just a local variable that i have within javascript you don't have to do this but if you'd like you can put the word var before them just so that you know that they all exist locally we're creating these and we can't reference them anywhere else in the flow but we're just fine referencing them like this inside this code block we also have our product quantity price and finally the customer i'm just double clicking it pushing quotes to force that character outside of the quotes and then putting plus signs on either side so we get this value string then we add inside that whatever value is this current date then we add in a comma character then we add in the product and so on going down until create the entire insert statement we'll of course have to save this to message.topic since that's where the mysql node looks for that insert statement so we'll just set that right there and go ahead and click done i'm not going to drag in the developer database node just yet i want to actually see this insert statement first so i'll click deploy and we'll reach over and press our button here right away we can see we've got this insert topic at the top here and we can also see it in the main body here insert into c store we've got the date the product the quantity price and customer and each of the values are well defined we should be good to insert this into the database i'll just make a copy of this database node here and paste this at the bottom and wired in so with that all set up we can go ahead and click deploy and i'll bring back open my database command line and confirm that we still have those same two rows there yep looks like we're good to go if i bring back open node red and select some fuel level and press the checkout button we can see there we go we've injected a message and we've gotten an ok from the developer database node so if i now switch back and do the select again there we go we've got a fuel entry where we've gotten 63 gallons at three dollars a gallon and you can see the product there is fuel so if i were to increase that quantity a little bit say we're filling up a large truck push that button once more and release if i do another select there we go we can see we've got another fuel checkout where the number did increase to 127 gallons let's do one final test where we change the price say we're in california and price has gone up over the summer it's five dollars and we're getting a lot of it this time we'll get 251 gallons now i'll press that checkout button and release you can see our fuel price is unchanged we have another inject statement over here and if i switch back to that command line and select once more we have a third checkout and here we're getting 251 gallons at five dollars per gallon so there we go now we've got all of our drinks and our snacks and our fuel all that we can see right here in this database now i may not just want to see it in the database though if i don't have access to the command line or i'm not an administrator or something like that i would never know what was in there and whether or not it was a successful purchase so let's quickly finish this up by creating a table in the dashboard and displaying all of this data through that table so in order to display this data which comes in as a table we'll first need to bring it into node-red and then we'll also need a new gadget that we can actually use to display it let's first start by selecting some data and see what format it looks like in the debug pane in order to read in that data we can just reuse this top little flow at the top here but instead of asking it to describe c store or show what's inside of some kind of database we'll just do select star from c store just like we did in the developer command line so i'll go ahead and click done click deploy and we'll hit inject on this top flow here we can see it returned an array of objects each object represents one row and each of the properties is the columns that make up that row so we've got our date product quantity price and customer just like we injected earlier and we'll see the same for the other objects including the fuel that we just added so we have this really nicely structured data here we just need to display it in some kind of table now in order to display this data in a nice table we will need to add a new node to our palette because you'll notice under the ui dashboard nodes there are none designed for a table thankfully we can just install one new package but you will need a gateway to the internet to do so because i'm here connected to the internet over my local router i can just come up here into the top right select manage palette and then search for the node under the install tab so all i have to type in is ui-table and we'll see it's this top single result here node-red-ui-table so i'll go ahead and install that confirm the installation and then i can view the log to make sure it comes down correctly depending on the version of node-red that you're using and the version of the node red ui table nodes you may see some errors here but as long as you get a return code of zero and see the nodes show up in your palette you should be good to go and there we go after just a couple of seconds of waiting we've got that return code of zero at the bottom and we'll see the green pop-up showing that the ui table is added at the top i'll close out of all of these windows and if i scroll down we can see it right there there's the table node so let's drag this in and figure out exactly how it works if i select that and then come over to the help tab we can see that a node-red dashboard ui widget node displays a table of data and all it has to have is an array of objects which contain row data which is exactly what we're reading in so it's perfect for this application we don't need to worry about the outputs we're just going to be writing to it so let's double click the node and configure it we'll start by adding a new group for this since it's not really part of the fuel checkout and it's not part of the purchasing of other store items so i'll select add new iui group and select the pencil icon and just call this group display data so i'll keep this in the same home tab and i'll give it a nice wide width of say 12 units now we'll go ahead and select add and we can see we've got this display data group selected here and we will make sure that we max that out by also selecting 12 for the size here now i just need to give this node a name like display table and so now we just need to set what our columns are and we can do that by just coming back over to the debug pane and there they all are so i'll select this little add button in the bottom left-hand corner under columns and we can see we can name exactly what property it is what the title should be and where we're aligning this so because it's coming off message.payload we know it'll be message.payload0.date 1 dot product and etc so because the data has to come in on message.payload as was described in the node settings right here we know that we're getting all of this row data we just need to determine which properties within that individual row make up a column so the column property name id is one of those properties so i can just do this and call this the entry number since that's what it represents now i'll click in and add and we'll just work our way down the list here the next one we have after the id is the date and we can title that the same we'll do another one for our product copy and paste that we'll have another property and that's going to be our quantity add one more for our price and add our final customer property and we'll be able to see all of this data really nice and easily so now all i have to do is click done wire that directly into the output of the developer node since that's what's outputting this message.payload and i can go ahead and click deploy so now we'll see i have my table here it shouldn't have anything in it because i haven't done any select statements if i bring open the dashboard you can see there are all the properties i just added but none of them have any data below them yet so let's go back inject right here on our developer database and when i come back there we go it's nicely populated with data i can scroll down and see that all here i can make this table a little bit bigger by double clicking it and increasing the size so that it's a bit longer let's say 12x6 and when i click deploy this is a really easy way to get started it's really convenient and the ui table node makes it dead easy to display a large amount of data from one single array here in the node red ui dashboard if you have any questions about how any of this works check out our other videos check out our forums that will be linked in the description or feel free to leave a comment on this video thanks for watching

Channel: Opto Video

Views: 39,693

Keywords: automation, opto 22, opto, opto22, automatizacion y control industrial, Node-RED, MySQL, My-SQL, My SQL, Database, Node-RED dashboard, groov, groov RIO, groov EPIC, Opto 22 groov, groov Nodes, tutorial, Node-RED tutorial, Node-RED Lesson

Id: xwLTby6b-IU

Length: 49min 25sec (2965 seconds)

Published: Wed Aug 04 2021