BMIS 289: Spring 2002 Gonzaga University

1
BMIS 289 Spring 2002Gonzaga University

• Class 09 Introduction to Databases ASP

2
Agenda

• Program 7
• Database Basics
• MS Data Access Model
• Introduction To MS Access
• ADO Object Model
• Basic ADO ASP Example
• Program 8 Overview

3
Program 7

• This program was a text file processor
• Individual dat files are used to input employee
  records.
• Format of dat files
• ltnamegt,ltjob titlegt,lthire dategt,ltsalarygt
• User selects dat file by browsing from HTML file
  selector
• NOTE in the case of this program we are assuming
  the user of this program is browsing off the
  server, otherwise the ASP code cannot read the
  dat files contents.

4
Program 7

• Once we have a path to a dat file we can begin
  processing it.
• We use the TextStream object to read the contents
  of the dat file
• The ReadLine method of the TextStream object
  returns an individual line from a text file as a
  string.
• We then need to parse out the individual data in
  each string.

5
Program 7

• Parsing information out of a string
• We know the format of the string (i.e., there are
  4 pieces of information in each line delimited by
  commas).
• The easiest way to get at each element of
  information is to use the Split function.
• The Split function takes a string and delimiter
  as arguments.
• It returns an array containing each individual
  element in the string, based on the delimiter.

6
Program 7

• Computing number of days employee has been with
  the company
• One of the pieces of information in the dat file
  records (individual lines of information) is the
  date the employee was hired.
• A separate ASP file was provided to you that has
  one function in it DateDiffDays.
• This ASP file must be included in program 7s
  processing page in order to access that function.

7
Program 7

• The main processing loop will perform these
  steps
• ReadLine from TextStream.
• Parse information out of line using Split into an
  array.
• Output employee information.
• Calculate number of days employee has been with
  company using DateDiffDays.
• Increment employee counter.

8
Intro To Databases

• The database is a fundamental information
  management concept.
• A database is nothing more than a collection of
  related information (like a table or a list).
• Nearly every major program ever written uses a
  database of some form or another, it is that
  critical.
• Databases let us, as programmers, create, access,
  and manipulate large collections of information
  in a quick and efficient manner.

9
Intro To Databases Cont.

• From the basic database concept the idea of a
  relational database was developed.
• A relational database is set of
  formally-described tables from which data can be
  accessed or reassembled in many different ways
  without having to reorganize the database
  tables.
• The Structured Query Language (SQL) is used to
  access and manipulate relational databases.
• One of the most popular relational database
  management systems out there is Microsoft Access,
  which we will use in this class.

10
Why Use Databases?

• Quite simply, databases help us organize pieces
  of information.
• Databases pull together extraneous bits of data
  and force humans to organize it into some
  meaningful relationship.
• For programmers, they provide efficient,
  convenient access to structured collections of
  data.

11
Databases In Web Applications

• E-Commerce
• Products
• Customers
• Logins
• Intranets
• Time sheets
• Expense reports
• Sales information
• Misc
• Music collections
• Wedding guest lists
• Personal finances

12
Basic Database Elements

• Tables
• Records
• Fields
• Keys

13
Table

• A table is an individual collection of
  information within a database.
• Each table should represent a single idea,
  object, or thing.
• For example, a Employee table should not
  include information about the companys quarterly
  earnings.
• A database is made up of one or more tables.

14
Record

• Think of a record as a single row in a table.
• If you are a student at Gonzaga then you have one
  record in the Students table of the GU database.
• Just as a table has many records, a record has
  many bits of information associated with it.
• Those bits of record information are fields.

15
Field

• Each individual piece of information in a record
  is one field.
• If a record is considered a row, then a field is
  a column, or, in each instance of a record, a
  cell.
• Every record contains one instance of each field.
• A field is defined by its name and its data type.
• For example, a user name field might be called
  Uname and have a string data type.
• In code, Fields are synonymous with variables.

16
Pulling It All Together
Table
Record
Field
17
Keys

• One problem we have with databases is how can we
  differentiate between records?
• For example, there may be more than one employee
  in a given company named John Smith.
• The answer is to use Keys.
• A key is some sort of identification
  number/string that is unique to a particular
  record.
• A key that uniquely identifies a record is called
  a primary key.
• No two records may have the same primary key
  within a given table.

18
Foreign Keys

• In some cases, primary keys of one table can be
  used in other tables to link data between records
  (a.k.a., create relationships among data).
• For example, an e-commerce web site may have a
  Customer and Purchase table.
• Customers and Purchases are uniquely identified
  via primary keys called CustID and PurchID
  respectively.
• The Purchase table would contain a CustID field
  that would only accept values from the Customer
  table.
