Transcript Slide Template

CPE 332 Database Processing
Chapter 12 ODBC, OLE DB, ADO, and ASP
13 XML and ADO.NET
Instructor:
Suthep Madarasmi, Ph.D.
ดร. สุเทพ มาดารัศมี
Email:
[email protected]
Telephone:
02-470-9080
Messages:
02-470-9085
Fax:
02-872-5050
Fundamentals, Design,
and Implementation, 9/e
David M. Kroenke
Introduction
 Because database applications today reside in a
complicated environment, various standards have
been developed for accessing database servers
 Some of the traditional standards are
– OBDC (Open Database Connectivity) is the early
standard for relational databases
– OLE DB is the Microsoft’s object-oriented interface for
relational and other databases
– ADO (Active Data Objects) is the Microsoft’s standard
providing easier access to OLE DB data for the nonobject-oriented programmer
Database Processing by Suthep Madarasmi
Lecture 3/2
Web Server Data Environment
 Web server
needs to
publish
applications
that involve
different
data types
Database Processing by Suthep Madarasmi
Lecture 3/3
Role of ODBC
Database Processing by Suthep Madarasmi
Lecture 3/4
Role of OLE DB
Database Processing by Suthep Madarasmi
Lecture 3/5
Role of ADO
Database Processing by Suthep Madarasmi
Lecture 3/6
OBDC
 ODBC (Open Database Connectivity) 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
Database Processing by Suthep Madarasmi
Lecture 3/7
ODBC Architecture
Database Processing by Suthep Madarasmi
Lecture 3/8
OBDC Components
 OBDC consists of data source, application
program, drivermanager, and DBMS driver
 Data source is the database, its associated
DBMS, operating system, and network
platform
– An ODBC data source can be a relational
database, a file server, or a spreadsheet
 Applications program issues requests to
create a connection with a data source
Database Processing by Suthep Madarasmi
Lecture 3/9
OBDC Components (cont.)
 Driver manager determines the type of
DBMS for a given ODBC data source and
loads that driver in memory
 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
Database Processing by Suthep Madarasmi
Lecture 3/10
OBDC Driver Types
Database Processing by Suthep Madarasmi
Lecture 3/11
Conformance Levels
 Levels of conformance balance the scope
of OBDC standard
 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
Database Processing by Suthep Madarasmi
Lecture 3/12
ODBC Conformance Levels


Core API
– Connect to data sources
– Prepare and execute SQL statements
– Retrieve data from a result set
– Commit or rollback transactions
– Retrieve error information
Level 1 API
– Core API
– Connect to data sources with driver-specific information
– Send and receive partial results
– Retrieve catalog information
– Retrieve information about driver options, capabilities, and
functions
Database Processing by Suthep Madarasmi
Lecture 3/13
ODBC Conformance Levels


Level 2 API
– Core and Level 1 API
– Browse possible connections and data sources
– Retrieve native form of SQL
– Call a translation library
– Process a scrollable cursor
Minimum SQL Grammar
– CREATE TABLE, DROP TABLE
– simple SELECT
(does not include subqueries)
– INSERT, UPDATE, DELETE
– Simple expressions
(A > B + C)
– CHAR, VARCHAR, LONGVARCHAR data types
Database Processing by Suthep Madarasmi
Lecture 3/14
SQL Conformance Levels

Core SQL Grammar
– Minimum SQL Grammar
– ALTER TABLE, CREATE INDEX, DROP INDEX
– CREATE VIEW, DROP VIEW
– GRANT, REVOKE
– Full SELECT (includes subqueries)
– Aggregate functions such as SUM, COUNT, MAX, MIN,
AVG
– DECIMAL, NUMERIC, SMALLINT, INTEGER, REAL,
FLOAT, DOUBLE PRECISION data types
Database Processing by Suthep Madarasmi
Lecture 3/15
SQL Conformance Levels
(cont.)
 Extended SQL Grammar
–
–
–
–
–
–
–
Core SQL Grammar
Outer joins
UPDATE and DELETE using cursor positions
Scalar functions such as SUBSTRING, ABS
Literals for date, time, and timestamp
Batch SQL statements
Stored Procedures
 The capability of the minimum SQL grammar is
very limited
 Most drivers support at least the core SQL
grammar
Database Processing by Suthep Madarasmi
Lecture 3/16
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
Database Processing by Suthep Madarasmi
Lecture 3/17
Key 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
Database Processing by Suthep Madarasmi
Lecture 3/18
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
 Object interface 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
Database Processing by Suthep Madarasmi
Lecture 3/19
OLE DB Basic Constructs
 Data providers
 Rowset interfaces
 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
Database Processing by Suthep Madarasmi
Lecture 3/20
OLE DB Data Providers
 OLE DB has two types of data providers
– Tabular data provider exposes data via
rowsets
• Examples: DBMS, spreadsheets, ISAMs
– Service provider is 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
Database Processing by Suthep Madarasmi
Lecture 3/21
ADO
 ADO (Active Data Objects) characteristics
