BMIS 289: Spring 2003 Gonzaga University

1
BMIS 289 Spring 2003Gonzaga University

• Class 10
• ADO Recordsets

2
Todays Agenda

• Remaining Quizzes
• More about final project
• suggested project milestones
• Program 8
• Recordsets
• Basic Concepts
• Cursors Locks
• Moving through recordsets
• Finding data
• Filtering data
• Program 9

3
Final Project

• Suggested Milestones
• April 10th Full outline for your project,
  detailed flow chart of how the site works,
  description of what each page does and how they
  interact (dont code first!!!!!!!)
• April 17th First version of your web
  application, all pages coded, first round of
  testing during the following week, ask me to take
  a look at it.
• April 24rd Second version of your web
  application, improvements made, bugs fixed, test
  any changes you make during the following week,
  prepare presentation.
• May 1st at regular time or May 8 at 8pm Final
  presentations.

4
Final Project More Details

• If you develop your code on a machine other than
  one in the Jepson labs I highly suggest you test
  it on one of the Jepson workstations.
• For your presentation you will need to install
  your application on the speakers console
  machine.
• Theoretically if you can get your app to work on
  one of the Jepson machines it should be good to
  go, but it may be good to test on the console as
  well (see me for scheduling time to do that, and
  give me plenty of advance notice).

5
Final Project Presentation

• Target for about 15-20 minutes.
• You may take longer if needed, but dont rattle
  on too much.
• 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 from audience and group

6
Program 8

• A modification of program 7
• Instead of processing a dat file we are
  processing a Microsoft Access database.
• Must use connection string include file.
• Algorithm
• Establish connection to database
• Open Employee table and loop through all records.
• For each record output every field and calculate
  number of days employee has been with company.
• Close database connection.

7
MDAC Recap

• Microsofts data access initiative is known as
  UDA (Universal Data Access).
• This is a generic view of data access that
  separates things into two categories
• Data providers serve up data
• Data consumers do something with data
• OLE-DB is a data provider and our ASP pages are
  data consumers (through ADO).

8
What Is ADO?
ASP Page
OLE-DB
ADO
Data Store
9
What Is ADO?

• As you can see from the image in the previous
  slide, ADO sits in front of OLE-DB.
• Recall that OLE-DB is an interface for accessing
  various different data stores.
• However, OLE-DB is very technical and not that
  easy to use.
• ADO provides a more convenient,
  programmer-friendly interface to OLE-DB.

10
What Is ADO?

• ADO is a programming interface that is
  object-oriented.
• It encapsulates database access into several
  objects that represent different aspects of a
  database
• The database itself
• A table
• A record
• A field

11
Object-Oriented Programming

• When we say ADO is object-oriented, what does
  that mean?
• Object-Oriented Programming (OOP) is a
  programming paradigm that models code/algorithms
  in terms of objects.
• For example, we may write a web application that
  is an online store. Our users could be considered
  customers.
• All the code in our web app that dealt with
  customers could be organized into a Customer
  object.

12
Object-Oriented Programming

• Objects contain code that do two things
• Store information
• Properties
• Perform Actions
• Methods (functions)
• At its core, object-oriented programming is just
  another methodology used to break code up into
  smaller, more manageable chunks.
• Modeling code in terms of objects promotes reuse
  and makes code much easier to share and re-adapt.

13
Object Oriented Programming

• Our Customer object has properties that represent
  information about it
• Name
• Address
• CustomerID
• Etc.
• A property is just like a variable in a normal
  program.

14
Object-Oriented Programming

• Our Customer object has methods which perform
  actions associated with a Customer in our
  application
• Create
• creates a new customer
• Load
• load the information about a particular customer
• Etc.
• A method is the same as a function in non-
  object-oriented code (the two names are often
  used interchangeably).

15
Object-Oriented Programming

• Functions and variables are encapsulated within
  an object.
• We access them by prefacing their names with the
  objects name
• Customer.Name John Smith
• Customer.Load(3827)
• We dont need to worry about creating our own
  objects. For now, we are just using them.

16
Object-Oriented Programming

• ASP itself is object-oriented. You have been
  using objects all along to program it
• Request
• Response
• ADO is also object-oriented. It has several
  objects to represent elements of generic data
  stores
• Tables
• Records
• Fields

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

18
Recordsets

• An ADO recordset represents data retrieved from a
  data store.
• It is structured a lot like a table (aka,
  two-dimensional array).
