Building a SQL Runner
Thank you for joining me for this video. For Servoy University, I’m Bob Cusick. In this hands-on video, we’re going to build a solution that will let you run SQL queries and see the results all on one form. They get started. I’ve created just an empty solution called SQL Runner, and we’re going to make a single new form. Let’s go here to Forms, Create New Form. We’re going to base this on no data source, and click OK. I’m just going to call line main and finish. OK. We’re going to need two fields, one to hold the SQL query, and one to hold the SQL result. But since this isn’t based on any database, we’re going to go ahead and create full and variables for that. To do that, let’s go and expand the Forms node. Right-click on Variables, and say Create Variable. I use FV for Form variables, and let’s do this one. This will be Query. And we’ll go ahead and we’ll make another one. Form Variable Results. Now that we have our two fields made, let’s go back to our main, and let’s add them to our form. I’m going to double-click this so we can, there we go. So I’m going to add two fields. Go down here to Text Field. Drag it out. We’ll change the Data Provider. We’re going to make them form variables. This will be the Query. We’ll actually make this a text area so that we can enter more data than one line. We’ll change the scroll bars. Horizontal Never. OK. And we’ll just make it a little bit bigger here on our form. Go ahead and anchor this to the right as well. So it will grow as the form grows. All right. So this will be our Query. We can put a little A on there. All right. So this will be our Query. And now what we’re going to do is we’re going to duplicate this. So I’m going to hold down like Control key. Control Shift, drag. All right. And this will be result. And we’ll drag this to be a little bit bigger. It’ll be down to the bottom of our form. OK. That’s all good. So now we have a Query, a Place, do the Query, and a Place to hold the result. And we’ll add one button on here that will call Run. So we’ll call this one Run. And it will have a new on action method. We’ll make a new form. We’ll call it Run Query. And I’ll make it a private. Click OK and show. So let’s go ahead and add some code here. What we’re going to do first is we’re going to check to make sure that that form very will query has something in it. And then we’re going to go ahead and use the database manager call so we can come down to the database manager. And we’re going to use data set by Query. So here are all the things that you can do. And we’re going to get a Query, a database manager, get data set by Query. And we’re going to do this one. So for the server name, we’re going to just do queries against one particular data source. As an extra challenge, I would leave it to you to go ahead and make another form variable and a pop-up list that has the different data source names. And I’ll leave that up to you. But right now, we’re going to just use the example data. So for a server name, example data, SQL query is going to come from our form variable called query. Arguments were not going to have any arguments. So this needs to be a blank or array. Max return rows. If you use the constant negative 1, it will get all of the rows. We’ll create a new variable to hold that data set result. So we’ll call it. Instantiated is null. And we’ll have the number of rows as a variable. Let’s move this out. I’m going to shift have it. We’ll move it all back one. So we’re going to set the data set variable to this. And then what we’ll say is if, actually, we’ll just do n rows equals ds get max row index. That’s the maximum number of rows that are returned by the query. And we’ll say if n rows is greater than 0. I’m going to do some more processing on it right now. OK, so far so good. We’ll go ahead and clear out our result. And then we’ll actually set our fv result equals data set. And we’re going to say get as HTML. And we’ll just make it the most simple kind. OK. And let’s see what we have. Let’s go back to our main. Save, and I’ll go ahead and run it in the smart client. All right, so let’s go ahead and run it. I’ll do, select asterisk from customers and click run. Whoops, it looks like I made an error here. I forgot to reassign that field. So let’s come back here. Change a data provider from the query to the result. Perfect. We’ll save that. Let’s try it again. Select asterisk from customers. Oh, that’s another mistake, right? We made it a text field rather than a text area. And you can see here that it’s going to bring back everything in a starting with a table. So we need to add some HTML headers to that as well. So first of all, let’s go back and change our field type from text area to HTML area. And we’ll make it non-editable so that editing thing goes away. We’ll save that. And then we need to go back to our code, and we’ll add an HTML wrapper around there. So we’re going to say we’re going to add a constant HTML. And I like to do well for HTML. So we’re going to put a head element in there, and then our body. Plus this. And then we’ll close it out as well. All right. That ought to do it. Save that. Let’s come back. Let’s just have an error. Here’s where the error was. All right. Let’s save that. No error, perfect. Let’s go back to our smart client and run it. From customers, push our run button. And here we go. We have an HTML table now that has all of the rows coming back. There we go. That’s better. So there you go. You built your very own SQL runner project. There are different ways that you can enhance this. Obviously, you can reformat the HTML as it comes through here. But I’ll leave that for your exploration. I hope you enjoyed this video. Thanks for watching.