Connecting Databases to the web

Download Report

Transcript Connecting Databases to the web 

Connecting Databases to the Web
Web Programming Technologies
1
Outline
•
•
•
•
•
•
•
•
Common Gateway Interface (CGI)
Java Applets
Server Extensions
PHP
Active Server Pages/ Java Server Pages
What else is out there?
Architectures
Extended PHP example
2
First Generation Architecture
3
Second Generation Architecture
4
Next Generation Architecture
5
Common Gateway Interface (CGI)
• Some files on server are interpreted as programs
depending on either ext., flag or special directory
• Program is invoked and generates MIME header
and HTML on stdout
Web-Server
HTTP-Request
Web-Server
HTML
HTML-File
Output
HTML?
File-System
Load File
File
Program?
Execute Program
I/O, Network, DB
6
CGI: Discussion
Advantages:
- Standardized: works for every web-server, browser
- Flexible: Any language (C++, Perl, Java, …) can be used
Disadvantages:
- Statelessness: query-by-query approach
- Inefficient: new process forked for every request
- Security: CGI programmer is responsible for security
- Updates: To update layout, one has to be a programmer
7
Java Applets
Web-Server
HTTP-Request
Web-Server
Load File
File-System
HTML-File
Load Applet...
Java-Class Requests
File
Java-Classes
ServerProcess
Execute Applet...
Java Virtual
Machine (JVM)
8
Java Applets: Discussion
Advantages:
- Platform independent: works for every web-server and
browser supporting Java
Disadvantages:
- Standalone Character:
· Entire session runs inside applet
· HTML forms are not used
- Inefficient: loading can take a long time ...
- Resource intensive: Client needs to be state of the art
- Restrictive: can only connect to server where applet was
loaded from (Java VM but … can be configured)
Note: Server-Process can be written in any language
9
DB Access in Java
Java Applet
TCP/UDP
IP
Java-Server-Process
JDBC Driver manager
JDBCDriver
JDBCDriver
JDBCDriver
Sybase
Oracle
...
10
Server Extensions
Previous Approaches
- Platform independent and standardized
- Simple interface
- Lots of programming necessary
- Inefficient
Server Extensions
- Server is extended with handler/module
- One handler for all incoming requests
- Much more efficient
11
Server Extensions: The Basic Idea
Web-Server
Web-Server
HTTP-Request
Load File
HTML
HTML?
HTML-File
Output
File-System
File
Script?
Server Extension
I/O, Network, DB
12
Server Extensions
• API depends on Server vendor:
- Apache Foundation Apache Server: Apache API
- Microsoft Internet Information Server: ISAPI
- Netscape Enterprise Server: NSAPI
• One can define it’s own server extension,
e.g.
- Authentication module
- Counter module
13
Active Server Pages
• Active Server Pages (ASPs)
-
Available in Personal Web Server
Based on VBScript, Jscript
Modular Object Model
Active Server Components
Active Data Objects
HTTP-Request
(ADO) for Database
access
HTML-File
- In MS .NET ASP+, ADO+ …
Web-Server
Load File
File-System
HTML
ASP-File
Output
ASP-Script
Active Server Page
Scripting Engine
I/O, Network, DB
Active Server
Components
14
ColdFusion
Web-Server
Web-Server
HTTP-Request
HTML
Load File File-System
HTML?
HTML-File
File
HTML
CF Script?
Cold Fusion Server
Extension
Cold Fusion Application
Server
ODBC-Driver
Native
Email
Directories
DB
DB
COM/CORBA
15
ColdFusion: Simple Query
• Proprietary Scripting Language CFML - similar to
other scripting languages
<CFQUERY NAME=“PersonList”
DATASOURCE=“PersonDB”>
SELECT * FROM Persons
</CFQUERY>
<HTML>
<BODY>
<H1> Person List </H1>
<CFOUTPUT QUERY=“PersonList”>
<B>Name:</B> #Name#
<B>Age:</B> #Age#
<B>Salary:</B> $#Sal# <BR>
</CFOUTPUT>
</BODY>
</HTML>
<HTML>
<BODY>
<H1> Person List </H1>
<B>Name:</B> Tom
<B>Age:</B> 45
<B>Salary:</B> $45000 <BR>
<B>Name:</B> Jim
<B>Age:</B> 38
<B>Salary:</B> $40000 <BR>
<B>Name:</B> Karen
<B>Age:</B> 26
<B>Salary:</B> $32000 <BR>
</BODY>
</HTML>
16
ColdFusion: Form Handling
<CFQUERY NAME=“PersonInfo”
DATASOURCE=“PersonDB”>
SELECT * FROM Persons
WHERE Name=#Form.PName#
</CFQUERY>
<HTML>
<BODY>
<CFOUTPUT QUERY=“PersonInfo”>
<HTML>
<H1> #Name# </H1>
<BODY>
<UL>
<FORM
<LI><B>Age=</B> #Age#
ACTION="http://www.abc.com/cf/pf.cfm">
<LI><B>Salary=</B> $#Sal#
<H1> Find Person </H1>
<LI><A href=“#URL#”><B>Homepage</B>
Person Name <INPUT NAME="PNAME">
</A>
<p>
</UL>
<INPUT TYPE="submit" VALUE="Find">
</CFOUTPUT>
</FORM>
</BODY>
</BODY>
</HTML>
</HTML>
<HTML>
<BODY>
<H1> Tom </H1>
<UL>
<LI><B>Age:</B> 45
<LI><B>Salary:</B> $45000
<LI><A HREF=“www.tom.com”
<B>Homepage</B></A>
</UL>
</BODY>
</HTML>
17
ColdFusion: Misc. Issues
• Site admin sets up data sources very similar to
the handling of ODBC data sources in MS
Windows
• In fact ColdFusion combines techniques to
access databases:
- Generation of HTML code
- Java Applets embedded via <CFGRID></CFGRID>
access the database through the application server
• Application server is also gateway to database
for the ColdFusion IDE (ColdFusion Studio)
18
PHP
Web-Server
Web-Server
HTTP-Request
Load File
File-System
HTML
HTML-File
PHP-File
Output
PHP-Script
PHP
Module
Database APIs,
other APIs SNMP,
IMAP, POP3,
LDAP, ...
How does PHP differ
from ASP and CF?
• Free, open source
• Many client libraries
integrated
• Runs on any web
server supporting
CGIs (MS Windows
or Unix)
• Module version for
Apache
19
PHP: A Simple Example
<HTML>
<BODY>
<?PHP
$db = mysql_connect("localhost", "dbuser");
mysql_select_db("mydb",$db);
$result = mysql_query("SELECT * FROM employees",$db);
?>
<TABLE BORDER=1>
<TR><TD>NAME</TD><TD>POSITION</TR>
<?PHP
while ($myrow = mysql_fetch_row($result)) {
printf("<tr><td>%s %s</td><td>%s</td></tr>\n",
$myrow[1], $myrow[2], $myrow[3]); }
?>
</TABLE>
</BODY>
</HTML>
20
PHP: Misc Issues
• Syntax Perl/C like
• Form fields are available as variables in
following page
• has e.g. image and PDF generation on the fly
• some OO features (e.g. classes)
• The number of functions is steadily increasing
21
Java Server Pages
22
JSP Example—Hello.jsp
<html>
<head>
<title>My first JSP page
</title>
</head>
<body>
<%@ page language=”java” %>
<% System.out.println(“Hello World”); %>
</body>
</html>
23
And the Output IS
<html>
<head>
<title>My first JSP page
</title>
</head>
<body>
Hello World
</body>
</html>
24
What Else Is Out There?
• Java Server Pages (JSP)
- similar to PHP
• Java Servlets
- very similar to CGIs
• A couple of solutions from Oracle
- PENN ExpressApp is based on OWS
• various web shop applications
- all of them use a more or less sophisticated scripting
language
• and a lot more ...
25
Databases Usually Used
• ASP
- MS Jet Engine (DB engine behind MS Access)
- MS SQL Server
- Oracle (ODBC)
• ColdFusion
- Oracle (native driver support)
- Informix (native driver support)
- Sybase (native driver support)
• PHP
-
MySQL (linked in client library)
mSQL (linked in client library)
Postgres (linked in client library)
Oracle (linked in client library)
26
Architectures
• The architecture type depends on kind and
number of servers involved
• Different archictures different advantages and
disadvantages
• Generally we can distinguish between
different types:
- 2-tier architecture
- 3-tier architecture
- n-tier architecture
• What matters: SPEED
27
2-tier Architecture
• Web server plus module connecting to
database, LDAP, IMAP, ...
HTTP-Request
1
Web-Server
HTML-File
Module
2
DB
Directory
Mail
Server
SNMP
28
2-tier Architecture
• Advantages:
- easy and fast to setup
- easy to administrate
• Disadvantages:
- not fail safe (single point of failure)
- scales badly on high loads
29
3-tier Architecture
• Web server plus application server connecting to
database, IMAP, ...
Web
Server
[Cluster]
Application
Server
[Cluster]
Other Servers
[Cluster]
DB
DB
Repl.
DB
Mail
Server
1
2
3
SNMP
30
3-tier Architecture
• Advantages:
- better scalabilty
- more reliable through failover mechanisms
- offers better load balancing
• Disadvantages:
- complicated to set up an maintain
31
Architectures: Usage
• 2-tier
- Apache-PHP plus Database etc.
• 3-tier
- ColdFusion 4.x, BEA Weblogic
- Oracle Web Application Server?
• n-tier
- big sites with custom systems
like Yahoo, Amazon.com, eBay
Classification not always 100%
32
Technology Choices
• PHP/{Oracle|mySQL}
• ASP/Access/ODBC -- Need MS IIS
• JSP/{Oracle|mySQL}/JDBC -- Need Tomcat or
Apache+JServ
• Any Other Choices?
33
Extended PHP Example
34
Extended PHP Example
35
Plain HTML
<HTML>
<TITLE>Simple SQL Web Interface for Movie
Table</TITLE>
<BODY>
<H1>Simple SQL Web Interface for Movie Table</H1>
[PHP code here]
<FORM ACTION="query.php3" METHOD=GET
ENCTYPE="TEXT/PLAIN">
<INPUT SIZE=100 MAXLENGTH=250 NAME="query"
VALUE="select title, year, mid from movies">
</FORM>
</BODY>
</HTML>
36
Table Generation (Part 1)
<?php
/* check for query, if empty describe movies */
if (!isset($query)) {
$query = "describe movies";
} else {
$query = stripslashes($query);
print "<B>Your query: </B>\"" . $query . "\"\n";
}
$result = mysql_query($query, $conID)
or die ("Invalid query");
// disable error reporting
$eLevel = error_reporting(0);
?>
37
Table Generation (Part 2)
<?php
/* generate table header or catch INSERT, DELETE and UPDATE statements */
if (mysql_affected_rows($conID) > 0)
print "<B>affected " . mysql_affected_rows($conID) . " row(s)!</B><P>\n";
if (mysql_num_rows($result) > 0) {
$noFields = mysql_num_fields($result);
print "<table border=\"1\">\n<tr>\n";
for ($i = 0; $i < $noFields; $i++)
print "<th><b>" . mysql_field_name($result, $i) . "</b></th>\n";
print "</tr>\n";
while ($row = mysql_fetch_array ($result)) {
print "<tr>\n";
for ($i = 0; $i < $noFields; $i++)
print "<td>&nbsp;" . $row[$i] . "&nbsp;</td>\n";
print "</tr>\n";
}
print "</table>\n";
}
?>
38
Links
•
Software:
-
•
Apache: http://www.apache.org/
ASP: http://msdn.microsoft.com/workshop/server/default.asp
ColdFusion: http://www.allaire.com/coldfusion/
MySQL: http://www.mysql.com/
Oracle: http://www.oracle.com/
Oracle Technet: http://technet.oracle.com/
PHP: http://www.php.net/, http://www.zend.com/
Others:
-
c|net: http://www.builder.com/
DevShed: http://www.devshed.com/Server_Side/
Webmonkey: http://www.webmonkey.com/
39