• A recordset may contain 0, 1, or many records.

19
Recordset Basics

• In general there are four basic procedures we
  perform when working with recordsets
• Create the recordset
• Instantiate the Recordset object.
• Open the recordset
• Fill it with data
• Manipulate the recordset
• Output the data, update it, delete it, etc.
• Close the recordset object
• Release it from memory to free up resources.

20
Creating Closing A Recordset

• Created like any other ADO object
• Closing a recordset
• Closing a recordset and then setting it equal to
  Nothing frees up valuable system resources.
• Whenever you are done using a recordset object
  and have no further use for it you should close
  it and set it equal to Nothing. The same goes
  for connection objects.

Dim objRSSet objRS Server.CreateObject(ADODB.R
ecordset)
objRS.CloseSet objRS Nothing
21
Opening A Recordset

• We use the Open method of the recordset object
  to fill it with data.
• The open method follows this format
• The Source parameter indicates where to get the
  data from. It could be the name of a table, a SQL
  statement, a stored procedure, or a query.
• Right now, we are just using table names to pull
  data from the database.

objRS.Open Source, Connection, CursorType,
LockType, Options
22
Recordsets Connections

• The Connection parameter indicates where the data
  provider is located.
• In the last lecture we learned that the
  Connection object is used to establish a link
  between a database and ADO (i.e., our ASP page).
• The Connection parameter can accept a connection
  object, or, it can accept a connection string and
  the recordset will create its own connection
  object.

23
Recordsets Connections

• If we can directly pass in a connection string to
  the recordset why do we bother with a connection
  object?
• When we pass in a connection string a connection
  object is still being created in the background
  we just cant see it.
• Also, if we are going to be drawing data from a
  particular connection multiple times it is more
  efficient to create the connection object once
  and then re-use it as many times as needed,
  rather than letting ASP create a new one every
  time.

24
ADO Constants

• When opening a record we provide parameters like
  CursorType, LockType, and SourceType which
  determine what kind of recordset we get back.
• These parameters are simply integer values (ex
  0, 1, 2)
• A call like this is not very meaningful though
• If we defined constants to indicate what those
  values meant that would be more meaningful.

objRS.Open table, objConn, 0, 1, 2
25
ADO Constants Cont.

• In our previous code examples we defined those
  constants ourselves
• There are actually hundreds of ADO constants you
  can use. Rather than define them all yourself you
  can reference the constants using two methods
• Reference the ADO Type Library
• Include adovbs.inc

adOpenForwardOnly 0 adLockReadOnly
1 adCmdTable 2
26
ADO Constants ADOVBS.inc

• ADOVBS.inc is a text file that contains all the
  declarations for the ADO constants in VBScript.
• You can include this file on any page via an SSI.
• If you have ADO installed search your machine for
  this file, you should already have it.
• Alternatively you can download it off the
  Internet.
• The problem with this method is that it
  dramatically increases the size of your ASP page,
  because the ASP compiler must read in all 400
  declarations.
• This can be inefficient if you only want to
  reference one or two constants.

27
ADO Constants Type Library

• The constants for ADO are originally listed in
  the code that implements ADO itself.
• This code resides in what is known as a DLL file
  on Windows. The DLL contains the constant
  definitions.
• We can reference this DLL directly in our ASP
  code and thus reference the constants.

28
ADO Constants Type Library

• The code to reference the DLL directly is similar
  to an SSI
• This reference can be used on a page-by-page
  basis or used once in global.asa
• If referenced in global.asa make sure you place
  the include before the ltSCRIPTgt tags.

lt!-- METADATA TYPE"typelib
FILE"C\Program Files\Common Files\System\ado\msa
do15.dll" --gt
29
Break
30
Locks And Cursors

• When we manipulate tables and records with code
  we, as programmers, must be concerned with two
  important concepts
• Locking
• Cursors
• Locking is a way of preventing other users (or
  programs) from manipulating records in a database
  table.
• Cursors are a way of navigating a table of
  records. A cursor is nothing more than a pointer.

31
Recordset Cursors

• A cursor has two major functions within
  ADO/OLE-DB
• Manage what can be done with the set of records
  in a recordset.
• Allow the programmer to access (see) individual
  records within the recordset.

32
Recordset Cursors - Pointer

• A recordset cursor is a pointer that indicates
  what record we are currently at in a recordset.
• Think of it as a way to navigate around the many
  records in a recordset.

