Title: BMIS 289: Spring 2002 Gonzaga University
1BMIS 289 Spring 2002Gonzaga University
- Class 12
- Stored Procedures
2Tonights Agenda
- Evaluations
- Final Projects
- Program 10
- Demo
- Connection.Execute
- Queries/Stored Procedures
- Basics
- GetCMD
3Final Project Milestone Dates
- April 17th First version of your code done. Do
initial testing to locate as many bugs as
possible. - April 24th Second version of your code done.
Final testing to verify correctness and locate
any last bugs. Begin working on presentation. - May 1st Final project presentations.
- May 8th Final Exam due back (email to me).
4Final Project - Presentation
- Target for about 15-20 minutes.
- You may take longer if needed
- Be sure to cover these points
- Purpose of your web application
- Design of the overall application and its
database - Your code
- Challenges, issues you faced when writing this
application. - Be organized, polished, and professional
- Visual aides are always nice (e.g., Power Point)
- Be ready for questions.
5Final Project What is turned in
- A one page summary of your final web application.
- What is its purpose?
- What functionality does it have?
- How did you construct it?
- What challenges did you face when constructing
it? - Copies of your code, databases, and any
additional files that make up your web
application. - Please be sure to retain directory structures on
the items you hand in. - Include a readme file if there are any special
installation instructions. - If everything is too big to fit on one floppy
then you may email me the files (zip them up). - Or you could burn a CD.
6Final Project Getting Help
- Standard warning
- Dont wait until the last minute. Have everything
lined up and ready to go so you can have a
polished presentation. - If you need assistance with anything you can call
or email me. - But please keep in mind that my time can be
limited so I cant deal with 10 huge problems
that flood in at the last minute. - Its your responsibility to keep on the ball.
7Connection.Execute
- Note that the connection object has the same
Execute method as the command object. - Cnn.Execute CommandText, RecordsAffected,
Parameters, Options - The functionality is virtually identical to the
command objects Execute method. - There is one additional argument CommandText
- Using the connection Execute method is most
useful for executing SQL statements that are
hard coded into your ASP code.
8Connection.Execute
ConnectionExecute.asp
- The CommandText argument is the same as the
CommandText property of the command object - Cnn.Execute SELECT FROM Employess
- Cmd.CommandText SELECT FROM Employees
- If the command text returns records then you can
use a variant to refer to the returned recordset - Dim rs, CnnSet Cnn Server.CreateObject(ADODB.C
onnection)Cnn.Open GetDBConnectionStringSet rs
Cnn.Execute(SELECT FROM Employees)
9Which is better?
- Once again we seem to have some duplication of
function - Connection.Execute
- Command.Execute
- Neither one of these methods is best suited to
every occasion. - It all really depends on what youre trying to do
and under what conditions it will happen
10Which is better?
- If you are writing a small page for an Intranet
application that at most five people will access
at any given time, then it is probably best to
take the approach that will be quickest and
produce the least code - Connection.Execute
- If you are writing part of a much larger
application you will need to be as generic and
structured as possible, while at the same time
getting the best performance - Command.Execute
11A Point
- Before we move on with more technical discussions
I think it would be good to make a small point. - Computing power today is very cheap, and its
only getting cheaper. - When computers first became commercially
available they were very expensive and had
comparatively little power. - As a result, programs that took even seconds less
to execute than a similar program yielded huge
cost savings.
12A Point
- As a consequence of this programmers had to learn
to be very efficient with their code - Speed, memory, storage space were their driving
concerns when writing a program. - This necessity lead to the Year 2000 problem.
- In todays world power is less of a concern to
programmers. - Now programmers worry
- Can my program be expanded easily and quickly?
- Can my program be leveraged by many users via the
web?
13A Point
- It is still true, however, that programmers are
taught to be as efficient and succinct as
possible when writing code. - So in many cases, people who make their living
writing programs often focus in on things that
can save those extra couple of seconds. - You may run across articles or other programmers
that swear such and such a method is the best
thing to do and by God youd better not use
anything else!
14A Point
- Its very likely that these methods are correct
and worthy of investigating and possibly using. - Often, however, there is an expense in time and
resources associated with using and maintaining
these implementations. - As MIS/IS programmers you need to consider both
the technical and non-technical (human) cost
associated with a given implementation method.
15A Point
- A given implementation may very well be the
best method but it also may be too expensive to
implement - Requires additional software/hardware/personnel
investment. - Requires more implementation time than is
available. - The moral of the story is to use the best
possible solution for your given problem. - They key to reaching that solution is having an
adequate understanding of the problem to begin
with and making sure your customers/management
understands the problem the same way you do.
16Stored Procedures
- Very often, we find ourselves using the same SQL
queries over and over again. - Hard coding these queries on every page we need
them on can be cumbersome. - It would be nice to write the query once, store
it in the database, and then call that query from
our ASP pages whenever we need it. - In Microsoft Access these are called Access
Queries, and in Microsoft SQL Server they are
called Stored Procedures. - For simplicity, we will refer to both as stored
procedures (SPs) from here on out.
17Stored Procedures
- Think of SPs as nothing more than SQL statements
which are stored and executed in your actual
database instead of the ASP page. - When we use SQL (right now) it has to be written
directly into the ASP page. - Instead, we can write the SQL in the database
(with the help of our databases GUI tools) and
simply call that SQL from the ASP page.
18Stored Procedures
Database
ASP Page
SQL Statements
Records
Without Stored Procedures
Database
ASP Page
Records
Stored Procedure
With Stored Procedures
19SP Benefits
- Stored Procedures have many benefits
- Speed the SQL is compiled into your database,
saving on processing and network transfer time. - Reuse the exact same SPs can be shared among
different ASP applications and other Windows
applications. - Readability reduces the amount of database code
that must be placed in an ASP page. - Encapsulation hides the details of the database
from the program.
20SP Drawbacks
- There are drawbacks to using stored procedures as
well - More levels of implementation instead of all
code being centralized in one program it is
spread out between the program and the database. - More Code implementing SPs in ASP pages requires
more lines of code than simply opening a
recordset or using Connection.Execute with hard
coded SQL statements.
21How SPs and ASP Mix
- These are the basic steps for using stored
procedures with ADO ASP pages - Create the SP using your database system (ex
Access). - If the SP will have any inputs (parameters)
define those as well. - In your ASP page create a command object.
- For as many inputs as your SP has, create
parameter objects and add them to the command
objects parameters collection. - Execute the command object.
22Creating a Stored Procedure
- We will now create a simple stored procedure in
Access 2000 using the Employees database. - The steps for creating SPs differ depending upon
the database system, but they are all called in
essentially the same manner using ADO. - Start Access 2000 and open the database you wish
to create an SP in. - Select Queries from the Object band and then
click the Design button.
23Creating a Stored Procedure
- The Show Table dialog appears. Select all the
tables from which your SP will draw data from and
click Add and then click Close. - Remember, if you are drawing data from more than
one table they must all be able to link with one
another in some way. - You will now be presented with the Query By
Example window (QBE).
24Creating A Stored Procedure
- The QBE allows you to create your query by
dragging and dropping fields down to the data
sheet. - Access attempts to create relationships
automatically. - You can manually link tables by dragging a
primary key over its associated foreign key in
another table.
25Creating A Stored Procedure
- First, define the fields you want in your SP by
dragging them to the data sheet. - In other words, get the fields you want to see
in your query by dragging them to the data sheet. - Now you need to define which fields will be your
parameters - From the Query menu select Parameters
- The Query Parameters window appears.
26Creating A Stored Procedure
- In this window you must define all the fields
that will be parameters in the SP. - Each parameter must have a name and a data type.
- For example, the Salary field could be named
Salary and would have a data type of Double. - The name of the parameter does not necessarily
need to match the name of the field in the
database.
27Creating A Stored Procedure
- Every parameter defined in this window must map
to a field in the SQL query. - Once you have finished defining the query
parameters close the Query Parameters window. - Make sure the parameters matching field is
listed in the QBE data sheet. - In the Criteria field underneath the fields
listing type the name of the parameter surrounded
by brackets.
28Creating A Stored Procedure
- In our example we have one parameter called
Salary. - Here the parameter is listed as Salary in the
Criteria field of the Salary field. - Remember, a parameter is the same as a WHERE
condition. So this criteria says pull all records
where the Salary equals the value passed in for
the Salary parameter.
29Creating A Stored Procedure
- Suppose we wanted to pull all employees who were
making more or at least a certain level of
salary? - We would modify the criteria to be
- gt Salary
30Creating A Stored Procedure
- Define the rest of the parameters in a similar
manner. - When you are done click the Save icon and give
the SP a name. This is the name you will call the
query from your code with. - In our example we will call the stored procedure
EmployeesBySalary.
31Creating A Stored Procedure
- You can verify the stored procedure works by
double clicking on its name in the Queries view. - For every parameter you have defined an input box
will appear where you can enter a value. - This is a useful way of debugging your stored
procedures to ensure they are functioning the way
you intend.
32Break
33SP Example
SpExample.asp
- The following example builds upon the stored
procedure we just created. - In this page we will
- Create parameter objects for each parameter in
the stored procedure. - Attach the parameter object(s) to the command
object. - Execute the command object and fetch the records
it returns. - Now lets trace through this code
34SP Example
- The first lines of code turn on Option Explicit
and reference two include files - DataStore.asp contains the function which
returns our connection string. - RecToTable.asp contains the function for
formatting output of recordsets as HTML tables.
lt Option Explicit gt lt!-- include
file"DataStore.asp" --gt lt!-- include
file"RecToTable.asp" --gt ltHTMLgt ltHEADgt ltTITLEgtSto
red Procedures in Accesslt/TITLEgt lt/HEADgt ltBODYgt
35SP Example
- The next line of ASP code declares all the
variables used on this page - Conn will be the connection to the Employees
database. - Rs the recordset that will hold the records
returned from the stored procedure. - Cmd the command object that will be used to
execute the stored procedure in the database. - Param parameter object that will hold the
information about the salary parameter in the
EmployeesBySalary stored procedure. - EmployeeSalary variant variable to hold the
value for the Salary parameter.
Dim Conn, Rs, Cmd, Param, EmpSalary
36SP Example
- The next segment of code creates the connection
object and opens the connection to the Employees
database - The command object will make the request for the
EmployeesBySalary stored procedure over this
connection.
'-- create the connection and open set Conn
Server.CreateObject("ADODB.Connection") Conn.Open
GetDBConnectionString
37SP Example
- The next code segment creates the command object
whose command text will be the name of the stored
procedure we want to access
'--- set the command object Set Cmd
Server.CreateObject("ADODB.Command") Set
Cmd.ActiveConnection Conn Cmd.CommandText
"EmployeesBySalary" Cmd.CommandType
adCmdStoredProc
38SP Example
- After the command object has been instantiated it
is linked with the connection object through this
line of code - Set Cmd.ActiveConnection Conn
- The next line of code sets the objects
CommandText property to the name of the stored
procedure we want to access - Cmd.CommandText "EmployeesBySalary"
- The line after that tells the command object that
it will be executing a stored procedure in the
database on its ActiveConnection - Cmd.CommandType adCmdStoredProc
39SP Example
- After the command is created and setup the
parameter for the stored procedure is
created - Parameters work in this manner with command
objects - Parameter object is created by the
CreateParameter method of the command object. - Parameter object has a value set.
- Parameter is added to the Parameters collection
of the command object.
'--- now the parameters Set Param
Cmd.CreateParameter("Salary", adDouble,
adParamInput) Cmd.Parameters.Append Param
40SP Example
- To create parameters we use the command objects
CreateParameter method - Set Param Cmd.CreateParameter(Name, Type,
Direction, Size, Value) - The create parameter method takes five arguments
and returns a Parameter object. - The parameter object can later be added to that
same command objects parameter collection for
transport to the database when the command is
executed.
41SP Example - CreateParameter
- Name the name of the parameter, as defined in
the stored procedure. - Type the data type of the parameter value.
- Direction used to specify if the parameter is
used to send data to the query, return data from
it, or both. - Size used to specify the maximum size of the
parameters value. - Value used to specify the value of the
parameter. - Note that all these method parameters are
optional. Typically, though, you will want to
provide the parameters Name and Value.
42SP Example
- The Type argument of the CreateParameter method
has several possible values, all of which are
defined in the ADO type library. - Some of the more common are
- adDouble large numbers
- adVarChar strings
- adBigInt autonumber fields
- Not every database supports every possible data
type value. Usually the database provider will
substitute the correct data type for you (but
dont count on it).
43SP Example
- In our code we created one parameter object
called Salary which has a data type of double
and is an input parameter - An input parameter is a parameter that returns no
value itself. In other words the database doesnt
modify the value of the parameter.
Set Param Cmd.CreateParameter("Salary",
adDouble, adParamInput)
44SP Example
- Finally, the parameter object itself is added to
the parameters collection of the command
object - This is an important step. If the stored
procedure is expecting parameters and they are
not included with the command object then it will
not be able to execute our request.
Cmd.Parameters.Append Param
45SP Example
- Very Important the order in which you append the
parameters is very important. The order of the
parameters in the parameter collection must be
the same as they appear in the stored procedure. - So if our stored procedure had two parameters
called Salary JobTitle we would want to ensure
that those parameters were added to the
parameters collection in that exact same order.
46SP Example
- Next, the parameters value is set
- Notice how we can reference individual parameters
within the command objects parameters collection
by using their names. - By referencing parameters in this fashion we can
treat them like normal variables.
EmpSalary 50000 Cmd.Parameters("Salary")
EmpSalary
47SP Example
- Note that we could have saved using these lines
of code by setting the value when we initially
created the parameter object - We can always change the value of the parameter
if we need to later
Set Param Cmd.CreateParameter("Salary",
adDouble, adParamInput, 50000) Cmd.Parameters.Appe
nd Param
Cmd.Parameters("Salary") 60000
48SP Example
- Now we will execute the command and fetch the
records it returns - This line of code
- sends the command object over its
ActiveConnection to the database. - Once there, the stored procedure
EmployeesBySalary is executed with the
parameter Salary as input. - The results are placed in a recordset and
returned to the ASP page.
Set Rs Cmd.Execute
49SP Example
- Once the command finishes executing we destroy
the parameter and command objects since we no
longer need them in this program
'don't need these any more so clean now Set Cmd
Nothing Set Param Nothing
50SP Example
- With the records retrieved we now output
- Notice how we check to see if no records were
returned by checking the BOF and EOF properties
of the recordset object. - If they are both true at the same time that means
the recordset is empty.
'output the results if not (Rs.EOF and Rs.BOF)
Then Response.Write "ltH2gtEmployees with
salaries gt " EmpSalary "lt/H2gt"
Response.Write RecsToTable(Rs) else
Response.Write "No records found!" end if
51SP Example
- Finally, we end the page by closing the recordset
and connection objects and then destroying them
to free memory on the server
'close and destroy the recordset and
connection Rs.Close Set Rs Nothing Conn.Close Se
t Conn Nothing
52Structuring Commands
- Using stored procedures and command objects leads
to many very powerful benefits (speed being the
most obvious) but this can lead to a lot of code. - To combat this we can encapsulate all the
commands our ASP page uses in a single function
called GetCmd which is stored in our
DataStore.asp include file.
53GetCmd
- GetCmd is a function that returns an ADO Command
object that is set to a specific stored
procedure, has its active connection set, and is
preloaded with parameter values. - The command object we get back from GetCmd should
be ready to execute and fetch records. - Think of GetCmd as a function for building
command objects.
54GetCmd
- GetCmd has three arguments
- SP_Name the name of the stored procedure we want
to build a command object for. - Param_Array an array that contains all the
values specific to a certain stored procedure
(ie the one indicated by SP_Name). - Conn an ADO Connection object that points to the
database where the stored procedure in SP_Name is
stored.
55Implementing GetCmd
- Based on the function arguments you probably have
an idea about how this function might be
implemented. - The main body of the code is a big SELECT CASE
statement whose driving value is the SP_Name
argument. - When a match is made the the command object is
initialized with the necessary data for that
particular stored procedure.
56Implementing GetCmd
- Initializing the command object consists of two
primary steps - Set the CommandText property to the stored
procedure name (ie the name you matched in the
case statement previously SP_Name). - Create and set the parameters for this stored
procedure and append to the command object. - Problem This function needs to be capable of
handling any stored procedure our program
requires. It is likely that our stored procedures
will have varying numbers of parameters (0..N).
How do we pass an unknown number of values into
this function?
57Implementing GetCmd
- This problem is solved by passing in all
parameter values within an array. - Using an array we can pass in as many values as
we need to. - The key is that the code passing in the array
must place the array (parameter) values in the
correct order. - There is really no way to programmatically ensure
this so there should be adequate documentation
available to the programmer for each stored
procedure. - With proper documentation the programmer will
know how to correctly set that parameter array.
58Implementing GetCmd
- After the parameters have been set the SELECT
CASE statement ends. - Before returning the command object its
ActiveConnection property should be set to the
connection objection that was passed into the
GetCmd function.
59Implementing GetCmd
- One last consideration we need to make is how the
names of the stored procedures should be
referenced. - It is best to code the stored procedure names as
constants within the datastore.asp include
file. - These constants can then be referenced by GetCmd
and any other ASP code that includes this file.
60Implementing GetCmd
- I recommend following a naming convention for
your stored procedure name constants - SP_DB_SPName
- SP stored procedure
- DB a shorthand code for the database the stored
procedure is located in. - SPName a shorthand code for a particular stored
procedure. - Ex SP_EMP_EMP_BY_SALARY
61For Next Time
- Is there any particular functionality you would
like to know how to accomplish with ASP? - You can tell me now or email me later and I will
present it during next class. - Dont email requests too late as I may not have
enough time to gather all the information.
62END