Time
9 hours 41 minutes
Difficulty
Intermediate
CEU/CPE
10

Video Transcription

00:00
I Welcome back to module five. This is listen 5 10 transactional procedure to in this lesson, we need to fix the air handling In the procedure that we discussed in the previous lesson, there is a problem in the loop. If there are some successes and then an error, those successes will insert into the database
00:18
and that will be explained as we fix it. Why that's occurring,
00:22
then we also need to add that procedure to the migration fouls.
00:26
We also need to fix a unique constraint on the student courses table to respect our composite school i d key.
00:33
And then, of course, we will also test the procedure to make sure that it's working the way that we expect it
00:39
now. With that said, let's go ahead and head over to the development environment.
00:44
I welcome back to the development environment. There is a small change we need to make to the procedure that we had in the previous lesson. There's an issue. If an air occurs in the transaction, the transactions that are successful will automatically commit, and there's a reason for that. So let's take a look at how we will handle that
01:02
so at the top, we declare sequel Air Tiny in default. False.
01:07
We declare a continue handler for sequel exception, and if that happens, we set sequel air equal to True.
01:15
Now you'll notice that this is one command because there's no semicolon that occurs at the end of this line, and it ends at this line where we set sequel air equal to true.
01:27
So we go down to the bottom if we get a string of courses and the 1st 2 are successful and the 3rd 1 has an air without that continue handler, what would happen is the procedure would exit at that point and automatically commit the successful inserts that had occurred.
01:46
And that's not what we want. We want all or nothing there, either all successful
01:49
or they all are. They all failed, and that has to be rerun. So what will happen now is we'll start transaction.
01:59
We'll go through the loop if in an air occurs because we have the continue handler at the top, will change this variable to true
02:07
and then after the loop will check the value of that variable. If it's false, that means we were successful. We will run the commit command, and we will print out a success message. Otherwise, if it's true, that means an error occurred. We will roll back the transactions or the inserts that have occurred and we'll print out. An error has occurred.
02:28
Try again.
02:29
So let's go ahead and run that. Make sure that works, and it does.
02:32
So we need to add this into a migration file now.
02:37
So let's bring up our terminal session. I'm in an ssh session with the Bagram machine. I'm getting ready to run this command, which is PHP artisan. Make migration. Add User, I got to underscores there, So let me get rid of one
02:52
add user to course procedure.
02:54
So that's a good name for the file.
02:58
I'll go ahead and run that that's gonna make the file. So I'm gonna go ahead and get that open.
03:04
I added this
03:06
close, these other ones I have open
03:09
no
03:12
so that we can keep it simple.
03:16
And
03:20
let's go ahead and open that again.
03:23
I know that I need to perform in action similar to something I've done in the past. I'm gonna open up one of my previous files.
03:30
He added that one so I know. I need the DB un prepared statement,
03:35
and I don't need that skim a table statement. So let me get rid of this.
03:42
So, Michael, And don't forget the semicolon
03:46
and with same thing here and now let's go ahead and move our procedure into these.
03:53
So I don't need the limiter swapping, so I'm gonna end at the end
03:58
one a copy all the way to the create statement.
04:01
I'm gonna switch these single quotes to double quotes because I have single quotes in the procedure
04:06
and this will make it easier. I won't have to use any kind of escape characters.
04:11
And then for the UN prepared,
04:13
we need to drop the procedure if it exists,
04:20
and I will go ahead and save that.
04:24
Now, let's test this and make sure that the migration runs.
04:28
So we go back to my session and I'm gonna do pH PR person.
04:32
My great refresh.
04:41
So that will dump any data out of the database and refresh all the tables, the functions in the procedures. Looks like I got an air. Let's let's see what happened.
04:49
Okay, who's a user to course
04:55
already exists. So that's because I was doing some testings already having in the database.
05:00
So it wasn't running the down command for it because it believes that it's the first time I've added it.
05:06
I'll go ahead and delete that.
05:10
Then I'll go back into my terminal session
05:13
and I'll go ahead and run a refresh command again.
05:16
I'm gonna also go ahead and seed the database after this completes because the database will be empty.
05:24
Peace be artists in
05:26
Bebe
05:27
Seed,
05:29
and that will put a bunch of fake data into the database.
05:35
Let's refresh this.
05:38
It's like it hasn't gotten to that point yet.
05:41
Dollars should be data there,
05:44
but we don't want data in this table cause this is gonna be our test
05:46
someone a trunk, it it
05:48
they don't want to deal with the possibility that the debt I happen to be trying to add already exists, and we go ahead and run that test. But I'm just gonna go ahead and empty all this data out first.
06:01
So
06:03
truncate user courses
06:09
go ahead and refresh.
06:10
It's empty.
06:12
And now let's go ahead and give our procedure a test. I'm gonna right click and refresh.
06:17
There's a procedure
06:19
gonna generate the sequel call.
06:23
I'm going to copy this portion.
06:27
Those that
06:28
on I'm going to paste.
06:30
So I know I'm gonna have a school idea of one.
06:32
No, I'm gonna have a user idea of one. I know. I'm gonna have a type idea of one
06:39
and let's add some courses.
06:43
So 123
06:45
go ahead and call that
06:47
printed out success. That's take a look.
06:51
Okay,
06:54
let's put him at a different schools. Say school too.
06:58
Not 12 to
07:00
run. That
07:01
says an air occurred.
07:03
Make sure nothing was added.
07:08
Okay, this is actually based on the other problem we need to fix were not respecting the fact that we have the school ideas part of our composite key and the user courses table. If we take a look at this,
07:19
we'll see that our constraints We have a unique constraint,
07:24
user. I d either type I d. Course I d. So we need to add the school. I d into this unique constraint. So we need to go into our migration files and make that change and then refresh and recede.
07:38
So there it is.
07:40
We go ahead and add it. This and we need to This is user courses, right? Create user courses
07:47
right here,
07:49
comma.
07:50
This is an array. It's in the brackets.
07:55
Add that string in there.
07:57
All right.
07:58
And let's go ahead and refresh.
08:07
And as soon as this finishes, we need to receive because all that data is gone.
08:11
That takes a moment for it to do that. So let's head back here. We're gonna need to truncate the user courses again and run this again.
08:20
And this being the add user to course procedure.
08:24
So let's check to see if we have data here.
08:28
Refresh.
08:30
And we do.
08:31
So we'll go ahead and truncate that table again.
08:35
Should be empty.
08:37
It is
08:39
gonna run this procedure with the two again. Which would be school I d to
08:43
We have success gonna switch that to a one.
08:48
We have success again.
08:50
You go look at the user courses. Refresh. So 121212 for six entries.
08:56
So we got the
08:58
same user I d at different schools, which is exactly what we wanted.
09:03
So let me go ahead and run this again. I should get in air.
09:07
Yep, and indicates an air. There should be no entries. That's correct.
09:13
Now let's Let's ah, make an entry that will be correct for the 1st 2 iterations of that loop and then fill on that last one. So 45 would be valid and would be successful. But three should fail.
09:26
Okay, it says that an air occurred. Let's double check that table and make sure that nothing was added.
09:33
And that's correct.
09:35
So our procedure appears to be working. If in error occurs in that loop, we don't add anything. If everything successful, we go ahead and commit all the changes, and that completes this portion of the lesson. Let's go ahead and head over to the summary,
09:48
and that brings us to the 5 10 summary and this lesson. We added a procedure to the migrations. We fix that unique constraint on one of our tables, and we also fix the air handling in our procedure with a continue sequel exception handler. Then we, of course, tested the procedure to make sure it was working the way we expected.
10:07
And that completes this lesson.
10:09
I hope to see you in the next Thank you

Up Next

Intermediate SQL

This free course introduces the student to intermediate concepts found in the implementation and application of Structured Query Language (SQL) within professional business environments.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor