BMIS 289: Spring 2003 Gonzaga University

1
BMIS 289 Spring 2003Gonzaga University

• Class 11
• SQL Command Objects

2
Tonights Agenda

• Final Projects
• Program 9
• Command Object
• Connection.Execute
• SQL
• Conditional Selects
• Joins
• Update, Insert, Delete
• Adding Deleting Using Recordsets
• Queries/Stored Procedures
• Program 10

3
Final Project Milestone Dates

• April 10th You should have a full outline and
  flow chart for your project done by now.
• 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
Program 9

• 2 database filters and searches.
• Things to consider
• If no records are found for the search or filter
  we should indicate so.
• Display all resulting records in an HTML table.
• Use error trapping to gracefully handle
  unexpected errors.

5
Program 9 Cont.

• How I organized the program
• Main.asp Contains all the forms for searching
  and filtering.
• Search.asp The search forms post to this page,
  it performs the record searches.
• Filter.asp The filter forms post to this page,
  it performs the record filters.
• Main.asp contains only HTML forms. You can have
  multiple forms on one page that post to separate
  ASP pages by differing the Action attribute of
  each forms ltFORMgt tag.

6
Program 9 Cont.

• Search.asp
• Uses On Error Resume Next to trap unanticipated
  errors.
• To determine which search to perform I capture
  both search form fields and then check if one is
  empty. If it is then I do the other search,
  otherwise I will search on the first field (the
  one we checked).
• Build search criteria and then use the Find
  method to perform the search.
• If record returned then output result (using a
  customized version of RecToTable function),
  otherwise report no records found.

7
Program 9 Cont.

• Filter.asp
• Uses On Error Resume Next to trap unanticipated
  errors.
• To determine which filter to perform I first
  capture the Country form field and check if
  its empty. If it isnt then I setup variables
  for a Country filter, otherwise I setup
  variables for the Freight Charge filter.
• Build filter criteria and then set the Filter
  property to perform the filter.
• If filter returned records then output results
  (using RecToTable function), otherwise report no
  records found.

8
Program 9 Cont.

• Things to note about program 9
• Searching/Filtering a large set of records can
  take a long amount of time.
• Since users generally get impatient after more
  than a few seconds of waiting we should be
  careful to not grab more data than we need.
• Using Find and Filter we still have to
  retrieve an larger recordset before we can narrow
  it down to a few specific records.

9
ADO Object Model

• Primary ADO Objects
• Connection the actual link between the ASP code
  and a database.
• Command allows you to run commands against a
  database.
• Recordset contains all the records returned from
  a specific action on a database.

10
The Command Object

• The Command object is the third major ADO object
  and is used to execute various commands against a
  data store.
• For example, we are used to opening a table into
  a recordset like this
• A command object gives us alternative ways of
  performing actions against a database.

Rs.open Orders, strConnect, adOpenStatic,
adLockReadOnly, adCmdTable
11
The Command Object Cont.

• The basic steps for using a command object
• Declare the command object.
• Give it some sort of command text
• The actual instructions that will be sent to the
  database and processed by it.
• Set the command objects connection.
• Execute the command, if it is suppose to return
  records then ADO will return a recordset.
• Some commands do not fetch records, such as ones
  to insert new records or delete existing ones.

12
Basic Command Object Example
cmd.asp
lt!-- include filedatastore.asp --gtlt!--
include fileRecToTable.asp --gt ltDim rs, conn,
cmdSet conn Server.CreateObject("ADODB.Connecti
on")Set cmd Server.CreateObject("ADODB.Command"
)conn.Open GetDBConnectionStringcmd.CommandText
"SELECT FROM Moviesset cmd.ActiveConnection
connset rs cmd.Execute(, ,
adCmdText)Response.Write RecsToTable(rs)rs.close
set rs nothingconn.closeSet conn
nothingSet cmd nothinggt
13
Breaking It Down

• The first include file contains the database
  connection string.
• The second include file contains a function that
  returns the HTML that outputs the contents of a
  recordset as an HTML table.
• The first three lines of ASP code declare some
  variables and instantiate ADO connection and
  command objects.