– Simple object model for OLE DB data
consumers
– Can be used from VBScript, JScript, Visual
Basic, Java, C#, C++
– Single Microsoft data access standard
– Data access objects are the same for all types
of OLE DB data
Database Processing by Suthep Madarasmi
Lecture 3/22
ADO Object Model
Database Processing by Suthep Madarasmi
Lecture 3/23
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
Database Processing by Suthep Madarasmi
Lecture 3/24
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.
Database Processing by Suthep Madarasmi
Lecture 3/25
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
Database Processing by Suthep Madarasmi
Lecture 3/26
Chapter 13
XML and ADO.NET
Fundamentals, Design,
and Implementation, 9/e
Introduction
 Database processing and document
processing need each other
– Database processing needs document
processing for expressing database views
– Document processing needs database
processing for storing and manipulating data
 As Internet usage increased, organizations
wanted to make their Web pages more
functional by displaying and updating data
from organizational databases
Database Processing by Suthep Madarasmi
Lecture 3/28
XML
 XML, or Extensible Markup Language, developed
in early 1990s
– XML is a subset of SGML, or Standard Generalized
Markup Language and so is HTML.
 Today XML is a hybrid of document processing
and database processing
– It provides a standardized yet customizable way to
describe the content of documents
– XML documents can automatically be generated from
database data, and vice versa
 SOAP Simple Object Access Protocol is an XMLbased standard protocol for sending messages of
any type (remote procedure call), using any
protocol over the Internet
Database Processing by Suthep Madarasmi
Lecture 3/29
XML (cont.)
 XML is used for describing, representing, and
materializing database views
 XML is better than HTML because
– It provides a clear separation between 1)document
structure, 2) content, and 3) materialization
– It is standardized but allows for extension by developers:
can create your own new tags.
– XML tags accurately represent the semantics of their
data
 Two Sections of Documents: 1) DTD’s and 2)
Document Data
 Document Type Declarations (DTDs) and
XML Schemas can be used to describe the content of XML
documents
Database Processing by Suthep Madarasmi
Lecture 3/30
Example: XML Document
Database Processing by Suthep Madarasmi
Lecture 3/31
XML DTD
 XML document consists of two sections:
– Document Type Declaration (DTD)
• The DTD begins with DOCTYPE <document_type_name>
– Document data
 XML documents could be
– Type-valid if the document conforms to its DTD
– Well-formed and not be type-valid, because
• It violates the structure of its DTD
• It has no DTD
 DTD may be stored externally so many documents
can be validated against the same DTD
Database Processing by Suthep Madarasmi
Lecture 3/32
XSLT
 XSLT, or the Extensible Style Language may be
used to materialize (transform) XML documents
using XSL document
– From XML documents into HTML or into XML in another
format
 XSLT is a declarative transformation language
– Declarative: create rules, not procedure, to materialize
the document
– Transformational: transforms the input document into
another document
 XSLT uses stylesheets to indicate how to
transform the elements of the XML document into
another format
Database Processing by Suthep Madarasmi
Lecture 3/33
Example: External DTD
Database Processing by Suthep Madarasmi
Lecture 3/34
Example: XML Document
Database Processing by Suthep Madarasmi
Lecture 3/35
Example: XML  HTML
Database Processing by Suthep Madarasmi
Lecture 3/36
Example: XML  Browser
Database Processing by Suthep Madarasmi
Lecture 3/37
XML Schema
 XML Schema is a standard for describing the
content of an XML document, i.e., defining custom
vocabularies
– Documents that conform to an XML Schema are called
schema-valid
– An XML document can be well-formed and be neither
type-valid nor schema-valid
 Unlike DTDs, XML Schema documents are
themselves XML documents that can be validated
against their schema maintained by W3C
Database Processing by Suthep Madarasmi
Lecture 3/38
Example: XML Schema
Database Processing by Suthep Madarasmi
Lecture 3/39
Example: XML Schema
Database Processing by Suthep Madarasmi
Lecture 3/40
Elements and Attributes
 Schemas consist of elements and
attributes
– Elements are used to carry data and attributes
are used to carry metadata
 Two types of elements:
– Simple elements have a single data value
– ComplexType elements can have one or more
simple or complexType elements
• ComplexType elements can have attributes
Database Processing by Suthep Madarasmi
Lecture 3/41
Flat Schemas
 Flat schemas have all elements at the
same level
Database Processing by Suthep Madarasmi
Lecture 3/42
Structured Schemas
 Structured
schemas
have
defined
subgroups
Database Processing by Suthep Madarasmi
Lecture 3/43
Global Elements
 To eliminate the definition duplication,
elements can be declared globally,
i.e., reside at the top level of the
schema, and then reused
Database Processing by Suthep Madarasmi
Lecture 3/44
Global Elements
Database Processing by Suthep Madarasmi
Lecture 3/45
Example XML Industry Standards
 Accounting
– Extensible Financial Reporting Markup Language (XFRML)
 Architecture and Construction
– Architecture, Engineering, and Construction XML (aecXML)
 Automotive