• Thus the field Purchase.CustID would allow us to
  trace an individual purchase back to a particular
  customer.

19
Illustrating Foreign Keys
Link with a Product table
20
Microsoft Data Access Model

• Microsoft is a proponent of a technology it calls
  Universal Data Access (UDA)
• UDA is a common way of accessing data stored in
  various formats, like Microsoft Access, Oracle,
  and even Microsoft Word.
• Microsoft calls these heterogeneous data sources
  data stores.
• The nice thing about the UDA concept is it allows
  us to access a wide array of data stores using a
  single programming interface.
• So the code to fetch a record from Oracle
  residing on Unix is (for the most part) the same
  as it is for Microsoft Access running on Windows
  98.

21
Microsoft Data Access Model

• ODBC
• Open Database Connectivity
• An earlier Microsoft attempt at UDA, it worked on
  many different database packages but that was it.

Application
ODBC
Oracle
SQLServer
Access
22
Microsoft Data Access Model

• OLE-DB
• Microsofts next attempt at UDA.
• It is a lower level interface that provides
  access to more than just databases (i.e., data
  stores)
• It is faster and more efficient than ODBC.

Application
OLE-DB
ODBC
Oracle
Access
Excel
MSExchange
ODBCData
23
Microsoft Data Access Model

• Data Provider vs. Data Consumer
• Microsoft thinks of data access in terms of
  providers and consumers.
• Data Provider
• Something that provides data
• For example, OLE-DB
• And in OLE-DB there are various providers for
  specific data store systems.
• Data Consumer
• Something that uses data
• For example, ASP pages

24
Microsoft Data Access Model

• ADO
• ActiveX Data Objects
• ADO is an application programming interface
  between a data consumer and OLE-DB (the provider)
• It has a set of objects which encapsulate several
  common actions we can take on databases.
• ASP ADO are not the same
• Many people tend to think of ASP ADO as one and
  the same.
• They are not.
• ADO is a separate technology that can be used in
  ASP.
• It can also be used in Visual Basic or a C
  windows application, to name a few.

25
Microsoft Data Access Model

• MDAC
• Microsoft Data Access Components (MDAC)
• ADO is part of the MDAC, which is Microsofts
  free suite of data access components for use by
  programmers.
• You can obtain the latest versions at
  www.microsoft.com/data (and our class web site)
• NOTE the most current version (as of this class)
  of MDAC is 2.7. At the time of your books writing
  it was 2.5.

26
Database Software

• As we have mentioned, there are various software
  packages available that provide relational
  database functionality.
• There are four major database packages we will
  briefly examine
• Microsoft Access
• Microsoft SQL Server
• Oracle
• MySQL

27
Microsoft Access

• Access is what is known as a desktop database.
• It is designed to be user friendly and run on
  individual client machines, rather than act as a
  backend on a server.
• It has many features for making the creation of
  custom queries, reports, and even applications
  based upon its data very easy.
• Access can act like a backend database but it
  really was not designed to be one.

28
Microsoft SQL Server

• A powerful database system that is designed to
  run as a backend database on a server.
• SQL server can hold a lot of data and has
  numerous features that make it more faster,
  robust, and secure than Microsoft Access.
• It is more expensive than Microsoft Access and
  not as easy to use or maintain.
• For very large applications that have many users
  SQL Server is more appropriate than Access.

29
Oracle

• Oracle was one of the first enterprise strength
  relational databases.
• It competes with large relational database
  packages like SQL Server and MySQL.
• Oracle is extremely powerful with the ability to
  handle very large volumes of data at a fast rate.
• Generally considered to be more complex and less
  user friendly than SQL Server.

30
MySQL

• MySQL is an enterprise relational database like
  Oracle and SQL Server that runs primarily on the
  Linux platform.
• Though it can run on Windows NT.
• MySQL is open source and free for public use.
• There are commercial licensed versions that can
  be purchased.
• MySQL is powerful but can be difficult to use.

31
MSDE vs. Access

• In the book (starting on pg. 472) the authors
  discuss the option of using MSDE over Access.
• MSDE is a light-weight version of Microsofts
  industrial strength database product SQL Server.
• We will use Access for all our database projects
  in this class.
• If you are interested in exploring more about
  MSDE and SQL Server, however, you may follow some
  of the examples in the book.

32
Break
33
Access 2000 Tutorial

• Creating a new Database
• Creating Tables
• Defining Fields
• Entering Data

34
Creating A New Database

• Start Access. A wizard screen will appear. Select
  Blank Access Database and click OK.
• Enter a name for thedatabase file.

35
Creating A New Table

• With Tables selected in the database window
  double-click Create Table In Design View

Database Window
36
Creating A Table

