Transcript Document

Chapter 22 - SQL, MySQL, DBI and
ADO
Outline
22.1
22.2
22.3
22.4
22.5
Introduction
Relational Database Model
Relational Database Overview
Structured Query Language
22.4.1 Basic SELECT Query
22.4.2 WHERE Clause
22.4.3 GROUP BY Clause
22.4.4 ORDER BY Clause
22.4.5 Merging Data from Multiple Tables
22.4.6 Inserting a Record
22.4.7 Updating a Record
22.4.8 DELETE FROM Statement
22.4.9 TitleAuthor Query from Books.mdb
MySQL
 2001 Prentice Hall, Inc. All rights reserved.
1
Chapter 22 - SQL, MySQL, DBI and
ADO
Outline
22.6
22.7
22.8
Introduction to DBI
22.6.1 Perl Database Interface
22.6.2 Python DB-API
22.6.3 PHP dbx module
ActiveX Data Objects (ADO)
Internet and World Wide Web Resources
 2001 Prentice Hall, Inc. All rights reserved.
2
3
22.1 Introduction
• Database
– Integrated collection of data
• Database management system (DBMS)
– Provides mechanisms for storing and organizing data
– Allows users to access and store data without addressing
internal representation of databases
• Relational databases
–
–
–
–
Consist of data corresponding to one another
Most popular database systems in use
Uses Structured Query Language (SQL) to create queries
Examples: Oracle, MS SQL Server, MySQL, Informix
 2001 Prentice Hall, Inc. All rights reserved.
4
22.2 Relational Database Model
• Relational database model
– Logical representation of data allowing users to consider
relationships between data
– Consists of tables
• Following figure illustrates Employee table
–
–
–
–
Might exist in personnel system
Table row called record
Table column called field
Number field is primary key
• Contains unique data that cannot be duplicated
• Identifies the record
• Examples: social security number, employee ID number, etc.
 2001 Prentice Hall, Inc. All rights reserved.
5
22.2 Relational Database Model
Row /Rec o rd
Number Name
Department Salary
Location
23603
Jones
413
1100
New Jerse y
24568
Kerw in
413
2000
New Jerse y
34589
Larson
642
1800
Los Ange les
35761
My ers
611
1400
Orland o
47132
Neuma nn
413
9000
New Jerse y
78321
Stephens
611
8500
Orland o
Prim ary ke y
Column/ Field
Fig. 22.1 Relational database structure.
 2001 Prentice Hall, Inc. All rights reserved.
6
22.2 Relational Database Model
• SQL statements
– Use to obtain table subsets
– Complete set of keywords enable programmers to define
complex queries
• Results of query called result sets (or record sets)
– Following table shows results of SQL query (Fig. 22.2)
• Provides geographical location of several departments
Department Location
413
New Jersey
611
Orland o
642
Lo s Angeles
Fig. 22.2 Result set formed by selecting data from a table.
 2001 Prentice Hall, Inc. All rights reserved.
7
22.3 Relational Database Overview
• Overview SQL using Books.mdb database
– Consists of four tables
• Authors, Publishers, AuthorISBN and Tables
• Primary key fields in italics
• Authors table (Figs. 22.3 and 22.4)
– Consists of four fields
• Unique ID number, first name, last name and year of birth
– Contains null value for YearBorn field
• Not primary key, so can contain null values
• FirstName and LastName can contain null values also
 2001 Prentice Hall, Inc. All rights reserved.
8
22.3 Relational Database Overview
Fie ld
De sc rip tio n
AuthorID
An integer representing the author’s ID number in the database. This field is the primary key
field for this table.
FirstName
LastName
YearBorn
Fig. 22.3 Authors ta b le
A string representing the author’s first name.
AuthorID
A string representing the author’s last name.
A string representing the author’s year of birth.
fro m
Books.mdb.
1
FirstName
Harvey
LastName
Deitel
YearBorn
1946
2
Paul
Deitel
1968
3
4
Fig. 22.4 Da ta
Tem
Nieto
1969
Kate
Steinbuhler
fro m the
Authors ta b le o f Books.mdb.
 2001 Prentice Hall, Inc. All rights reserved.
