Transcript Chapter 11 – JavaScript/Jscript: Functions

Chapter 25 – Database: SQL, ADO and RDS
Outline
25.1
Introduction
25.2
Relational Database Model
25.3
Relational Database Overview: Books.mdb
25.4
Structured Query Language
25.4.1
Basic SELECT Query
25.4.2
WHERE Clause
25.4.3
ORDER BY Clause
25.4.4
Using INNER JOIN to Merge Data from Multiple Tables
25.4.5
TitleAuthor Query from Books.mdb
25.4.6
Inserting a Record
25.4.7
Updating a Record
25.5
Registering Books.mdb as an ODBC Source
25.6
ActiveX Data Objects (ADO)
25.7
Remote Data Services (RDS
 2000 Deitel & Associates, Inc. All rights reserved.
25.1 Introduction
• Database
– Integrated collection of data
• Database Management System (DBMS)
– Has mechanisms for storing and organizing data
– Sophisticated queries and manipulations of data
• Relational Databases
– Most popular database system in use today
• Structured Query Language (SQL – pronounced “sequel”)
– Almost universally used with relational databases
– Makes queries (request information) and manipulate data
 2000 Deitel & Associates, Inc. All rights reserved.
25.1 Introduction (II)
• In this chapter
– Presentation of basic SQL queries using a database
– Introduction of two Microsoft technologies
• ActiveX Data Objects (ADO)
• Remote Data Services (RDS)
• These two technologies
– Enable client browser to
• Retrieve information from a database on a Web server
• Process that information on the client computer
• Return modifications of the data to the Web server so that the data can
be updated in the database
• Manipulation of data in the client
– Increases performance of Web-based database applications
– Reduces overall load on the server computer
 2000 Deitel & Associates, Inc. All rights reserved.
25.2 Relational Database Model
• Relational Database Model
– Logical representation of the data
– Consider relationships between data without worrying about
physical implementation
• Relational Database
– Composed of tables
– Any row of the table is called a record
– The first field is used as the primary key for referencing
• Records are normally unique (by primary key)
• Primary key can be composed of more than one field or column
– Each column represents a different field (or attribute)
 2000 Deitel & Associates, Inc. All rights reserved.
25.2 Relational Database Model (II)
Relational Database Structure
Table: Employee
A record
Number
Name
Department Salary
Location
23603
JONES, A.
413
1100
NEW JERSEY
24568
KERWIN, R.
413
2000
NEW JERSEY
34589
LARSON, P.
642
1800
LOS ANGELES
35761
MYERS, B.
611
1400
ORLANDO
47132
NEUMANN, C.
413
9000
NEW JERSEY
78321
STEPHENS, T.
611
8000
ORLANDO
Primary Key
 2000 Deitel & Associates, Inc. All rights reserved.
A column
25.2 Relational Database Model (III)
• Different users interested in different parts of the table
– SQL statements specify the data to select from the table
• SQL
– Provides complete set of keywords
– Smaller databases can also be combined to form larger ones
– Results of a query called result sets (or record sets)
Example: Result set to show where departments are located
Department
Location
413
NEW JERSEY
611
ORLANDO
642
LOS ANGELES
 2000 Deitel & Associates, Inc. All rights reserved.
25.3 Relational Database Overview: Books.mdb
• Overview of Database Books.mdb
– This database will be used throughout the chapter to introduce
• Various database concepts
• Use of SQL to obtain useful information from the database and to
manipulate the database
– Books.mdb consists of four tables:
• Authors, Publishers, AuthorISBN and Titles
• Primary key for each table is shown in italics
 2000 Deitel & Associates, Inc. All rights reserved.
25.3 Relational Database Overview: Books.mdb
(II)
•
Authors table description
–
AuthorID
•
–
FirstName
•
–
String representing author’s last name
YearBorn
•
•
String representing author’s first name
LastName
•
–
Integer representing author’s ID number in database (primary key)
String representing author’s year of birth
Authors table data
AuthorID FirstNa me La stNa me Yea rBorn
1
Harvey
Deitel
1946
2
Paul
Deitel
1968
3
Tem
Nieto
1969
 2000 Deitel & Associates, Inc. All rights reserved.
25.3 Relational Database Overview: Books.mdb
(III)
• Publishers table description
–
PublisherID
•
–
Integer representing publisher’s ID number in database (primary key)
PublisherName
•
String representing abbreviated name for publisher
• Publishers table data
Pub lisherID
PublisherName
1
2
Prentice Hall
Prentice Hall PTR
 2000 Deitel & Associates, Inc. All rights reserved.
25.3 Relational Database Overview: Books.mdb
(IV)
•
AuthorISBN table description
–
ISBN
•
–
String representing ISBN number for a book (linking table - no primary key)
AuthorID
•
Integer representing author’s ID number
 2000 Deitel & Associates, Inc. All rights reserved.
25.3 Relational Database Overview: Books.mdb
(V)
AuthorISBN table data
ISBN
AuthorID
0-13-010671-2
1
0-13-010671-2
2
0-13-020522-2
1
0-13-020522-2
2
0-13-082925-0
2
0-13-082927-7
1
0-13-082927-7
2
0-13-082928-5
1
0-13-082928-5
2
0-13-082928-5
3
0-13-083054-2
1
0-13-083054-2
2
0-13-083055-0
1
0-13-083055-0
2
0-13-118043-6
1
0-13-118043-6
2
0-13-226119-7
1
0-13-226119-7
2
(continued on top of next row)
ISBN
AuthorID
(continued from bottom of previous row)
0-13-271974-6
1
0-13-271974-6
2
0-13-456955-5
1
0-13-456955-5
2
0-13-456955-5
3
0-13-528910-6
1
0-13-528910-6
2
0-13-565912-4
1
0-13-226119-7
2
0-13-020522-2
3
0-13-082714-2
1
0-13-082714-2
2
0-13-082925-0
1
0-13-565912-4
2
0-13-565912-4
3
0-13-899394-7
1
0-13-899394-7
2
(continued on top of next row)
 2000 Deitel & Associates, Inc. All rights reserved.
ISBN
AuthorID
(continued from bottom of previous row)
0-13-904947-9
1
0-13-904947-9
2
0-13-904947-9
3
0-13-013249-7
1
0-13-013249-7
2
0-13-085609-6
1
0-13-085609-6
2
0-13-085609-6
3
0-13-016143-8
1
0-13-016143-8
2
0-13-016143-8
3
0-13-015870-4
1
0-13-015870-4
2
0-13-015870-4
3
0-13-012507-5
1
0-13-012507-5
2
0-13-085248-1
1
0-13-085248-1
2
25.3 Relational Database Overview: Books.mdb
(VI)
• Titles table description
–
ISBN
•
–
Title
•
–
String representing year in which book was published
Description
•
–
String representing edition of the book
YearPublished
•
–
String representing title of the book
EditionNumber
•
–
String representing ISBN number of the book (primary key)
String representing description of the book
PublisherID
•
Integer representing publisher’s ID number (corresponds to ID
number in Publishers table)
 2000 Deitel & Associates, Inc. All rights reserved.
25.3 Relational Database Overview: Books.mdb
(VII)
• Titles table data
ISBN
Title
0-13-226119-7
0-13-528910-6
0-13-899394-7
0-13-012507-5
0-13-456955-5
0-13-016143-8
0-13-013249-7
0-13-565912-4
0-13-904947-9
0-13-020522-2
0-13-015870-4
0-13-082925-0
0-13-082927-7
0-13-082928-5
0-13-085248-1
0-13-085609-6
0-13-082714-2
0-13-010671-2
0-13-083054-2
0-13-083055-0
0-13-118043-6
0-13-271974-6
C How to Program
C++ How to Program
Java How to Program
Java How to Program
Visual Basic 6 How to Program
Internet and World Wide Web How to Program
Getting Started with Visual C++ 6 with an Introduction to MFC
C++ How to Program Instructor's Manual with Solutions Disk
Java How to Program Instructor's Manual with Solution Disk
Visual Basic 6 How to Program Instructor's Manual with Solution Disk
Internet and World Wide Web How to Program Instructor's Manual with Solutions Disk
The Complete C++ Training Course
The Complete Java Training Course
The Complete Visual Basic 6 Training Course
The Complete Java Training Course
The Internet and World Wide Web How to Program Complete Training Course
C++ How to Program 2/e and Getting Started with Visual C++ 5.0 Tutorial
Java How to Program 2/e and Getting Started with Visual J++ 1.1 Tutorial
The Complete C++ Training Course 2/e and Getting Started with Visual C++ 5.0 Tutorial
The Complete Java Training Course 2/e and Getting Started with Visual J++ 1.1 Tutorial
C How to Program
Java Multimedia Cyber Classroom
 2000 Deitel & Associates, Inc. All rights reserved.
Edition
Yea r- PublisherID
Number Published
2
2
2
3
1
1
1
2
2
1
1
2
2
1
3
1
2
2
2
2
1
1
1994
1997
1997
1999
1998
1999
1999
1998
1997
1999
1999
1998
1997
1999
1999
1999
1998
1998
1998
1998
1992
1996
1
1
1
1
1
1
1
1
1
1
1
2
2
2
2
2
1
1
1
1
1
2
Table relationships in books.mdb
•
Lines between tables represent relationships
–
Example: Line between Publishers and Titles
•
•
–
•
One-to-many relationship
Every publisher can have an infinite number of books
PublisherID is the foreign key in the Titles table
•
Must correspond to primary key in Publishers table
•
Maintains Rule of Referential Integrity
AuthorISBN table–
–
No primary key
Linking table between Titles and Authors
 2000 Deitel & Associates, Inc. All rights reserved.
25.4 Structured Query Language
• Structured Query Language (SQL) keywords used to
– Query a database
– Insert records into a database
– Update existing records in a database
SQL keyword
SELECT
FROM
WHERE
ORDER BY
INSERT INTO
UPDATE
Description
Select (retrieve) fields from one or more tables.
Tables from which to get fields. Required in every SELECT.
Criteria for selection that determine the rows to be retrieved.
Criteria for ordering (sorting) of records.
Insert values into one or more tables. [Note: Some databases do
not require the SQL keyword INTO.]
Update existing data in one or more tables.
(Note: there are other keywords not included in this table)
 2000 Deitel & Associates, Inc. All rights reserved.
25.4.1 Basic SELECT Query
• SELECT * FROM TableName
– TableName specifies table in database where data is located
– * selects all rows and fields from TableName
• SELECT * FROM Authors
– Selects the entire contents of the Authors table
• SELECT AuthorID, LastName FROM Authors
– To select fields from table, replace * with comma-separated list of field
names to select
– Returns the following:
 2000 Deitel & Associates, Inc. All rights reserved.
AuthorID
LastName
1
2
3
Deitel
Deitel
Nieto
25.4.1 Basic SELECT Query (II)
• Order of fields
– Specifying field names ensures that the fields are returned in
the same order, even if in different order in the table
• Allows result set to be processed more efficiently by the application
– SQL statement using * does not ensure that fields will be
returned in the expected order
• Do not use to specify field names to select from one or several table
• Field name with spaces
– If contains spaces, must be enclosed in square brackets ([])
• Error if you forget to enclose in brackets
• Therefore, try to avoid field names with spaces
 2000 Deitel & Associates, Inc. All rights reserved.
25.4.2 WHERE Clause
• WHERE clause
– Most cases, only necessary to locate records that satisfy certain selection
criteria
– SQL uses the WHERE clause to specify the selection criteria
– Can contain operators
• <, >, <=, >=, =, <> and LIKE
• Simplest form:
SELECT fieldName1, fieldName2, … FROM TableName WHERE Criteria
• SQL is case-sensitive on some systems
– Check your database system document to determine the syntax to be used
for keywords
• All uppercase, all lowercase, combination?
 2000 Deitel & Associates, Inc. All rights reserved.
25.4.2 WHERE Clause (II)
• Example
SELECT AuthorID, FirstName, LastName, YearBorn
FROM Authors
WHERE YearBorn > 1960
• Returns
AuthorID FirstNa me La stNa me Yea rBorn
2
3
Paul
Tem
 2000 Deitel & Associates, Inc. All rights reserved.
Deitel
Nieto
1968
1969
25.4.2 WHERE Clause (III)
• LIKE Operator
– Used for pattern matching with wildcard characters
• asterisk (*)
• question mark (?)
– Allows SQL to search for similar strings that “match a
pattern”
– Asterisk
• Indicates any number of characters in a row at location in pattern
• Surrounded by single-quote characters
– Question Mark
• Indicates a single character at that position in the pattern
• Surrounded by single-quote characters
– Not supported by all database systems
 2000 Deitel & Associates, Inc. All rights reserved.
25.4.2 WHERE Clause (IV)
• LIKE Operator Example
SELECT AuthorID, FirstName, LastName, YearBorn
FROM Authors
WHERE Lastname LIKE ‘d*’
– Returns records of all authors whose last name’s begin with the letter d
AuthorID FirstNa me La stNa me Yea rBorn
1
2
Harvey
Paul
 2000 Deitel & Associates, Inc. All rights reserved.
Deitel
Deitel
1946
1968
25.4.2 WHERE Clause (V)
• LIKE Operator Example II
SELECT AuthorID, FirstName, LastName, YearBorn
FROM Authors
WHERE Lastname LIKE ‘?i*’
– Returns records of all authors whose last name second letter is the
letter i
AuthorID FirstNa me La stNa me Yea rBorn
3
Tem
 2000 Deitel & Associates, Inc. All rights reserved.
Nieto
1969
25.4.2 WHERE Clause (VI)
• LIKE Operator Example III
– Query can be specialized to allow any character in a range of
characters in one position in the string
• [startValue-endValue]
SELECT AuthorID, FirstName, LastName, YearBorn
FROM Authors
WHERE Lastname LIKE ‘?[a-i]*’
– Returns records of all authors in table because all fit range
AuthorID FirstNa me La stNa me Yea rBorn
1
2
3
Harvey
Paul
Tem
 2000 Deitel & Associates, Inc. All rights reserved.
Deitel
Deitel
Nieto
1946
1968
1969
25.4.3 ORDER BY Clause
• ORDER BY Clause
– Sorts results of query into ascending or descending order
SELECT fieldName1, fieldName2, … FROM TableName ORDER BY fieldName ASC
SELECT fieldName1, fieldName2, … FROM TableName ORDER BY fieldName DESC
• Example
SELECT AuthorID, FirstName, LastName, YearBorn
FROM Authors
ORDER BY LastName ASC
– Returns authors sorted by last name in ascending order
AuthorID FirstNa me La stNa me Yea rBorn
2
1
3
Paul
Harvey
Tem
 2000 Deitel & Associates, Inc. All rights reserved.
Deitel
Deitel
Nieto
1968
1946
1969
25.4.3 ORDER BY Clause (II)
• The WHERE and ORDER BY clauses can be combined
– Example:
SELECT ISBN, Title, EditionNumber, YearPublished,
PublisherID
FROM Titles
WHERE Title LIKE ‘*How to Program’
ORDER BY Title ASC
– Returns:
ISBN
Title
0-13-118043-6
0-13-226119-7
0-13-528910-6
0-13-016143-8
C How to Program
C How to Program
C++ How to Program
Internet and World Wide Web How
to Program
Java How to Program
Java How to Program
Visual Basic 6 How to Program
0-13-012507-5
0-13-899394-7
0-13-456955-5
 2000 Deitel & Associates, Inc. All rights reserved.
Edition Yea r
Publisher
Number Published ID
1
2
2
1
1992
1994
1997
1999
1
1
1
1
3
2
1
1999
1997
1998
1
1
1
25.4.3 ORDER BY Clause (III)
• ORDER BY can be applied to multiple fields
ORDER BY fieldName1 SortingOrder, fieldName2 SortingOrder, …
• When we construct a query
– Create one long string containing query
– Multiple lines and indentation only used for readability when
query statement is displayed in text
 2000 Deitel & Associates, Inc. All rights reserved.
25.4.4 Using INNER JOIN to Merge Data from
Multiple Tables
• INNER JOIN Clause
– Merges data from multiple tables into single view
– Referred to as joining the tables
– Placed in the FROM clause of an SQL query
• Simplest form
SELECT fieldName1, fieldName2, …
FROM Table1 INNER JOIN Table2 ON Table1.field = Table2.field
 2000 Deitel & Associates, Inc. All rights reserved.
25.4.4 Using INNER JOIN to Merge Data from
Multiple Tables (II)
• ON part of INNER JOIN
– Specifies fields from each table to be compared to determine
records to be selected
– “TableName.” syntax required if fields have same name in
both tables
– Example:
SELECT FirstName, LastName, ISBN
FROM Authors INNER JOIN AuthorISBN
ON Authors.AuthorID = AuthorISBN.AuthorID
ORDER BY LastName, FirstName
 2000 Deitel & Associates, Inc. All rights reserved.
25.4.4 Using INNER JOIN to Merge Data from
Multiple Tables (III)
First Last ISBN
Name Name
First Last ISBN
Name Name
Harvey
Deitel
0-13-013249-7
(Continued from bottom of previous column) (Continued from bottom of previous column)
Harvey
Deitel
0-13-271974-6
Harvey
Deitel
0-13-020522-2
Paul
Deitel
0-13-085609-6
Harvey
Deitel
0-13-528910-6
Harvey
Deitel
0-13-020522-2
Paul
Deitel
0-13-013249-7
Harvey
Deitel
0-13-083055-0
Harvey
Deitel
0-13-010671-2
Paul
Deitel
0-13-226119-7
Harvey
Deitel
0-13-565912-4
Harvey
Deitel
0-13-118043-6
Paul
Deitel
0-13-899394-7
Harvey
Deitel
0-13-083054-2
Paul
Deitel
0-13-010671-2
Paul
Deitel
0-13-565912-4
Harvey
Deitel
0-13-899394-7
Paul
Deitel
0-13-083055-0
Paul
Deitel
0-13-528910-6
Harvey
Deitel
0-13-904947-9
Paul
Deitel
0-13-082927-7
Paul
Deitel
0-13-085248-1
Harvey
Deitel
0-13-226119-7
Paul
Deitel
0-13-083054-2
Paul
Deitel
0-13-456955-5
Harvey
Deitel
0-13-082928-5
Paul
Deitel
0-13-082714-2
Paul
Deitel
0-13-271974-6
Harvey
Deitel
0-13-456955-5
Paul
Deitel
0-13-118043-6
Paul
Deitel
0-13-013249-7
Harvey
Deitel
0-13-015870-4
Paul
Deitel
0-13-118043-6
Tem
Nieto
0-13-082928-5
Harvey
Deitel
0-13-085609-6
Paul
Deitel
0-13-082928-5
Tem
Nieto
0-13-565912-4
Harvey
Deitel
0-13-085248-1
Paul
Deitel
0-13-082925-0
Tem
Nieto
0-13-456955-5
Harvey
Deitel
0-13-082925-0
Paul
Deitel
0-13-020522-2
Tem
Nieto
0-13-085609-6
Harvey
Deitel
0-13-016143-8
Paul
Deitel
0-13-904947-9
Tem
Nieto
0-13-016143-8
Harvey
Deitel
0-13-082714-2
Paul
Deitel
0-13-012507-5
Tem
Nieto
0-13-020522-2
Harvey
Deitel
0-13-082927-7
Paul
Deitel
0-13-015870-4
Tem
Nieto
0-13-015870-4
Harvey
Deitel
0-13-012507-5
Paul
Deitel
0-13-016143-8
Tem
Nieto
0-13-904947-9
Tem
Nieto
0-13-904947-9
(Continued at top of next column)
 2000 Deitel & Associates, Inc. All rights reserved.
(Continued at top of next column)
First Last ISBN
Name Name
24.4.5 TitleAuthor Query from Books.mdb
• Predefined query: TitleAuthor
– Table containing book title, ISBN number, author’s first and last names,
year published, and publisher’s name
– Note the nested INNER JOIN structures
– Note: spacing and indenting only for readability
SELECT Titles.Title, Titles.ISBN, Authors.FirstName,
Authors.Lastname, Titles.YearPublished,
Publishers.PublisherName
FROM
( Publishers INNER JOIN Titles
ON Publishers.PublisherID = Titles.PublisherID )
INNER JOIN
( Authors INNER JOIN AuthorsISBN
ON Authors.AuthorID = AuthorISBN.AuthorID )
ON Titles.ISBN = AuthorsISBN.ISBN
ORDER BY Titles.Title
 2000 Deitel & Associates, Inc. All rights reserved.
24.4.5 TitleAuthor Query from Books.mdb (II)
Portions of TitleAuthor query output
Title
ISBN
First Last Year
Publisher
Name Name Published Name
C How to Program
C How to Program
C How to Program
C How to Program
C++ How to Program
C++ How to Program
0-13-226119-7
0-13-118043-6
0-13-118043-6
0-13-226119-7
0-13-528910-6
0-13-528910-6
Paul
Paul
Harvey
Harvey
Harvey
Paul
Deitel
Deitel
Deitel
Deitel
Deitel
Deitel
1994
1992
1992
1994
1997
1997
Prentice Hall
Prentice Hall
Prentice Hall
Prentice Hall
Prentice Hall
Prentice Hall
Internet and World Wide Web
How to Program
Internet and World Wide Web
How to Program
Internet and World Wide Web
How to Program
0-13-016143-8
Paul
Deitel
1999
Prentice Hall
0-13-016143-8
Harvey
Deitel
1999
Prentice Hall
0-13-016143-8
Tem
Nieto
1999
Prentice Hall
Java How to Program
Java How to Program
Java How to Program
Java How to Program
0-13-012507-5
0-13-899394-7
0-13-899394-7
0-13-012507-5
Harvey
Paul
Harvey
Paul
Deitel
Deitel
Deitel
Deitel
1999
1997
1997
1999
Prentice Hall
Prentice Hall
Prentice Hall
Prentice Hall
Visual Basic 6 How to Program 0-13-456955-5
Visual Basic 6 How to Program 0-13-456955-5
Visual Basic 6 How to Program 0-13-456955-5
Harvey
Paul
Tem
Deitel
Deitel
Nieto
1998
1998
1998
Prentice Hall
Prentice Hall
Prentice Hall
 2000 Deitel & Associates, Inc. All rights reserved.
25.4.6 Inserting a Record
• INSERT INTO operation
– Inserts data into the table (e.g; adds a record)
– Simplest form:
List of field names into
Table into
which record
will be inserted
which to insert values
(not required if inserting
complete record)
INSERT INTO TableName ( fieldName1, fieldName2, …, fieldNameN )
Values ( value1, value 2, …, valueN )
KEYWORDS
Values to be inserted into fields
– in order of fields listed before
– The single quote character should be used as a delimiter for
strings to be inserted into the database
 2000 Deitel & Associates, Inc. All rights reserved.
25.4.6 Inserting a Record
• Sample insert operation
– Note: AuthorID field not specified because it has been set up in Microsoft
Access, our database software, as an auto-numbered field
INSERT INTO Authors ( FirstName, LastName, YearBorn )
Values ( ‘Sue’, ‘Smith’, 1960 )
• Result
AuthorID
FirstName
LastName
YearBorn
1
Harvey
Deitel
1946
2
Paul
Deitel
1968
3
Tem
Nieto
1969
4
Sue
Smith
1960
 2000 Deitel & Associates, Inc. All rights reserved.
25.4.7 Updating a Record
• Modify a record with the UPDATE operation
UPDATE TableName
SET fieldName1 = value1, fieldName2 = value2, …,
fieldNameN = valueN
WHERE criteria
• Example:
UPDATE Authors
SET YearBorn = 1969
WHERE LastName = ‘Deitel’, FirstName = ‘Paul’
– Changes YearBorn value for Paul Deitel in Authors table to 1969
 2000 Deitel & Associates, Inc. All rights reserved.
25.5 Registering Books.mdb as an ODBC Data
Source
• Open DataBase Connectivity (ODBC) Application
Programming Interface (API)
– Developed by Microsoft to allow Windows applications to communicate in
a uniform manner with relational databases
• To execute an SQL query
– Program must be able to access the database
• Database must be given a System Data Source Name (DSN) on the
server
• Database must be registered as an ODBC source
• ODBC Driver written by vendors
– Uses ODBC API to provide uniform access to the database
• For specific instructions
– See section 25.5 in your textbook
 2000 Deitel & Associates, Inc. All rights reserved.
25.6 ActiveX Data Objects (ADO)
• Microsoft’s Universal Data Access (UDA)
– Architecture designed for high-performance data access to
• Relational data sources
• Non-relational data sources
• Mainframe/legacy data sources
UDA Architecture:
Application or Browser
ADO
OLE DB
ODB
C
Relational data sources
Mainframe/legacy data
Non-relational data sources
 2000 Deitel & Associates, Inc. All rights reserved.
25.6 ActiveX Data Objects (ADO)
• UDA consists of three primary components
– OLE DB
• Core of UDA architecture
• Provides low-level access to any data source
– ODBC
• C programming language library
• Uses SQL to access data
– ActiveX Data Controls
• Simple object model
• Provides uniform access to any data source by interacting with OLE
DB
 2000 Deitel & Associates, Inc. All rights reserved.
25.6 ActiveX Data Objects (ADO)
• ADO object model
– Provides objects and collections
Object/ Collection
Description
Connection object
Command object
The connection to the data source.
Contains the query that will interact with the database (the data
source) to manipulate data.
Parameter object
Contains information needed by a Command object to query
the data source.
Parameters collection
Error object
Errors collection
Contains one or more Parameter objects.
Recordset object
Contains zero or more records that match the database query.
Collectively this group of records is called a recordset.
Field object
Fields collection
Contains the value (and other attributes) of one data source field.
Created when an error occurs while accessing data.
Contains one or more Error objects.
Contains one or more Field objects.
 2000 Deitel & Associates, Inc. All rights reserved.
25.7 Remote Data Services (RDS)
• RDS
– Emerging Microsoft technology for client-side database
manipulation over the internet
– Recordsets retrieved on the server using ADO and sent to
client browser
• Called disconnected recordset
– Client can execute queries against the recordset
– Similar to Tabular Data Control
• RDS provides mechanism for sending updated records to Web server
• Tabular Data Control does not
• RDS is still developing
– Future releases will work with Personal Web Server (PWS)
– Server-side setup complex and developing rapidly
 2000 Deitel & Associates, Inc. All rights reserved.
25.7 Remote Data Services (RDS) (II)
RDS Architecture
Client
Browser
Server
PWS/IIS
RDS
ADO
OLE DB
ODB
C
Relational data sources
Mainframe/legacy data
Non-relational data sources
• RDS still developing
– Future releases will work with PWS
– Server-side setup complex and developing rapidly
 2000 Deitel & Associates, Inc. All rights reserved.
25.7 Remote Data Services (RDS) (III)
• RDS implemented as client-side ActiveX control
– Named RDS.Datacontrol
Properties Table
Property
Description
Connect
The data source name (DSN) of a Web server’s database.
Sql
The actual query to execute against the database.
Server
Indicates the Web server to which the RDS.Datacontrol connects.
Recordset
The actual recordset downloaded from the Web server.
Methods Table
Method
Description
Refresh
Executes a query against the database. Also forces any
records that have not been uploaded to the server to upload.
SubmitChanges
Sends updated records to the Web server.
CancelUpdate
Cancels all records that were updated (but not sent to the
server) and reverts them back to their original values.
 2000 Deitel & Associates, Inc. All rights reserved.
25.7 Remote Data Services (RDS) (IV)
• RDS.Datacontrol
– Transfers data asynchronously
• Transfer occurs in parallel with other executing code
• ReadyState property stores control’s current state
– Uses constants to declare states used in the program
Sta te
Description
adcReadyStateLoaded
The recordset does not contain any data because the
query has not completed executing.
adcReadyStateInteractive Some records in the recordset are available, but others
are still being downloaded (or uploaded).
adcReadyStateComplete
All recordset records are available. The download (or
upload) is complete.
– state can have one of three values
• 0 – no user activity
• 1 – download state
• 2 – upload state
 2000 Deitel & Associates, Inc. All rights reserved.
1<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
2<HTML>
3<!--Fig. 25.38: rds.html -->
4
5<!--Creates an instance of the RDS.Datacontrol-->
6<OBJECT CLASSID = "clsid:BD96C556-65A3-11D0-983A-00C04FC29E33"
7
ID = deitelDC WIDTH = 0 HEIGHT = 0>
8</OBJECT>
9
10<SCRIPT LANGUAGE = VBScript>
11<!-12Option Explicit
13
14<!-- Microsoft predefined constants -->
15Const adcReadyStateLoaded = 2
16Const adcReadyStateInteractive = 3
17Const adcReadyStateComplete = 4
18
19Dim state
20
21Public Sub window_OnLoad()
22
’ Set the state machine at the first state
23
StatusText.Value = "Click Find."
24
state = 0
25End Sub
26
27Public Sub deitelDC_OnReadyStateChange()
28
If state = 1 Then
29
Select Case deitelDC.ReadyState
30
’ Just started downloading, there is no
31
’ data to look at.
 2000 Deitel & Associates, Inc. All rights reserved.
Outline
1.1 Define OBJECT
2.1 Open and define
<SCRIPT> tag
2.2 Set predefined
ReadyState constants
2.3 Set Initial
StatusText.Value
3.1 Define
deitelDC_OnReadySt
ateChange() Sub
procedure
3.2 Open Select
structure inside if
structure for
downloading actions
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
Case adcReadyStateLoaded
StatusText.Value = "Downloading..."
Outline
’ Partially downloaded the data,
’ there is more to come.
Case adcReadyStateInteractive
StatusText.Value = "Still downloading..."
3.3 set
StatusText.value
downloading
’ Completely downloaded the data,
’ there’s no more coming.
Case adcReadyStateComplete
3.4 Fill in fields for
updating
’ Fill in the fields for updating
’ Get ISBN field to prove we got the right one
FoundISBN.Value = deitelDC.Recordset( "ISBN" )
’ The title we want to modify
FoundTitle.Value = deitelDC.Recordset( "Title" )
’ OK for updating. Everything worked.
StatusText.Value = "Finished downloading."
state = 2
End Select
ElseIf state = 2 Then
Select Case deitelDC.ReadyState
’ Started uploading, there is no data sent.
Case adcReadyStateLoaded
’ OK for updating. Everything worked.
StatusText.Value = "Uploading..."
 2000 Deitel & Associates, Inc. All rights reserved.
3.5 Set final
StatusText.Value
downloading value
4.1 Open select
structure inside
ElseIf structure for
uploading
4.2 Set first
StatusText.value
output for uploading
Case
63
64
’ Partially uploaded the data, there is more to send.
65
Case adcReadyStateInteractive
66
’ OK for updating. Everything worked.
67
StatusText.value = "Still Uploading..."
68
69
’ Completely downloaded the data,
70
’ there is no more coming.
71
’ Goto readystate = complete if it works
72
Case adcReadyStateComplete
73
’ OK for updating. Everything worked.
74
StatusText.value = "Finished updating."
75
state = 0
76
End Select
77
End If
78 End Sub
79
80 Public Sub Find_OnClick()
81
’ Validate the input values. Never assume valid
82
’ data is in the text boxes.
83
84
’ Server.Value will be used to designate the
85
’ server with the database
86
If Server.Value = "" Then
87
Call Msgbox( "Please specify a web server. " & _
88
"Suggest: http://xxx.xxx.xxx.xxx" )
89
90
’ ISBN.Value is the record we want to search for
91
ElseIf ISBN.value = "" Then
92
Call MsgBox( "Please specify an ISBN to examine. " & _
93
"Suggest: 0-13-226119-7" )
 2000 Deitel & Associates, Inc. All rights reserved.
Outline
4.3 Finish setting
StatusText.value’s
for uploading
4.4 Close all structures
5.1 Define OnClick()
Sub procedure
5.2 Validate input
values
94
95
’ All data is probably valid so begin the data download.
96
Else
97
’ Request the data from.
98
deitelDC.Server = Server.Value
99
100
’ Set the SQL query.
101
deitelDC.SQL = "SELECT ISBN, " & _
102
5.3 Begin data
download
5.4 Set values
"Title FROM Titles WHERE ISBN = ’" & ISBN.Value & "’"
103
104
’ Set the DSN to fetch the data.
105
deitelDC.Connect = "DSN=Books;"
106
107
’ Tell the server to begin sending the data to us.
108
Call deitelDC.Refresh()
109
state = 1
110
Outline
5.5 Tell server to begin
sending data - call
deitelDC.Refresh()
Sub procedure
5.6 Close structures
End If
111 End Sub
112
113 Public Sub Update_OnClick()
114
’ If everything worked above, we can change
115
’ the record in the database
116
If state = 2 Then
117
’ We are only updating this field in the database.
118
’ so we fetch the value from the text box and set it to go
119
’ back to the server for update
120
deitelDC.Recordset( "Title" ) = FoundTitle.Value
121
 2000 Deitel & Associates, Inc. All rights reserved.
6.1 Define
Update_OnClick() Sub
procedure
6.2 Fetch value from
text box, setup to go
back to server
122
’ Save these changes
123
Call deitelDC.SubmitChanges()
124
125
’ Refresh after submit operation
126
Call deitelDC.Refresh()
127
End If
128End Sub
129-->
130</SCRIPT>
131
132<HEAD>
133<META NAME = VI60_defaultClientScript CONTENT = VBScript>
134</HEAD>
135
136<BODY>
137RDS Example--Correct titles in a remote database<BR>
138Record to Find<BR>
139<TABLE BORDER = "0" CELLPADDING = "0">
140<TR>
141<TD>Server:</TD>
142<TD><INPUT ID= server NAME= server VALUE= "http://xxx.xxx.xxx.xxx"
143
TYPE = "text" SIZE = 60></TD>
144</TR>
145<TR>
146<TD>ISBN:</TD>
147<TD><INPUT ID = ISBN NAME = ISBN VALUE = "0-13-226119-7"
148
TYPE = "text"></TD>
149</TR>
150</TABLE>
151<INPUT TYPE = "button" ID = "Find" NAME = "Find" VALUE= "Find"><BR>
152<BR>
153Results<BR>
 2000 Deitel & Associates, Inc. All rights reserved.
Outline
6.3 Save changes and
refresh
6.4 Close all structures
and </SCRIPT> tag
7.1 Insert and define
META tag
7.2 Insert and define
searching INPUTs
7.3 Insert button
INPUT: “Find”
154<TABLE BORDER = "0" CELLPADDING = "0">
155<TR>
156<TD>Found ISBN( readonly ):</TD>
157<TD><INPUT ID = FoundISBN READONLY NAME = FoundISBN TYPE = "text">
158</TD>
159</TR>
160<TR>
161<TD>Found Title:</TD>
162<TD><INPUT ID = FoundTitle NAME = FoundTitle TYPE = "text"></TD>
163</TR>
164</TABLE>
165<INPUT TYPE = "button" ID = "Update" NAME = "Update"
166
VALUE = "Update Title"><BR>
167<BR>
168Status:<INPUT TYPE = "text" ID = "StatusText" NAME = "StatusText"
169
170</BODY>
171</HTML>
SIZE = 30 READONLY VALUE = "Click Find."><BR>
 2000 Deitel & Associates, Inc. All rights reserved.
Outline
7.4 Insert and define
Results INPUT
elements
7.5 Insert button
INPUT: “Update
Title”
7.6 Insert StatusText
INPUT textbox
25.7 Remote Data Services (RDS) (V)
Document Output 1
The
page is
loaded
 2000 Deitel & Associates, Inc. All rights reserved.
25.7 Remote Data Services (RDS) (VI)
Document Output 2
The use has clicked
the Find button
which results in the
Found ISBN and
Found Title
fields being
populated
 2000 Deitel & Associates, Inc. All rights reserved.
25.7 Remote Data Services (RDS) (VII)
Document Output 3
User has
edited the
Found
Title field
 2000 Deitel & Associates, Inc. All rights reserved.
25.7 Remote Data Services (RDS) (VIII)
Document Output 4
User has
clicked
Update
Title
 2000 Deitel & Associates, Inc. All rights reserved.
25.7 Remote Data Services (RDS) (IX)
Document Output 5
User has
clicked
Find
 2000 Deitel & Associates, Inc. All rights reserved.