• The window that appears is a sheet for defining
  fields within the table. This view is known as
  the design view.
• Enter the field name, select its data type and
  set any special properties for the field.

Fields
Field Properties
37
Creating A Table Field Types

• A field can have several data types in Access.
  Here are the more common ones
• Autonumber used primarily for record ids.
  Numeric values automatically generated by Access.
  For example the first records would have a value
  of 1, the next 2, the next 3 and so on.
• Text used for textual data. Has a max size limit
  of 255.
• Number numeric values.
• Yes/No a boolean field (the only possible values
  are true/false).
• Date/Time used to hold dates and/or times.

38
Create A Table Field Type Properties

• The different field data types have properties
  associated with them that extend their meaning.
  Here are the more common ones for some
• Number
• Field Size the type of number this field can
  hold (ex long, integer, double)
• Decimal Places the amount of precision on the
  number (usually left at auto, which lets
  FieldSize determine the precision).
• Text
• Field Size the maximum number of characters
  allowed. Can be no more than 255.

39
Create A Table Field Type Common Properties

• Many field data types have common properties
• Required A yes/no value. If yes, Access
  generates an error if either a user or ADO
  attempts to create a record without providing
  data for this field.
• Default Value the value a new record has when it
  is initially created.
• Indexed a yes/no value. If yes, Access indexes
  all the values in this field, making performance
  faster but increasing database size.

40
Create A Table Primary Keys

• To define the primary key for a table follow
  these steps
• In Design View select the field that will be the
  tables primary key.
• Right-click on the field and select Primary Key

41
Create A Table Save It

• When you are done defining a new tables fields
  click the Save icon on the main toolbar.
• When prompted, enter a name for the table.

42
Create A Table Define A Foreign Key

• Follow these steps to create a foreign key
  reference in a table.
• Remember, two tables with their own primary keys
  are required to create a foreign key in one of
  the tables, so ensure you have two tables.
• In the table that will have the foreign key type
  in the fields name.
• For the fields data type select Lookup Wizard.

43
Create A Table Define A Foreign Key (cont)

• A wizard starts that lets you define the foreign
  key.
• Select I want the lookup column to lookup the
  values in a table or query.
• Click Next.
• Select the name of the table that this table will
  link to on the next screen.
• Click Next.

44
Create A Table Define A Foreign Key (cont)

• In the left hand column select the field that
  will be the foreign key in this table.
• Click ? to move the field name to the right hand
  column.
• Click Next.

45
Create A Table Define A Foreign Key (cont)

• In the next screen click Finish (there is no need
  to continue on with the Wizard at this point) and
  Access will create the foreign key relationship.
• When Access gives you this prompt say Yes

46
Entering Data

• To manually enter data double click the table
  name from the database window.
• A data sheet appears showing all the tables
  data.
• The last row in the data sheet represents a new
  record. Click inside any blank field to begin
  entering data.
• When you have finished entering data click
  outside of the row and Access will attempt to
  create the new record.
• If there are any errors Access will tell you
  about them before permanently writing the record
  to the database.

47
Create A Table Add Records

• The pencil icon in the extreme left edge of a
  record means it is being edited
• When the focus moves from that record any changes
  to the record are written to the database

Record is being edited
Record is not being edited
48
ADO Object Model

• Primary ADO Objects
• Connection the actual link between the web page
  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.

49
A Simple ASP Page w/Database

• Based upon example starting on page 475 in the
  textbook.
• Download class 9 sample code from class web site.
• DisplayAllMovies.asp

50
A Simple ASP Page w/Database

• The DisplayAllMovies.asp example has three basic
  steps for displaying all the titles out of the
  Movies table
• Connect to the database
• Display the data
• Close the connection

51
Breaking It Down Connection String

• strConnect is a variable that will hold the
  connection string.
• The connection string is the information about
  how to connect to the database and where it is
  located on the server.

'--- create the connection string strConnect
"ProviderMicrosoft.Jet.OLEDB.4.0" _
"Data SourceC\datastores\Movie2000.mdb" _
"Persist Security InfoFalse"
Where the Access database is located
52
Breaking It Down Recordset Open Arguments

• These variables will be passed in as arguments to
  the function that opens the recordset and puts
  data into it
• For now dont worry about what they do, just copy
  them into your code and pass them as we will see
  illustrated later.

'--- variables to hold argument values
adOpenForwardOnly 0 adLockReadOnly 1
adCmdTable 2
53
Breaking It Down Create ADO Objects

• This section of code creates two objects a
  connection object and a recordset object
• The connection object is what will actually
  establish the link between the ASP page and the
  database.
• The recordset object will, later, get filled with
  data from the connection (database).