9
22.3 Relational Database Overview
• Publishers table (Figs. 22.5 and 22.6)
– Consists of two fields
• Unique ID and publisher name
Field
De sc rip tio n
PublisherID
An integer representing the publisher’s ID number in the database. This is the primary key
field for this table.
A string representing the abbreviated name for the publisher.
PublisherName
Fig. 22.5 Publishers ta b le from Books.mdb.
PublisherID
PublisherName
Prentice Hall
1
2
Fig. 22.6 Da ta
Prentice Hall PTR
from the
Publishers ta b le of Books.mdb.
 2001 Prentice Hall, Inc. All rights reserved.
10
22.3 Relational Database Overview
• Titles table (Figs. 22.7 and 22.8)
– Consists of six fields
• ISBN number, title, edition number, year published, book
description and publisher ID number
Fie ld
De sc rip tio n
ISBN
A string representing the ISBN number of the book.
Title
EditionNumber
YearPublished
A string representing the title of the book.
Description
PublisherID
A string representing the description of the book.
Fig. 22.7 Titles ta b le
A string representing the edition number of the book.
A string representing the publication year.
An integer representing the publisher’s ID number. This value must correspond to an ID
number in the Publishers table.
fro m
Books.mdb.
 2001 Prentice Hall, Inc. All rights reserved.
11
22.3 Relational Database Overview
ISBN
Title
Edition
Number
3
Year
Published
1999
Publisher
ID
1
0-13-012507-5
Java How to Program
0-13-013249-7
Getting Started with Visual C++ 6 with an
Introduction to MFC
1
1999
1
0-13-016143-8
Internet and World Wide Web How to
Program
1
1999
1
0-13-020522-2
Visual Basic 6 How to
Program Instructor's Manual with Solution
Disk
1
1999
1
0-13-028417-3
0-13-089571-1
0-13-089572-5
0-13-271974-6
XML How to Program
1
2001
1
C++ How to Program
3
2001
1
C How to Program
3
2001
1
Java Multimedia Cyber Classroom
1
1996
2
0-13-456955-5
Visual Basic 6 How to
Program
1
1998
1
1997
1
Java How to Program
2
0-13-899394-7
Fig. 22.8 Portion of the d a ta from the Titles ta b le of Books.mdb.
 2001 Prentice Hall, Inc. All rights reserved.
12
22.3 Relational Database Overview
• AuthorISBN table (Figs. 22.9 and 22.10)
– Consists of two fields
• ISBN number and author ID number
– Links names of authors with respective book titles
 2001 Prentice Hall, Inc. All rights reserved.
13
22.3 Relational Database Overview
Field
De sc rip tio n
ISBN
A string representing the ISBN number for a book. The ISBN number in this field also must
appear in the Titles table.
AuthorID
An integer representing the author’s ID number, which allows the database to connect each
book to a specific author. The ID number in this field must also appear in the Authors
table.
Fig. 22.9 AuthorISBN ta b le from Books.mdb.
ISBN
0-13-010671-2
AuthorID
1
0-13-010671-2
2
0-13-012507-5
1
0-13-013249-7
2
0-13-016143-8
2
0-13-020522-2
3
0-13-032364-0
2
0-13-032364-0
4
0-13-082928-5
3
Fig. 22.10
Portion of the d a ta from the
 2001 Prentice Hall, Inc. All rights reserved.
AuthorISBN ta b le of Books.mdb.
14
22.3 Relational Database Overview
• Microsoft Access diagram (Fig. 22.11)
– Illustrates relationships between tables in database
– Lines represent table relationships
– One-to-many relationship
• Example: line between Publishers and Titles tables
• Single publisher can have many books in Titles table
 2001 Prentice Hall, Inc. All rights reserved.
15
22.3 Relational Database Overview
Fig. 22.11 Table relationships in Books.mdb.
 2001 Prentice Hall, Inc. All rights reserved.
16
22.3 Relational Database Overview
• Rule of Entity Integrity
– Every record must have value in primary key field
– Primary key values must be unique
• Foreign key field (or constraints)
– References primary key field in another table
– Specified when creating tables
– Maintains Rule of Referential Integrity
• Every foreign key field value must appear in another table’s
primary key field
• Example: PublisherID field in Titles table
– Enables information from multiple tables to be joined for
analysis
 2001 Prentice Hall, Inc. All rights reserved.