– Automotive Industry Action Group (AIAG)
– XML for the Automotive Industry (SAE J2008)
 Banking
– Banking Industry Technology Secretariat (BITS)
– Bank Internet Payment System (BIPS)
 Electronic Data Interchange
– Data Interchange Standards Association (DISA)
– XML/EDI Group
Database Processing by Suthep Madarasmi
Lecture 3/46
Example XML Industry Standards
(cont.)
 Human Resources
– Human Resources Markup Language (hrml)
 Insurance
– ACORD: Property and Casualty
 Real Estate
– Real Estate Listing Management System (OpenMLS)
– Real Estate Transaction Standard (RETS)
 Software
– IBM
– INRIA: Koala Bean Markup Language (KBML)
– Open Software Description Format (OSD)
 Workflow
– Simple Workflow Access Protocol (SWAP)
– Workflow Management Coalition (MfMC): Wf-XML
Database Processing by Suthep Madarasmi
Lecture 3/47
ADO.NET
 ADO.NET is a new, improved, and greatly
expanded version of ADO that was
developed for the Microsoft .NET initiative
 It incorporates all of the functionality of
ADO and facilitates the transformation of
XML documents to and from database data
 It uses datasets, which is an in-memory,
fully-functioned, independent databases
Database Processing by Suthep Madarasmi
Lecture 3/48
Role of ADO.NET
 ADO.NET serves as an intermediary
between all types of .NET
applications and the DBMS and
database
Database Processing by Suthep Madarasmi
Lecture 3/49
Data Provider
 A .NET data provider is a library of classes
that provides ADO.NET services
 Microsoft’s provides three data providers
– OLE DB data provider can be used to process
any OLE DB-compliant data source
– SQLClient data provider is purpose-built for use
with SQL Server
– OracleClient data provider is purpose-built for
use with Oracle
Database Processing by Suthep Madarasmi
Lecture 3/50
Data Provider Components
Database Processing by Suthep Madarasmi
Lecture 3/51
Data Provider Components
 A connection object is similar to the OBDC’s
connection object
 A command object is created on an established
connection
 A data reader provides read-only, forward-only,
fast access to database data
 An application can get and put data to and from
the database using the command object
 A dataset is an in-memory database that is
disconnected from any regular database
– It distinguishes ADO.NET from the previous data access
technology
Database Processing by Suthep Madarasmi
Lecture 3/52
The ADO.NET Dataset
 A dataset is an in-memory database that is
disconnected from any regular database
 Datasets (reg databases) can have
– Multiple tables, views, and relationships
• Tables may have surrogate key (auto increment
columns), primary keys, and be declared as unique
– Referential integrity rules and actions
– The equivalent of triggers
 Datasets may be constructed from several
different databases and managed by
different DBMS
Database Processing by Suthep Madarasmi
Lecture 3/53
Dataset Advantages
 Dataset contents and its XML schema can be
easily formatted as an XML document
 Also, XML schema documents can be read to
create the structure of the dataset, and XML
documents can be read to fill the dataset
 Datasets are needed to provide a standardized,
non-proprietary means to process database views
– This is important for the processing of views with multiple
multi-value paths
Database Processing by Suthep Madarasmi
Lecture 3/54
Dataset Disadvantages
 Because dataset data are disconnected from
regular database, only optimistic locking can be
used when updating the regular database with the
dataset
 In the case of conflict, either the dataset must be
reprocessed or the data change must be forced
onto the database, causing the lost update
problem
 Thus, datasets cannot be used for applications in
which optimistic locking is problematical
– Instead, the ADO.NET command object should be used
Database Processing by Suthep Madarasmi
Lecture 3/55
XML Standards
 XML: Extensible Markup Language
 XSL: XSLT Stylesheet. The document that
provides the {match, action} pairs and other data
for XSLT to use when transforming an XML
document
 XSLT: A program that applies XSLT Stylesheets to
an XML document to produce a transformed XML
document
 XML schema: An XML-compliant language for
constraining the structure of an XML document
Database Processing by Suthep Madarasmi
Lecture 3/56
Additional XML Standards
 Xpath
– A sublanguage within XSLT used to identify parts of an
XML document to be transformed
– Can also be used for calculations and string manipulation
 Xpointer
– A standard for linking one document to another
 SAX: Simple API (application program interface)
for XML
– An event-based parser that notifies a program when the
elements of an XML document have been encountered
during document parsing
Database Processing by Suthep Madarasmi
Lecture 3/57
Additional XML Standards
(cont.)
 DOM: Document Object Model
– An API that represents an XML document as a tree
– Each node of the tree represents a piece of the XML document
– A program can directly access and manipulate a node of the DOM
representation
 Xquery
– A standard for expressing database queries as XML documents
– The structure of the query uses XPath facilities, and the result of
the query is represented in an XML format
 XML Namespaces: A standard for allocating terminology to
defined collections
– X:Name is interpreted as the element Name as defined in
namespace X
– Useful for disambiguating terms
Database Processing by Suthep Madarasmi
Lecture 3/58