Transcript cos346day22

COS 346
Day 22
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-1
Agenda
• Questions?
• 2 Quizzes 2 go
– April 20
• DP Chap 9 & 11, SQL Chap 11
– May 4
• DP Chap 12 – 15
• Next Capstone progress report Due April 24
• Assignment 8 Corrected
– 3 B’s, 2 C’s, 1 D and 1 F (late)
– 3 more assignments to Go
– Lowest assignment score dropped
• Assignment 9 is posted
– Due April 24
– More detailed instructions on 12.53 will be provided
• Capstones projects and presentations are due May 12 at 10AM
• Today we will be discussing ODBC, OLE DB, ADO, and ASP
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-2
David M. Kroenke’s
Database Processing:
Fundamentals, Design, and Implementation
Chapter Twelve:
ODBC, OLE DB, ADO,
and ASP
Part One
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-3
Introduction
• Because database applications today reside in a
complicated environment, various standards have been
developed for accessing database servers.
• Some of the important standards are
– OBDC (Open Database Connectivity) is the early standard for
relational databases.
– OLE DB is Microsoft’s object-oriented interface for relational and
other databases.
– ADO (Active Data Objects) is Microsoft’s standard providing
easier access to OLE DB data for the non-object-oriented
programmer. Lasted version is ADO.NET
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-4
The Web Server Data Environment
• A Web
server
needs to
publish
applications
that involve
different
data types.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-5
The Role of the ODBC Standard
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-6
The Role of OLE DB
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-7
The Role of ADO
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-8
Open Database Connectivity
(OBDC)
• The Open Database Connectivity (ODBC) standard
provides a DBMS-independent means for processing
relational database data.
• It was developed in the early 1990s by an industry
committee and has been implemented by Microsoft and
many other vendors.
• The goal is to allow a developer to create a single
application that can access databases supported by
different DBMS products without needing to be changed
or recompiled.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-9
OBDC Components (Continued)
• OBDC consists of a data source, an
application program, a driver manager, and a
DBMS driver.
• A data source is the database and its
associated DBMS, operating system, and
network platform.
– An ODBC data source can be a relational database, a
file server, or a spreadsheet.
• An applications program issues requests to
create a connection with a data source.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-10
ODBC Architecture
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-11
OBDC Components
• A driver manager determines the type of DBMS
for a given ODBC data source and loads that
driver in memory.
• A DBMS driver processes ODBC requests and
submits specific SQL statements to a given type
of data source.
– A single-tier driver processes both ODBC calls and
SQL statements.
– A multiple-tier driver processes ODBC calls, but
passes the SQL requests to the database server.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-12
ODBC Driver Types:
ODBC Single-Tier Driver
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-13
ODBC Driver Types:
ODBC Multiple-Tier Driver
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-14
Conformance Levels
• Levels of conformance balance the scope of the
OBDC standard.
• There are two types of conformance levels:
– ODBC conformance levels concern the features and
functions that are made available through the driver’s
application program interface (API).
• A driver API is a set of functions that the application can call
to receive services.
– SQL conformance levels specify which SQL
statements, expressions, and data types a driver can
process.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-15
Summary of
OBDC Conformance Levels
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-16
Summary of
OBDC Conformance Levels
(Continued)
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-17
Summary of
SQL Conformance Levels
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-18
Summary of
SQL Conformance Levels
(Continued)
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-19
ODBC Data Sources
• A data source is an ODBC data structure that identifies
a database and the DBMS that processes it.
• Three types of data source names:
– A file data source is a file that can be shared among database
users having the same DBMS driver and privilege.
– A system data source is local to a single computer and may be
used by the operating system and any user on that system.
• System data sources are recommended for Web servers.
• To define a system data source name, the type of driver and the
database need to be specified.
– A user data source is available only to the user
who created it.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-20
Creating a System Data Source:
Selecting the Oracle Driver
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-21
Creating a System Data Source:
Setting Data Source Properties
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-22
Creating an OBDC connector
for View Ridge
• Restore the view ridge Database
– Create a new database called View Ridge on
your SQL server
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-23
• Right click on the database and select
restore database
• Select from device
• Select ‘Select Device’
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-24
• Click ADD
• Navigate to f:\Program Files\Microsoft SQL
Server\<first name>\MSSQL$<first
name>\backup and select the View ridge
Backup
• Click OK 3 times
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-25
Restoring View Ridge
• Click options tab
• Select “Force restore”
• Change file locations
• F:\Program Files\Microsoft SQL Server\<first
name>\MSSQL$<first name>\data\ “database
file”
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-26
Create a Connector
• On your local machine
– Control panel > administrative tools >data
sources (OBDC) > system DSN
– Click add
– Select SQL server
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-27
Create a Connector
• Name = ViewRidgeSS
• Description =?
• Server
– LittleBlack\<name>
• Select Next
–
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-28
Create a connector
• Set as shown
• NEXT
• Set default DB to
– View ridge
• Next
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-29
Create a connector
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-30
David M. Kroenke’s
Database Processing
Fundamentals, Design, and Implementation
(10th Edition)
End of Presentation:
Chapter Twelve Part One
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-31
David M. Kroenke’s
Database Processing:
Fundamentals, Design, and Implementation
Chapter Twelve:
ODBC, OLE DB, ADO,
and ASP
Part Two
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-32
OLE DB
• OLE DB is an implementation of the Microsoft
OLE object standard.
– OLE DB objects are COM objects and support all required
interfaces for such objects.
• OLE DB breaks the features and functions of a DBMS
into COM objects, making it easier for vendors to
implement portions of functionality.
– This characteristic overcomes a major disadvantage of ODBC.
– With ODBC, a vendor must create an ODBC driver for almost all
DBMS features and functions in order to participate in ODBC at
all.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-33
The Role of OLE DB
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-34
Object-Oriented Concepts
• An object-oriented programming object is an
abstraction that is defined by its properties and
methods.
– An abstraction is a generalization of something.
– A property specifies set of characteristics of an
object.
– A method refers to actions that an object can
perform.
– A collection is an object that contains a group of
other objects.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-35
OLE DB Goals
• Create object interfaces for DBMS functionality pieces:
– Query, update, transaction management, etc.
• Increase flexibility:
–
–
–
–
Allow data consumers to use only the objects they need.
Allow data providers to expose pieces of DBMS functionality.
Providers can deliver functionality in multiple interfaces.
Interfaces are standardized and extensible.
• Provide object interfaces over any type of data:
– Relational and non-relational database, ODBC or native, VSAM
and other files, Email, etc.
• Do not force data to be converted or moved from where
it is.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-36
OLE DB Basic Constructs
• There are data consumers and data providers:
– Data consumers - Users of OLE DB functionality.
– Data providers - Sources of OLE DB functionality.
• An interface is a set of objects and the properties and
methods they expose in that interface:
– Objects may expose different properties and methods in different
interfaces.
• An implementation is how an object accomplishes its
tasks:
– Implementations are hidden from the outside world and may be
changed without impacting the users of the objects.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-37
OLE DB Terminology:
Data Providers
• A rowset is equivalent to a cursor.
• OLE DB has two types of data providers:
– Tabular data provider — exposes data via rowsets.
• Examples: DBMS, spreadsheets, ISAMs, email.
– Service provider — a transformer of data through
OLE DB interfaces.
• It is both a consumer and a provider of transformed data.
• Examples: query processors, XML document creator.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-38
Rowset Interfaces
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-39
Active Data Objects (ADO)
• Active Data Objects (ADO) characteristics:
– A simple object model for OLE DB data consumers
– It can be used from VBScript, JScript, Visual Basic,
Java, C#, C++
– It is a single Microsoft data access standard
– Data access objects are the same for all types of OLE
DB data
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-40
Invoking ADO from Active Server Pages
• In Microsoft’s Active Server Pages (ASP)
are Web pages where:
– Statements are enclosed within the
characters <% . . .%>.
– ASP statements are processed on the Web
server.
– Other (HTML) statements are processed by
the client Web browser.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-41
The ADO Object Model
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-42
Connection Object
• A connection object establishes a connection
to a data provider and data source.
– Connections have an isolation mode.
• Once a connection is created, it can be used to
create RecordSet and Command objects.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-43
RecordSet Objects
• RecordSet objects represent cursors:
– They have both CursorType and LockType properties.
– RecordSets can be created with SQL statements.
– The Fields collection of a RecordSet can be
processed to individually manipulate fields.
– The Errors collection contains one or more error
messages that result from an ADO operation.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-44
Command Object
• The command object is used to execute
stored parameterized queries or stored
procedures:
– Input data can be sent to the correct ASP
using the HTML FORM tag.
– Table updates are made using the RecordSet
Update method.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-45
ADO Constants:
Isolation Levels
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-46
ADO Constants:
Cursor Levels
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-47
ADO Constants:
Lock Types
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-48
Connection Object:
ASP Code
<%
Dim objConn
Set objConn = Server.CreateObject (“ADODB.connection”)
objConn.IsolationLevel = adXactReadCommitted
‘ use ADOVBS
objConn.Open “ViewRidgeSS”,
%>
<!--#include virtual =“ADOExamples/ADOVBS.inc -->
<%
objConn.Open “DSN=ViewRidgeOracle2;UID=DK1;PWD=Sesame”
%>
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-49
RecordSet Object:
ASP Code
<%
Dim objRecordSet, varSql
varSQL = “SELECT * FROM ARTIST”
Set objRecordSet = Server.CreateObject(“ADODB.Recordset”)
objRecordSet.CursorTye = adOpenStatic
objRecordSet.LockType = adLockReadOnly
objRecordSet.Open varSQL, objConn
%>
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-50
Fields Collection:
ASP Code
<%
Dim varI, varNumCols, objField
varNumCols = objRecordSet.Fields.Count
For varI = 0 to varNumCols - 1
Set objField = objRecordSet.Fields(varI)
‘ objField.Name now has the name of the field
‘ objField.Value now has the value of the field
‘ can do something with them here
Next
>%
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-51
Errors Collection:
ASP Code
<%
Dim varI, varErrorCount, objError
On Error Resume Next
varErrorCount = objConn.Errors.Count
If varErrorCount > 0 Then
For varI = 0 to varErrorCount - 1
Set objError = objConn.Errors(varI)
‘ objError.Description contains
‘ a description of the error
Next
End If
>%
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-52
Command Object:
ASP Code
<%
Dim
objCommand, objParam, objRs
‘Create the Command object, connect it to objConn and
set its format
Set objCommand = Server.CreateObject(“ADODB.command”)
Set objCommand.ActiveConnection = objConn
objCommand.CommandText=“{call FindArtist (?)}”
‘Set up the parameter with the necessary value
Set objParam = objCommand.CreateParameter (“Nationality”,
adChar, adParamInput, 25)
objCommand.Parameters.Append objParam
objParam.Value = “Spanish”
‘Fire the Stored Proc
Set objRs = objCommand.Execute
>%
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-53
ADO
Example:
Reading a
Table
Artist.asp
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-54
ADO Example: Reading a Table
The Artist.asp Results
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-55
David M. Kroenke’s
Database Processing
Fundamentals, Design, and Implementation
(10th Edition)
End of Presentation:
Chapter Twelve Part Two
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
12-56