1 / 36

Database Access From the Web

Database Access From the Web. [Textbook Chapter 13]. Relational Databases - A database is a collection of data organized for easy, fast, and worry-free retrievals & modification , managed by DBMS A relational database is based on the relational data model –

kare
Download Presentation

Database Access From the Web

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. Database Access From the Web [Textbook Chapter 13]

  2. Relational Databases • - A database is a collection of data organized for easy, fast, andworry-freeretrievals & modification, managed by DBMS • A relationaldatabase is based on the relational data model – • - Tables representing relations, with columns and rows • - rows also called entities/records, while columns as attributes • - We’ll design an example database, usedCorvettesfor sale • - Could just put all data in a single table, leading to redundant store • - Better to put the equipment in a separate table and use another • cross-reference table to relate cars to equipment • - To save space, use yet another table for state names, and other • tables just hold references to the stored state name entries

  3. Relational Databases (continued) • - Logical model (i.e., ER Model) • Corvettes States • Corvettes_Equipment Equipment • (Note the symbols in this ER Diagram) • Implementation: The Corvettes Table • Vette_id Body_style Miles Year State • 1 coupe 18.0 1997 4 • 2 hatchback 58.0 1996 7 • 3 convertible 13.5 2001 1 • 4 hatchback 19.0 1995 2 • 5 hatchback 25.0 1991 5 • 6 hardtop 15.0 2000 2 • 7 coupe 55.0 1979 10 • 8 convertible 17.0 1999 5 • 9 hardtop 17.0 2000 5 • 10 hatchback 50.0 1995 7

  4. Relational Databases (continued) • The States Table • State_id State • 1 Alabama • 2 Alaska • 3 Arizona • 4 Arkansas • 5 California • 6 Colorado • 7 Connecticut • 8 Delaware • 9 Florida • 10 Georgia The Equipment table Equip_id Equipment 1 Automatic 2 4-speed 3 5-speed 4 6-speed 5 CD 6 leather

  5. Relational Databases (continued) The Corvettes-Equipmentcross-reference table Vette_id Equip (a Many to Many relationship) 1 1 1 5 1 6 2 1 2 5 2 6 3 1 3 6 4 2 4 6 5 1 5 6 6 2 7 4 7 6 8 4 8 5 8 6 9 4 9 5 9 6 10 1 10 5

  6. Intro to SQL • A standard language to create, query, and modify relational DBs • - More like a structured English than a programming language • - We cover only six basic commands/clauses: • CREATE TABLE, SELECT, INSERT, UPDATE, DELETE, and DROP • SQL reserved words are case insensitive • The SELECT command specifies what you want • - General form: • SELECTcolumn names • FROMtable names • WHEREcondition • - Example: • SELECT Body_style FROM Corvettes • WHERE Year > 1994

  7. Intro to SQL (continued) - Joins - If you need to query over multiple tables you need to first jointhem together, - e.g., want all cars that have CD players, you joinCorvettes and Equipment - a Queryimplicitly builds a temporary table with info from two tables using the joinoperation - Tables are specified in FROM clause and conditions in WHERE clause - For our example, we use multiple WHERE conditions  See the example on the next slide – 3 tables’ join

  8. Intro to SQL (continued) - Joins (continued) SELECT Corvettes.Vette_id, Corvettes.Body_style, Corvettes.Miles, Corvettes.Year, Corvettes.State, Equipment.Equip FROM Corvettes, Corvettes_Equipment, Equipment WHERE Corvettes.Vette_id = Corvettes_Equipment.Vette_id AND Corvettes_Equipment.Equip = Equipment.Equip_id AND Equipment.Equip = 'CD' This query produces VETTE_ID BODY_STYLE MILES YEAR STATE EQUIP. 1 coupe 18.0 1997 4 CD 2 hatchback 58.0 1996 7 CD 8 convertible 17.0 1999 5 CD 9 hardtop 17.0 2000 5 CD 10 hatchback 50.0 1995 7 CD

  9. Intro to SQL (continued) • - The INSERT Command • INSERT INTOtable_name(col_name1, … col_namen) • VALUES(value1, …, valuen) • - The correspondence between column names and values is important • INSERT INTO Corvettes(Vette_id, Body_style, Miles, Year, State) • VALUES (37, 'convertible', 25.5, 1986, 17) • - The UPDATE Command • - To change one or more values of a row in a table • UPDATEtable_name • SETcol_name1=value1, • … • col_namen=valuen • WHEREcol_name=value • - The WHERE clause decides which (one or more) rows to be updated!

  10. Intro to SQL (continued) - The UPDATE Command (continued) - Example: UPDATE Corvettes SET Year = 1996 WHERE Vette_id = 17 - The DELETE Command - Example: DELETE FROM Corvettes WHERE Vette_id = 27 - The WHERE clause could specify more than one row of the table! - The DROP Command - To delete whole databases or complete tables DROP (TABLE | DATABASE)[IF EXISTS] name Example, DROP TABLE IF EXISTS States

  11. Intro to SQL (continued) - The CREATE TABLEcommand: CREATE TABLEtable_name( column_name1data_typeconstraints, … column_namendata_typeconstraints) - There are many different data types (not really standardized) (INTEGER, REAL, CHAR(length), …) - There are several constraintspossible e.g., NOT NULL, PRIMARY KEY, and much more CREATE TABLE States ( State_id INTEGER PRIMARY KEY NOT NULL, State CHAR(20))

  12. Don’t assume SQL is as simple as what you just saw !!!!!!!!!!!!! • Question: given relation schemas: Parts (pid, name, color), Suppliers (sid, name, address), Catalog (sid, pid), write an SQL query finding the suppliers (sids) who supply every part. • Analysis: It’s hard to directly address this question because of the implication of “every” is not directly expressible in SQL. • We can restate the query: find those suppliers such that there exist no parts that are not supplied by these suppliers. This more easily leads to a feasible solution.

  13. Solution to the “Every” Query SELECT C.sid FROM Catalog C WHERE NOT EXISTS (SELECT P.pid FROM Parts P WHERE NOT EXISTS (SELECT C1.sid FROM Catalog C1 WHERE C1.sid = C.sid AND C1.pid = P.pid));

  14. Architectures for Database Access • Two-Tier Client-Server Architectures • - Client tasks: • - Provide a way for users to submit queries • - Run applications that use the results of queries • - Display results of queries • - Server tasks: • - Implement the core functions of a DBMS: store, query, update, … • - A two-tier system that clients are connected directly to the server • - Problems with a two-tier system: • Because the relative power of clients has grown considerably, we could shift processing to the client, but keeping all clients current with application updated is difficult  fat client undesired!!

  15. Architectures for Database Access (continued) - A solution to the problems of two-tier systems is to add a component in the middle - create a three-tier system - For Web-based database access, the middle tier runs applications (and client just gets results) DB Client Middle tier Server Browser Web Server Database & Apps System

  16. Architectures for Database Access (continued) - Microsoft Access Architecture - Need a tool to access anycommon database structure - Through the Open Database Connectivity (ODBC) standard (actually an API) - ODBC is an API for a set of objects and methods that form a standard interface to different databases - Database vendors provide ODBC drivers for their products – a driver that bridgesthe API and the internal of the DBMS - An application can include SQL statements that work for any database for which a driver is available

  17. Architectures for Database Access (continued) • - The Java JDBC Architecture • - JDBC is a standard protocol that can be implemented as a driver for • anydatabase system • - JDBC allows SQL to be included in Java applications, applets, and • servlets • - JDBC has the advantage of portability over the embedded SQL • method (the traditional way to access DBs, more efficient, still used!) • - A JDBC application works with any database system for which there • must be a JDBC driver

  18. The MySQLDatabase System - A free, efficient, widely usedSQL implementation - Available from http://www.mysql.org - Logging on to MySQL (starting it): mysql [-h host] [-u username] [database name] [-p] - Host is the name of the MySQL server, default to the user’s machine - Username is that of the db, default to the name logged into the system - The given database name becomes the “focus” of MySQL - If you want to access an existing db, which was not named in the mysql command, then you must explicitly gain focuson it in the following way: use cars; Response is: Database changed

  19. The MySQL Database System (continued) - If the focus has not been set on MySQL, you get: ERROR 1046: No Database Selected - To create a new database, CREATE DATABASE cars; Response: Query ok, 1 row affected (0.05 sec) - Example: CREATE TABLE Equipment (Equip_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, Equip INT UNSIGNED ); - To see (all) the tables of a database: SHOW TABLES; - To see the description of a table (columns): DESCRIBE Corvettes;

  20. Database Access with PHP/MySQL - When values from a DB need be put into XHTML, be careful with XHTML special characters, &, <, and > - htmlspecialchars($str)replaces them with corresponding HTML entities, &amp, &lt, and &gt, respectively - Reversely, special characters (″, ", \, and NULL), coming from Web (via $_GETand$_POST) may interfere with server side processing - For example, the quotesmay prematurely terminate a query string. - To fix this, magic_quotes_gpc in the PHP.ini file is set to ON (by default) which automatically escape (backslash) these special characters - However, when later applied to DB, these slashes must be removed: $query = stripslashes($query);

  21. Database Access with PHP/MySQL (continued) • - To connect from PHP to a MySQL database, use mysql_pconnect, which can have three (optional) parameters: • 1. host (default is localhost) • 2. Username (default is the username of the PHP script) • 3. Password (default is blank, if the DB does not require password) • $db = mysql_pconnect(); • - To sever the connection to the database: • mysql_close; • How to connect to MySQL and Oracle DB servers in our department: http://www2.cs.siu.edu/~dche/courses/CS350/lab_setting/database-setting.pdf

  22. Database Access with PHP/MySQL (continued) • - To focus MySQL, • mysql_select_db("cars"); • - Requesting MySQL Operations • - Call mysql_query with a string parameter, which is a SQL command • $query = "SELECT * from States"; • $result = mysql_query($query); • - Dealing with the result: • 1. Get the number of rows in the result • $num_rows = mysql_num_rows($result); • // mysql_num_fieldsgets the # of fields • 2. Get the result rows with mysql_fetch_array • for ($row_num = 0; $row_num < $num_rows; $row_num++) { • $row = mysql_fetch_array($result); • print "<p> Result row number" . ($row_num + 1) . " State_id: "; • print htmlspecialchars($row["State_id"]); • print "State: "; • ...

  23. Database Access with PHP/MySQL (continued) - We have had it easy – the column titles were known, e.g., State_id - If they are not known, we must get them - The result rows are in PHP arrays with double sets of elements - one uses a numeric key and one uses a string key - E.g., if the result has the field values (1, Alabama), the array has: ((0, 1), (State_id, 1), (1, Alabama), (State, Alabama)) K[0] V[0] k[1] v[1] k[2] v[2] k[3] v[3] - If the row is indexed with numbers, the element values are returned - To display all field values (marked as bold blue above) from $row $values = array_values($row); for ($index = 0; $index < $num_fields; $index++) print "$values[2 * $index + 1] <br />";

  24. Database Access with PHP/MySQL (continued) - To display all column names (marked as blue on last slide) : $keys = array_keys($row); for ($index = 0; $index < $num_fields; $index++) print "$keys[2 * $index + 1] <br />";  SHOW carsdata.html  SHOW access_cars.php

  25. Database Access with PHP/MySQL (continued) - It would be neat to combinethe two documents - Modifications needed: 1. Change the value of the action attribute of the form 2. Create a hidden input element to differentiate whether it’s the initial request <input type = "hidden" name = "stage“ value = "1" /> The PHP code to test this has the form: $stage = $_POST["stage"]; if (!IsSet($stage))) { … } The then clause includes the form processing while the else clause includes the form display  SHOW access_cars2.php Check this yourself: Using PHP with Oracle Database 11g

  26. Database Access with JDBC/MySQL - Approaches to using JDBC outside the Web - JDBC is a Java API for database access - The API is defined in java.sql package (part of Java distribution) - Can use a two-tier configuration - But recommend three-tier configuration (more popular now!) - JDBC and MySQL - Connecting the application to the driver - use the getConnection method of DriverManager class which selects the correct driver from those registered drivers

  27. Database Access with JDBC/MySQL (continued) - The general form of a reference to a database is: jdbc:subprotocol_name:more_info - The subprotocol specifies the driver (often product/vender name) - For the MySQL, it is mysql - The “more info” part depends on the specific database being used - For MySQL and the cars database, the reference is: jdbc:mysql://localhost/cars?user=root - Two ways to register a database driver: 1. The general way is to have the system property jdbc.drivers maintain a list of registered drivers (accessible to all users/applications) - now add one entry for mysql with jdbc.drivers = org.gjt.mm.mysql.Driver;

  28. Database Access with JDBC/MySQL (continued) 2. Manual registration using the forName method of the Class class obj, Class.forName("org.gjt.mm.mysql.Driver").NewInstance(); // this will only allows access from theprogram - The actual connection is then made by creating a Connection object DriverManager.getConnection(database_address, database_user_id, password) - If the application owner owns the db, user id & password can be omitted: myCon = DriverManager.getConnection( "jdbc:mysql://localhost/cars?user=root"); - SQL commands through JDBC for accessing the DB - First, you need a SQLStatement object Statement myStmt = myCon.createStatement();

  29. Database Access with JDBC/MySQL (continued) • - SQL commands are String objects • final String sql_com = "UPDATE Corvettes " + • "Year = 1991 WHERE Vette_id = 7"; • - Categories of SQL commands • - Action - INSERT, UPDATE, DELETE, CREATE TABLE, and DROP TABLE • - Query - SELECT • The actions are executed with the executeUpdate method of Statement: • myStmt.executeUpdate(sql_com); • - which returns the number of affected rows • A purequery is executed by sending it as the actual parameter to the • executeQuery method of Statement • - The executeQuery method returns an object of class ResultSet • - Get rows from ResultSet with the next iterator

  30. Database Access with JDBC/MySQL (continued) ResultSet result; final String sql_com = "SELECT * FROM Corvettes WHERE Year <= 1990" result = myStmt.executeQuery(sql_com); while(result.next()) { // access and process the current element } - Query result is extracted from the ResultSet object with an access method defined with each data type For example, If an extracted row is as follows 3, "convertible", 13.5, 2001, 1 String style; style = result.getString("Body_style"); or style = result.getString(2);// for the 2nd column

  31. Database Access with JDBC/MySQL (continued) - Metadata – data about data, in the context of DBs, it is typically tables and their column names from a database - Two kinds: 1. Metadata that describes the database 2. Metadata that describes a ResultSetobject - A Connection method, getMetaData, creates an object of class DatabaseMetaData DatabaseMetaData dbmd = myCon.getMetaData();

  32. Database Access with JDBC/MySQL (continued) • The getTables method of DatabaseMetaData takes four parameters, • only one of which is necessary • String tbl[] = {"TABLE"}; • DatabaseMetaData dbmd = myCon.getMetaData(); • result = dbmd.getTables(null, null, null, tbl); • System.out.println("The tables in the database are: \n\n"); • while (result.next()) { • System.out.println(result.getString(3)); //or use “TABLE_NAME” • } • - Output from this: • The tables in this database are: • CORVETTES • CORVETTES_EQUIPMENT • EQUIPMENT • STATES • Metadata about query results has a different structure: • ResultSetMetaData resultMd = result.getMetaData();

  33. Database Access with JDBC/MySQL (continued) - We can get the number of columns, their names, types, and sizes from the resultMd object, using its methods - getColumnCount returns the number of columns - getColumnLable(i)returns the ith column’s name // Create an object for the metadata ResultSetMetaData resultMd = result.getMetaData(); // Loop to fetch and display the column names for (int i = 1; i <= resultMd.getColumnCount(); i++) { String columnName = resultMd.getColumnLabel(i); System.out.print(columnName + "\t"); } System.out.println("\n"); Output would be: Vette_id Body_style Miles Year State

  34. Database Access with JDBC/MySQL (continued)  SHOW JDBCServlet.java

  35. Schedule of the rest of the semester • Last Lab (#8): redo Lab 7 with Oracle instead of files • Monday (4/30): Lab session on Oracle/JDBC/Servlet • Make sure you read and get familiar with this online Oracle handout beforehand: http://www.cs.siu.edu/~dche/courses/CS350/lectures/OracleSQLfor350.doc • Wednesday (5/5): Course wrap-up • Summary of the course • Q&A session • Whatever issues that may emerge • Course survey (for future adaptation and improvement) • Workload • Teaching methods • Course organization, etc. • Course evaluation • Friday (5/4): Exam 2

  36. Database Access with JDBC/Oracle • With Oracle, the overall procedure is similar (different in specifics) • An example tailored to work with our Oracle server configuration: • my-jdbc-oracle-example.txt • Read this to exceed beyond this course:Oracle’s Java Servlet Tutorial • Lab 8 (the second/last bonus earning project): Redo Lab 5 using PHP&Oracle or Servlet&Oracle. Due April 30 (firm, don’t request for extension – it’s nearly semester end!)

More Related