17
22.4 Structured Query Language
• Overview SQL using Books.mdb database
• Following table lists some SQL keywords
SQL keyw ord
De sc rip tio n
SELECT
Select (retrieve) fields from one or more tables.
FROM
WHERE
GROUP BY
ORDER BY
INSERT INTO
Tables from which to get fields. Required in every SELECT.
Criteria for selection that determine the rows to be retrieved.
UPDATE
DELETE FROM
Fig. 22.12
Som e
Criteria for grouping records.
Criteria for ordering (sorting) records.
Insert values into one or more tables. Some databases do not require the SQL keyword
INTO.
Update existing data in one or more tables.
Delete data from a specified table.
SQL q ue ry keyw o rd s.
 2001 Prentice Hall, Inc. All rights reserved.
18
22.4.1 Basic SELECT Query
• Extracts information from one or more tables
• Simplest form
– SELECT * FROM TableName
• Asterisk (*) notifies query to select all rows and columns from
table
• TableName specifies a table in database
– Example: SELECT * FROM Authors
• Selecting specific fields
– Replace asterisk (*) with field names
– Example: SELECT AuthorID, LastName
FROM Authors
 2001 Prentice Hall, Inc. All rights reserved.
19
22.4.1 Basic SELECT Query
AuthorID
LastName
1
Deitel
2
Deitel
3
Nieto
4
Steinbuhler
Fig. 22.13
AuthorID a nd LastName from the Authors ta b le.
 2001 Prentice Hall, Inc. All rights reserved.
20
22.4.2 WHERE Clause
• Optional clause in SELECT query
• Selects records satisfying selection criteria
• Basic form
– SELECT fieldName1, fieldName2
FROM TableName WHERE criteria
– Example: SELECT * FROM Authors
WHERE YearBorn > 1960
• Result set contains two authors born after 1960
AuthorID
FirstName
LastName
YearBorn
2
Paul
Deitel
1968
3
Tem
Nieto
1969
Fig. 22.14
Authors from the
Authors ta b le b orn a fter 1960.
 2001 Prentice Hall, Inc. All rights reserved.
21
22.4.2 WHERE Clause
• Can contain operators
– <, >, <=, >=, =, <> and LIKE
• LIKE operator
– Performs pattern matching with wildcard characters asterisk
(*) and question mark (?)
– Pattern matching allows SQL to search for particular string
or string of characters
– Asterisk (*) indicates string can have zero or more
characters at it’s position
• Example: SELECT AuthorID, FirstName, LastName,
YearBorn FROM Authors
WHERE LastName LIKE ‘D*’
– Result set contains two records
 2001 Prentice Hall, Inc. All rights reserved.
22
22.4.2 WHERE Clause
AuthorID
FirstName
LastName
YearBorn
1
Harvey
Deitel
1946
2
Paul
Deitel
1968
Fig. 22.15
Authors from the
Authors ta b le w hose la st na m es sta rt w ith D.
 2001 Prentice Hall, Inc. All rights reserved.
23
22.4.2 WHERE Clause
• LIKE operator, cont.
– Question mark (?) indicates single character can occupy it’s
position
• Example: SELECT AuthorID, FirstName, LastName,
YearBorn FROM Authors
WHERE LastName LIKE ‘?i*’
– Result set contains one author
AuthorID
FirstName
LastName
YearBorn
3
Tem
Nieto
1969
Fig. 22.16
Authors from the
Authors ta b le w hose la st na m es c onta in i a s the sec ond lette r.
 2001 Prentice Hall, Inc. All rights reserved.
24
22.4.2 WHERE Clause
• Specifying range of characters
– Use [startValue-endValue]
• startValue is first character in range
• endValue is last character in range
 2001 Prentice Hall, Inc. All rights reserved.
25
22.4.3 GROUP BY Clause
• Groups result set by a particular column
• Basic form
– SELECT fieldName, COUNT(*) FROM TableName
GROUP BY fieldName
– COUNT returns number of records selected by query
• Example: SELECT AuthorID, COUNT (*) AS Count
FROM AuthorISBN GROUP BY AuthorID
• COUNT (*) AS Count assigns name to column that contains
total count values
AuthorID
Count
1
28
2
28
3
11
4
1
Fig. 22.17
Num b er of ISBN va lues a ssoc ia ted w ith e a c h a utho r.
 2001 Prentice Hall, Inc. All rights reserved.
