30 minutes with Servoy part 5: saving data

Hello and welcome everybody to number five of the very popular series 30 minutes with the Servoy in which we within 30 minutes or less briefly discuss important topics in the Servoy programming environment. We talk about new components that we are releasing as open source of three components and all the interesting things that you should be aware of as a Servoy development. Today and again a very interesting topic before we get started very briefly Servoy world has been announced last month and these are the last weeks that you can still get the early good discount to get to Amsterdam so it’s May 17 to 20 really the greatest place to learn about Servoy and meet other Servoy developers, meet the Servoy engineers, meet Sean, the main presenter of the series so far and interact with other Servoy developers. It’s going to be announced in that great location in the center and also keep in mind that right now flying to booking flights to Amsterdam from most parts in Europe and other parts in the world, including the US is pretty cheap. You can have some flights from the US as low as $350 two ways to Amsterdam, so it’s a pecking thought. To find your flights, I recommend flights.google.com really gives you a flexible and easy way to find all the different flights and how much time you wish to spend my plane. All right, with that out of the way, let’s get started. We have 28 minutes left. I’m going to hand over to Sean who is going to present to this session number five. Welcome, Sean. Thanks, Sean. My screen is visible. You can see the slide. Yes, thank you very much. Okay, just checking. Yeah, so this is number five in the tech series which we started towards the end of last year and apart from a holiday break, we’ve been really sticking to the every two weeks schedule and we’re going to try to do that no matter what life throws at us. So today January 11th, we’re switching it up a little bit. So in the past, we’ve been announcing open source modules and web components and new stuff and we will continue to do that. However, we also want to make this a bit of a learning, not just about what’s available, but also learning regular things and Servoyand best practices and how to do things. So we have a topic today about saving records, saving data in your applications. And this is really coming from my experience and my colleagues experience when we work with customers. A lot of times we see problems just in the part where they want their applications to save a user’s edits or something like that. So today we’re just going to be focusing on a few key topics there. We’re going to talk about handling failed save. So when you try to save data, but it’s maybe rejected by the database, we’re going to be talking about using database transactions and sort of the correct way to do that. Also, if you choose to do record locking, the correct way to do that and to deal with conflicts. And we’ll talk about doing batch updates as well. So first, let’s do a bit of demo and then we’ll come back and review what we learned. So I have in my browser a small little application that I’ve built and you can see that across the top, I have four different save icons because we’re going to go a couple examples. In the first example, when I edit something in my screen here, in this case, I’m going to choose a product that is already on the order. Now this is the sample database that ships by default with Servoy and this particular table for the order details has a composite keys that you can’t have a duplicate product. I don’t have any business rules in my application. This is just to make an example of when I try to do something as a user and maybe this should have been checked by the programmers first, but for whatever reason it gets through and we try to save the database is going to reject it because it’s a key constraint violation. In my first example, I click save and nothing happens. The record not saved, the user doesn’t know that it wasn’t saved. Nobody knows that it wasn’t saved. The fact the record is still has that change on it in memory, in the in memory transaction in Servoy. So I can cancel this and you can see that it rolled back from to the apples. Now this, despite how bad it looks, is quite common and I’m going to switch into the code for just a minute and go to the first save method here called save one. I see this all the time. Just database manager saved data or maybe save data with a record or a found set because you can save everything that’s in the in memory transaction or just a particular record or all edited records in a found set. So what happens when when save data returns false, it doesn’t it doesn’t throw an error. It doesn’t alert you. There’s nothing that you see as a user. It just this method should it does return a boolean and it would be false, but in this case I haven’t even I haven’t even chosen to handle it. I just often basically say I’m going to save data and everything’s going to be okay. The only indication that there’s a problem is if we go to our log and we look at the server log you can see that there’s some some indication that a duplicate key constraint was violated. And so when I’m helping customers troubleshoot a problem, we’ll typically start with some some something that happens. Maybe that record wasn’t saved and later there’s another problem and it’s not even evident that that record wasn’t saved at first. Something else later happens that now becomes weird and then we go okay well let’s let’s look at your log. Let’s see what’s going on. Oh we see this this error. When does this happen? I don’t know when this happens. You know, so long story short, save data returns a boolean. You should always use it. You should never call save data without evaluating if something actually saved. So let’s go ahead and do a let me scale this back down again. Let’s do another save here. This time I will again break the database rule of selecting the something weird here with my type ahead. There we go. So I’m going to again break the duplicate key constraint and I’m going to try to save this time I have a bit of error handling. Now and I’ve printed it out to the user. It’s not the most beautiful error but this was not an error that I expected. This was an error that came back from the database and at least I know immediately there’s a problem. And I can call tech support or whatever. It’s evident that for whatever reason the record failed to save. Let’s go in and look at the code for that. This time we have save to which is hooked up to that second save button and all I’ve done is I attempt to save I put this in an if statement and if I fail to save then I have some code here to notify that there’s a failed save. When when database manager saved data returns false. The next thing you can do if you want you could just print up a message to the user and say you know record failed to save for an unknown reason or something. If you want to inspect it a bit you have complete control over the in memory transaction that’s happening in Servoy and you can use to get failed records method of the database manager API and you can iterate over those records and every failed record. Should have this exception object in the case of a failed save it’s usually a data exception which is a specific class of exception which has a method so I did a cast here and you can see some of the. Some of the. The methods that are available for that kind of exception. In this case I’ve chosen to print either the value or the message in the exception sometimes it’s one sometimes it’s the other so I picked either one that’s not null and I printed it out to the user. Using the web notification service. So at least at least you should do you know if not you know save data. Show a message throw an error whatever but don’t just assume that it saves and everything was good because you won’t find out right away you’ll find out later and it will be really hard to trace back to the original problem. Okay. Let’s do another example. This time I want to show how. How you can also handle errors on saves that maybe you produced on purpose. In the case of table events we can we can also generate save errors and then handle them in the UI. I’m going to open up my order details table. Using the resource locator and in the table editor I’m going to go to the events on that table and you can see that I’ve implemented two. Event handlers for inserts and updates. There’s also one for deletes as well. These events fire both before and after so the on record insert update the lead happened before. The changes actually submitted to the database through SQL and then the after events happen afterwards that’s I think a subject for a different. I’ve been our someday after record events are usually used to handle cascading changes that you might want to do when when a new records created over here maybe I create. You know five related records over here or something like that. I’m just talking about the pre events which allow you to to actually block the action from happening which will result in an error. So we’ll put either one of these because I do the same thing in both of them. And you can see that on the insert on record insert event handler passes in the order details record and I’ve created a little private method to validate order total. There’s an arbitrary business rule here that that the whatever I’m modifying on the order details that the the total which is a calculation on that order can exceed 50,000. So if I change the unit price or the quantity or an item it doesn’t matter either of these events will fire and then I’m going to say you know if the order total is less than 50,000. So I’m just going to throw an error here. Also returning false will will also block the the sequel for being executed but in that case I don’t get this message back on the record exception. I would just get a save data failed generic thing and I can’t actually see what the problem was. And then your last line of defense I’m not saying that this is the best way to do all validation and gives you great UX but I have a business rule. I can’t exceed 50k on an order and I don’t want to allow any data change that’s going to this going to make that happen. I could maybe handle this earlier and and not wait till they save the data but that’s that’s a different topic that’s just about UX this is about the our business rule. I won’t go into the database and it will produce an error. So back in my my save data where my save to method the one that’s actually checking to see if it failed. When I when I iterate over those failed records, even though the database doesn’t reject it, I will still get that error back in my records exception and I’ll be able to show to the user. because it’s coming for me. It’s a lot nicer than some you know SQL exception from the database vendor. Let’s try it out. So again, I actually haven’t I still have these duplicate products here on this order. The save never happened. I could do a revert and you would see it would go back. But again, I’m going to select the duplicate. Oh no, we’re doing the the business rule. Sorry. I’m going to change the quantity on this to from four to four hundred. I’m going to tab out and you can see the calculations fired my order totals gone above 50,000. I haven’t yet tried to save so that doesn’t produce an area yet. But now when I do the the save event, you can see that I change in items cost the order one oh two four nine to exceed the 50k limit. And so again, the SQL was never sent to the database. The record is still modified in the in memory transaction and I can go and I can revert it back to four and you can see that it went back down. So that’s an example of how your own errors can also be attached to that record in that exception when saved data returns false. So that’s a good start. However, there’s a situation where the way that I’ve written that still might give a bit of a inconsistency in the UX. And the example that I’m going to give is I’m going to modify the the sales rep and I’ll change it to Margaret Dcock and I will also exceed the 50k limit by making the quantity four hundred again. And I’m going to try to save using that same that same method. So I get the I get the error which is great. However, now I’m going to go ahead and say OK, I’m going to cancel this and I roll it back and you can see that the quantity went back to four. But the sales rep did not go back to the original value. That’s a different record. This is the order record and this was the order detail record. So it’s important to know that when you call save data and you have more than one edited record, it’s going to it’s going to commit all of the records that it can and any records that fail are going to be marked as failed. So if you had five edited records and four of them committed successfully and one of them failed, save data will still return false and then you’ll be able to look at the failed record. Now in some cases that’s ideal in other cases it can give a bit of a weird UX and I think this is one of those cases because I assume that when I click save that nothing’s going to happen if there was a problem. In fact something did happen I say if one record but not the other. So the only way around that is because you don’t know that a record failed until you try to save it, the only way around that is to do this inside of a database transaction. So that if any of them failed you can go ahead and roll everything back. So let’s do that. Let’s do this change one more time and I’ll save it through a transaction. You can see that everything was able to be rolled back. So I’m going to go ahead and change the sales rep again to Janet Leverling and I’m going to exceed my 50k limit again and now I’m going to use a third save method. And you can see I get the same error because I’m running it through the same notification subroutine. But now when I roll back you’ll see that not only will the quantity go back but the sales rep should go from Janet back to Margaret P. Cock or Andrew Fuller that was the original value and you see that it has. So even though one of those records the save was okay it still wrapped inside a database transaction. So when the other records failed and I caught it I can roll everything back. Let’s take a look at the code. So I have a third method called save three that’s hooked up to that third save button. And in this time I am using the database manager API to start a transaction. Now when you start a transaction it means that everything that happens within that transaction can be committed or rolled back together. One of the first things as far as best practices goes that the moment you open a transaction you should surround all your code in a tri-catch or try finally until the transaction can be until the transactions rolled back are committed. Another best practices that you shouldn’t open a transaction that you don’t intend to close in the same thread meaning don’t have a button that runs a method that opens a transaction and then the thread dies and then the user is now in an open transaction they can do other stuff and you’re relying on some other event to close it. They should open and close and transactions should be open as briefly as possible. So you don’t want to open transaction, run a bunch of code and then try to save data. You really want to open a transaction right at the moment where you’re going to save things and then you can then you can commit it or roll it back. So I do the tri-catch block now I do the same thing as before I call save data in an if save data fails I warn the user and then I roll back my transaction. Now roll back transaction has some options you can see I put false false. If you want to also roll back edit records and revert records that actually would have been saved. You can do that in this case I chose false because I will leave all the edits on the screen and then I can manually hit the cancel button to roll back it also makes for a nicer demo. So I’ve handled the failed save I notify the user and I rolled it back. If this never happened I move on to the next thing which is I try to commit the transaction. Now commit transaction can still return false if there was a problem. In that case I want to throw another error because I still want to make sure that the transaction gets rolled back. If everything went well the transaction was committed I notify the user that there was a success. Down in the catch block I can handle any runtime error and roll back the transaction and notify the user that there was a problem. I should point out that I’m not just catching the error that I threw here I’m catching any runtime error. So if I have a programming mistake and at some point I get like a null pointer problem whatever that happens that will kill the thread unless it’s caught. If I open the transaction and then I had some JavaScript error the thread dies and the transaction is left open and now I could get deadlocks on my database and that’s not good. So that’s why I’m doing a catch the moment I open it. Another way this can be done is with a try finally some people will roll it back in the finally. So that’s a matter of personal preference. The point is that you’re rolling back either in the catch or the finally. If there’s any problem. Okay so that’s saving and transaction. I have another example some people are using record locking. So I want to make sure that that is also demonstrated a best practice. So, record locking is where you have two different client sessions in an application server and maybe they’re both looking at the same record or maybe it’s just a batch operation that’s doing things programmatically doesn’t matter. One client says I’m going to get a lock on this record or this group of records in a found set. And at that moment you get a lock another another client cannot try to modify those records. Now you can modify them in the in memory transaction. That’s fine but you can’t save any modifications if a modified record has been locked in another client session. So I want to simulate how this this might happen and to do that I need two clients. So there’s only one debug client and developer of each variety. Someone used the smart client and I have that running here with another form. And I’m looking at the same record the the order to bees beverage and monster Germany and I’m just going to click the lock. And that has acquired a lock on the record. Now when I go back to the client here and I attempt to save with this fourth method it’s going to fail because there was a lock. You can see record is currently locked. So if I switch back to my smart client let’s say whatever operation was happening here it released the lock. And now I can come and I can save. And it would say I’ll change a saved so the lock wasn’t blocking the editor from happening. So let’s take a look at the code briefly. Coming here to my save for method one that does locking. And what I’ve done is I created a little subroutine here to acquire the lock with the timeout. This is just a trick that I like to use because in actuality locking should be just like transactions should be as brief as possible. You don’t want to lock a record and then do a bunch of other things unrelated to that record. You want to lock it and then save it and then release it. So I’ve locked the record in a timeout here which means that if I fail to get the lock the first time I’m going to keep trying up until a certain limit and then I’m going to just give up. I think that’s a nice way to do because it may be that the other client that locked that record was just chugging through a list of records locking them doing some things and saving them. And that lock maybe was acquired for just a few milliseconds but I happen to get it at the same time and it caused this error in my application. And what I’d really like to do is just wait for a few seconds or a few milliseconds whatever it is and then try again. So I made this little method which takes an argument for the number of milliseconds and returns to your false if it was able to get the lock. And really we’re just calling database manager acquire lock on this found set in zero indicates that it’s the selected record we want to try to lock. So just like the other methods on that API acquire lock returns a boolean and it will be false if you weren’t able to acquire the lock. So we’re not throwing errors in the core APIs of Servoywhere we’re letting you handle it and choose to throw an error or not. So back to my save for method if I don’t get the lock within the time out then what I’m going to do is notify the user otherwise I’m just going to default to the other method where I saved with the transaction. Again you want to always do things in a try catcher try finally here I use a finally because I don’t care what happens I just want to make sure that the lock is released after I save. Okay so let’s change the time out and I don’t know what we’ll do something really long 30 seconds which will give me time to unlock it while it’s still trying to save and you can see what that’s like. So I’m going to go to first my smart client I’m going to lock it there so now the records locked and I’ll go to my web client here and I’ll change the I don’t know the sales rep. and now what I’m going to do is I’m going to try to save and I have 30 seconds before it’s going to give up so. Well it’s trying to save I’m going to switch back and I’m going to unlock this as I switch back here you can see that the change was committed so as soon as it was trying it was waiting it was waiting as soon as that lock was released this client was able to grab it and make the change and commit it. So that’s one way to think about locking so you can you can do it in a time out so that that you can you don’t necessarily fail if the first try failed. I should point out that this this was going through the other save methods so even if I had a problem say I put this to again to 400 I break my 50k rule. I’ll change this to Margaret Peacock and now I’ll lock it over here. And I save it over here and it’s waiting and I come back and I release the lock you can see that here. I still got the air it still went through all the same channels in the table event was called on record update and it also happened in transactions so now if I cancel you’ll see that the sales rep will also roll back to so it’s still. taking all the other best practices that we had in the other examples. We’re running up on the time limit there’s one other. One other thing we wanted to go over which is doing batch updates because I see this sometimes as well and I want to just point out a couple things in our APIs. So I’m going to go to a different form here the products form and I just have say I don’t know arbitrary business case that if I change the unit price on the product I want to go and take all the orders where that product appears and update the unit price on the on the order details. It’s an arbitrary thing but it shows us an example of a batch update. So I’m going to change the unit price to to a hundred and and tab off and you can see that I get a question do you want to update all the order details referencing this product and I click yes. And they were all they were all updated to a hundred I want to show you the code because there’s a bunch of different ways to do this and I want to show you what I’ve chosen to do. So I’m going to go to the on data change handler of that particular unit price field. And I want to show you what I’ve done. Once they confirm it I have an entity method so it’s actually implemented on the product found set itself so I’m separating my logic from my UI a bit. And so if I jump inside this update order details iterative drift method you can see that I use the get found set update or API. And then I use the iterator and so basically goes over every record in the found set and sets the call. The the found set update or API has kind of two different ways to use it. I’m using the iteration way and that’s the reason I’ve done that is it’s a safe iteration so the records that I get is not going to change. If I do something and it say it’s sorted by unit price and then it shows up in a different place or I’m deleting records and then now it’s changing the actual size or selected index. This is like a safe iteration so it basically just goes over the list and I can change every column. I should point out that that this does it on those records in memory and nothing is committed to the database yet. So when I come back here and I choose to save I’ll just choose with my third save on that one that does the transaction. You can see that some some business logic was still fired. And I believe actually I think that was over here. The role this back which roles everything back because I don’t think you should have fired yet so I do the 100. That’s the reorder level I do the 100 and then I say yes everything was changed but it’s not yet saved and now I can save it. I do have one of these that exceeds 50k. I don’t see which one it is but one of them does. The point is that it only updated the records in memory so now when I save everything still went through that table event and so one of those orders was violating my rule so I can’t save it. There’s another way to do the update you can see that I’ve written a second method here which also uses the iterator but it are the update or but it doesn’t call while updated next. It just does set column and perform update and that’s entirely different although the outcome is the same. It actually does a sequel update like update. Order details were product ID equals my current product ID so that’s one query the performance is much better there’s a lot less overhead. The downside is is that it will happen it will happen immediately without doing any table events or auditing that I might have set up so if I switch my implementation. On the data change event handler and I do the immediate one not the iteration one. So I’ll set this to something really big like 10,000 and I say yes. You can see that a lot of orders. That was 1000 will do 10,000. So we really make a lot of orders to break that rule so now a lot of orders have broken that 50k limit the data is actually saved if I click roll back nothing happens. The data saved immediately and I didn’t get a chance to run it through my table event so that’s just one thing you should know if I look at the query statistics you can see. You can see that that update. Down here where it’s it’s updating order details and saying the enterprise where product ID equals. And that’s that’s a much more efficient query than looping through the found set but the downside was just my business logic got short circuit so you got to know that when you choosing one or the other. Okay, we’re really running over time I was going to kind of review this stuff in slides but I’ll just post the slides on. On the forum it’s really just kind of reviewing what we covered I want to leave time for questions you on do we have any coming in. Yes, we do. There is a question from on some data and this question is does all this mean that as best practice you would avoid using auto saved mode on. Right yeah I kind of forgot to mention that if you look at the. At the on solution open event handler for my solution so. Over got to get my go to meeting thing out of the way so over here in the in the properties. You can see that I handled the solution on open event and I’m actually. Setting auto saved to false right here the other stuff is just ignore that right here I’m setting the auto saved to false so. Auto commit is on by default meaning your records will get saved kind of as you click around you don’t need to explicitly call save data. But that’s kind of out of scope for what we want to talk about because a lot of people use auto save and when they do I see a lot of the save data without handling and all that stuff. So would you consider it best practice service turn off by default and do your own error handling or. Well it’s up to you there’s the table events to apply and if auto save is off though then you have to handle errors differently and most common I see that the auto save is. Sorry when auto save is on you have to handle differently most common I see that people turn out to save off because they really want control they don’t want the user to have the experience that everything they do is saved but it’s really a UX thing. Okay. There’s a question from Steve. And by the way if anybody has lost me questions that posted now on the questions general. This question from Steve is the sample solution available for download. Yes Steve I’ll just click forward to one of my slides here that we should show. So some useful links there’s a link to this series where you can get a recording of this. Questions and ideas and feedback can be posted on the form or emailed directly to us. And this example solution I will upload to the GitHub repository where we put example solutions. And that’s that third URL right there. It’s where we put examples from past webinars as well. I really encourage you guys to make GitHub accounts and when you encounter some of the repositories that we’re sharing in these webinars. If you have a GitHub account you can watch a repository and you can be notified when things change as well.

Close