1 / 36

Lecture 8 Accessing Database

Lecture 8 Accessing Database. Database server Application access to the database: Open Database Connectivity Accessing Database using Perl Script. Internet. Client computers with web browsers. Database server. Web server. HTTP request. HTTP response. Data and service level.

noah
Download Presentation

Lecture 8 Accessing Database

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. Lecture 8 Accessing Database • Database server • Application access to the database: • Open Database Connectivity • Accessing Database using Perl Script 5: Web-based Applications

  2. Internet Client computers with web browsers Database server Web server HTTP request HTTP response Data and service level Content level Presentation level 1. Web Architecture • Three-tier architecture: • Presentation: clients contains both the presentation and application logic components. • Content: web server provides interactive view of information form a data store. • Data and service level: provides data for the web server. 5: Web-based Applications

  3. 1.1. Database • Database • Integrated collection of data • Database Management System (DBMS) • Has mechanisms for storing and organizing data • Allows sophisticated queries and manipulations of data • Database applications: • Banking: all transactions • Airlines: reservations, schedules • Universities: registration, grades 5: Web-based Applications

  4. view level view 1 view 1 view n logical level physical level 1.1. Database • Architecture for a database system 5: Web-based Applications

  5. 1.1. Database • Architecture for a database system • Physical level describes how a record is stored. • single file: MS Access, Lotus Approach, MS Fox Pro, Paradox • true data server (multiple files): MySQL, Oracle, Sybase • Logical level describes data stored in database, and the relationships among the data. • View level: application programs hide details of data types. • Views can hide information (e.g. salary) for security purposes. 5: Web-based Applications

  6. 1.1.1. Relational Database Model • Relational Database Model • Most popular database system in use today • 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: Web-based Applications

  7. Attributes A record A column Primary Key 1.1.1. Relational Database Model • Example of Relational Database Structure 5: Web-based Applications

  8. Table: Balance Table: Account 1.1.1. Relational Database Model • Example of Relational Database Structure • Physical level: Microsoft Access - banking.mdb • Logical level: • Three tables: Customer, Account and Balance • The primary key in Customer table is customer-id. Table: Customer 5: Web-based Applications

  9. 1.1.1. Relational Database Model • Relationships among the three tables: • Lines between tables represent relationships • Example: Line between Customer and Account • One-to-many relationship • Every customer can have a number of accounts. 5: Web-based Applications

  10. Attributes A record A column Primary Key 1.1.1. Relational Database Model • View level: join Customer and Account tables. 5: Web-based Applications

  11. 1.1.1. Relational Database Model • Different users interested in different parts of the table • Needs a data manipulation or query language - to access and manipulate the data from the table/database • Structured Query Language(SQL – pronounced “sequel”) • The most widely used query language. • Provides complete set of keywords • Allows smaller databases to be combined to form larger ones • Results of a query called result sets (or record sets) • e.g. Result set to show the customers who stay in Kowloon 5: Web-based Applications

  12. 1.1.2. Structured Query Language • Structured Query Language (SQL) keywords used to • Query a database • Insert records into a database • Update existing records in a database (Note: there are other keywords not included in this table) 5: Web-based Applications

  13. 1.1.2 SELECT Query • SELECT * FROMTableName • TableName specifies table in database where data is located • * selects all rows and fields from TableName • SELECT * FROM Customer • Selects the entire contents of the customer table • SELECT customer-id, customer-name FROM Customer • To select fields from table, replace * with comma-separated list of field names to select • Returns the following: 5: Web-based Applications

  14. 1.1.2 SELECT Query • 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 • e.q. SELECT customer-id, district FROM customer WHERE district = “KOWLOON”return the followings: 5: Web-based Applications

  15. 1.1.2 SELECT Query • ORDER BY clause • Sorts results of query into ascending or descending order SELECTfieldName1, fieldName2, …FROMTableNameORDER BYfieldNameASC SELECTfieldName1, fieldName2, …FROMTableNameORDER BYfieldNameDESC • Example SELECT customer-id, customer-name FROM Customer ORDER BY customer-name ASC • Returns customers sorted by customer name in ascending order 5: Web-based Applications

  16. 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 1.1.2 Inserting a Record • 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 5: Web-based Applications

  17. 1.1.2 Inserting a Record • Sample insert operation INSERT INTO Customer ( customer-id, customer-name, district, phone-no ) Values (10006, ‘Kwok, Charles’, ‘NT’, ‘9342-9432’) • Results: Newly added record 5: Web-based Applications

  18. 1.1.2 Updating a Record • Modify a record with the UPDATE operation UPDATETableName SETfieldName1 = value1, fieldName2 = value2, …, fieldNameN = valueN WHERE criteria • Example: UPDATE Customer SET phone-no = ‘9434-4321’ WHERE customer-id = 10003 • Change phone number value for the customer id = 10003 from 2344-0979 to 9434-4321. 5: Web-based Applications

  19. 1.1.2 Updating a Record • Old table: • New table: 5: Web-based Applications

  20. Application or Browser ADO OLE DB ODBC Relational data sources Mainframe/legacy data Non-relational data sources 2 Application Access to the Database • Microsoft Universal Data Access (UDA) • Architecture designed for high-performance data access to different data sources. 5: Web-based Applications

  21. 2. Application Access to the Database • UDA consists of three primary components • OLE DB (Object Linking and Embedding DB) • Core of UDA architecture • Provides low-level access to any data source • ODBC (Open Database Connectivity) • API (Application Programming Interface) or programming language library written in C • Developed by Microsoft to allow Windows applications to communicate in a uniform manner with relational databases • Uses SQL to access data • ADO (ActiveX Data Objects) • Simple object model • Provides uniform access to any data source by interacting with OLE DB 5: Web-based Applications

  22. Application or Browser ADO OLE DB ODBC Relational data sources Mainframe/legacy data Non-relational data sources 2. Application Access to the Database • Two common methods: • ODBC approach - e.g. using Perl scripts (red line). • ADO approach - e.g. using ASP (blue line). 5: Web-based Applications

  23. 2.1. ODBC Approach • ODBC (Open Database Connectivity) • API (Application Programming Interface) or programming language library written in C • Uses SQL to access data • ODBC Driver written by vendors • Uses ODBC API to provide uniform access to the database • 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 5: Web-based Applications

  24. 2.1. Registering a Database • Specific instructions for Windows 98/2000: • double-click the ODBC Data Sources (32 bit) icon in the Windows Control Panel to display the ODBC Data Source Administrator dialog. • Click the System DSN tab to view a list of all systems DSNs. • Select the name to the right of Microsoft Access Driver (*.mdb) from the list. 5: Web-based Applications

  25. 2.1. Registering a Database • Click Add… to display the Create New Data Source dialog. Select Microsoft Access Driver (*.mdb) and click Finish to display the ODBC Microsoft Access Setup dialog. 5: Web-based Applications

  26. 2.1. Registering a Database • Enter the Data Source Name and Description field. Click the Select… button to display the Select Database dialog. Select the database that you want. Click OK to close this dialog. 5: Web-based Applications

  27. 2.1. Registering a Database • The Banking DSN is now listed. 5: Web-based Applications

  28. 3. Accessing Database using Perl Script • After registering database as ODBC source, we can access it using Perl scripts. • Perl package Win32-ODBC • Enables Perl programs to connect to ODBC data sources • Example: From Web browser 1. Client enters SQL query string 2. String sent to Web server 3. Perl script executed • Database queried 4. Record set in HTML form sent back to client • Use tables to output fields in a database • Organizes information neatly 5: Web-based Applications

  29. 3. Accessing Database using Perl Script 5: Web-based Applications

  30. 3. Accessing Database using Perl Script • Example of how to access database by using HTML form and Perl scripts. <HTML> <HEAD> <TITLE>Sample Database Query</TITLE> </HEAD> <BODY BACKGROUND = "images/back.gif"> <BASEFONT FACE = "ARIAL,SANS-SERIF" SIZE = 2> <FONT SIZE = +2> <STRONG>Querying an ODBC database.</STRONG> </FONT><BR> <FORM METHOD = "POST" ACTION = "cgi-bin/data.pl"> <INPUT TYPE = "TEXT" NAME = "QUERY" SIZE = 40 VALUE = "SELECT * FROM CUSTOMER"><BR><BR> <INPUT TYPE = "SUBMIT" VALUE = "Send Query"> </FORM> </BODY> </HTML> Insert and define textINPUT for entering SQL query 5: Web-based Applications

  31. CUSTOMER 3. Accessing Database using Perl Script • Script output: 5: Web-based Applications

  32. 3. Accessing Database using Perl Script • Perl scripts: # Program to query a database and send results to the client. use Win32::ODBC; use CGI qw (:standard); $querystring = param(QUERY); $DSN = "Banking"; print header; if (!($Data = new Win32::ODBC($DSN))) { print "Error connecting to $DSN\n"; print "Error: " . Win32::ODBC::Error() . "\n"; exit; } if ($Data->Sql($querystring)) { print "SQL failed.\n"; print "Error: " . $Data->Error() . "\n"; $Data->Close(); exit; } Specifies that a new instant of the object is to be created Query string sent to the database If fails, error message is returned 5: Web-based Applications

  33. 3. Accessing Database using Perl Script print "<BODY BACKGROUND = \"/images/back.gif\">"; print "<BASEFONT FACE = \"ARIAL,SANS-SERIF\" SIZE = 3>"; print "<FONT COLOR = BLUE SIZE = 4> Search Results </FONT>"; $counter = 0; print "<TABLE BORDER = 0 CELLPADDING = 5 CELLSPACING = 0>"; while($Data->FetchRow()) { %Data = $Data->DataHash(); @key_entries = keys(%Data); print "<TR>"; foreach $key( keys( %Data ) ) { print "<TD BGCOLOR = #9999CC>$Data{$key}</TD>"; } print "</TR>"; $counter++; } print "</TABLE>"; print end_html; $Data->Close(); Retrieves the fields in a row from the record set Coding HTML in Perl open using print header close using print end_html 5: Web-based Applications

  34. 3. Accessing Database using Perl Script • Error() - Returns the last encountered error • FetchRow() - Retrieves the next record from the keyset. • DataHash() - Returns the contents for the entire row from the record set • keys() - Returns an unordered array containing all keys in a hash • Close() - Closes the ODBC connection • new - Creates a new ODBC connection based on DSN (Data Source Name) 5: Web-based Applications

  35. 3. Accessing Database using Perl Script • Script output: 5: Web-based Applications

  36. Further Readings • Note: This topic is designed with the objective of providing an introduction toaccessing database using perl scripts. • Students who wish to invest more time on studying advanced features and topics of ODBC and accessing databases are referred to the following resources: • Deitel Chapter 22 • http://aspn.activestate.com/ASPN/Reference/Products/ActivePerl/site/lib/Win32/ODBC.html • http://aspn.activestate.com/ • http://www.roth.net/perl/odbc/faq/ 5: Web-based Applications

More Related