26
22.4.3 GROUP BY Clause
• Combining WHERE and GROUP BY clauses
– Example: SELECT AuthorID, COUNT(*) AS Count
FROM AuthorISBN WHERE AuthorID <= 3
GROUP BY AuthorID
• Result set contains three records
AuthorID
Count
1
28
2
28
3
11
Fig. 22.18
Com b ining
ea c h a uthor.
WHERE a nd GROUP BY to re trieve the num b er of ISBN va lues a sso c ia ted w ith
 2001 Prentice Hall, Inc. All rights reserved.
27
22.4.4 ORDER BY Clause
• Sorts result set by given criteria
– In ascending order (ASC) or descending order (DESC)
• Basic form
– SELECT fieldName1, fieldName2 FROM TableName
ORDER BY fieldName ASC
• Can replace ASC with DESC
– Example: SELECT AuthorID, FirstName, LastName,
YearBorn FROM Authors
ORDER BY LastName ASC
AuthorID
FirstName
LastName
YearBorn
1
Harvey
Deitel
1946
2
Paul
Deitel
1968
3
Tem
Nieto
1969
4
Kate
Steinbuhler
Fig. 22.19
Authors from the
Authors ta b le in a sc e nd ing ord e r b y LastName.
 2001 Prentice Hall, Inc. All rights reserved.
28
22.4.4 ORDER BY Clause
• Basic form, cont.
– To obtain same list in descending order
• Example: SELECT AuthorID, FirstName, LastName,
YearBorn FROM Authors
ORDER BY LastName DESC
AuthorID
FirstName
LastName
4
Kate
Steinbuhler
3
Tem
Nieto
1969
1
Harvey
Deitel
1946
2
Paul
Deitel
1968
Fig. 22.20
Authors from the
YearBorn
Authors ta b le in d esc end ing ord e r b y LastName.
 2001 Prentice Hall, Inc. All rights reserved.
29
22.4.4 ORDER BY Clause
• Sorts multiple fields
– Example: SELECT AuthorID, FirstName, LastName,
YearBorn FROM Authors
ORDER BY LastName, FirstName
• ASC keyword is default sorting order, so is optional
• First sorts by last name, then by first name
AuthorID
FirstName
LastName
YearBorn
1
Harvey
Deitel
1946
2
Paul
Deitel
1968
3
Tem
Nieto
1969
4
Kate
Steinbuhler
Fig. 22.21
Authors from the
Authors ta b le in a sc e nd ing ord e r b y LastName a nd b y FirstName.
 2001 Prentice Hall, Inc. All rights reserved.
30
22.4.4 ORDER BY Clause
• Combining WHERE and ORDER BY clauses
– Example: SELECT ISBN, Title, EditionNumber,
YearPublished, PublisherID FROM Titles
WHERE Title LIKE ‘*How to Program’
ORDER BY Title ASC
• Selects titles ending in “How to Program”
• Sorts them in ascending order
 2001 Prentice Hall, Inc. All rights reserved.
31
22.4.4 ORDER BY Clause
ISBN
Title
EditionNumber Year
Published
Pub lisher
ID
0-13-089572-5
C How to Program
3
2001
1
0-13-089571-7
C++ How to Program
3
2001
1
0-13-528910-6
C++ How to Program
2
1997
1
0-13-028419-X
e-Business and e-Commerce How to
Program
1
2001
1
0-13-016143-8
Internet and World Wide Web How to
Program
1
1999
1
0-13-012507-5
Java How to Program
3
1999
1
0-13-028418-1
Perl How to Program
1
2001
1
0-13-456955-5
Visual Basic 6 How to Program
1
1998
1
0-13-028417-3
XML How to Program
1
2001
1
Fig. 22.22
Portion of the b o oks from the
in a sc end ing o rd er b y Title.
 2001 Prentice Hall, Inc. All rights reserved.
Titles ta b le whose titles e nd with How to Program, sorted
32
22.4.5 Merging Data from Multiple Tables
• May need to combine data from different tables
• Merging data = joining tables
– Combines records from different tables
– Extracts records common to tables
• Basic form
– SELECT fieldName1, fieldName2
FROM TableName1, TableName2
WHERE TableName1.fieldName = TableName2.fieldName
– WHERE clause specifies fields to be compared from each
table
• Normally represent primary key field in one table and foreign
key field in other table
 2001 Prentice Hall, Inc. All rights reserved.
