Servoy 8.3 launch part 1: Stored Procedures
Servoy 8.3 launch part 1: Stored Procedures
Good morning, afternoon or evening, depending on where you are. And welcome to the Servoy 8.3 launch series, where Shandavana will be presenting in three sessions. Good morning, Shandavana. Hey, Jan. Can you hear me OK? Yes, the sound is pretty good. I think you have a new microphone, isn’t it? The sound is very good. OK. Jan sent me a microphone, and he likes to remind me how nice it sounds every time I use it. I think everyone will appreciate the quality of your mind. Right. Well, we have a very nice topic to cover today, which is a new capability of server 8.3, which is connecting to stored procedures in a very easy way. It used to be possible, but you needed to know the name and what arguments to pass, and then build a lot of code to data bind it to a form. And we’ve made that really easy and server 8.3. So that’s what we’re going to talk about today. If you do have any questions, feel free to post them on the right-hand side in the questions channel. Let me make sure that the webinar is being recorded as well. I think it is. That looks OK. All right, Sean, the floor is yours. OK. Thanks, Jan. So as is the tradition with these webinars, we’ll start with showing you something first. That way, if you don’t like it, you can leave before you hear me talk about it in show slides. And today, we’re going to do a three demos about the new stored procedure integration that’s available in Servoy 8.3. The first example we’ll look at how we can turn stored procedures to data sources. Then the second one will look at a little more involved example of that. And then on the third one, we’ll look at sort of calling back-end business logic using a stored procedure. So let’s jump right over to our demo. You can see here I have an example solution. This one is showing a form which is based on the results of a stored procedure, a simple one which is kind of blending different tables into one. So we have here a table that we call contacts. But it’s really customers, suppliers, employees. They share sort of common fields. So it makes sense that you might want to look for them separately. So I’m just going to do a quick search here. But in King, you can see that I get several records. But the contact type is different for each one, customers, employees, and suppliers. I can search further down and just find the employee. If I can type. And then you can see I narrow down to one record. So I think you get the idea, just for another example, I could do France. And I could show suppliers and France, something like that. Let’s take a look at how we produce something like this. I think it makes sense to start with the stored procedure itself, although that’s not really the point of this. I think it helps to see that this is a real procedure. In this case, it’s in a post-crustful database. So I have a function here called get contacts. You can see that it’s just defined as a simple SQL function. So of course, I could have done this in query builder in Servoy really easily. However, the use case for this is that maybe you have a bunch of legacy data sets that you’ve configured or coded, and you want to reuse them. So it will show you how easy it is to generate a data source from those. So you can see that it’s just unining different types of tables into one common table and returning that. I did create a custom data type called contacts, which has the fields in it. So that’s the stored procedure definition. Now how does Servoy integrate with that? If we come into our IDE, the first thing that I would point out is that whenever you’re looking at the settings for any database server connection, you’ll see a new property called enabled procedures. By default, this will be turned off. So if you want to enable procedures on your data source, you want to come in and make sure that this is enabled. After you’ve done that, you would see a new node in the solution explorer right underneath your database server. And this is for your procedures. Clicking on that, and you’ll see your procedures load in the lower panel. Now Postgres Google comes with a bunch of additional functions right in the main catalog there. But I’ll jump to our get contacts one right here. So you can see that my own custom procedure shows up here. Now if I want to take that and convert it to a data source, we have a nice feature where you just right click, and you select create in-mem table from procedure. Once you do that, you pick the solution and you give your data source a name. Of course, I’ve prepared this in advance, so I’ll just show you the one that I already made. So if I look at my in-memory data sources here, you can see that I have set up one already called contacts. So we take a look at that. This is what gets generated automatically. So I don’t have to come in here and add the columns. It just comes right in because it’s actually sort of looking into what the return type of the store procedure is. And it’s always going to return a data set, but it infers what the values will be in that data set. It could also return an array of data sets, which we see in a lot of legacy systems, particularly for like master detail types of things. But in this case, it’s just returning one data set, and it knows the column types from that automatically. The only thing I had to do is I came in here and told it which columns were going to be the PK. And in this case, it’s just a unique combination of the contact type and the name. So once I have that, I want to load my data source. And I’m taking advantage of an event here that was introduced pretty recently in Servoy 8.2, I believe, which is the onload event of the table. And in this case, it’s a temp table. So the on-founds at load gets called the first time you sort of touch that or refer to it. And it only takes one line of code. And what I would point out here is the SP here. This is a new kind of data source in Servoy.3. And I’ll give you some code complete just to kind of show you. So if I do data sources. Traditionally, we would see DB and then MEM for the in memory data sources. Now it gives us a new category SP for store procedure. I pick available servers. Of course, example data is the only one right. Have that checkbox enable procedures. So that shows up. And I get a list of all my procedures. And I narrow it down to get contacts. And you can see that the return type is a JS data set. And with any JS data set, you can convert that into an in memory data source or a temporary table with one extra line. to create data source. In this in this particular event, the name of the data source gets passed in. So you just sort of pass that through because that’s the one that’s being requested to be created. So that fills it. So the moment the solution loads and you touch that table, this gets created and populated when that procedure gets called. This is maybe a tangent just about the in memory data sources in general. But since Servoy8.1, they are available as design time objects. So if I wanted to create a form, all I have to do is make a new form. And you can see when I select the data source that contacts for the in memory data source comes right up. So once you have that defined, you can work with it just like any other design time. Data source that might be based on a real table. Let’s take a look at a second example that’s a bit more involved. Let me close all these here. Come back to the client and I’m going to jump down to a form called product summary. So the purpose of this form here is to show products and to show some key, some will KPI some key indicators about these products that aren’t available in the basic table. So you can see the total number of orders, the total sales and the total volume that’s been sold for a particular product. And that’s changing as I as I change this. Now, of course, again, these are based on SQL aggregate. So you could do it quite easily in query builder, but we want to reuse maybe we have thousands or hundreds of existing procedures. And we don’t want to rewrite them. We want to reuse them. That’s the idea here. So I guess we’ll quickly take a look at the procedure that we’re going to use and make sense to start there. I created one here called product summary. And you can see, again, this is just a fairly simple SQL procedure here that’s just going to return a new type, which I called product summary. You can see my custom types down here. So I defined the custom type and I wrote this equal to return that pretty easy to do. And again, when we go back to Servoy developer, we would look for our procedure, again, in our procedures node. I look for the get, I think it’s just called product summary. Yeah, the product summary. And again, I just created the in memory table. If I want to look for what that looks like. And this is down here, the mem product summary. And this is what got generated. So really these are the three aggregates that you saw there on the form. And they just get returned as a data set in this case for all the products. So this could be something that you call at the beginning and you cash and you just sort of hang on to or something that you refresh. I actually wrote a version of this that just takes one product ID for refreshing it. So let’s take a look at when the when the event fires. And again, this is very simple. So now we’ve done two two different data sources based on procedures the same way. On found set load event fires the first time it’s touched. And we again find that store procedure. And when we call it it returns the JS data set which we can convert to a data source. Now let’s take a look at a third more involved example. In this case we let’s say we want to run some business logic. And what we’re seeing a lot recently is some customers that are using using Servoy the way that it’s, you know, it really shines which is with database objects and data bound stuff. But they do have a lot of procedures for business logic that they want to take advantage for seeing this in particularly in the progress market for example. One nice approach is to use Servoy for the direct data binding, but then to also augment that with calling existing procedures. So we’re going to simulate that here by looking at orders. Let’s say that we want to let’s find an order with coffee on it. So here I have some some orders here with with coffee and what I want to do is sort of allocate this order so that it deduct from inventory. And you can see that there’s 25 units of coffee on this particular order. Let’s say we have a back end procedure that that does all kinds of complicated stuff checking, checking inventory, maybe doing some other, some other things and we don’t want to rewrite that. So let’s also bring up the same product in our product summary form here. You can see that there’s 20 units in stock. And on the order there’s 25 so we should go below inventory on that when we allocate this order. So I’m going to push the allocate button. You can see that it ran for a minute and then notified us that the order was allocated and now the allocate button is. It’s disabled. And if we go over to the product summary now, you can see that the number of units in stock has gone below zero it’s highlighted and read to indicate that it’s. That is unavailable. So let’s take a look at how that happened because that code was not executed in Servoy that was done in the back end. So that’s the biggest thing to do with beat to bring up the method that gets called when we push that button. So this is a Servoy method, which is happening right on click of that button. The first thing I do is I, I show that allocate order using the block UI. Then I simulated something which would take a while because it actually runs really fast and you don’t see it. But that’s just more for cosmetics. Right here is the line that that really does all the work. You can see that again, I use that data sources dot SP and I bring up. The data source and the allocate order function. You’ll notice that when I do that and I’ll just do that in code complete to show you. That you can see the parameter order ID is is found so not only does it show you the procedures not only does it look into. The data type that’s returned and the columns in a data set if it’s a data set. But it also looks at the parameters and the data type so we know that we need to pass an order ID. To you know specify which order gets allocated. So that’s all done for you by the ID. All you have to do is supply the parameters. So we’re using the order ID of the current record so that’s pretty easy. And it returns a data set in this case it’s not meant to return a whole table but all store procedures return a data set regardless. So even if the return type in the store procedure in this case it was just a. A boolean. It’s going to be in the data set so that’s why I did this get value at the end. Row one column one because there’s just going to be one one thing. And it’s going to be either true or false so that’s the success. Another thing to note is that. When you’re calling procedures if they’re doing things in the database. Servoy runs on on caching so if any records that you’ve already looked at. Or found sets are loaded. They’re already cached and it doesn’t know that they’ve changed in the back end system so it’s it’s kind of on you to remember. To deal with that if you need to and we provide a couple of APIs for doing that we’ve done a whole webinar related to this topic. But just suffice to say you know a couple too easy ways are to flush all client cash for a particular table. That will notify all clients of an update kind of a brute force it has them reload all those caches. The more nuanced ways the notified data change we just say hey here are the records that have changed that’s a bit more lightweight. In this case I just said to flush all client cash and said you know the product stable has been has been modified. And that’s why I didn’t have to do anything else when I switched over to my products form you saw the units on order was in red it was the flush cash that caused that and it was immediately updated. So after that I you know I just hide the UI and I show the little toaster but it’s really this one line of code here that does everything so that’s just an example of calling data business logic on the back end using a store procedure. Those are the three examples. I just want to go through a quick recap of what we saw and also let’s talk about the use cases first. So one reason we added this was not just because we thought it would be fun. We’d actually been getting lots and lots of requests for this. And the reason is I think is that I think that is we’re seeing a lot more applications that are being modernized as opposed to total rewrites. So businesses want to leverage as much of existing systems that they have. And so this is one area where you can reuse you know legacy legacy code. We saw the two examples store procedures as whole data sources. And I should say that I didn’t show what happens when you update one of these table and that’s certainly supported procedures can take a data set or multiple data sets as input parameters as well. And then the back end logic handles how those changes get processed. So we see a lot of systems lately that are I should say a lot but we’re seeing enough systems lately that are really like you don’t touch the database directly you go through procedures and all the everything is a data sets. We see that a lot in the progress community for example. A specific business logic functions that are run more for logic less about just data access. Another thing that I would point out is that there’s a lot of good vendor specific database functions out there. For example, Postgres has support for Jason objects it has support they have a whole extension for GIS or geospatial extensions. So you can do things like spatial queries. Show me all the records that fall within you know this box or show me all the records that were within a hundred miles of each other that sort of thing. There’s there’s all kinds of built in functions for that and you saw all those list of system functions if I were to add one of those extensions to Postgres you would have seen those come up right there and I’d be able to just call them directly right from the IDE. So that’s another added benefit of being able to see these functions directly. So real quick we’ll just go over what we did. The first step is you got to enable procedures it’s not enabled by default so you go in and I highlighted the wrong one it just occurred to me there I’ll fix the slide. Should be the one below it that’s highlighted in in yellow enable procedures not just enabled. Wouldn’t be a webinar if I didn’t have one of those in there. The next thing that will happen is you’ll see that on the solution explorer the procedures node is available if you click on it down below you’ll see the list of all the procedures that are available on that data source. And you can find the one that you need if you want to create a in memory data source from it right click is your friend. And you create the mem table and you’ll get something like this here where it finds all of the column names and the data types do remember that you have to set a row identity or pk it doesn’t know that just based on the procedure. At least I don’t think that’s possible to know that I might be wrong. After that you need to call the procedure if you want to load it as a data source the way that you call it procedure programmatically there’s a new sp data source type so you’ll get that in code complete when you do data sources dot. And that will call the procedure directly it’ll also suggest what the parameters should be and these always return a J as data set doesn’t matter what the procedure definition is it will return a data set even if it returns a single value. that’s actually an actual data set from there it’s one liner to create a data source from a J as data set and from there all the design time stuff applies to those as regular data sources. So where you execute business logic the syntax is exactly the same in this case we were passing an order ID so that’s going to show up as one of the parameters. And we didn’t return a data set we’re not we’re not binding that result to do a data source in this case we’re just running some logic so do remember to flush cash it’s not going to be updated automatically if you know tables have changed in the back end. that’s really it we’re happy to take questions now all leave these links up and. Servoite eight dot three is out is a release candidate I really recommend that you download today and try it out and get your any bugs that you find submitted before we before we go with final release. Thank you, there’s a couple of questions coming in and feel free to continue posting questions as we go through them. And the first one is from Chris is there a way to bind a store procedure to a data broadcast for a specific table or set of tables. Right i’m not sure I fully understand you can you can call a store procedure and then you can broadcast the change so if you’ve called a store procedure and you know that it’s changed something in the back end. The plugins dot raw sequel dot notify data change it’s in there in the one example that I showed that will initiate a data broadcast to to all clients so that that’s probably the way you want to go for that. Yeah, if you want to do the other way around so if you want to if you want to trigger a store procedure when a data broadcast event is. There’s there’s an event for that yeah it’s on the solution level on data broadcast. Those are events that you want to use with caution that you can if you have thousands of users on your flushing the cash all the time and then that’s going to have an impact on your performance because your app server will go back to your database and re query it every time. So with great power comes responsibilities make sure when you’re using the powerful events that you know what you’re doing. Do you have more questions. Kim is asking is row. I dense equal to primary key in you in 8.3. I don’t think it is is it. Row identity and primary key are separate although on the memory data sources you get to say what the primary key is because those are totally. user defined. So typically if you if your table doesn’t have a primary key it won’t come in you don’t get to set up you do get to say what the row identity is to let. Let Servoy know what what columns make up a unique row. In this case you can use either row identity or primary key. Because it’s user defined. So this is the question from Paul. Can this be used by all clients. Yes, this is available for ng client and the classic web client and the smart client. And headless client. Because it’s all happening at the back end. Okay. Is there an easy way to see which features work with which clients in Servoy. I don’t know if that’s related to the topic we showed. Almost everything is supported across the board but there are some exceptions and I think the answer that is no it’s not too easy to see unless you look at a specific. Function and then it’ll tell you what is supported and it should tell you what it’s supported in or what it rather what it’s not supported in. So for example, plug-in.dialogs. Or showing a form in a modal dialogue. Works differently in the legacy web client but those are few exceptions. Excellent. And actually one of my favorite features is going to be showed on Friday which. Initially we couldn’t support an ng client which is a client design mode which we had in smart client and in web client. And it’s now coming to the ng client but for that you’ll have to tune in on Friday. And what’s the topic on Wednesday, Sean? Right. So Wednesday we’re going to do the multi-tenancy so we’ve added sort of built in support for multi-tenancy. And also some performance enhancements around that. So if you’re interested in multi-tenancy then I would tune into that webinar because we’ve had some changes. Cool. Well, I would look like all questions are covered. We will be posting this recording to our website very soon. And as Sean mentioned please do download server 8.3 and give it a spin. Even if you don’t plan to use it very soon. It always helps in getting us feedback before we do an actual product release. And we look forward to seeing you on Wednesday and Sean. Thanks for presenting. Thank you.