Thank you for joining me for this video using Database Manager. Servoy University, I'm Bob Cusick. In this lesson, we're going to take a look at the Database Manager. This node down here in the Solution Explorer. There's all kinds of functions in here that are really useful if you're going to deal with the back-end database directly. And one of the things that you're going to use a lot, especially if you're building software as a service applications or multi-tenant applications, is add Table Filter Peram, which is this command right here. So the idea behind Table Filter parameters is you can have, sir, boy automatically limit the data that's returned in any table or any server connection. And we're going to see how that works with this little example that I've made here. This is a simple customer's form, and there's two buttons. One says Apply Filter and one says Remove Filter. So let's see how this looks in the client. So here in the Smart Client, I have the customer's table and it has 91 rows in it, and I can go through here. Now what I've done is I've written a short two-line method that will apply a filter parameter to the city field, and it will show only the records where the city matches London. So let's click it and see what happens. Here we've applied the filter, and now our found set is down to six records. But it's done more than just a find. What it's done is it's actually limited the number of records that this user can interact with to where the city equals London. For example, if I were to come up here to the Select menu and say Find, and then do a search for any business that started with the letter B, and press Enter. You'll notice there's only one record. And there's only one record because, so what will automatically for all the SQL queries that it issues will add that condition to every single one of the queries that it issues automatically. So your add table filter parameter really becomes an automatic way for you to filter those records, and you're going to use it all the time if you build multi-tent solutions. Now the great thing about table filter parameter is that not only can you add them, but you can remove them. So let's go ahead here and remove the filter, and now we're back to our 91 records. And if I go back up here to the Select menu and choose Find, and do the same thing that I did before. So find all the businesses with a B. You'll see that there are seven, because the filter is not applied to the city field anymore. Let's have a look at the code behind those buttons. So as you can see, there's two methods, one called Apply Filter, and one called Remove Filter. So the Apply Filter uses the Database Manager function, Add Table Filter Perum. And let's roll back over there so you can see the parameters. So it takes a server name, a table name, the field that you want to limit it by, the operator, the value that you want to filter, and an optional parameter which is the filter name. It's a good practice for you to give all of your filter parameters a name, because if you don't give them a name, you won't be able to ever remove them during that session. So let's take a look at the code that I've written and see what's going on. So I've said, example data is my connection. Customers is my table. It's the city field that I want to limit by where it equals the value London, and I've given it the name London Filter. If you just stop right there, you won't see your found set change, because what'll happen is, the voice already loaded all of the records. So we need to do one more command, which is tell the forms controller to go ahead and load all records. And as soon as we do that, the filter will reapply. Now the way you're really going to use this in the real world is you're going to call these filter parameters right after the user logs in. So you'll probably have a login form that ties the customer to a company, and that company will have a tenant ID. And then in your tables, you'll have another column called tenant ID that you're going to limit to that particular tenant. In this example, I've just done something simple to show you the concept. The great thing about table filter parameters is that you can apply them at any time. You don't have to do them just at startup. But that's the place you're going to use them most often. So for the remove filter, we call another function down here called remove filter param. And the remove table filter param takes the server name and the name of the filter and takes it all off. Now again, because I did this on a form that's already been loaded, unless you tell the controller to reload the records, you're not going to see the found set change. This is an extremely powerful feature of servoy. It allows you to build multi tenant solutions very, very easily and how servoy automatically handle all of those table filter parameters. You can add more than one table filter parameter at a time to the same connection to different tables. You can do it per table per connection. And if you want all of your data to be filtered by the same field, for example, I can limit all of the example data simply by taking out the word customers and putting in the word null. If you use null as the table name, then servoy will automatically filter every single table in there by this city equals London. In your table filter parameter, you can also use different operators. So it's not just equals. It's not equals less than greater than. And you can apply multiple filters to the same field. There is one exception to this add table filter param and having servoy automatically limit these found sets for you. And that is when you write your own SQL. When you write your own SQL queries, you're going to be responsible for making sure that you filter all of your queries automatically. Because servoy is not going to do it for you. So you need to make sure that your SQL statements are correct and will limit the data according to the way that you need it limited. However, if you use servoy objects like found sets and forms and controllers and all of those things, you'll never have to worry about it. So why will do it for you automatically to every SQL query every time? Now, we don't have the time to go through all of the functions inside of the database manager, but another one that you're going to use very frequently is get data set by query. So let's take a look at what that one is and to demonstrate get data set by query. We're going to go ahead and write our own basic SQL runner. So to do that, I've created a simple form called SQL runner and we'll open it here in the form designer. And this is a form that I've built that's based on no table. And we're going to add a couple of form variables and a button and we're going to have an interactive SQL runner. So let's go to our form here. Let's create a couple of variables. Right click and say create variable. We're going to call this one SQL statement and it's going to be text. Then the next one that we're going to create is going to be the actual SQL return value. And here's a tip for you. You don't necessarily have to use the GUI to create variables. You can simply type var SQL return. And now when you click the save button, sort of what will actually automatically create that variable for you. So it's just a little time saver. So now we've created our two form variables that are going to hold the SQL statement and one that's going to format the results for us. And now let's write the actual code that's going to go onto a button that's going to execute that SQL query. So again, I can just start typing the word function. Execute SQL is going to be the name of my function. It's going to have an event tied to it because I'm going to tie it to a button. So that's there in case I ever want to use it. Do a curly bracket. And now if I hit save, which is control s, it will create that function for me. So coming back down to the database manager node, we're going to say get data set by query. And get data set by query contains a server name, the query that you want to run, any arguments for prepared statements, which we'll cover in a minute. And the number of max return rows that you want to return. Well, the first time that you use a new function, it's helpful to move sample code so that you can kind of see what's going on. So let's go ahead and do that. I'm going to click in my method where I wanted to insert the code. I'm going to right click and say move sample. And when you do boom, I get all of this code in here that is part of the sample. Let's take a look at the code so you can get an idea about how you would use it. And then we'll go ahead and just create our own. So the first example will go ahead and it will do a query executed and then tell the controller to load the records of the primary keys that came back. That's cool. I mean, you can use queries to load up records, but that's not what we're interested in. We're going to go ahead and write a SQL runner that's going to perform the query that we told it to. So here the max return rows are 10. You can do a basic SQL query. And then the r0 comes in here where it says where start date equals question mark. This is called using a prepared statement. Rather than hard coding your value into your SQL query. If you use a prepared statement and put all your variables with question marks and then you make a new array and you put your arguments into the array, what will happen is your SQL queries will run much, much faster because when you issue a query to the backend database, it has to work out what's called a query plan. So it checks all of the tables, fields, indices, permissions, and it makes a plan on the best way to execute that SQL. It only does it the first time it encounters a SQL query. Using a prepared statement means that it will go ahead and make this query plan the first time it runs it, but then it will cache that query plan. And if it sees the same query again, it knows to go ahead and use that. However, if you hard code your values into your SQL query, it's going to make a new query plan every time the SQL executes. So you should always use prepared statements by using the question mark for your variables and then making an array and putting your variables into the array. Now here's where the magic happens. VAR data set equals database manager get data set by query and it's getting the data source name from the current form, but you can specify one either in a global or hard code it, whatever makes sense. And then it takes the query, the arguments, and it has the maximum return rows. Now what will happen is what gets returned as a result of get data set by query if we roll over here, you can see the very first word in there is JS data set. That's the type of object that's going to get returned from this function. Now if you come over here to the database manager node and you expand it and you click on the JS data set object, you'll see that there are other transformational functions that in there that you can do. So you can get it as HTML, you can add a row to it, get column names, all of those kinds of things. See how many columns are in there, see how many rows are in there. So it's a very powerful object in and of itself. Now this example code will then go ahead and loop through all of the rows that will return and it's just adding this grand total up by going through and looping through all the records. We're not going to do any of that stuff. What we're going to do is we're going to remove all of this sample code for now and let's go ahead and build this function from scratch so that you can understand how to do it yourself. So the first thing we're going to do is specify the maximum number of rows. I'm going to say the max rows is going to be 500. Now what we're going to do is we're going to come back to our database manager. I'm going to right click on get data set by query and say move code rather than move sample. So this gives us a list of all of the parameters that are required for this and then we can go ahead and fill it out. So our server name right now I'm just going to hard code this to example data. You can get this again from a global or from the current form. Our query is going to actually be held in that field called SQL statement. So we're going to say SQL statement in that variable. The arguments we're not going to have any arguments. Basically all of our queries that we're going to run live will not be prepared statements. So we're going to just pass the word null here or none. And the max returned rows we're going to use our variable max rows. And that's it. Now remember that the database manager get data set by query will return something. So we need a container or a variable to hold that data set object that comes back. So what we're going to say is we're going to call ours ds for data set. So our data set is going to be whatever comes back as a result of this query. Now what we're going to do is we're going to see if there's anything in that data set. And if there is, then we are going to automatically transform it into HTML. So we're going to say if ds and then we can say in our data set get max row index. Is greater than zero. If there's records returned from it, then what I want you to do is I want to take this SQL return variable. And I want to set it to that data set. We're going to say SQL return equals data set. And now we want to get it as HTML. So I'm going to say move code. And there are all kinds of optional parameters that you can put in here. And these are Boolean values. But we're just going to take all of them out. And we're going to say set it to the HTML. All right, very simple. Let's save this. Let's go to our form now and set up our form. So on our form we can come over here to our SQL runner. And we can place our fields on there. So we're going to say our form variables are these two SQL statement and SQL return. So here's our statement. Let's put it up here. Let's make this a little bit bigger. Let's show our properties here. This is not going to be a text field. Remember a text field as a single line. We want this to be a text area. We're going to set our scroll bars so it never scrolls horizontally. All right, so that we'll wrap for us nicely. And then for our return, we're just going to make this another really big field here. Like so. We're going to change the type of this from image media. To HTML area. Let's make sure that our variable is the right way. So yep, it's a text I can tell by rolling over here. By the way, if you ever need to edit the data type, you can right click and say edit variable. And here you can change data type to be whatever you want it to be. And now we just need a button to execute our SQL. So let's move that up. We'll add a button here. And we'll call this button run. OK. We'll click on the button and tell it the odd action that we want is the four method called execute SQL. Now, I'm going to do one more thing here. I'm going to show how anchors work. We talked a little bit about anchors in the video working with forms and form objects. I'm going to go ahead and anchor this to the right side as well. And I'm going to anchor this one to the right and to the bottom. And I'm going to anchor this one rather than to the top and left, I'm going to do bottom right. So remember anchoring means keeping the space between the object and the right and the object and the bottom the same no matter how big the screen gets. So let's go ahead and save that. We'll open up our client. OK. We'll navigate to our SQL runner. So here's our SQL runner. And as I expand this window, notice how all the objects will go ahead and redraw. So I don't need any records here. So we can hide this. This thing is called the navigator on our form. So if we go back to the development environment, we come back to the navigator. Let's click on the form. Go down to navigator. And we're going to say none that'll just be a little bit cleaner. We'll save that. And as you save, when you reopen the client, it will automatically update. All right. So here we are. We know that we have our little SQL runner. This is our form variable. So we're going to say select asterisk from customers. And we're going to click this run button. Now notice here that although we set this to be an HTML area, and we're seeing HTML code in here. So when you have HTML data, you need to begin with the string HTML or else your HTML area will display what it has here. And that's just the raw text. So let's go modify our code to fix this. Right before our DS get is HTML, we need to put in the string HTML. So we're going to say HTML, we're going to do it in brackets HTML. Plus all the stuff that seroy gives us back automatically. We'll save that. Let's open it up and client again. And let's try it again. Select asterisk from customers. Click run. And here we have all of the data from all of the columns. And then we can go ahead and just refine our SQL. Where city equals London. And then we can even say something like order by. Company name. Order by contact name. So that's a really basic SQL query runner. Now let's take a look at some of the other database manager functions. And I'll explain a couple of other ones that you'll use a lot. So coming back down here to the database manager. The other one that you're going to use a lot is converting things to found sets and working with found sets. Remember that found sets are simply a wrapped object that allows you to programmatically get to table data without writing SQL. So there's lots of different functions inside the database manager to help you do that. One of them simply called get found set. We'll get found set. All you have to do is pass it a server name or data source name and optionally the name of the table. And you will now have a wrapped object that will be this found set object. So the found set object is very, very powerful. It tells you a lot of things. You can add the found set filter parameter. So this is really handy. It works just like the table filter parameter. It works the same way as the add table filter parameter. But this one just works on a particular found set. You can delete records, add records. You can do finds. You can get selected record, the get the size of something that you're going to do quite a bit. How many records are in this found set? And there's lots more things you can do, including sorting, updating, relook up, make a new record. All of those kinds of things you can do with the found set object. You're going to really want to take advantage of this rather than writing your own SQL queries to do stuff. If you can stick to this found set object, then that's the best way to go, especially if you're using table filter parameters. That way you can ensure that they'll all be filtered properly and automatically for you throughout the session. And the last function that we're going to talk about briefly here on the database manager is the auto save. So on the database manager node down here is you have set auto save by default. So avoid auto commits any new data or updated data, as soon as the user clicks out of the field or changes rows. And most of the time, this is probably perfectly fine for your application. But there may be times when you want to turn this auto save off. And when you turn the auto save off, you stop this auto commit, behavior that servoy does for you automatically. And that means that you need to manage it yourself. So if you set this auto save to false, servoy will stop automatically saving the data, which means that servoy automatically stops saving the data. So you need to make sure that you do it. One of the ways you can do it is simply by calling database manager saved data. You can optionally save the current record that's there or you can save all outstanding changes since the last time you saved it. You can also save the data by saying set auto save to true. And then you can turn it back off again by saying set auto save to false. So by setting it to true, you'll automatically save all of the outstanding records, any edits, deletes, new records will be committed to the back end database. However, they won't be committed until you either call database manager saved data or you set the auto save to true. Let's assume that you set the auto save to false. And there's all kinds of things that now you can do with those records. So for example, one of those things is called rollback edited records and it doesn't take any parameters and it doesn't return anything. That's what the void means there. What it will do is every edited record since the last time you turned auto save to false will automatically be undone. One example of this usage would be if you have a form that's a dialogue box and you want to allow the user to create a new record and you have a cancel button. So before you show the dialogue, you would simply say set auto save false, show make a new record, show your dialogue that the user doing whatever they're going to do. And if they say cancel, you just say rollback edited records. And if they say OK, then you just simply say set auto save true and will automatically save whatever they've added or changed to that role data. Well, that about does it for our brief look at using the database manager. This is a very powerful set of functions and you should spend a little bit of time getting familiar with them. Thanks for watching.