33
22.4.5 Merging Data from Multiple Tables
• Basic Form, cont.
– Example: SELECT FirstName, LastName, ISBN
FROM Authors, AuthorISBN
WHERE Authors.AuthorID =
AuthorISBN.AuthorID
ORDER BY LastName, FirstName
• Merges FirstName and LastName fields from Authors
table with ISBN field from AuthorISBN table
• Result set contains list of authors and ISBN numbers of books
each author wrote
• Sorts in ascending order by LastName and FirstName
 2001 Prentice Hall, Inc. All rights reserved.
34
22.4.5 Merging Data from Multiple Tables
FirstName
LastName
ISBN
Harvey
Deitel
0-13-226119-7
Harvey
Deitel
0-13-016143-8
Harvey
Deitel
0-13-085609-6
Harvey
Deitel
0-13-013249-7
Harvey
Deitel
0-13-899394-7
Paul
Deitel
0-13-899394-7
Paul
Deitel
0-13-226119-7
Paul
Deitel
0-13-118043-6
Paul
Deitel
0-13-028418-1
Paul
Deitel
0-13-083055-0
Tem
Nieto
0-13-016143-8
Tem
Nieto
0-13-456955-5
Tem
Nieto
0-13-020522-2
Tem
Nieto
0-13-904947-9
Tem
Nieto
0-13-028419-X
Kate
Steinbuhler
0-13-0323-64-0
Fig. 22.23
Portion of the result se t c o nta ining a utho rs a nd ISBN num b ers sorted in a sc end ing ord er b y
LastName a nd FirstName.
 2001 Prentice Hall, Inc. All rights reserved.
35
22.4.5 Merging Data from Multiple Tables
• Fully qualified name
– TableName.fieldName specifies fields to be compared
– Fields with same name in tables require “TableName.”
syntax
– Cross database queries require database name before
TableName
 2001 Prentice Hall, Inc. All rights reserved.
36
22.4.6 Inserting a Record
• Use INSERT INTO operation
• Basic Form
– INSERT INTO TableName (fieldName1, fieldName2)
VALUES (value1, value2)
• TableName specifies table that receives new records
• Comma-separated list of field names specify the fields of
TableName
– Specifies columns that receive new records
• VALUES specifies data to be inserted into table
– Example: INSERT INTO Authors (FirstName,
LastName, YearBorn)
VALUES (‘Sue’, ‘Smith’, 1960)
• Inserts three values into three columns of Authors table
 2001 Prentice Hall, Inc. All rights reserved.
37
22.4.6 Inserting a Record
AuthorID
FirstName
LastName
YearBorn
1
Harvey
Deitel
1946
2
Paul
Deitel
1968
3
Tem
Nieto
1969
4
Kate
Steinbuhler
5
Sue
Smith
Fig. 22.24
1960
Authors ta b le a fter a n INSERT INTO op era tion to a d d a rec ord .
 2001 Prentice Hall, Inc. All rights reserved.
38
22.4.7 Updating a Record
• Modifies data in tables (updates records)
• Basic form:
– UPDATE TableName SET fieldName = value1
WHERE criteria
• SET assigns values to certain fields
– Example: UPDATE Authors SET YearBorn = ‘1969’
WHERE LastName = ‘Deitel’
AND FirstName = ‘Paul’
• AND states all components of selection criteria must be
satisfied
• Can replace WHERE clause with WHERE AuthorID = 2
 2001 Prentice Hall, Inc. All rights reserved.
39
22.4.7 Updating a Record
AuthorID
FirstName
LastName
YearBorn
1
Harvey
Deitel
1946
2
Paul
Deitel
1969
3
Tem
Nieto
1969
4
Kate
Steinbuhler
5
Sue
Smith
Fig. 22.25
Authors ta b le a fter a n UPDATE op e ra tion to m od ify a rec ord .
 2001 Prentice Hall, Inc. All rights reserved.
1960
40
22.4.8 DELETE FROM Statement
• Removes data from tables
• Basic form
– DELETE FROM TableName WHERE criteria
– Example: DELETE FROM Authors
WHERE LastName = ‘Smith’
AND FirstName = ‘Sue’
• Can replace WHERE clause with WHERE AuthorID = 5
AuthorID
FirstName
LastName
Yea rBorn
1
Harvey
Deitel
1946
2
Paul
Deitel
1969
3
Tem
Nieto
1969
4
Kate
Steinbuhler
Fig. 22.26
Ta b le
Authors a fte r a DELETE op era tion to rem ove a re c ord .
 2001 Prentice Hall, Inc. All rights reserved.
