1 / 40

Programming for WWW (ICE 1338)

Programming for WWW (ICE 1338). Lecture #11 July 30, 2004 In-Young Ko iko .AT. i cu . ac.kr Information and Communications University (ICU). Announcements. Your midterm score can be checked from the class homepage Average: 81.0 Homework #3 is due by August 4 th.

vivek
Download Presentation

Programming for WWW (ICE 1338)

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. Programming for WWW(ICE 1338) Lecture #11July 30, 2004In-Young Koiko .AT. icu.ac.krInformation and Communications University (ICU)

  2. Announcements • Your midterm score can be checked from the class homepage • Average: 81.0 • Homework #3 is due by August 4th Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  3. Review of the Previous Lecture • XML-Based Languages • SVG (Scalable Vector Graphics) • Web Servers • Servlets (JSP) Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  4. Contents of Today’s Lecture • XML Addressing and Linking • Database Access on the Web • Relational Database • SQL • Database Access Architectures • MySQL • Perl/MySQL • PHP/MySQL • JDBC/MySQL Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  5. XLink (XML Linking Language) http://www.w3.org/XML/Linking • A generalization of the HTML link concept • Simple Links <students xlink:href="students.xml"> The list of students.</students> • Extended Links <element xmlns:xlink="http://www.w3.org/1999/xlink/namespace/" xlink:type="extended"> <locator href="Source" role="f"/> <locator href="Target" role="t"/> ... </element> • External Link Sets <annot xmlns:xlink="http://www.w3.org/1999/xlink/namespace/" role="xlink:external-linkset"> <title>DV's Annotations</title> <locator href="http://rpmfind.net/veillard/linkset.xml"/> </annot> http://daniel.veillard.com/Talks/2000-Linking/slide12-0.html Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  6. XPath http://www.w3.org/TR/xpath http://www.w3schools.com/xpath/xpath_examples.asp • A language to address parts of an XML document • Selecting elements in an absolute path • /catalog • /catalog/cd/price • /catalog/cd[price>10.80] • Selecting elements in different levels • //cd • Selecting elements by matching patterns • /catalog/cd/* • /catalog/*/price • /*/*/price • Selecting branches • /catalog/cd[1] • /catalog/cd[last()] • Selecting attributes • //cd[@country='UK'] <?xml version="1.0"?> <catalog> <cd> <title>Empire Burlesque</title> <artist>Bob Dylan</artist> <country>USA</country> <company>Columbia</company> <price>10.90</price> <year>1985</year> </cd> <cd> <title>Hide your heart</title> <artist>Bonnie Tyler</artist> <country>UK</country> <company>CBS Records</company> <price>9.90</price> <year>1988</year> </cd> … </catalog> Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  7. XPointer http://www.w3.org/XML/Linking • Defines the fragment identifier syntax for XML resources • Is based on XPath (extension of XPath) • Returns a set of nodes, points or ranges within the document e.g., #xpointer(id("foo")) xpointer(/chapter[3]/elem[@name="foo"]) http://daniel.veillard.com/Talks/2000-Linking/slide12-0.html Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  8. Relational Databases • A database is a collection of data organized to allowrelatively easy access for retrievals, additions, and deletions • A relational database is a collection of tables of data, each of which has one special column thatstores the primary keys of the table • Rows are sometimes called entities http://coronet.iicm.edu/Dbase1/reldb_p.htm AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  9. Relational DB Example • Designing a relational database for used Corvettes that are for sale • The table could have information about various equipment the cars could have • Use a separate table for state names, with only references in the main table • Logical Model: AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  10. Relational DB Example (cont.) The Corvettes table The States table The Corvettes-Equipmentcross-reference table The Equipment table Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  11. SQL (Structured Query Language) • A standard language to create, query, and modify databases • Supported by all major database vendors • More like structured English than a programming language • We cover only six basic commands: CREATETABLE, SELECT, INSERT, UPDATE, DELETE, and DROP • SQL reserved words are case insensitive AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  12. Table Creation • The CREATE TABLE command: CREATE TABLE table_name ( column_name1 data_type constraints, … column_namen data_type constraints) • There are many different data types(INTEGER, FLOAT, CHAR(length), …) • There are several constraints possible, e.g., NOT NULL, PRIMARY KEY e.g.,CREATE TABLE States ( State_id INTEGER PRIMARY KEY NOT NULL, State CHAR(20)) AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  13. SELECT & INSERT • The SELECT Command – Usedto specify queries e.g., SELECT Body_style FROM Corvettes WHERE Year > 1994 • The INSERT Command: INSERT INTO table_name (col_name1, … col_namen) VALUES (value1, …, valuen) • The correspondence between column names andvalues is positional e.g.,INSERT INTO Corvettes(Vette_id, Body_style, Miles, Year, State) VALUES (37, 'convertible', 25.5, 1986, 17) AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  14. UPDATE & DELETE • The UPDATE Command – Tochange one or more values of a row in a table UPDATE table_name SET col_name1 = value1,…, col_namen = valuen WHERE col_name = value • The WHERE clause is the primary key of the row to be updated e.g., UPDATE CorvettesSET Year = 1996 WHERE Vette_id = 17 • The DELETE Command • e.g., DELETE FROM Corvettes WHERE Vette_id = 27 • The WHERE clause could specify more than one row of the table AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  15. Delete Tables and Databases • The DROP Command – Todelete whose databases or complete tables • DROP (TABLE | DATABASE) [IF EXISTS] name • e.g., DROP TABLE IF EXISTS States AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  16. Joins • A Join is an operation to build a temporary table by combining columns from different tables • e.g., Retrieve all cars that have CD players SELECT Corvettes.Vette_id, Corvettes.Body_style, Corvettes.Miles, Corvettes.Year, Corvettes.State FROM Corvettes, Equipment WHERE Corvettes.Vette_id =Corvettes_Equipment.Vette_id AND Corvettes_Equipment.Equip = Equipment.Equip_id AND Equipment.Equip = 'CD' AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  17. Client DB Server Architectures for Database Access • Client-Server (two-tier) 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 a data manipulation language, whichcan directly access and update the database • Because the relative power of clients has grownconsiderably, we could shift processing to theclient, but then maintaining data integrity isdifficult AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  18. DB Server Client Middle Tier e.g., Web Browser e.g., Web Server & Server Applications Architectures for Database Access (Cont.) • A Three-tier System • For Web-based database access, the middle tier can run applications • The middle tier provides Web-based access to a database • Client just gets results AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  19. Embedded SQL • SQL commands are embedded in programs written in a host programming language, whose compiler is extended to accept some form of SQL commands • e.g., int a; EXEC SQL SELECT gpa INTO :a FROM Student WHERE SID=2001234; printf("The GPA is %d\n", a); • Advantage: One package has computational support of the programming language, as well as database access with SQL • Disadvantage:Portability among database systems AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  20. Database Access Mechanisms • Microsoft Access Architecture • A tool to access any common database structure • Provides an API for a set of objects andmethods that are an interface to different databases • The Perl DBI/DBD Architecture • Database Interface (DBI) provides methods & attributes for generic SQL commands • Database Driver (DBD) is an interface to a specific database system (MySQL, Oracle, etc.) • Convenient for Web access to databases, because the Perl program can be run as CGI on the Web server system AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  21. Database Access Mechanisms • PHP & Database Access • An API for each specific database system • Also convenient for Web access to databases, because PHP is run on the Web server • The Java JDBC Architecture • JDBC is a standard protocol that can be implemented as a driver for any database system • JDBC allows SQL to be embedded in Java applications, applets, and servlets • JDBC has the advantage of portability over embedded SQL • A JDBC application will work with any databasesystem for which there is a JDBC driver AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  22. The MySQL Database System • A free, efficient, widely used SQL implementation • Available from http://www.mysql.org • Logging on to MySQL (starting it): mysql [-h host] [-u username] [database name] [-p] • The given database name becomes thefocus of MySQL • Database focus can be changed by the use command: • e.g., use cars; AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  23. MySQL Commands • To create a new database e.g., CREATE DATABASE cars; • To create a database table e.g., CREATE TABLE Equipment (Equip_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, Equip INT UNSIGNED); • To see the tables of a database: SHOW TABLES; • To see the description of a table (columns): e.g., DESCRIBE Corvettes; AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  24. Database Access with Perl/MySQL • Needed: • DBI – a standard object-oriented module • DBD – for the specific database system • DBI Module • Interface is similar to Perl’s interface to external files – through a filehandle • To provide access to DBI and create a DBI object:use DBI; • Access to the object is through the reference variable, DBI AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  25. Perl/MySQL(cont.) • To connect to the database: $dbh = DBI->connect( "DBI:driver_name:db_name [, username] [, password]); e.g.,$dbh = DBI->connect("DBI:mysql:cars"); • The connect method is usually used with die e.g., $dbh = DBI->connect("DBI:mysql:cars")or die("Could not connect!"); • A Perl program can have connections to anynumber of databases AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  26. Perl/MySQL(cont.) • To create a query, we usually compile the SQL command first, then use it against the database e.g., $sth = $dbh->prepare( "SELECT Vette_id, Body_style, Year, States.State FROM Corvettes, States WHERE Corvettes.State = States.State_id AND States.State = 'California'"); • To execute a compiled query, use execute, as in: e.g., $sth->execute() or die "Error –query: $dbh->errstr\n"; AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  27. Perl/MySQL Example access_cars.pl #!/usr/bin/perl -w use DBI; use CGI ":standard"; print header(); print start_html("CGI-Perl MySQL databaseaccess"); my $dbh = DBI->connect("DBI:mysql:cars", "root", ""); my $query = param("query"); print "<p> <b> The query is: </b>", $query, "</p>"; my $sth = $dbh->prepare($query); $sth->execute or die "Error - unable to execute query: $dbh->errstr\n"; Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  28. Perl/MySQL(cont.) • The $sth object keeps the result of a query • To display the results, we would like column names, which are stored in a hash • $col_names = $sth->{NAME}; • Rows of the result are available with the fetchrow_array method, which returns a reference to an array that has the next row of the result • Returns false if there are no more rows AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  29. Perl/MySQL(cont.) • Putting query results in an HTML documentcan cause trouble (>, <, “, and &) • Avoid the problem by using the CGI function, escapeHTML AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  30. Perl/MySQL Example access_cars.pl print "<table> <caption> <h2> Query Results </h2> </caption>", "<tr align = 'center'>"; my $col_names = $sth->{NAME}; foreach $field_name (@$col_names) { print "<th> $field_name </th>"; } print "</tr>"; while (@result_rows = $sth->fetchrow_array) { print "<tr align = 'center'>"; while ($#result_rows >= 0) { $field = shift @result_rows; $field = escapeHTML($field); print "<td> $field </td>"; } print "</tr>"; } print "</table>"; $sth->finish; $dbh->disconnect; print end_html(); Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  31. PHP/MySQL Example access_cars.php <html> <head><title> Access the cars database with MySQL </title></head> <body> <?php $db = mysql_connect("localhost", "root", ""); // Connect to MySQL if (!$db) { print "Error - Could not connect to MySQL"; exit; } $er = mysql_select_db("cars"); // Select the cars database if (!$er) { print "Error - Could not select the cars database"; exit; } trim($query); print "<p> <b> The query is: </b> " . $query . "</p>"; $result = mysql_query($query); // Execute the query if (!$result) { print "Error - the query could not be executed"; $error = mysql_error(); print "<p>" . $error . "</p>"; exit; } Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  32. PHP/MySQL Example access_cars.php print "<table><caption> <h2> Query Results </h2> </caption>"; print "<tr align = 'center'>"; $num_rows = mysql_num_rows($result); $row = mysql_fetch_array($result); $num_fields = sizeof($row); while ($next_element = each($row)) { // Produce the column labels $next_element = each($row); $next_key = $next_element['key']; print "<th>" . $next_key . "</th>"; } print "</tr>"; for ($row_num = 0; $row_num < $num_rows; $row_num++) { reset($row); print "<tr align = 'center'>"; for ($field_num = 0; $field_num < $num_fields / 2; $field_num++) print "<th>" . $row[$field_num] . "</th> "; print "</tr>"; $row = mysql_fetch_array($result); } print "</table>"; ?> </body></html> Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  33. Database Access withJDBC/MySQL • Approaches to using JDBC outside the Web • JDBC is a Java API for database access • The API is defined in thejava.sqlpackage • Can use a two-tier configuration • Disadvantage: Every client must have a driver for every database vendor • Can also use a three-tier configuration • The application runs on the client side, the middle machine runs JDBC, and the third system runs the database system AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  34. JDBC/MySQL • Connecting the application to the driver • The getConnection method ofDriverManager, which select the correct driver from those thatare registered • The general form of a reference to a database for the connection operation is: jdbc:subprotocol_name:more_info • The “subprotocol” specifies the driver (e.g., odbc, mysql) • The “more info” part depends on the specific database being used e.g., For MySQL and the cars database, myCon = DriverManager.getConnection( "jdbc:mysql://localhost/cars?user=root"); AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  35. JDBC/MySQL • SQL commands through JDBC Statement myStmt = myCon.createStatement(); • SQL commands are String objects e.g., final String sql_com = "UPDATE Corvettes " + "Year = 1991 WHERE Vette_id = 7"; • The action commands are executed with the executeUpdate method of Statement e.g., myStmt.executeUpdate(sql_com); Returns the number of affected rows AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  36. JDBC/MySQL • A SELECT is executed by sending it as the actualparameter to the executeQuery method of Statement • The executeQuery method returns an object of class ResultSet • Get rows from ResultSet with next iterator e.g., ResultSet result; final String sql_com = "SELECT * FROM Corvettes WHERE Year <= 1990" result = myStmt.executeQuery(sql_com); while(result.next()) { String aCol = result.getString(2); String style = result.getString("Body_style"); } AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  37. JDBC/MySQL Metadata • Metadata - to get table and column names from adatabase • Two kinds of metadata: • Metadata that describes the database DatabaseMetaData dbmd =myCon.getMetaData(); • Metadata that describes a ResultSet object ResultSetMetaData resultMd = result.getMetaData(); AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  38. JDBC/MySQL Database Metadata 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)); } • Output from this: The tables in this database are: CORVETTES CORVETTES_EQUIPMENT EQUIPMENT STATES AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  39. JDBC/MySQL Result Metadata // 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: Vette_id Body_style Miles Year State AW lecture notes Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

  40. Web-based DB References • Introduction to Relational Database Design: http://www.edm2.com/0612/msql7.html • XML representation of a relational database: http://www.w3.org/XML/RDB.html Programming for WWW (Lecture#11) In-Young Ko, Information Communications University

More Related