BMIS 289: Spring 2002 Gonzaga University - PowerPoint PPT Presentation

1 / 62
About This Presentation
Title:

BMIS 289: Spring 2002 Gonzaga University

Description:

Please be sure to retain directory structures on the items you hand in. ... By referencing parameters in this fashion we can treat them like normal variables. ... – PowerPoint PPT presentation

Number of Views:66
Avg rating:3.0/5.0
Slides: 63
Provided by: colin83
Category:

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com