Thank you for joining me for this video, Raw SQL plugin. For Servoy University, I'm Bob Cusick. Now the Raw SQL plugin is a very powerful plugin that allows you to execute any SQL statement against any of the databases in your database connections. That means it's extremely powerful, but it can also mean that it's extremely dangerous. So it's up to you as the developer to make sure that you know the ins and outs of your SQL schema, and that you also know about maybe other processes or websites or other interfaces to the same database before you start messing around with either the data or the schema. Having said all that, the Raw SQL plugin only has a few functions. The first one and the most obvious is the Execute SQL. So you simply pass it a server name, a table name, the SQL that you want to execute, and any arguments if you're using a SQL prepared statement. I will have a look at that in just a second. You can also go ahead and execute stored procedures. A stored procedure is a function that stored at the database level inside the database completely outside of the Servoy War environment. Many pre-packaged systems and larger CRM packages have lots of stored procedures that they use in their code rather than hard coding it into, for example, web pages. This function flush all clients cache, you pass in a server name and a table name. Now you really want to use this one with care because you can really affect the performance of all of your connected clients. What this does is it invalidates the local client side cache, all of it for that server and for that table. So if you have multiple forms that are based on that server and table, or you have multiple found sets based on that server and table, all of those will be in effect wiped away. And all of your clients then will simultaneously hit the server to re-get their found set. So it will require you to get the first 200 primary keys, re-queer you to get the first 10 rows of data, and so on. And all of that's going to happen kind of at the same time. So if you have lots of concurrent clients, unless you really, really, really have to, you should try to avoid flush all clients cache. Sometimes you can't get around it, and that's why it's in there. Get exception gives you the full exception object that came back as a result of re-queer. So if something happens someplace, you can get all the information about it, including a stack trace on what went wrong where, and where you were in what method when it happened. Also great for logging. Then you also have the notify data change. And you pass it a server name, a table name, and a set of primary keys, and also a number that indicates an action, for example, insert update or delete. Now what this will do is go through the normal servoiter of broadcast engine, and broadcast it out only to the clients who are affected. So if somebody's looking at a form, and their found set contains those rows that were inserted, updated, or deleted, their cache will be updated, and their screen will update. So to put this in a perspective, I've created a very simple sample solution. So let's take a look at that. So here I have a server, a table. I have an arguments, a SQL statement, and an execute SQL button. So the execute SQL command takes in a server name and a table name. Now in my SQL command here, I said select asterisk from companies where city equals question mark, and that question mark will be filled with the parameter of London when it gets executed. This is called a prepared statement. And when you're doing SQL queries inside of servo, if you're writing your own SQL, it's the best practice to always use prepared statements. I have intentionally substituted companies for customers, so it will error. So I can show you what will happen when you get the SQL. So it says that company does not exist because the true name is customers of the table. So when we executed it, it executed successfully, but it didn't really do anything, because that's not what the raw SQL plugin is really made for. If you wanted to do this type of query, you would use the database manager, get data set by query. And that would actually return the results of your query, which you're going to want to do with the raw SQL plugin, are usually adding a column, deleting a column, adding a table, dropping a table, maybe you're going to do a very complex update through a very complex series of joins, or something that's going to affect a lot of rows. And that way it will get done quickly, and each row won't have to be update through the Servoy application server. So I have another query here. And it says, update customers set the city equals something where the city equals something. So what we're going to do is we're going to replace everything that is London to the city more park. And I'm going to separate that with a comma. So what we're saying is update the customers set the city equals more park where the city equals London. Now let's go take a look at our data real quickly to see what this is going to do. So here if I do a find or London, you'll see that there's six rows here. We're going to change all of those into the city more park. So let's go ahead and execute my SQL. And it says successfully executed. Now let's go back and take a look at my data. So I'm going to do a find for London. There should be no records that match, which is correct because we changed them all to more park. And now if I search for more park. Oh, gee, what happened? Well, it did change the data in the underlying database to more park because that's how we were able to find these rows. But if you notice the city still says London, the Servoy client cash doesn't know that those records has changed. That's when you need to use flush all clients cash or you can also use notified data change as well to tell the application server that these rows with these primary keys have changed and then it will go ahead and update the local client cash. This is also something important to know if you have outside processes that are also working on the same tables as your solution because Servoy doesn't know that things are happening to the data other than the changes that you make within your solution. And it won't broadcast those changes and update the client cash unless you tell it to. Let's get back to our example here for a minute. The raw SQL plugin is actually the wrong tool to use for this kind of update. And it's the wrong tool to use for that select SQL statement that we did earlier. Why? Because there are other functions within Servoy that will allow you to do that. For example, let's come back over here to Servoy developer for a moment. And let's go to our database manager. If you're going to actually do an update like that where you wanted to change the cities, you would use the database manager function get found set up data because the found set up data once you get that object, then you can actually perform that update. And so what will happen is if you perform that update, it will perform it across all of those rows just like you wanted to. But what will happen is because it's going through a survey, it will automatically data broadcast those changes to the affected clients. Likewise, if you're going to do a SQL query and you want something back, you would use get data set by query. And that would return to you a data set object which also contains its own functions and properties. So let's take a look at some of the code behind the example. And I've also moved some sample code there and we can talk a little bit more about what the raw SQL plugin can help you to do. So here it's a very simple call. I just have form variable set up for the server table, the SQL I'm going to execute. And then I use the split command on the arguments to turn it into an array. And if it's a success because it returns a Boolean value, then I put into my form variable results successfully executed. If not, I use the raw SQL plugins get exception to get the exception object. And to show you what's available here, I used get message, but there's other things that are there. Let's go ahead and erase that. And then you can see that there's stack trace, fill and stack trace, get cause, localize message, message, and it cause print stack, set the stack trace, all kinds of things that you can do with that error object. Generally, you're going to want to get the message and that's about it. Now here in this fictional function called tester, I've actually moved some of the sample code from the execute SQL and other commands over here so that you can see what they are. Now the first thing that you'll notice is this big giant warning comment that's there. So because you can do real damage to your back end database, that's undoable like drop table, drop column. And none of those things is undoable. So it's just saying, hey, make sure you really know what you're doing before you issue these raw SQL commands. Here in line 60 is exactly what we did in our example, except these are hard-coded rather than coming from formed variables. So it executes the raw SQL. If it's successful, it flushes all the clients cache and again, it passes in the same server name and table name and then it uses the get exception dot get message. When you're talking about working with stored procedures, it gets a little bit more complex because there are more parameters that you need to be able to to turn on. And so here you have to define the data types that are input or output and then at the bottom there once you get your data set back, assuming that your store procedure returns a data set, then you can do things like loop through them and get a total. And then down here finally is the raw SQL plugin notify data change and you'll see that the action can be primary keys were deleted, they were inserted or their updates and again, this will allow you you to do individual changes via the raw SQL plugin to individual rows and then if you keep track of those primary keys that were affected, you can pass it through the Servoyapplication server data broadcasting mechanism and it will automatically go out and inform just those clients that needed. So that's about it for the raw SQL plugin. Use it with care but it's a very powerful tool that allows you to do anything against your backend database. Thanks for watching.