41
22.4.9 TitleAuthor Query from Books.mdb
• Books.mdb contains one predefined query
– TitleAuthor
• Produces table containing book title, ISBN number, last name,
book’s year published and publisher’s name
• Figure 22.27 shows query, Figure 22.28 shows result set
 2001 Prentice Hall, Inc. All rights reserved.
42
Outline
Fully qualified names for clarity
1
2
3
4
5
6
7
8
SELECT Titles.Title, Titles.ISBN, Authors.FirstName,
Authors.LastName, Titles.YearPublished,
Publishers.PublisherName
FROM Publishers, Titles, Authors, AuthorISBN
Lines= 1-3
indicate fields that
WHERE Publishers.PublisherID
Titles.PublisherID
AND Authors.AuthorID = AuthorISBN.AuthorID
AND Titles.ISBN = AuthorISBN.ISBN
ORDER BY Titles.Title
TitleAuthor
query selects
Joins tables provided that PublisherID in Publishers
table matches
PublisherID
in Titles
table
Fig. 22.27Combines
TitleAuthor
query
from
Books.mdb
two preceding
resultthe
sets
on conditiondatabase.
that
ISBN field
in
Titles table matches ISBN field in AuthorISBN table
 2001 Prentice Hall, Inc.
All rights reserved.
43
22.4.9 TitleAuthor Query from Books.mdb
Title
ISBN
First
Name
Last
Name
Year
Published
Publisher
Name
C How to Program
0-13-226119-7
Paul
Deitel
1994
Prentice Hall
C How to Program
0-13-089572-5
Harvey
Deitel
2001
Prentice Hall
C++ How to
Program
0-13-089571-7
Paul
Deitel
2001
Prentice Hall
e-Business and e-Commerce 0-13-032364-0
for Managers
Kate
Steinbuhler
2001
Prentice Hall
e-Business and e-Commerce 0-13-028419-X
How to Program
Harvey
Deitel
2001
Prentice Hall
Internet and World Wide
Web How to Program
0-13-016143-8
Paul
Deitel
1999
Prentice Hall
Java How to
Program
0-13-899394-7
Paul
Deitel
1997
Prentice Hall
Perl How to
Program
0-13-028418-1
Tem
Nieto
2001
Prentice Hall
Visual Basic 6 How to
Program
0-13-456955-5
Tem
Nieto
1998
Prentice Hall
XML How to
Program
0-13-028417-3
Harvey
Deitel
2001
Prentice Hall
XML How to
Program
0-13-028417-3
Paul
Deitel
2001
Prentice Hall
Fig. 22.28
Po rtio n o f the q ue ry re sults fro m the
 2001 Prentice Hall, Inc. All rights reserved.
TitleAuthor q ue ry.
44
22.5 MySQL
• Pronounced “My Ess Que Ell”
• Robust and scalable RDBMS
• Multiuser, multithreaded server
– Performs multiple commands concurrently
• Uses SQL to interact with data
• Supports various programming languages
– C, C++, Java, Python, Perl, PHP, etc
• Supports various operating systems
– Windows, Linux and Unix
• Access multiple databases with single query
 2001 Prentice Hall, Inc. All rights reserved.
45
22.6 Introduction to DBI
• Databases part of distributed applications
– Divides work across multiple computers
• Retrieves result set and displays results
• Driver
– Helps programs access databases
– Each can have different syntax
– Each database requires its own driver
• Interface
– Provides uniform access to all database systems
• Database interface
– Programmatic library for accessing relational database
 2001 Prentice Hall, Inc. All rights reserved.
46
22.6.1 Perl Database Interface
• Perl DBI
– Enables users to access relational databases from Perl
programs
– Database independent
– Most widely used interface in Perl
– Uses handles (Fig. 22.29)
• Object-oriented interfaces
• Driver handles, database handles, statement handles
Da ta Ob jec t Ha nd le s
De sc rip tio n
Driver Handles
Encapsulates the driver for the database; rarely used in a Perl script.
Database Handles
Encapsulates a specific connection to a database; can send SQL statements to a database.
Statement Handles
Encapsulates specific SQL statements and the results returned from them.
Fig. 22.29
Da ta ob jec t ha nd les for Pe rl DBI.
 2001 Prentice Hall, Inc. All rights reserved.
