Chapter 22 - SQL, MySQL, DBI and ADO

1. Chapter 22 - SQL, MySQL, DBI and ADO Outline 22.1 Introduction 22.2 Relational Database Model 22.3 Relational Database Overview 22.4 Structured Query Language 22.4.1 Basic SELECT Query 22.4.2 WHERE Clause 22.4.3 GROUPBY Clause 22.4.4 ORDERBY Clause 22.4.5 Merging Data from Multiple Tables 22.4.6 Inserting a Record 22.4.7 Updating a Record 22.4.8 DELETEFROM Statement 22.4.9 TitleAuthor Query from Books.mdb 22.5 MySQL

2. Chapter 22 - SQL, MySQL, DBI and ADO Outline 22.6 Introduction to DBI 22.6.1 Perl Database Interface 22.6.2 Python DB-API 22.6.3 PHP dbx module 22.7 ActiveX Data Objects (ADO) 22.8 Internet and World Wide Web Resources

3. 22.4.6 Inserting a Record • Use INSERTINTO operation • Basic Form • INSERTINTO 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: INSERTINTOAuthors (FirstName,LastName, YearBorn)VALUES (‘Sue’, ‘Smith’, 1960) • Inserts three values into three columns of Authors table

4. 22.4.6 Inserting a Record

5. 22.4.7 Updating a Record • Modifies data in tables (updates records) • Basic form: • UPDATETableNameSETfieldName = value1WHEREcriteria • SET assigns values to certain fields • Example: UPDATEAuthorsSETYearBorn=‘1969’WHERELastName=‘Deitel’ANDFirstName=‘Paul’ • AND states all components of selection criteria must be satisfied • Can replace WHERE clause with WHEREAuthorID=2

6. 22.4.7 Updating a Record

7. 22.4.8 DELETEFROM Statement • Removes data from tables • Basic form • DELETEFROMTableNameWHEREcriteria • Example: DELETEFROMAuthorsWHERELastName=‘Smith’ANDFirstName=‘Sue’ • Can replace WHERE clause with WHEREAuthorID=5

8. 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

9. Fully qualified names for clarity Lines 1-3 indicate fields that query selects Joins tables provided that PublisherID in Publishers table matches PublisherID in Titles table Combines two preceding result sets on condition that ISBN field in Titles table matches ISBN field in AuthorISBN table 1 SELECT Titles.Title, Titles.ISBN, Authors.FirstName, 2 Authors.LastName, Titles.YearPublished, 3 Publishers.PublisherName 4 FROM Publishers, Titles, Authors, AuthorISBN 5 WHERE Publishers.PublisherID = Titles.PublisherID 6 AND Authors.AuthorID = AuthorISBN.AuthorID 7 AND Titles.ISBN = AuthorISBN.ISBN 8 ORDER BY Titles.Title TitleAuthor Fig. 22.27 TitleAuthor query from the Books.mdb database.

10. 22.4.9 TitleAuthor Query from Books.mdb

11. 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

12. 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

13. 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

14. 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

15. 22.6.2 Python DB-API

16. 22.6.3 PHP dbx module • dbx module • Consists of seven functions that interface to database modules • Supports MySQL, PostgreSQL and ODBC databases

17. 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

18. 22.7 ActiveX Data Objects (ADO) Fig. 22.33 Microsoft’s UDA architecture. Fig. 22.34 Portion of the ADO object model.

19. 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

20. 22.7 ActiveX Data Objects (ADO)