Title: Connecting Databases to the Web Web Programming Technologies
1Connecting Databases to the WebWeb Programming
Technologies
2Outline
- Common Gateway Interface (CGI)
- Java Applets
- Server Extensions
- PHP
- Active Server Pages/ Java Server Pages
- What else is out there?
- Architectures
- Extended PHP example
3First Generation Architecture
4Second Generation Architecture
5Next Generation Architecture
6Common Gateway Interface (CGI)
- Some files on server are interpreted as
programsdepending on either ext., flag or
special directory - Program is invoked and generates MIME header and
HTML on stdout
Web-Server
Web-Server
File-System
HTTP-Request
Load File
HTML
HTML?
HTML-File
File
7CGI 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
8Java Applets
Web-Server
Web-Server
HTTP-Request
Load File
File-System
HTML-File
File
Load Applet...
9Java 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
10DB Access in Java
Java Applet
TCP/UDP IP
Java-Server-Process
JDBC Driver manager
JDBC-Driver
JDBC-Driver
JDBC-Driver
Sybase
Oracle
...
11Server 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
12Server Extensions The Basic Idea
Web-Server
Web-Server
HTTP-Request
File-System
Load File
HTML
HTML?
File
HTML-File
13Server Extensions
- API depends on Server vendor
- Apache Foundation Apache Server Apache API
- Microsoft Internet Information Server ISAPI
- Netscape Enterprise Server NSAPI
- One can define its own server extension, e.g.
- Authentication module
- Counter module
14Active Server Pages
- Active Server Pages (ASPs)
- Available in Personal Web Server
- Based on VBScript, Jscript
- Modular Object Model
- Active Server Components
- Active Data Objects (ADO) for Databaseaccess
- In MS .NET ASP, ADO
15ColdFusion
Web-Server
Web-Server
File-System
HTTP-Request
Load File
HTML
HTML?
HTML-File
File
HTML
CF Script?
Cold Fusion Server Extension
16ColdFusion Simple Query
- Proprietary Scripting Language CFML - similar to
other scripting languages
ltCFQUERY NAMEPersonList DATASOURCEPersonDB
gt SELECT FROM Persons lt/CFQUERYgt ltHTMLgt ltBODYgt
ltH1gt Person List lt/H1gt ltCFOUTPUT
QUERYPersonListgt ltBgtNamelt/Bgt
Name ltBgtAgelt/Bgt Age ltBgtSalarylt/Bgt Sal
ltBRgt lt/CFOUTPUTgt lt/BODYgt lt/HTMLgt
ltHTMLgt ltBODYgt ltH1gt Person List lt/H1gt ltBgtNamelt/Bgt
Tom ltBgtAgelt/Bgt 45 ltBgtSalarylt/Bgt 45000
ltBRgt ltBgtNamelt/Bgt Jim ltBgtAgelt/Bgt
38 ltBgtSalarylt/Bgt 40000 ltBRgt ltBgtNamelt/Bgt Karen
ltBgtAgelt/Bgt 26 ltBgtSalarylt/Bgt 32000
ltBRgt lt/BODYgt lt/HTMLgt
17ColdFusion Form Handling
ltHTMLgt ltBODYgt ltH1gt Tom lt/H1gt ltULgt
ltLIgtltBgtAgelt/Bgt 45 ltLIgtltBgtSalarylt/Bgt 45000
ltLIgtltA HREFwww.tom.com
ltBgtHomepagelt/Bgtlt/Agt lt/ULgt lt/BODYgt lt/HTMLgt
ltCFQUERY NAMEPersonInfo DATASOURCEPersonDB
gt SELECT FROM Persons WHERE
NameForm.PName lt/CFQUERYgt ltHTMLgt ltBODYgt ltCFOUTP
UT QUERYPersonInfogt ltH1gt Name lt/H1gt ltULgt
ltLIgtltBgtAgelt/Bgt Age ltLIgtltBgtSalarylt/Bgt
Sal ltLIgtltA hrefURLgtltBgtHomepagelt/Bgt
lt/Agt lt/ULgt lt/CFOUTPUTgt lt/BODYgt lt/HTMLgt
ltHTMLgt ltBODYgt ltFORM ACTION"http//www.abc.co
m/cf/pf.cfm"gt ltH1gt Find Person lt/H1gt Person Name
ltINPUT NAME"PNAME"gt ltpgt ltINPUT TYPE"submit"
VALUE"Find"gt lt/FORMgt lt/BODYgt lt/HTMLgt
18ColdFusion 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 ltCFGRIDgtlt/CFGRIDgt
access the database through the application
server - Application server is also gateway to database
for the ColdFusion IDE (ColdFusion Studio)
19PHP
- 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
Web-Server
Web-Server
File-System
HTTP-Request
Load File
HTML
HTML-File
PHP-File
PHP-Script
Output
PHP Module
Database APIs, other APIs SNMP, IMAP, POP3,
LDAP, ...
20PHP A Simple Example
ltHTMLgt ltBODYgt lt?PHP db mysql_connect("loca
lhost", "dbuser") mysql_select_db("mydb",db)
result mysql_query("SELECT FROM
employees",db) ?gt ltTABLE BORDER1gt
ltTRgtltTDgtNAMElt/TDgtltTDgtPOSITIONlt/TRgt lt?PHP while
(myrow mysql_fetch_row(result))
printf("lttrgtlttdgts slt/tdgtlttdgtslt/tdgtlt/trgt\n",
myrow1, myrow2, myrow3)
?gt lt/TABLEgt lt/BODYgt lt/HTMLgt
21PHP 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
22Java Server Pages
23JSP ExampleHello.jsp
- lthtmlgt
- ltheadgt
- lttitlegtMy first JSP page
- lt/titlegt
- lt/headgt
- ltbodygt
- lt_at_ page languagejava gt
- lt System.out.println(Hello World) gt
- lt/bodygt
- lt/htmlgt
24And the Output IS
- lthtmlgt
- ltheadgt
- lttitlegtMy first JSP page
- lt/titlegt
- lt/headgt
- ltbodygt
- Hello World
- lt/bodygt
- lt/htmlgt
25What 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 ...
26Databases 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)
27Architectures
- 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
282-tier Architecture
- Web server plus module connecting to database,
LDAP, IMAP, ...
1
Web-Server
Module
2
DB
Directory
Mail Server
SNMP
292-tier Architecture
- Advantages
- easy and fast to setup
- easy to administrate
- Disadvantages
- not fail safe (single point of failure)
- scales badly on high loads
303-tier Architecture
- Web server plus application server connecting to
database, IMAP, ...
1
Web Server Cluster
2
Application Server Cluster
3
Other Servers Cluster
DBRepl.
DB
DB
Mail Server
SNMP
313-tier Architecture
- Advantages
- better scalabilty
- more reliable through failover mechanisms
- offers better load balancing
- Disadvantages
- complicated to set up an maintain
32Architectures 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 systemslike Yahoo,
Amazon.com, eBay - Classification not always 100
33Technology Choices
- PHP/OraclemySQL
- ASP/Access/ODBC -- Need MS IIS
- JSP/OraclemySQL/JDBC -- Need Tomcat or
ApacheJServ - Any Other Choices?
34Extended PHP Example
35Extended PHP Example
36Plain HTML
ltHTMLgt ltTITLEgtSimple SQL Web Interface for Movie
Tablelt/TITLEgt ltBODYgt ltH1gtSimple SQL Web Interface
for Movie Tablelt/H1gt PHP code here ltFORM
ACTION"query.php3" METHODGET
ENCTYPE"TEXT/PLAIN"gt ltINPUT SIZE100
MAXLENGTH250 NAME"query" VALUE"select
title, year, mid from movies"gt lt/FORMgt lt/BODYgt lt
/HTMLgt
37Table Generation (Part 1)
lt?php / check for query, if empty describe
movies / if (!isset(query)) query
"describe movies" else query
stripslashes(query) print "ltBgtYour query
lt/Bgt\"" . query . "\"\n" result
mysql_query(query, conID) or die ("Invalid
query") // disable error reporting eLevel
error_reporting(0) ?gt
38Table Generation (Part 2)
lt?php / generate table header or catch INSERT,
DELETE and UPDATE statements / if
(mysql_affected_rows(conID) gt 0) print
"ltBgtaffected " . mysql_affected_rows(conID) . "
row(s)!lt/BgtltPgt\n" if (mysql_num_rows(result) gt
0) noFields mysql_num_fields(result) pr
int "lttable border\"1\"gt\nlttrgt\n" for (i
0 i lt noFields i) print "ltthgtltbgt" .
mysql_field_name(result, i) .
"lt/bgtlt/thgt\n" print "lt/trgt\n" while (row
mysql_fetch_array (result)) print
"lttrgt\n" for (i 0 i lt noFields i)
print "lttdgtnbsp" . rowi .
"nbsplt/tdgt\n" print "lt/trgt\n"
print "lt/tablegt\n" ?gt
39Links
- Software
- Apache http//www.apache.org/
- ASP http//msdn.microsoft.com/workshop/server/de
fault.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
- cnet http//www.builder.com/
- DevShed http//www.devshed.com/Server_Side/
- Webmonkey http//www.webmonkey.com/