Read Part 1
In Part 1, I introduced a lot of new things, so lets
slow down for a minute and I'll go through them. First thing I did was create a command
object for ADO. I did this with:
Set adocmd = Server.CreateObject("ADODB.Command")
|
Next I had to tell the object what command it would be executing, with this line:
adocmd.CommandText = "sp_IsValidLogon"
|
Notice that the command is the name of the stored procedure.
You must tell the command object which connection (database) to use, to do this you use
.ActiveConnection. .CommandType is a property that tells sql
what type of command it is trying to execute. adCmdStoredProc is a constant
variable declared in the include file adovbs.inc.
(For more information on adovbs.inc, be sure to read
ADOVBS.INC - Use It!)
It represents the number telling sql that the command is to execute a stored procedure.
The .Append method is used to add return values and parameters. I had to add
the username and password parameters, as well as set up the return value. I then executed
the command with .Execute, and .Parameters("return").Value held
the return value from the procedure. I set that to the variable IsValid.
If IsValid is 0, the login is incorrect, if it is 1, the login is correct.
Now even after the explanation this is still a lot to take in. My recommendation to you
is to dive into your server and try a few simple tasks like this. Practice makes perfect.
One note: sometimes I get errors when I try to .Append the return value after
I have already set the parameters. Meaning I might get an error if the above code looked
like this:
<%
.....
Set adocmd = Server.CreateObject("ADODB.Command")
adocmd.CommandText = "sp_IsValidLogon"
adocmd.ActiveConnection = dataConn
adocmd.CommandType = adCmdStoredProc
adocmd.Parameters.Append adocmd.CreateParameter("username", _
adVarChar, adParamInput, 16, Request.Form("UserName"))
adocmd.Parameters.Append .CreateParameter("password", _
adVarChar, adParamInput, 16, Request.Form("Password"))
adocmd.Parameters.Append .CreateParameter("return", _
adInteger, adParamReturnValue, 4)
adocmd.Execute
IsValid = adocmd.Parameters("return").Value
.....
%>
|
I'm not exactly sure why this happens, but I just made it a habit to declare the return
value first, then the parameters.
Now I know what some of you are saying. "The original ASP example for checking the username
and password without using a stored procedure is so much easier, all you did was confuse
me! Can Stored Procedures actually be used to improve efficiency?" Well I'm glad you
asked, because although the example above did require a bit more code, it is important to
realize that it is much more efficient. Stored procedures have other benefits besides
efficiency, though. For a full explanation of the benefits of stored procedures, be sure
to read the SQL Guru's Advice on the issue.
And now I am going to show you an example of a task where using stored procedures minimizes
your database calls.
Assume you have the same script as before for validating usernames and passwords. All it
really does is say whether it is a valid username and password. Suppose you want to add
functionality in to log all failed attempts at logging on into another table called
FailedLogons. If you weren't using a stored procedure you would have to
make another call to the database from your ASP code. However, in the example using the
stored procedure, we don't have to touch the ASP code at all, we simply modify the
procedure like so:
ALTER PROCEDURE sp_IsValidLogon
@UserName varchar(16),
@Password varchar(16)
As
if exists(Select * From User_Table
Where UserName = @UserName
And
Password = @Password)
begin
return(1)
end
else
begin
INSERT INTO FailedLogons(UserName, Password)
values(@UserName, @Password)
return(0)
end
Go
|
Wasn't that neat? But that's not all, while we're at it why not add a little more
functionality? Let's say that we want to run a check on each incorrect login, and if
there have been more than 5 incorrect logins for that username within the past day, that
account will be disabled. We would have to have the FailedLogons table set
up to have a dtFailed column with a default value of (GetDate()).
So when the incorrect logon is inserted into the table, the date and time is recorded
automatically. Then we would modify our stored procedure like this:
ALTER PROCEDURE sp_IsValidLogon
@UserName varchar(16),
@Password varchar(16)
As
if exists(Select * From User_Table
Where UserName = @UserName
And
Password = @Password)
begin
return(1)
end
else
begin
INSERT INTO FailedLogons(UserName, Password)
values(@UserName, @Password)
declare @totalFails int
Select @totalFails = Count(*) From FailedLogons
Where UserName = @UserName
And dtFailed > GetDate()-1
if (@totalFails > 5)
UPDATE User_Table Set Active = 0
Where UserName = @UserName
return(0)
end
Go
|
Now, let's take a closer look at what I was doing. First thing, check to see if the
username and password exist on the same row, if they do, login is fine, return 1 to the
user and exit the procedure. If the login is not ok though, we want to log it. The first
thing the procedure does is insert the record into the FailedLogons table.
Next we declare a variable to hold the number of failed logons for that same day. Next we
assign that value by using a sql statement to retrieve the number of records for that username,
within the same day. If that number is greater than 5, it's likely someone is trying to
hack that account so the the username will be disabled by setting the active flag in the
User_Table to 0. Finally, return 0 letting the calling code (ASP) know that
the login was unsuccessful. To accomplish this same task using only ASP, you would have
needed to make 4 database calls. The way we just did it it is still only one database call,
plus the fact that all that functionality we added at the end was in the stored procedure,
we didn't have to touch the ASP code at all!
Note about begin/end: When using an If statement in a stored procedure, as long
as you keep the conditional code to one line you won't need a begin or
end statement. Example:
if (@myvar=1)
return(1)
else
return(2)
|
However, if you need more than one line, it is required that you use begin
and end. Example:
if (@myvar=1)
begin
do this.....
and this.....
return(1)
end
else
begin
do this....
return(2)
end
|
I hope that I have given enough information to keep you active in learning stored procedures.
If you're anything like me, getting the basics is the hard part, from there you can experiment
and learn on your own. That is why I decided to create these two articles. Remember, feel
free to e-mail me at npond@bgnet.bgsu.edu with any
questions or comments about either of my articles. And thanks to everyone who wrote to me
regarding part one of this series.
Happy Programing!
