Connecting Databases to the Web

jellytrickInternet και Εφαρμογές Web

10 Νοε 2013 (πριν από 3 χρόνια και 9 μήνες)

94 εμφανίσεις

1

Connecting Databases to the
Web


October 30
th

, 2001


Xinguang Sheng

Marcus Haebler



Connecting Databases to the Web

2

Outline


How Does Web application architecture evolves


Common Gateway Interface (CGI)


Java Applets


Server Extensions


PHP


Active Server Pages/ Java Server Pages


What else is out there?


Architectures


Extended PHP example


Connecting Databases to the Web

3

First Generation Architecture

Connecting Databases to the Web

4

Second Generation Architecture

Connecting Databases to the Web

5

Next Generation Architecture

Connecting Databases to the Web

6


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





Common Gateway Interface (CGI)

HTTP
-
Request

HTML
-
File

Web
-
Server

File
-
System

Load File

File

HTML?

HTML

Execute Program

Program?

Output

I/O, Network, DB

Connecting Databases to the Web

7

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

CGI: Discussion

Connecting Databases to the Web

8

Java Applets

Web
-
Server




HTTP
-
Request

HTML
-
File

Web
-
Server

File
-
System

Load File

File

Load Applet...

Java
-
Class Requests

Java
-
Classes

Execute Applet...

Java Virtual
Machine (JVM)

Server
-
Process

Connecting Databases to the Web

9

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

Java Applets: Discussion

Connecting Databases to the Web

10

Java
-
Server
-
Process





DB Access in Java

Sybase

Java Applet

TCP/UDP

IP

Oracle

...

JDBC
-
Driver

JDBC
-
Driver

JDBC
-
Driver

JDBC Driver manager

Connecting Databases to the Web

11

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

Server Extensions

Connecting Databases to the Web

12

Server Extensions: The Basic Idea

File
-
System

Web
-
Server








HTTP
-
Request

HTML
-
File

Web
-
Server

Load File

File

HTML?

HTML

I/O, Network, DB

Script?

Output

Server Extension

Connecting Databases to the Web

13


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





Server Extensions

Connecting Databases to the Web

14


Active Server Pages (ASPs)

-
Available in IIS and Personal Web
Server

-
Based on VBScript, Jscript

-
Modular Object Model

-
Active Server Components

-
Active Data Objects

(ADO) for Database

access

-
In MS .NET ASP+, ADO+ …

Active Server Pages

File
-
System

Web
-
Server





HTTP
-
Request

HTML
-
File

Load File

ASP
-
File

HTML

ASP
-
Script

Output

I/O, Network, DB

Active Server Page

Scripting Engine

Active Server
Components

Connecting Databases to the Web

15

ColdFusion

File
-
System

Web
-
Server







HTTP
-
Request

HTML
-
File

Web
-
Server

Load File

File

HTML?

HTML

CF Script?

HTML

Cold Fusion Server
Extension

Cold Fusion Application
Server

ODBC
-
Driver

Native

DB

DB

Email

Directories

COM/CORBA

Connecting Databases to the Web

16

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>

Connecting Databases to the Web

17

ColdFusion: Form Handling

<HTML>

<BODY>

<FORM


ACTION="
http://www.abc.com/cf/pf.cfm
">

<H1> Find Person </H1>

Person Name <INPUT NAME="
PNAME
">

<p>

<INPUT TYPE="submit" VALUE="Find">

</FORM>

</BODY>

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

<CFQUERY NAME=“PersonInfo”



DATASOURCE=“PersonDB”>


SELECT * FROM Persons



WHERE Name=
#Form.PName#

</CFQUERY>

<HTML>

<BODY>

<CFOUTPUT QUERY=“PersonInfo”>

<H1>

#Name#

</H1>

<UL>


<LI><B>Age=</B>
#Age#


<LI><B>Salary=</B> $
#Sal#


<LI><A href=“
#URL#”
><B>Homepage</B>


</A>

</UL>

</CFOUTPUT>

</BODY>

</HTML>

Connecting Databases to the Web

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

access the database through the application server


Application server is also gateway to database
for the ColdFusion IDE

(ColdFusion Studio)

Connecting Databases to the Web

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

File
-
System

Web
-
Server





HTTP
-
Request

HTML
-
File

Load File

PHP
-
File

HTML

PHP
-
Script

Output

Database APIs,

other APIs SNMP,

IMAP, POP3,

LDAP, ...

PHP

Module

Web
-
Server

Connecting Databases to the Web

20

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>


Connecting Databases to the Web

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)


Version 3

installed on fling.seas.upenn.edu


Version 4.0.2

latest version (zend engine)

-
more OO

features

-
is based on a
different, faster scripting engine

-
more
modular

architecture


The number of functions is steadily increasing

Connecting Databases to the Web

22

Java Server Pages

Connecting Databases to the Web

23

JSP Example

Hello.jsp

<html>

<head>

<title>My first JSP page

</title>

</head>

<body>

<%@ page language=”java” %>

<% System.out.println(“Hello World”); %>

</body>

</html>


Connecting Databases to the Web

24

And the Output IS

<html>

<head>

<title>My first JSP page

</title>

</head>

<body>

Hello World

</body>

</html>

Connecting Databases to the Web

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

Connecting Databases to the Web

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)

Connecting Databases to the Web

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


Connecting Databases to the Web

28

Web
-
Server


2
-
tier Architecture


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

HTTP
-
Request

HTML
-
File

Module

DB

Directory

Mail
Server

SNMP

1

2

Connecting Databases to the Web

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

Connecting Databases to the Web

30

3
-
tier Architecture


Web server plus application server connecting to
database, IMAP, ...

DB

DB

Mail
Server

SNMP

1

3

2

Web
Server
[Cluster]

Application
Server
[Cluster]

Other Servers
[Cluster]

DB

Repl.

Connecting Databases to the Web

31

3
-
tier Architecture


Advantages
:

-
better scalabilty

-
more reliable through failover mechanisms

-
offers better load balancing



Disadvantages
:

-
complicated to set up an maintain

Connecting Databases to the Web

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 systems

like Yahoo, Amazon.com, eBay

Classification not always 100%

Connecting Databases to the Web

33

Technology Choices


PHP/{Oracle|mySQL}


Minimum Setup use fling
or Eniac.


ASP/Access/ODBC
--

Need MS IIS


JSP/{Oracle|mySQL}/JDBC
--

Need Tcomcat or
Apache+JServ


Any Other Choices?

Connecting Databases to the Web

34

Extended PHP Example

browser

Connecting Databases to the Web

35

Extended PHP Example

browser

Connecting Databases to the Web

36

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>

browser

Connecting Databases to the Web

37

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

?>




browser

Connecting Databases to the Web

38

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";


}

?>



browser

Connecting Databases to the Web

39

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/