Connecting Databases to the Web

1
Connecting Databases to the Web
2
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

3
First Generation Architecture
4
Second Generation Architecture
5
Next Generation Architecture
6
Common 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
7
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

8
Java Applets
Web-Server
Web-Server
HTTP-Request
Load File
File-System
HTML-File
File
Load Applet...
9
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

10
DB Access in Java
Java Applet
TCP/UDP IP
Java-Server-Process
JDBC Driver manager
JDBC-Driver
JDBC-Driver
JDBC-Driver
Sybase
Oracle
...
11
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

12
Server Extensions The Basic Idea
Web-Server
Web-Server
HTTP-Request
File-System
Load File
HTML
HTML?
File
HTML-File
13
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 its own server extension, e.g.
• Authentication module
• Counter module

14
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 (ADO) for Databaseaccess
• In MS .NET ASP, ADO

15
ColdFusion
Web-Server
Web-Server
File-System
HTTP-Request
Load File
HTML
HTML?
HTML-File
File
HTML
CF Script?
Cold Fusion Server Extension
16
ColdFusion 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
17
ColdFusion 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
18
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 ltCFGRIDgtlt/CFGRIDgt
  access the database through the application
  server
• Application server is also gateway to database
  for the ColdFusion IDE (ColdFusion Studio)

19
PHP

• 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, ...
20
PHP 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
21
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

22
Java Server Pages
23
JSP 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

24
And the Output IS

• lthtmlgt
• ltheadgt
• lttitlegtMy first JSP page
• lt/titlegt
• lt/headgt
• ltbodygt
• Hello World
• lt/bodygt
• lt/htmlgt

25
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 ...

26
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)

27
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

28
2-tier Architecture

• Web server plus module connecting to database,
  LDAP, IMAP, ...

1
Web-Server
Module
2
DB
Directory
Mail Server
SNMP
29
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

30
3-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
31
3-tier Architecture

• Advantages
• better scalabilty
• more reliable through failover mechanisms
• offers better load balancing
• Disadvantages
• complicated to set up an maintain

32
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 systemslike Yahoo,
  Amazon.com, eBay
• Classification not always 100

33
Technology Choices

• PHP/OraclemySQL
• ASP/Access/ODBC -- Need MS IIS
• JSP/OraclemySQL/JDBC -- Need Tomcat or
  ApacheJServ
• Any Other Choices?

34
Extended PHP Example
35
Extended PHP Example
36
Plain 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
37
Table 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
38
Table 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
39
Links

• 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/