cursor
33
Manipulating The Cursor

• ADO provides us with methods and properties to
  manipulate the cursor.
• By manipulating the cursors position we can
  accomplish two things
• Search for a particular record we can jump the
  cursor to a specific record.
• Navigate many records we can move through the
  records one record at a time or jump to the
  beginning or end of a recordset.

34
Cursor Type

• In addition to pointing at individual records,
  the cursor itself contains information about what
  can be done with those records.
• This notion is known as the cursor type.
• Depending on what type of cursor you have in your
  recordset you may or may not be able to perform
  certain functions
• Like update the data in the recordset.

35
Types Of Recordsets (Cursor Type)

• When we think of the CursorType property we need
  to think about what type of recordset we want
• Updateable vs. Non-Updateable
• Scrollable vs. Non-Scrollable
• Keyset vs. Non-Keyset
• Dynamic vs. Static

36
Updateable vs. Non-Updateable

• If you plan to change a records data in some
  fashion then you will want to get an updateable
  recordset.
• If you do not plan to change any of the data in a
  recordset then you only need a non-updateable
  (read-only) recordset.
• A read-only recordset is faster than an
  updateable one.

37
Scrollable vs. Non-Scrollable

• If you only need to move forward through a
  recordset then use a non-scrollable
  (forward-only) recordset.
• If you need to move forwards and backwards
  through a recordset then a scrollable recordset
  is required.
• In most cases, when you are just outputting data,
  a forward-only recordset is most appropriate
  since it is faster.

38
Keyset vs. Non-Keyset

• A non-keyset recordset will return all the fields
  for the records you request (as indicated by the
  source parameter) at once.
• A keyset recordset will return only the requested
  records keys (i.e., primary key)
• Once you move to a particular record in the
  recordset then ADO fetches that records entire
  data.
• Databases maintain their own internal keys for
  each record, even if you dont define a key
  field.
• If you are going to fetch a large amount of data
  then a keyset recordset might be faster.

39
Dynamic vs. Static

• In a static recordset any changes that occur to
  the data after you have retrieved it will not be
  reflected in the recordset.
• In other words you are working off a cached copy
  of data.
• In a dynamic recordset records are loaded as they
  are requested, so updates and changes can be seen
  quicker.
• A static recordset is less resource intensive as
  a dynamic one. But not as up to date.
• Dynamic recordsets are appropriate for real
  time data.

40
CursorType

• The following ADO constants can be used in the
  CursorType parameter of the Open method
• adForwardOnly default, non-scrollable
• adOpenStatic scrollable
• adOpenDynamic dynamic, scrollable
• adOpenKeyset similar to dynamic but cant see
  records other users add, but can see changes to
  existing records (records that are deleted become
  inaccessible)

41
CursorType Property

• A recordsets CursorType does not have to be set
  using the Open method.
• The recordset object has a property called
  CursorType which can be read or set by the code
• If you assign CursorType a value it must be done
  before the recordset is assigned a connection.

Response.Write rs.CursorType rs.CursorType
adOpenStatic
42
Cursor Location

• The recordset object contains a property called
  CursorLocation which allows you to change who is
  responsible for creating its cursor.
• The location is either client or server
• The client is the ASP application
• The server is the data provider (OLE-DB)
• Server is the default cursor type
• Certain ADO functionality is not available on
  client cursors, and the same goes for server
  created cursors.
• For example, dynamic recordsets can only be used
  with server created cursors.
• In general, you should not need to change the
  cursor location default for every day things like
  reading and output.

43
Recordset Locks

• Locking is important to consider when we need to
  update records.
• When you query records from a table you are
  actually reading a copy of the records from the
  table.
• The original data remains in the table.
• Thus, when you want to update the records a two
  step process must be followed
• Update the copy of the records.
• Apply the changes, made to the copy, to the
  actual records in the table.

44
Recordset Locks Cont.

• This process of updating is where record locking
  comes into place.
• While the update process is happening the
  record(s) being updated are locked, which will
  prevent other users or programs from updating
  those records while the changes are being made.
• Locking is generally only pertinent when we are
  updating records in a table.
• In other words, if you are just reading data from
  a table then dont lock the records!

45
LockType

• The following ADO constants can be used in the
  LockType parameter of the Open method
• adReadOnly default, no updating allowed.
• adLockPessimistic updateable recordset, record
  is locked as soon as you start editing and
  unlocked only when edits are complete.
