7 hours 36 minutes
I welcome to Module four. Sequel programming to this is less than three concurrency and transactions were moving into sub lesson 3.3 roll back and the previous lesson we took a look at using transactions with rollbacks. In this lesson, we're gonna take a closer look at the role back and really showing
that the committee is working when there's not in there. And the rollback occurs when there is an air, and I've created a simplified procedure to really display that functionality.
Now in D Beaver, if you want to create a new procedure, it's very easy. You just right click
on the procedures select, create new procedure and name it whatever you want.
And then you just type in your code and between the beginning and statement and they go, and when you click, save it go. It will go ahead and do what we have been doing. In other instances where we drop it exists. It will have the delimit er for us.
Um, so if you need to ah create a procedure, that's one way to do it. Now let's go ahead and go back to that rollback example and walk through the code
so at the top here we're declaring a variable. We're calling it sequel Air. We're saying it's a tiny ant,
which is commonly used for bullion values, which are
two or false values were defaulting it to false
were then declaring a continue handler for sequel exception.
And we're saying we're telling my sequel What to do
and the event.
A sequel exception occurs, And the action we've told my sequel to do with this statement is to set sequel Air too true.
So what? We see what we've done by making this statement a Sze wee wee have told my sequel, Do not crash or stop when you encounter an air. Instead, you need to continue.
However, you also need to set the sequel Air too true,
and that allows us to examine it later on in the procedure.
So online. Seven. We start the transaction.
I'm inserting into a very simple table I have called Count Table. It's very easy to create its a one column or two column table with an I D field and a counter value. Let me go ahead and to leave that value
ice by hitting, delete and just clicking, save Now it's empty. There's no values in this table.
Go back to my rollback example.
So we insert into that very simple table one value of zero.
And the next statement is what's going to create an air? We're I'm indicating that I want to select one divided by T
which, as far as my Secret is concerned, makes no sense. It's going to throw in a Rh, but it's going to continue.
And sequel Heir will be
true. So this
if sequel Air Eagles false will fail and we will run the else image, which is rolled back, and we will select a record which will print out that an heir has occurred.
So let's go ahead and save this
and let's go run it.
So now if I
run the rollback example, we'll see that also, if you have a table a table that you want to empty really quickly,
you can just run this truncate statement. Truncate table name
course. Be very, very careful with that. If you happen to find yourself in a production data, because that is a very quick way to get rid of a lot of production data.
So let's go ahead and run this procedure to see what happens. We're expecting it to fail.
And sure enough, we get a message that says error occurred.
And if you go and check the count table where the insert should have occurred, will see that it's still empty, which is exactly what we expected.
So this procedure worked exactly as we expected with the committee and roll back and air handling.
So if I get rid of this
air inducing statement right here
and I saved the procedure
now, it will work.
So if we go and we run the procedure again,
we get a success message.
If we go and look at the count table,
we get a, ah, new record that was inserted in that simple example. Now it's not doing anything important. We're just showing that the air handling is occurring correctly. So what would happen if we didn't
do that? Let's say we get rid of the,
uh, continue handler. What would happen?
Well, my sequel's gonna take its default action, and it's just gonna air out,
and it's going to insert that record. But then it's gonna air here so we don't do any air handling. And
in a live production procedure. This would probably be a bad thing. So let's go ahead and delete this again.
and go back over here
and run the rollback example.
So what we got on Air column tea and field this unknown? That makes sense to me. Um, but unfortunately, this procedure has done some work.
Now we go into the count table.
We'll see that the record is there,
and that is because we didn't correctly control for an heir
and an error occurred. So it did the insert statement
which was successful.
And then he got to this point and crashed,
at that point we had inserted the record.
We crashed and we left.
So you can see there's a big difference between correctly handling ares
that have multiple or important sequel statements in them
and being able to roll back and prevent them from occurring if something goes wrong. And that's a very important concept
as your production application grows and increases in complexity. Otherwise, you can have very weird data appearing in the database where things aren't complete. Pictures aren't complete.
In any case that finishes this lesson. I hope you enjoyed it and hope you were able to see how the roll back and they commit used with air handling can be very helpful in my sequel procedures or any sequel procedures, not just my sequel.
In any case, I hope to see you in the next lesson and thanks for your time. By