14
Breaking It Down
Dim rs, conn, cmdSet conn Server.CreateObject("
ADODB.Connection")Set cmd Server.CreateObject("
ADODB.Command")

• Note that the recordset object is not
  instantiated (set equal to anything).
• This is because we will later execute the command
  object and receive a recordset object back from
  it.
• Creating an empty recordset now would be a waste
  of server resources.

15
Breaking It Down

• After the objects are instantiated the connection
  to the database is opened
• Once the connection is opened the command
  objects CommandText property is set
• Command text represents the actual instructions
  the database should use to perform an operation.

conn.Open GetDBConnectionString
cmd.CommandText "SELECT FROM Movies"
16
Breaking It Down

• The syntax of the commands you send to the
  database depends on what database you are sending
  the command to.
• For example, the commands sent to an Oracle
  database may vary from those sent to an Access
  database.
• By and large, though, nearly every relational
  database accepts standard SQL commands.
• SQL is a standardized language for retrieving
  records from a database (as well as deleting,
  adding, and updating them).
• SELECT FROM Movies simply means retrieve
  every field of every record from the Movies
  table
• We will talk more about SQL a little later.

17
Breaking It Down

• After setting the command text we need to attach
  the command object to a specific connection.
• This is accomplished by setting the command
  objects ActiveConnection property
• ActiveConnection is really nothing more than a
  connection object.
• We are just setting the commands connection to
  an existing connection object.

set cmd.ActiveConnection conn
18
Breaking It Down

• Now we want to actually execute the command and
  retrieve the records it produces.
• The command object has a method called Execute,
  which does just that.
• The Execute method
• sends the command objects command text to the
  database (the one on its ActiveConnection)
• the database performs the command
• the results are returned back to the ASP page.
• In our example the call to Execute looks like
  this

set rs cmd.Execute(, , adCmdText)
19
Command.Execute

• Cmd.Execute RecordsAffected, Parameters, Options
• RecordsAffected returns the number of records
  that were changed, added, or deleted by the
  command text (optional).
• Parameters an array of parameters passed to the
  command (used with stored procedures and
  queries).
• Options ADO constants that indicate what type of
  command is being executed (e.g. adCmdText).

20
Breaking It Down

• The call to the Execute method in the command
  object looks like this
• Note that we left the first two arguments empty
  (ie we passed nothing in).
• When the Execute method returns a recordset the
  default settings for cursor and locktype are
  forward-only and read-only respectively.
• Since our command text is a SQL statement we set
  the Options argument to adCmdTxt.
• Our SQL statement is requesting that records be
  returned, so the Execute method will return a
  recordset.

set rs cmd.Execute(, , adCmdText)
21
Breaking It Down

• After retrieving the records they are outputted
  by calling the function RecsToTable, located in
  the include file RecToTable.asp.
• The function returns HTML as a string, so it is
  directly outputted

Response.Write RecsToTable(rs)
22
Breaking It Down

• Finally, the page is finished by closing the
  connection and destroying the objects

rs.close set rs nothing conn.close Set conn
nothing Set cmd nothing
23
Why Use The Command Object?

• It seems that we could write less code by just
  doing everything through Recordset.Open, so why
  bother with the command object?
• For simple commands, like extracting data from a
  data store, it is probably best to forgo
  explicitly creating a command object.
• More complex commands, like stored procedures or
  Access queries, will require us to use the
  command object.

24
SQL Introduction

• SQL Structured Query Language
• It is a universal language used to manage
  relational databases.
• It is a declarative language, and not a
  procedural language like Visual Basic or C.
• In a declarative language we describe to the
  computer what we want and it handles the details
  of making it happen.
• In a procedural language we have to write the
  details.

25
SQL Introduction Cont.

• Think of SQL in this manner
• If you could actually talk to a database
  directly, and it could take actions based on what
  you said, you might say something like Give me
  all the employee records with a first name of
  Colin, please.
• The database would then return all the employee
  records it had with a first name of Colin.
• SQL works in exactly the same way, but instead of
  vocally communicating to the database we interact
  through written SQL statements.

26
Types of SQL Statements

• There are four basic types of SQL statements
• SELECT used to retrieve data from a table.
• INSERT used to add data to a table.
• UPDATE used to change records already in a
  table.
• DELETE used to remove data from a table.
• The statement types that are underlined are known
  as action queries, meaning theyre SQL statements
  that do not return records.
• SQL statements are also known as queries (in
  Microsoft Access).

27
SELECT Statements

• Basic Syntax
• Suppose we had an Employee table and we wanted to
  get every employees first name and last name out
  of it
• IF we wanted to get every field from the Employee
  records

SELECT ltfield(s)gt FROM lttablegt
SELECT FirstName, LastName FROM Employees
SELECT FROM Employees
28
Conditional Selects

• The previous SELECT statements will grab every
  record from the indicated table.
• Often, we only want to grab certain records from
  the database, such as in a filter or a search.
• To do this we add conditions to our SELECT
  statement

SELECT ltfield(s)gt FROM lttablegt WHERE ltconditionsgt
29
Conditional Selects Cont.

• Suppose we wanted to grab all records from an
  Employees table with the first name of Colin
• We can create more complex conditional selects
  using the OR and the AND operators

SELECT FROM Employees WHERE FirstName Colin
SELECT FROM Employees WHERE FirstName
Colin AND LastName Fukai
30
Conditional Selects Cont.

• More Examples
• Notice that we use single-quotes around strings
  in conditional selects. This is because the
  entire SQL statement will be surrounded by
  double-quotes when we pass it as command text

SELECT FROM Orders WHERE Freight gt 30.00
SELECT FROM OrdersWHERE OrderDate 03/12/01
OR OrderDate 04/12/01
SELECT FROM Customers WHERE LastName LIKE
Smith
SELECT FROM Customers ORDER BY LastName
31
ORDER BY

• Note the use of ORDER BY in the last SQL
  statement in the previous slide.
• This clause is used to sort the returned records
  if a specific order.
• By default, the field(s) the records are ordered
  by are in ascending order.
• You can optionally sort descending

SELECT FROM Customers ORDER BY LastName DESC
32
ORDER BY

• You can also order by more than one field at a
  time
• This SQL sorts the records primarily by birthdate
  in descending order, then each date is sorted by
  the persons last name ascending.

SELECT FROM Customers ORDER BY BirthDate DESC,
LastName
33
Break
34
SQL Joins

• Databases usually have more than one table. This
  is for the sake of efficiency and logic.
• Imagine if Gonzaga used only one table to hold
  all the information it needed.
• It would have hundreds (maybe thousands) of
  fields, lots of repetitive data, and would take
  massive amounts of hard disk space to store.
• To create a more efficient database Gonzaga would
  probably divide tables based on things like
  Faculty, Student, NonFaculty, Class, School,
  Grades, Schedule, etc.
• When we want to get a set of data from more than
  one table we need to JOIN the tables in our SQL
  statement.

35
SQL Joins Cont.

• See page 575-578 for example
• Storing movie data in this fashion is inefficient

36
SQL Joins Cont.

• This would be more efficient

Movies
Directors
Cast
37
SQL Joins Cont.
SQLSelectInnerJoin.asp

• Suppose we wanted to display all the movies
  directed by Quentin Tarantino or Francis
  Coppola

SELECT Movies.Title, Director.DirectorName FROM
Movies INNER JOIN Director ON Movies.MovieID
Director.MovieID WHERE (Director.DirectorName
LIKE 'Quentin Tarantino' OR Director.DirectorName
LIKE 'Francis Coppola')
38
SQL Updates

• This statement is used to make changes to
  existing records.
• For example, suppose we wanted to change an
  employees age

UPDATE lttablegt SET ltfield_namegt ltoperatorgt
ltvaluegt WHERE ltconditiongt
UPDATE Employees SET Age 32 WHERE EmpID
34214
39
SQL Inserts

• This statement is used to add new records to a
  table
• Make sure your list of fields and list of values
  are in the same order!
• Example

INSERT INTO lttablegt (ltfieldsgt) VALUES (ltvaluesgt)
INSERT INTO Employees (Name, Age) VALUES (Colin
Fukai, 22)
40
SQL Deletes

• This SQL statement is used to delete records
• Example
• Note that this statement deletes whole records,
  not just particular fields.
• To clear certain fields within a record you must
  UPDATE the field to equal nothing.

DELETE FROM lttablegt WHERE ltconditiongt
DELETE FROM Employees WHERE EmpID 34562
41
Connection.Execute

• Note that the connection object has the same
  Execute method as the command object
• The functionality is virtually identical to the
  command objects Execute method.
• Using the connection Execute method is most
  useful for executing SQL statements that are
  hard coded into your ASP code.

Conn.Execute CommandText, RecordsAffected,
Parameters, Options
42
Adding Data Using A Recordset

• Instead of SQL, you can use the Recordset object
  to create new records.
• To indicate you want to create a new record you
  call the AddNew method of the Recordset object.
• This method creates a blank record and moves the
  recordset cursor to it.
• You then set each of the new records fields
  equal to some value.
• Finally, you call the Update method of the
  recordset object to commit the new record to the
  database.

43
Example
AddNew.asp
Dim objRSSet objRS Server.CreateObject("ADODB.R
ecordset")objRS.Open "Employees",
GetDBConnectionString, adOpenStatic,
adLockOptimistic, adCmdTableobjRS.AddNewobjRS("F
irstName") KatieobjRS("LastName")
FukaiobjRS("JobTitle") Apprentice Coffee
GirlobjRS("Salary") 50000objRS("HireDate")
09/18/2000objRS.UpdateobjRS.CloseSet objRS
NothingResponse.Write "Finished!"

• Notice how we opened a static recordset and set
  an optimistic lock.

44
Canceling A New Record

• The Update method is what actually commits the
  new record to the database.
• You can cancel this new record any time before
  the update by calling the CancelUpdate method
  of the recordset object

objRS.AddNewobjRS(Name) Colin
FukaiobjRS(Age) 22objRS.CancelUpdate
45
Deleting Records Using The Recordset

• You can also delete records using the Recordset
  object
• This statement deletes the record that the cursor
  is currently pointing to.
• You want to be careful using this method, because
  it can be very easy to accidentally delete all
  the records in the recordset (especially when
  used in a loop).
• It is usually best apply a filter before deleting
  records (if you are deleting multiple records) so
  we only delete the records we want to

objRS.Delete
objRS.Filter Name Colin FukaiWhile Not
objRS.EOF objRS.Delete objRS.MoveNextWend
46
Queries 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.

47
Parameters

• Typically, Access queries and stored procedures
  have parameters that allow you to customize them.
• For example, your database may have a query that
  returns all employees with a specific first name.
• In this case the parameter is the employees
  first name.
• Whatever value is passed in as the first name
  will dictate which employee record(s) get
  returned back.

48
How Parameters Work
AccQueryParam.asp

• First you build a collection of parameter
  objects.
• Each parameter object represents one particular
  parameter in the Access query or SQL Server
  stored procedure.
• The collection of parameter objects is then
  passed into the query.
• The query is then executed against the command
  object
• See page 586 for an example.

49
How Parameters Work Cont.

• Create a command object to work with stored
  procedures

Dim objCmdSet objCmd _ Server.CreateObject(ADO
DB.Command)objCmd.ActiveConnection
GetDBConnStringobjCmd.CommandText
ltquery_namegtobjCmd.CommandType adCmdStoredProc
50
How Parameters Work Cont.

• Now we need to create a parameter and append it
  to the command objects parameter collection.
• To create parameters we use the command objects
  CreateParameter method

Set objParam _ objCmd.CreateParameter(Name,
Type, Direction, Size, Value)
51
How Parameters Work Cont.

• Name the name of the parameter, as defined in
  the query or 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.

52
How Parameters Work Cont.

• Once you have created a parameter object you must
  append it to the command objects parameter
  collection
• 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 database
  query (at least for Access).

objCmd.Parameters.Append objParam
53
How Parameters Work Cont.

• After you have appended all the needed parameters
  to the command objects parameter collection you
  just execute the stored query
• If the stored query returns records then you will
  want to set a recordset equal to it

objCmd.Execute
Set objRS objCmd.Execute
54
Introduction To Flash

• Flash is a client-side web technology that
  delivers compact, optimized animations to a
  client web browser.
• It is used to create rich, interactive web pages
  that contain complex graphics and animations.

55
Introduction To Flash

• How Does Flash Work?
• Designer/Programmer creates a Flash file (.swf)
  using the Flash Design software (from
  Macromedia).
• Designer/Programmer embeds the Flash file into a
  web page and copies both the page and Flash file
  to the server.
• Client browses to page and downloads the embeded
  Flash file.
• The Flash file is rendered using a special Flash
  plugin program that must be downloaded and
  installed by the client.
• Client watches/interacts with content in the
  Flash file.

56
Introduction To Flash

• A Flash file itself is essentially no more than a
  slide show that allows interactivity (input) with
  the user.
• So rather than just watch the movie a client
  may interact with it.
• When you create a Flash animation you are really
  drawing/creating the multimedia slides that make
  up the animation, and optionally adding
  interactive elements.

57
Introduction To Flash

• Advantages of Flash
• Flash is very compact. It is optimized to be
  delivered across the Internet.
• A large amount of graphic information can be
  embedded into a Flash file and not greatly
  increase its size.
• The Flash format uses vector graphics to store
  graphic information.
• Flash is available on many platforms.

58
Introduction To Flash

• Disadvantages of Flash
• Flash files require the Flash reader (plugin) in
  order to function. Without the plugin Flash will
  not be viewable.
• Flash files compact graphics quite a bit, but in
  the end the graphics still have to be rendered on
  the client and the client must have the
  horsepower to handle that.
• Some images (like photographs) do not work well
  as vector graphics so larger, bitmap images must
  be used.

59
Introduction To Flash

• The Flash Interface

Layers
Timeline
Stage
Toolbox
Properties
60
Introduction To Flash

• The Flash Interface
• Stage This is where most of the editing occurs.
  Here you design/draw the cards that will make
  up the Flash animation.
• Toolbox Tools for creating/editing cards on the
  Stage.
• Properties Settings to modify the elements on
  the cards.
• Timeline This is how the designer controls how
  the animation flows in the Flash file.
• Layers Layers are used to simplify the editing
  of complex images on a card.

61
Introduction To Flash

• Tonight well go over the basic illustrating
  techniques.
• We will see next time how to create animations
  and how to mix ASP and Flash together to create
  dynamic web applications with a rich user
  interface.

62
Program 10

• Build a company database
• Employees
• Jobs
• Select, Insert, Delete, Update records
• Due April 24
• This is our last regular class program
  assignment.
• Continue work on your final project!

63
END