• adLockOptimistic updateable recordset, records
  locked only when you update, if two changes
  happen at the same time the first one to be
  applied wins.
• adLockBatchOptimistic allows you to modify
  several records and update all-at-once, only
  locks each record as it is being updated.

46
Locking At The DBMS Level

• ADO/OLE-DB provides a level of abstraction at
  which we can manipulate the locking functionality
  of a given database.
• Remember, ultimately the database system itself
  is responsible for implementing and maintaining
  its own locking system.
• Some DBMSs give you broad access to their
  locking system, while others keep it largely
  hidden and automatic.
• The golden rule is to read all available
  documentation about your particular database to
  see how it implements locking.

47
Options

• This parameter tells the ADO method Open what
  form the Source will take
• adCmdText command text of some kind, like a SQL
  statement.
• adCmdTable a table (generated from SQL)
• adCmdTableDirect a table
• adCmdStoredProc stored procedure.
• adCmdFile a saved recordset.
• adCmdURLBind a url.
• If you tell the recordset what kind of data the
  source is it can more efficiently fetch it.

48
BOF EOF

• These are two properties of all ADO recordsets.
• They reflect the current position of the cursor
  within the recordset.
• EOF end of file
• When we move one position past the last record
  then EOF becomes true.
• BOF beginning of file
• When we move one position before the first record
  then BOF becomes true.
• If EOF and BOF are both true then there are no
  records in the recordset.

49
Ex Moving Through Records

• See page 530 and our code example move.asp
  (available for download on the class website).
• We can move through a recordset in a relative
  fashion by using
• MoveFirst move to the first record
• MoveNext move to the record next to current
• MovePrevious move to record behind current
• MoveLast move to last record
• We can move directly to a record in a recordset
  using the Move method

Move NumRecords, Start
50
Moving Through Records
Move.asp

• The argument Start has three possible values
• 0 default, start search at the current record.
• 1 start moving from first record.
• 2 start moving from last record.
• Move is not used very often, but when used it is
  critical to know what record your cursor is
  pointing at right before it is called.

51
Finding Records

• ADO recordsets have the Find method to search for
  a particular record in a recordset.
• Note SQL searches are a better way to search for
  data (particularly searches that return multiple
  records), we will look at this in detail next
  time.
• A basic search is structured like this
• Where SearchCriteria is a string that contains
  a set of comparisons that describes what were
  looking for in the recordset.

objRS.Find SearchCriteria
52
Search Criteria

• Some examples of search criteria
• Notice how when searching for strings we need to
  enclose the actual search value with
  single-quotes instead of double-quotes.
• Also notice how date values are surrounded with
  number signs.

objRS.Find Name Colin Fukai objRS.Find Age
22 objRS.Find Birthday 04/28/78 objRS.Fin
d Name LIKE Fukai
53
Successful Unsuccessful Searches

• If a search is successful then the cursor is
  placed at a specific record.
• If you are searching forwards and no records are
  found then EOF is true
• Note by default, we search forward.
• If you are searching backwards and no records are
  found then BOF is true.

54
A Search Example
PromptForDirector.htm FindDirector.asp

• See page 541-545
• Notice how the programmer built the criteria
  string from a form input and then passed that
  criteria string into the Find method.
• Also notice how this code is not really useful
  unless we can be guaranteed that we will only
  find one record at a time (e.g., what if the
  database contained more than one movie directed
  by the same director?).

55
Filtering

• One way to get around the problem of searching
  for multiple records is filtering.
• A filter involves finding all the records in a
  recordset that meet a certain criteria and then
  hiding all the records that dont meet that
  criteria.
• When we search (find) we point a cursor to one
  particular record. When we filter we are setting
  a property that says, show only these records.

56
Filtering Cont.
Filter.asp

• A filter would look something like this
• The criteria used in filters is the same that is
  used in searches.
• You remove filters from a recordset by setting
  the filter equal to the ADO constant
  adFilterNone
• See page 548 for an example

objRS.Filter Age 22
objRS.Filter adFilterNone
57
Program 9

• Searching and Filtering database records.
• Use generic error handling routines
• If no records are found based on criteria then
  output a message that says so.
• Since the database is Access 2000 you will want
  to write this program on a machine that has
  Access 2000 on it.
• Get started early on this one!!!!

58
END
Dont forget to work on your final project!!! I
suggest you have a detailed spec done by next
week.