Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
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
00:18
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.
00:30
Now in D Beaver, if you want to create a new procedure, it's very easy. You just right click
00:37
on the procedures select, create new procedure and name it whatever you want.
00:42
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.
00:57
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
01:06
so at the top here we're declaring a variable. We're calling it sequel Air. We're saying it's a tiny ant,
01:11
which is commonly used for bullion values, which are
01:15
two or false values were defaulting it to false
01:19
were then declaring a continue handler for sequel exception.
01:23
And we're saying we're telling my sequel What to do
01:26
and the event.
01:29
A sequel exception occurs, And the action we've told my sequel to do with this statement is to set sequel Air too true.
01:36
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.
01:47
However, you also need to set the sequel Air too true,
01:51
and that allows us to examine it later on in the procedure.
01:56
So online. Seven. We start the transaction.
01:59
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
02:12
ice by hitting, delete and just clicking, save Now it's empty. There's no values in this table.
02:17
Go back to my rollback example.
02:20
So we insert into that very simple table one value of zero.
02:23
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
02:30
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.
02:37
And sequel Heir will be
02:39
true. So this
02:42
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.
02:52
So let's go ahead and save this
02:54
persist
02:57
and let's go run it.
02:59
So now if I
03:00
run the rollback example, we'll see that also, if you have a table a table that you want to empty really quickly,
03:07
you can just run this truncate statement. Truncate table name
03:10
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.
03:20
So let's go ahead and run this procedure to see what happens. We're expecting it to fail.
03:24
And sure enough, we get a message that says error occurred.
03:28
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.
03:36
So this procedure worked exactly as we expected with the committee and roll back and air handling.
03:43
So if I get rid of this
03:45
air inducing statement right here
03:47
and I saved the procedure
03:51
now, it will work.
03:53
So if we go and we run the procedure again,
04:01
we get a success message.
04:03
If we go and look at the count table,
04:08
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
04:19
do that? Let's say we get rid of the,
04:21
uh, continue handler. What would happen?
04:26
Well, my sequel's gonna take its default action, and it's just gonna air out,
04:30
and it's going to insert that record. But then it's gonna air here so we don't do any air handling. And
04:36
in a live production procedure. This would probably be a bad thing. So let's go ahead and delete this again.
04:43
Delete
04:44
safe
04:46
and go back over here
04:48
and run the rollback example.
04:53
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.
05:01
Now we go into the count table.
05:05
We'll see that the record is there,
05:08
and that is because we didn't correctly control for an heir
05:12
and an error occurred. So it did the insert statement
05:15
which was successful.
05:17
And then he got to this point and crashed,
05:20
and
05:21
at that point we had inserted the record.
05:25
We crashed and we left.
05:28
So you can see there's a big difference between correctly handling ares
05:30
that have multiple or important sequel statements in them
05:33
and being able to roll back and prevent them from occurring if something goes wrong. And that's a very important concept
05:41
to, ah
05:43
to implement
05:44
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.
05:55
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.
06:09
In any case, I hope to see you in the next lesson and thanks for your time. By

Up Next

Introduction to SQL

This introductory SQL training teaches SQL core concepts that can be applied in professional environments. Once students complete this course, they will be able to query and interact with an SQL database, and know how to design database schemas.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor