1 / 53

Chapter 25 – Database: SQL, ADO and RDS

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

james-conner
Download Presentation

Chapter 25 – Database: SQL, ADO and RDS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

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

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

  4. 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)

  5. 25.2 Relational Database Model (II) Table: Employee Number Name Department Salary Location 23603 JONES, A. 413 1100 NEW JERSEY 24568 KERWIN, R. 413 2000 NEW JERSEY A record 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 A column Relational Database Structure

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

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

  8. 25.3 Relational Database Overview: Books.mdb(II) • Authors table description • AuthorID • Integer representing author’s ID number in database (primary key) • FirstName • String representing author’s first name • LastName • String representing author’s last name • YearBorn • String representing author’s year of birth • Authors table data

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

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

  11. 25.3 Relational Database Overview: Books.mdb(V) AuthorISBN table data

  12. 25.3 Relational Database Overview: Books.mdb(VI) • Titles table description • ISBN • String representing ISBN number of the book (primary key) • Title • String representing title of the book • EditionNumber • String representing edition of the book • YearPublished • String representing year in which book was published • Description • String representing description of the book • PublisherID • Integer representing publisher’s ID number (corresponds to ID number in Publishers table)

  13. 25.3 Relational Database Overview: Books.mdb(VII) • Titles table data

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

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

  16. 25.4.1 Basic SELECT Query • SELECT * FROMTableName • 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:

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

  18. 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: SELECTfieldName1, fieldName2, …FROMTableNameWHERECriteria • 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?

  19. 25.4.2 WHERE Clause (II) • Example SELECT AuthorID, FirstName, LastName, YearBorn FROM Authors WHERE YearBorn > 1960 • Returns

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

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

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

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

  24. 25.4.3 ORDER BY Clause • ORDER BY Clause • Sorts results of query into ascending or descending order SELECTfieldName1, fieldName2, …FROMTableNameORDER BYfieldNameASC SELECTfieldName1, fieldName2, …FROMTableNameORDER BYfieldNameDESC • Example SELECT AuthorID, FirstName, LastName, YearBorn FROM Authors ORDER BY LastName ASC • Returns authors sorted by last name in ascending order

  25. 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:

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

  27. 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 SELECTfieldName1, fieldName2, … FROMTable1INNER JOINTable2 ONTable1.field = Table2.field

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

  29. 25.4.4 Using INNER JOIN to Merge Data from Multiple Tables (III)

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

  31. 24.4.5 TitleAuthor Query from Books.mdb (II) Portions of TitleAuthor query output

  32. 25.4.6 Inserting a Record List of field names into which to insert values (not required if inserting complete record) Table into which record will be inserted • INSERT INTO TableName (fieldName1, fieldName2, …,fieldNameN) • Values(value1, value 2, …, valueN) Values to be inserted into fields – in order of fields listed before KEYWORDS • INSERT INTO operation • Inserts data into the table (e.g; adds a record) • Simplest form: • The single quote character should be used as a delimiter for strings to be inserted into the database

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

  34. 25.4.7 Updating a Record • Modify a record with the UPDATE operation UPDATETableName SETfieldName1 = 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

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

  36. 25.6 ActiveX Data Objects (ADO) Application or Browser ADO OLE DB ODBC Relational data sources Mainframe/legacy data Non-relational data sources • 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:

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

  38. 25.6 ActiveX Data Objects (ADO) • ADO object model • Provides objects and collections

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

  40. 25.7 Remote Data Services (RDS) (II) RDS Architecture • RDS still developing • Future releases will work with PWS • Server-side setup complex and developing rapidly RDS Browser Client Server PWS/IIS ADO OLE DB ODBC Relational data sources Mainframe/legacy data Non-relational data sources

  41. 25.7 Remote Data Services (RDS) (III) • RDS implemented as client-side ActiveX control • Named RDS.Datacontrol Properties Table Methods Table

  42. 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 • state can have one of three values • 0 – no user activity • 1 – download state • 2 – upload state

  43. 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" 7ID =deitelDCWIDTH =0HEIGHT =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() 28If state = 1 Then 29Select Case deitelDC.ReadyState 30 ’ Just started downloading, there is no 31 ’ data to look at. 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_OnReadyStateChange() Sub procedure 3.2 Open Select structure inside if structure for downloading actions

  44. 32 Case adcReadyStateLoaded 33 StatusText.Value = "Downloading..." 34 35 ’ Partially downloaded the data, 36 ’ there is more to come. 37 Case adcReadyStateInteractive 38 StatusText.Value = "Still downloading..." 39 40 ’ Completely downloaded the data, 41 ’ there’s no more coming. 42 Case adcReadyStateComplete 43 44 ’ Fill in the fields for updating 45 ’ Get ISBN field to prove we got the right one 46 FoundISBN.Value = deitelDC.Recordset( "ISBN" ) 47 48 ’ The title we want to modify 49 FoundTitle.Value = deitelDC.Recordset( "Title" ) 50 51 ’ OK for updating. Everything worked. 52 StatusText.Value = "Finished downloading." 53 state = 2 54 End Select 55 56 ElseIf state = 2 Then 57 58 Select Case deitelDC.ReadyState 59 ’ Started uploading, there is no data sent. 60 Case adcReadyStateLoaded 61 ’ OK for updating. Everything worked. 62 StatusText.Value = "Uploading..." 3.3 set StatusText.value downloading 3.4 Fill in fields for updating 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

  45. 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" ) 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

  46. 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 "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 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 5.3 Begin data download 5.4 Set values 5.5 Tell server to begin sending data - call deitelDC.Refresh() Sub procedure 5.6 Close structures 6.1 Define Update_OnClick() Sub procedure 6.2 Fetch value from text box, setup to go back to server

  47. 122 ’ Save these changes 123Call deitelDC.SubmitChanges() 124 125 ’ Refresh after submit operation 126Call deitelDC.Refresh() 127 End If 128End Sub 129--> 130</SCRIPT> 131 132<HEAD> 133<META NAME =VI60_defaultClientScriptCONTENT =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= serverNAME= serverVALUE= "http://xxx.xxx.xxx.xxx" 143 TYPE = "text"SIZE =60></TD> 144</TR> 145<TR> 146<TD>ISBN:</TD> 147<TD><INPUT ID =ISBNNAME =ISBNVALUE ="0-13-226119-7" 148TYPE = "text"></TD> 149</TR> 150</TABLE> 151<INPUT TYPE = "button"ID ="Find"NAME = "Find"VALUE= "Find"><BR> 152<BR> 153Results<BR> 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”

  48. 154<TABLE BORDER ="0"CELLPADDING = "0"> 155<TR> 156<TD>Found ISBN( readonly ):</TD> 157<TD><INPUT ID = FoundISBNREADONLYNAME = FoundISBNTYPE ="text"> 158</TD> 159</TR> 160<TR> 161<TD>Found Title:</TD> 162<TD><INPUT ID = FoundTitleNAME = FoundTitleTYPE ="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" 169SIZE =30READONLYVALUE ="Click Find."><BR> 170</BODY> 171</HTML> 7.4 Insert and define Results INPUT elements 7.5 Insert button INPUT: “Update Title” 7.6 Insert StatusTextINPUT textbox

  49. 25.7 Remote Data Services (RDS) (V) Document Output 1 The page is loaded

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

More Related