BMIS 289: Spring 2002 Gonzaga University

1
BMIS 289 Spring 2002Gonzaga University

• Class 12
• Stored Procedures

2
Tonights Agenda

• Evaluations
• Final Projects
• Program 10
• Demo
• Connection.Execute
• Queries/Stored Procedures
• Basics
• GetCMD

3
Final 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).

4
Final 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.

5
Final 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.

6
Final 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.

7
Connection.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.

8
Connection.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)

9
Which 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

10
Which 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

11
A 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.

12
A 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?

13
A 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!

14
A 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.

15
A 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.

16
Stored 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.

17
Stored 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.

18
Stored Procedures
Database
ASP Page
SQL Statements
Records
Without Stored Procedures
Database
ASP Page
Records
Stored Procedure
With Stored Procedures
19
SP 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.

20
SP 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.

21
How 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.

22
Creating 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.

23
Creating 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).

24
Creating 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.

25
Creating 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.

26
Creating 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.

27
Creating 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.

28
Creating 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.

29
Creating 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

30
Creating 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.

31
Creating 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.

32
Break
33
SP 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

34
SP 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
35
SP 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
36
SP 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
37
SP 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
38
SP 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

39
SP 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
40
SP 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.

41
SP 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.

42
SP 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).

43
SP 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)
44
SP 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
45
SP 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.

46
SP 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
47
SP 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
48
SP 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
49
SP 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
50
SP 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
51
SP 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
52
Structuring 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.

53
GetCmd

• 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.

54
GetCmd

• 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.

55
Implementing 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.

56
Implementing 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?

57
Implementing 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.

58
Implementing 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.

59
Implementing 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.

60
Implementing 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

61
For 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.

62
END