'--- create the connection and recordset
objects Set objConn Server.CreateObject("ADODB
.Connection") Set objRS Server.CreateObject("A
DODB.Recordset")
54
Breaking It Down Open The Connection

• This line of code opens the actual connection to
  the database
• The method Open takes a valid connection string
  as an argument.
• Remember, strConnect holds the actual
  instructions for connecting. Without it, the
  connection object doesnt know what database to
  point to.

'--- open the connection to the database
objConn.Open strConnect
55
Breaking It Down Open The Recordset

• This line of code opens the table called Movies
  and fills the recordset with all records from
  that table
• The Open method for the recordset takes five
  arguments.
• The first is the name of the table to open
• The second is the connection where the table is
  located at (database).
• The last three are the variables we defined
  earlier. Just leave them as is for now.

objRS.Open "Movies", objConn, adOpenForwardOnly,
adLockReadOnly, adCmdTable
56
Breaking It Down Outputting

• The following code loops through the recordset
  and outputs the Title of each record

While Not objRS.EOF Response.Write
objRS("Title") "ltBRgt" objRS.MoveNext Wend
57
Breaking It Down Outputting

• A recordset is a lot like an array.
• In arrays we use indexes to keep track of where
  we are in the array.
• Recordsets use something called cursors that
  point at a particular record in the recordset.
• There are properties and methods built into the
  recordset object for manipulating this cursor and
  seeing where it is currently pointed.

58
Breaking It Down - Outputting

• The EOF property of a recordset is a boolean
  value
• If true it means we are at the end of the
  recordset and there are no more records left.
• If false it means the cursor is still pointing at
  a valid record.
• The MoveNext method makes the cursor in the
  recordset point to the very next record.
• MoveNext is like incrementing the index counter
  when processing an array with a loop.
• When MoveNext points to nothing then EOF becomes
  true.

59
Breaking It Down - Outputting

• To access fields in a recordset we simply pass
  the name of the field we want to read from to the
  recordset.
• For example, the code to access the field called
  Title would look like this
• objRS(Title)
• Think of objRS(Title) as a variable. You can
  output it, process it, or change it.
• For now we are just concerned with reading it.

60
Close The Connection
objRS.Close objConn.Close Set objRS Nothing Set
objConn Nothing

• Here we are closing both the recordset and
  connection objects, and then releasing them from
  memory.

61
What Is A Connection?

• A connection is what links the ADO objects to the
  data store (database).
• It is nothing more than a text string that
  contains information for connecting to a
  particular database
• Some types of information provided in this
  connection string are
• The type of OLE-DB provider used
• Name of the database file and its location
• Any username or password the database requires

62
Methods of Connecting

• There are three methods for establishing a
  database connection in ADO
• connection string
• data link files,
• data source names (DSN)

63
Connection Strings

• This is the method used in the example on page
  475.
• A character string that lists all of the
  information needed to connect.
• Difficult, at first, to use but very powerful and
  flexible.
• This is the method we will use in our programs to
  perform connections.

64
Using Connection Strings SSI

• It is most convenient to write your connection
  string in a separate ASP file and then include
  that in a page youre making a database
  connection on.
• In the included file you would write a function
  that returns a valid connection string.
• In your ASP code you would then call that
  function in order to get the connection string
  used to open the connection object.

65
Connection String Example

• The sample file DisplayAllMoviesSSI.asp is a
  modification of the previous example
  (DisplayAllMovies.asp).
• The include file datastore.asp contains a
  function called GetDBConnectionString() which
  returns a valid connection string.
• In the code that includes datastore.asp you
  simply call this function to get the connection
  string.

66
Data Link Files

• These are not used for actual connections very
  often anymore.
• They are useful for generating a connection
  string you then code into your ASP programs
• Create a blank text document and rename it
  temp.udl
• Right click on temp.udl and select properties
• Click the Provider tab, select Microsoft Jet 4.0
  OLE-DB Provider, and then click Next.
• Select the Access database you want to connect to
  and click OK.
• Open the temp.udl file in Notepad. Copy all the
  line starting with Provider into your ASP code.
  That is your connection string.

67
Data Source Names

• Also known as DSNs
• Very similar to data link files except they are
  easier to create and all you have to remember is
  the name of the DSN (you dont even have to know
  the path to the DSN).
• Avoid using these because they can be too easily
  changed by another user.

68
Program 8

• Just like program 7, but now we are reading from
  a database file.
• You must create an Access database that contains
  the four employee fields
• Name
• Job Title
• Hire Date
• Salary
• Make sure you dont forget to calculate the
  number of days the employee has been with the
  company.
• Also, be sure to place the database file in a
  directory called datastores on your c\ drive

69
END