Bmis 289 spring 2002 gonzaga university
Download
1 / 69

BMIS 289: Spring 2002 Gonzaga University - PowerPoint PPT Presentation


  • 316 Views
  • Uploaded on

BMIS 289: Spring 2002 Gonzaga University Class # 09 Introduction to Databases & ASP Agenda Program 7 Database Basics MS Data Access Model Introduction To MS Access ADO Object Model Basic ADO & ASP Example Program 8 Overview Program 7 This program was a text file processor:

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'BMIS 289: Spring 2002 Gonzaga University' - paul2


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Bmis 289 spring 2002 gonzaga university l.jpg

BMIS 289: Spring 2002Gonzaga University

Class # 09 Introduction to Databases & ASP


Agenda l.jpg
Agenda

  • Program 7

  • Database Basics

  • MS Data Access Model

  • Introduction To MS Access

  • ADO Object Model

  • Basic ADO & ASP Example

  • Program 8 Overview

BMIS 289: Spring 2002


Program 7 l.jpg
Program 7

  • This program was a text file processor:

    • Individual dat files are used to input employee records.

    • Format of dat files:

      • <name>,<job title>,<hire date>,<salary>

    • 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 file’s contents.

BMIS 289: Spring 2002


Program 74 l.jpg
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.

BMIS 289: Spring 2002


Program 75 l.jpg
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.

BMIS 289: Spring 2002


Program 76 l.jpg
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 7’s processing page in order to access that function.

BMIS 289: Spring 2002


Program 77 l.jpg
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.

BMIS 289: Spring 2002


Intro to databases l.jpg
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.

BMIS 289: Spring 2002


Intro to databases cont l.jpg
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.

BMIS 289: Spring 2002


Why use databases l.jpg
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.

BMIS 289: Spring 2002


Databases in web applications l.jpg
Databases In Web Applications

  • E-Commerce:

    • Products

    • Customers

    • Logins

  • Intranets

    • Time sheets

    • Expense reports

    • Sales information

  • Misc

    • Music collections

    • Wedding guest lists

    • Personal finances

BMIS 289: Spring 2002


Basic database elements l.jpg
Basic Database Elements

  • Tables

  • Records

  • Fields

  • Keys

BMIS 289: Spring 2002


Table l.jpg
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 company’s quarterly earnings.

  • A database is made up of one or more tables.

BMIS 289: Spring 2002


Record l.jpg
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.

BMIS 289: Spring 2002


Field l.jpg
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.

BMIS 289: Spring 2002


Pulling it all together l.jpg
Pulling It All Together

Table

Record

Field

BMIS 289: Spring 2002


Slide17 l.jpg
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.

BMIS 289: Spring 2002


Foreign keys l.jpg
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.

BMIS 289: Spring 2002


Illustrating foreign keys l.jpg
Illustrating Foreign Keys

Link with a Product table

BMIS 289: Spring 2002


Microsoft data access model l.jpg
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.

BMIS 289: Spring 2002


Microsoft data access model21 l.jpg
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

BMIS 289: Spring 2002


Microsoft data access model22 l.jpg
Microsoft Data Access Model

  • OLE-DB

    • Microsoft’s 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

BMIS 289: Spring 2002


Microsoft data access model23 l.jpg
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

BMIS 289: Spring 2002


Microsoft data access model24 l.jpg
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.

BMIS 289: Spring 2002


Microsoft data access model25 l.jpg
Microsoft Data Access Model

  • MDAC

    • Microsoft Data Access Components (MDAC)

    • ADO is part of the MDAC, which is Microsoft’s 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.

BMIS 289: Spring 2002


Database software l.jpg
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

BMIS 289: Spring 2002


Microsoft access l.jpg
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.

BMIS 289: Spring 2002


Microsoft sql server l.jpg
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.

BMIS 289: Spring 2002


Oracle l.jpg
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.

BMIS 289: Spring 2002


Mysql l.jpg
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.

BMIS 289: Spring 2002


Msde vs access l.jpg
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 Microsoft’s 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.

BMIS 289: Spring 2002


Break l.jpg
Break

