Databases and the Internet
Download
Report
Transcript Databases and the Internet
Databases Illuminated
Chapter 13
Databases and the Internet
Uses for Web-based DB
Applications
• e-commerce has pushed organizations to
develop Web-based database applications
– To create world-wide markets
– To deliver information
– To provide better customer service
– To communicate with their suppliers
– To provide training for employees
– To expand the workplace
– …Many other innovative activities
Databases and the WWW
• WWW is a loosely organized information
resource
• Some websites use static linked HTML files
– can become inconsistent and outdated
• Many organizations provide dynamic access to
databases directly from the Web
– introduces new problems for designers and DBAs
– combination of communications technology, information retrieval
technology and database technology
– XML a standard for document storage, exchange, and retrieval.
Origins of The Internet
• Developed from Arpanet, communications
network created in the 1960s by DARPA, US
agency, for linking government and academic
research institutions
• Used a common protocol, TCP/IP
• US National Science Foundation took over
management of the network, then referred to as
the Internet
• Navigating and using the Internet required
considerable sophistication
World Wide Web
• Tim Berners-Lee proposed a method of
simplifying access to Internet resources in
1989
• Led to the development of the World
Wide Web
• included notions of URL, HTTP, HTML,
hypertext, graphical browsers with links
• Automated finding, downloading, and
displaying files on the Internet
Browsers and URLs
• Browser– software for searching, retrieving, presenting, and
traversing resources on the WWW
– Ex. Microsoft Internet Explorer, Mozilla Firefox,
Google Chrome, Apple Safari and others
• URL –Uniform Resource Locator
– Specific type of Uniform Resource Identifier (URI)
– String giving the location of any type of resource on
the Internet-Web pages, mailboxes, downloadable
files, etc.
– Gives protocol, server name, path name to resource
HTTP
• Communications protocol
– Standard for structure of messages
– HTTP request usually has a few lines of text
• Sample http interaction
• HTTP method field (GET or POST), URI of resource, HTTP
version
• host header, with host name and HTTP port
• user agent line, shows the type of the client (optional)
• types of files the client will accept (optional)
• HTTP is a stateless protocol
– No facility for remembering previous interactions
– Creates a problem for e-commerce, which requires a
continuous session with the user
HTML
• Data format used for presenting content
on the Internet
• A markup language because HTML
documents contain tags that provide
formatting information for the text
– Ex <HEAD> ,<BODY> , <B> ,<U> ,<I>, <H1> <UL> <LI> and their ending tags
• HTML document can contain applets,
audio files, images, video files, other
content
XML
• Extensible Markup Language - standard for
document storage, exchange, and retrieval
• Created in 1996 by the World Wide Web
Consortium (W3) XML Special Interest Group
• Users can define their own markup language,
with their own tags for data items in documents,
including databases
• Can define the structure of heterogeneous
databases
• supports translation of data between different
databases
Components of XML
Documents
• Element-the basic component
• Contains one or more XML elements
– each with a start tag showing the name of the element, some
character data, and an end tag
• Can have sub-elements-must be properly nested
• Can have attributes-names and values shown inside
the element’s start tag
• Attributes occur only once within each element, while
sub-elements can occur any number of times
• Comments can occur anywhere <!--->,
• can contain entity references-refer to external files,
common text, Unicode characters, or reserved symbols
Well-Formed XML Document
• Obey rules of XML
– Starts with XML declaration
– Root element contains all other elements
– All elements properly nested
HTML Document Showing
Customer List in a Browser
XML
Instance
Document
Showing
Customer
List
DTD and XML Schema
• Users can define their own markup language by
writing either
– A Document Type Declaration (DTD)
• A specification for a set of rules for the elements, attributes,
and entities of a document
• A document that obeys the rules of its associated DTD is
type-valid
– An XML Schema
• New, more powerful way to describe the structure of
documents
• A document that conforms to an XML schema is schemavalid
DTD Rules
• DTD is enclosed in <!DOCTYPE
name[DTDdeclaration]>
• each element is declared using a type
declaration with structure <!ELEMENT (content
type)>
• In an element declaration, the name of any subelement can be followed by one of the symbols
*, + or ?, to indicate the number of times the
sub-element occurs
• Attribute list declarations for elements are
declared outside the element
• DTD can be external or internal (embedded in
instance document)
DTD for CUSTOMERLIST
XML Schema
•
•
•
•
•
Permits more complex structure than DTD
Additional fundamental datatypes, UDTs
User-created domain vocabulary
Supports uniqueness and foreign key constraints
Schema lists elements and attributes
– Elements may be complex, which means they have subelements, or simple
– elements can occur multiple times
– Attributes or elements can be used to store data values
– Attributes used for simple values that are not repeated
• Can validate schema at several websites
• See possible XML Schema for CustomerList on next
slide
Three-tier Architecture
• Three major functions for an Internet environment:
presentation, application logic, data management
• Placement of functions depends on architecture of
system
• Three tier architectures completely separate
application logic from data management
– Client handles user interface, the presentation layer- first tier
– Application server executes application logic -the middle tier
– Database server forms the third tier
• Communications network connects each tier to the next
Advantages of 3-tier
Architecture
• Allows support for thin clients that only
handle the presentation layer
• Independence of tiers; may use different
platforms
• Easier application maintenance on the
application server
• Integrated transparent data access to
heterogeneous data sources
• Scalability
Presentation Layer
• HTML forms often used at the
presentation layer
• Scripting languages such as JavaScript,
JScript, VBScript, may be embedded in
HTML to provide some client-side
processing
• Style sheets specify how data is
presented on specific devices-CSS, XSL
Application Server
• Middle tier - responsible for executing applications
–
–
–
–
–
Determines the flow of control
Acquires input data from presentation layer
Makes data requests to database server
Accepts query results from database layer
Uses them to assemble dynamically generated HTML pages
• Server-side processing can use different technologies
such as Java Servlets, Java Server pages, etc.
• CGI, Common Gateway Interface, can be used to
connect HTML forms with application programs
• To maintain state during a session, servers may use
cookies, hidden fields in HTML forms, and URI
extensions.
– Cookies generated at the middle tier using Java’s Cookie class,
sent to the client, where they are stored in the browser cache
Data Layer
• Third layer is standard database or other
data source
• Ideally on separate server
Oracle Web Programming
• PL/SQL Web Toolkit
– Handles all three layers using PL/SQL
• PL/SQL Server Pages (PSP)
– embed PL/SQL code in HTML pages
• JDBC
– Application Programming Interface (API)
standard for relational database access from
Java
• And several other methods
PL/SQL Web Toolkit
• Allows user to access Oracle from a webpage
– Using a browser, user sends request to a Web server,
passing input values
– Server sends a message to the database server,
invoking a PL/SQL stored procedure in the Oracle
database, passing parameters as needed.
– Stored procedure invokes subprograms in the
PL/SQL Web Toolkit, generating a Web page
dynamically
– Generated page is passed to the Web server
– Web server displays the page in the client’s browser
– Illustration on next slide
PL/SQL Web Toolkit
Web Toolkit htp Package
• Enter from SQL*Plus in usual way
CREATE OR REPLACE PROCEDURE pagename IS BEGIN
• Functions generate HTML tags
HTP.HTMLOPEN; HTP.HEADOPEN; HTP.TITLE(‘ ‘); HTP.HEADCLOSE;
HTP.BODYOPEN; HTP.HEADER(n,‘ ‘ ); HTP.PARA; HTP.PRINT(' ');
HTP.BODYCLOSE; HTP.HTMLCLOSE
• Inside the body, htp.formOpen (parameter list )
creates a form
• Form elements created using procedures
htp.formText, htp.formCheckbox, htp.formRadio, htp.formTextarea, and others
PL/SQL Server Pages (PSP)
• provide the dynamic database content
• embed PL/SQL statements in HTML pages
• Write standard HTML scripts, using the delimiters <%
and %> to identify PL/SQL statements
• Can have Web Toolkit calls in the same application
• handled by the Oracle PL/SQL Web gateway
(mod_plsql)
• reside on the server side
• PSP page is loaded into Oracle database space using
utility program loadpsp
• Sample code shown on next slide
<%@ page language="PL/SQL" %>
<%@ page contentType="text/html" %>
<%@ plsql procedure="StudentList" %>
<%-- This example displays the last name and first name of every
student in the University.Student table. --%>
<%!
CURSOR stu_cursor IS
SELECT lastName, firstName
FROM University.Student
ORDER BY lastName;
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html">
<title>Student List</title>
</head>
<body TEXT="#000000" BGCOLOR="#FFFFFF">
<h1>Student List</h1>
<table width="40%" border="1">
<tr>
<th align="left">Last Name</th>
<th align="left">First Name</th>
</tr>
<% FOR stu IN stu_cursor LOOP %>
<tr>
<td> <%= stu.lastName %> </td>
<td> <%= stu.firstName %> </td>
</tr>
<% END LOOP; %>
</table>
</body>
</html>
A PSP
Script
to
Display
Student
Names
JDBC
• API-standard for relational database
access from Java
• Includes a set of Java classes and
interfaces
• Oracle and other vendors have extended
functionality
• Applications are platform independent
• Can run on a variety of servers and
DBMSs.
JDBC Application Steps
• At the start of the application, import the Java classes
import java.sql.*;
• For Oracle database add
import oracle.jdbc;
• Load the JDBC drivers. For Oracle,write
Class.forName(“oracle.jdbc.driver.OracleDriver”);
• Connect to the database using the DriverManager class GetConnection method creates a connection object, which is used for
all communication
conn = DriverManager.getConnection("jdbc:oracle:oci8:url”,”yourId”,”yourpassword”);
• Use SQL to interact with the database and Java for the logic in the
application program
• Close the connection object to disconnect from the database
conn.close();
•
Sample code on next slide
import java.sql.*;
import java.io.*;
class Test{
public static void main(String args[]) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch (ClassNotFoundException e) {
System.out.println("Cannot load OracleDriver");
}
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
try {
conn = DriverManager.getConnection("jdbc:oracle:oci8","userId","password");
stmt = conn.createStatement();
rset = stmt.executeQuery("select stuid, lastName, firstName from Student");
while (rset.next())
System.out.println(rset.getString(1)+" " + rset.getString(2)+"
"+rset.getString(3));
rset.close();
stmt.close();
conn.close();
}
catch (SQLException e) {
System.out.println("SQL error: " + e.getMessage());
}
}
}
Java
Program
Using
JDBC
The Connection Object
• Has 3 JDBC classes for communicating with database
–
Statement -for SQL statements with no parameters
–
PreparedStatement precompiled SQL statement – to be executed many times
–
CallableStatement -for executing stored procedures
• Has 3 methods to create instances of these classes
–
createStatement returns a new Statement object
–
prepareStatement takes an SQL statement, precompiles it, and stores it in a PreparedStatement
object
–
prepareCall for call to a stored procedure; has methods for handling input and output parameters
and executing the procedure;returns a CallableStatement
• Statement object is used for executing SQL statements
–
Methods executeUpdate, executeQuery. etc.
–
executeQuery method executes an SQL statement and returns a ResultSet object
–
ResultSet class has many useful methods; includes a cursor to present one row at a time
–
Within a row, columns can be retrieved using a get method