47
22.6.2 Python DB-API
• Python DB-API
– Database application programming interface
– Portable across several databases
– Consists of Connection and Cursor data objects
• Connection data object (Fig. 22.30)
– Accesses database through four methods
• Cursor data object (Fig. 22.31)
– Manipulates and retrieves data
 2001 Prentice Hall, Inc. All rights reserved.
48
22.6.2 Python DB-API
Connec tio n Da ta Ob jec ts
De sc rip tio n
close
Closes the connection to the database.
commit
Commits (saves) a transaction (i.e., interaction with a database through SQL keywords
and commands).
rollback
Exits a pending transaction without saving changes. Returns the user to the beginning
of the transaction.
cursor
Fig. 22.30
Returns a new Cursor object or the current connection.
Connection d a ta ob jec ts fo r Pytho n DB-API.
Cursor Da ta Ob jec ts
De sc rip tio n
rowcount
Returns the number of rows retrieved by the last execute method call.
close
execute( operation )
Closes the Cursor object.
Executes a database query or command. Return values not defined.
executemany( operation,
parameters )
fetchone
fetchmany( size )
Executes a database query or command against a set of parameters. Return values not
defined.
fetchall
Fig. 22.31
Returns all the rows of a query result set.
Returns the next row of a query result.
Returns a set of rows—defined in the parameter—for a query result set.
Som e Cursor d a ta ob jec ts fo r PB-API.
 2001 Prentice Hall, Inc. All rights reserved.
49
22.6.3 PHP dbx module
• dbx module
– Consists of seven functions that interface to database
modules
– Supports MySQL, PostgreSQL and ODBC databases
d b x func tions
De sc rip tio n
dbx_close
Closes an open connection/database.
dbx_connect
Opens a connection/database.
dbx_error
dbx_query
dbx_sort
dbx_cmp_asc
dbx_cmp_desc
Fig. 22.32
Da ta
Reports any error messages from the last function call in the module.
Executes a query and returns the results.
Sorts a result by a custom sort function.
Compares two rows and sorts them in ascending order.
Compares two rows and sorts them in descending order.
ob jec ts fo r PHP dbx m od ules.
 2001 Prentice Hall, Inc. All rights reserved.
50
22.7 ActiveX Data Objects (ADO)
• Microsoft Universal Data Access (UDA)
– Supports high-performance data access to relational,
non-relational and mainframe data sources
– Consists of three primary components
• OLE DB
– Core of UDA architecture
– Provides low-level access to data source
• Open Database Connectivity (ODBC)
– C programming-language library
– Uses SQL to access data
• ActiveX Data Objects
– Simple object modules
– Provide uniform access to data source through OLE DB
 2001 Prentice Hall, Inc. All rights reserved.
51
22.7 ActiveX Data Objects (ADO)
App lic ation or Brow ser
ADO
OLE DB
ODBC
Rela tiona l da ta sourc es
Non-rela tiona l da ta sourc es
Leg ac y da ta
Fig. 22.33 Microsoft’s UDA architecture.
Connection
Command
Parameters
Errors
Error
RecordSet
Fields
Parameter
Field
Fig. 22.34 Portion of the ADO object model.
 2001 Prentice Hall, Inc. All rights reserved.
52
22.7 ActiveX Data Objects (ADO)
• ADO object model
– Provides objects and collections
• Collections
– Containers that hold one or more objects of specific type
– Following table lists some ADO objects and collections
 2001 Prentice Hall, Inc. All rights reserved.
53
22.7 ActiveX Data Objects (ADO)
Ob je c t/ C olle c tio n
De sc rip tio n
Connection object
Connects to the data
Command object
Contains the query that interacts 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
Recordset object
Contains one or more Parameter objects.
Created when an error occurs while accessing data.
source.
Contains one or more Error objects.
Contains zero or more records that match the database query. Collectively, this group of
records is called a recordset.
Contains the value (and other attributes) of one data source field.
Field object
Fields collection
Contains one or more Field objects.
Contains a characteristic of an ADO object.
Property object
Properties collection
Contains one or more Property objects.
Record object
Contains a single row of a Recordset.
Contains a stream of binary data.
Stream object
Fig. 22.35
Som e ADO ob jec t a nd c ollec tion typ es.
 2001 Prentice Hall, Inc. All rights reserved.