BMIS 289: Spring 2002


Access 2000 tutorial l.jpg
Access 2000 Tutorial

  • Creating a new Database

  • Creating Tables

  • Defining Fields

  • Entering Data

BMIS 289: Spring 2002


Creating a new database l.jpg
Creating A New Database

  • Start Access. A wizard screen will appear. Select “Blank Access Database” and click “OK”.

  • Enter a name for thedatabase file.

BMIS 289: Spring 2002


Creating a new table l.jpg
Creating A New Table

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

Database Window

BMIS 289: Spring 2002


Creating a table l.jpg
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

BMIS 289: Spring 2002


Creating a table field types l.jpg
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.

BMIS 289: Spring 2002


Create a table field type properties l.jpg
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.

BMIS 289: Spring 2002


Create a table field type common properties l.jpg
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.

BMIS 289: Spring 2002


Create a table primary keys l.jpg
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 table’s primary key.

    • Right-click on the field and select Primary Key

BMIS 289: Spring 2002


Create a table save it l.jpg
Create A Table – Save It

  • When you are done defining a new table’s fields click the Save icon on the main toolbar.

  • When prompted, enter a name for the table.

BMIS 289: Spring 2002


Create a table define a foreign key l.jpg
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 field’s name.

    • For the field’s data type select Lookup Wizard.

BMIS 289: Spring 2002


Create a table define a foreign key cont l.jpg
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.

BMIS 289: Spring 2002


Create a table define a foreign key cont44 l.jpg
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.

BMIS 289: Spring 2002


Create a table define a foreign key cont45 l.jpg
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:

BMIS 289: Spring 2002


Entering data l.jpg
Entering Data

  • To manually enter data double click the table name from the database window.

  • A data sheet appears showing all the table’s 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.

BMIS 289: Spring 2002


Create a table add records l.jpg
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

BMIS 289: Spring 2002


Ado object model l.jpg
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.

BMIS 289: Spring 2002


A simple asp page w database l.jpg
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

BMIS 289: Spring 2002


A simple asp page w database50 l.jpg
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

BMIS 289: Spring 2002


Breaking it down connection string l.jpg
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 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\datastores\Movie2000.mdb;" & _

"Persist Security Info=False"

Where the Access database is located

BMIS 289: Spring 2002


Breaking it down recordset open arguments l.jpg
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 don’t 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

BMIS 289: Spring 2002


Breaking it down create ado objects l.jpg
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("ADODB.Recordset")

BMIS 289: Spring 2002


Breaking it down open the connection l.jpg
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 doesn’t know what database to point to.

'--- open the connection to the database

objConn.Open strConnect

BMIS 289: Spring 2002


Breaking it down open the recordset l.jpg
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

BMIS 289: Spring 2002


Breaking it down outputting l.jpg
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") & "<BR>"

objRS.MoveNext

Wend

BMIS 289: Spring 2002


Breaking it down outputting57 l.jpg
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.

BMIS 289: Spring 2002


Breaking it down outputting58 l.jpg
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.

BMIS 289: Spring 2002


Breaking it down outputting59 l.jpg
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.

BMIS 289: Spring 2002


Close the connection l.jpg
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.

BMIS 289: Spring 2002


What is a connection l.jpg
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

BMIS 289: Spring 2002


Methods of connecting l.jpg
Methods of Connecting

  • There are three methods for establishing a database connection in ADO:

    • connection string

    • data link files,

    • data source names (DSN)

BMIS 289: Spring 2002


Connection strings l.jpg
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.

BMIS 289: Spring 2002


Using connection strings ssi l.jpg
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 you’re 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.

BMIS 289: Spring 2002


Connection string example l.jpg
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.

BMIS 289: Spring 2002


Data link files l.jpg
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.

BMIS 289: Spring 2002


Data source names l.jpg
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 don’t even have to know the path to the DSN).

  • Avoid using these because they can be too easily changed by another user.

BMIS 289: Spring 2002


Program 8 l.jpg
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 don’t 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

BMIS 289: Spring 2002


Slide69 l.jpg
END

BMIS 289: Spring 2002


ad