ppt

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

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

77 εμφανίσεις

Murali Mani

SQL
-
PL Interface

Murali Mani

Some Possible Options


Web Interface


Perl /CGI with Oracle/mySQL


Install your own web server and use servlets with
JDBC and Oracle/mySQL


Non
-
Web Interface


JDBC with Oracle/mySQL


Also other options like embedded SQL

Murali Mani

Perl Features (from Mike
Ciaraldi’s slides)


Created around 1987 by Larry Wall.


A scripting language.


Widely used.


Utilities


Web server programs (CGI).


Quick reference to Perl

Murali Mani

Perl


Hello World

#!/usr/local/bin/perl

use CGI::Carp qw(fatalsToBrowser
warningsToBrowser);

print "Content
-
type: text/html
\
n
\
n";

print ("<html><head><title>Hello World
Script</title></head>
\
n");

print ("<body>
\
n");

print ("<h1>Hello World !!!!</h1>
\
n");

print ("</body></html>
\
n");

Murali Mani

Perl


Hello World (Again !!)

#!/usr/local/bin/perl

use CGI::Carp qw(fatalsToBrowser
warningsToBrowser);

print "Content
-
type: text/html
\
n
\
n";

print <<ENDHTML;

<html><head><title>Hello World
Script</title></head>

<body>

<h1>Hello World !!!!</h1>

</body></html>

ENDHTML

Murali Mani

Perl


printing out environment
variables

#!/usr/local/bin/perl
-
w


use CGI::Carp qw(fatalsToBrowser
warningsToBrowser);


print "Content
-
type: text/html
\
n
\
n";


foreach $key (keys %{ENV}) {


print ("$key = $ENV{$key}<br>
\
n");

}

Murali Mani

Perl DBI (Data Base Interface)
module


Module that supports functions for connecting
to pretty much any DBMS software


Oracle,
mySQL, Microsoft databases through ODBC
etc.


Murali Mani

How does Perl DBI work?
(Image from OReilly)

Murali Mani

Perl DBI: Main functions

#!/usr/local/bin/perl

use CGI::Carp qw(fatalsToBrowser warningsToBrowser);

use DBI;

use configOracle;

print "Content
-
type: text/html
\
n
\
n";

print ("<html><head><title>Test Oracle
-

1</title></head>
\
n");

print ("<body>
\
n");

if ($ENV{HTTP_ACCEPT}) {


$ENV{ORACLE_HOME} =
"/usr/local/oracle/product/11.1.0/db_1";

}

$dbh = DBI
-
>connect
("DBI:Oracle:host=$host;sid=$sid;port=$port",
$userName, $passwd) || die "Database connection not
made: $DBI::errstr";

Murali Mani

Perl DBI (contd…)


Note the usage of DBI
-
>connect


Also note configOracle.pm which looks like


#!/usr/local/bin/perl

package configOracle;

use Exporter;

@ISA = ('Exporter');

@EXPORT = qw($host $sid $port $userName $passwd);

$host = "oracle.wpi.edu";

$sid = "WPIDBR2";

$port = "1521";

$userName = "mmani";

$passwd = "mmani";

Murali Mani

Perl DBI (contd…)

$dropTable = $dbh
-
>do ("DROP TABLE studentTemp");

if (!defined ($dropTable)) {


print ("error in dropping table studentTemp
$DBI::errstr<br>
\
n"); }

$crTable = $dbh
-
>do ("CREATE TABLE studentTemp
(num int, name varchar (10))");

if (!defined ($crTable)) {


print ("error in creating table studentTemp
$DBI::errstr<br>
\
n"); }

$rows = $dbh
-
>do ("INSERT INTO studentTemp VALUES
(1," . $dbh
-
>quote ("Matt") . ")");

$rows = $dbh
-
>do ("INSERT INTO studentTemp VALUES
(2," . $dbh
-
>quote ("Greg") . ")");

Murali Mani

Perl DBI (contd…)

$st = $dbh
-
>prepare("SELECT * from
studentTemp");

$st
-
>execute();

print ("<table>
\
n");

while ($data = $st
-
>fetchrow_hashref()) {


print "<tr><td> $data
-
>{NUM} </td><td>
$data
-
>{NAME} </td></tr>
\
n"; }

print ("</table></body></html>
\
n");

$st
-
>finish();

$dbh
-
>disconnect();

Murali Mani

Perl with mySQL


Everything stays the same except for the
connect string


$dbh = DBI
-
>connect
("DBI:mysql:$schema:$server", $userName,
$passwd) || die "Database connection not
made: $DBI::errstr";

Murali Mani

Perl with HTML forms and
Oracle

#!/usr/local/bin/perl

use CGI::Carp qw(fatalsToBrowser warningsToBrowser);

use DBI;

use configOracle;

use CGI ":standard";

print "Content
-
type: text/html
\
n
\
n";

print ("<html><head><title>Test Oracle
-

2</title></head>
\
n");

print ("<body>
\
n");

print ('<form method=post action=testOracle2.pl>');

if ($ENV{HTTP_ACCEPT}) {


$ENV{ORACLE_HOME} =
"/usr/local/oracle/product/11.1.0/db_1";

}

$dbh = DBI
-
>connect
("DBI:Oracle:host=$host;sid=$sid;port=$port",
$userName,$passwd) || die "Database connection not made:
$DBI::errstr";

Murali Mani

Perl with HTML forms (contd..)

if (defined (param ("submit"))) {


$rows = $dbh
-
>do ("INSERT INTO studentTemp
VALUES (" .



$dbh
-
>quote (param ("a1")) . ", " .



$dbh
-
>quote (param ("a2")) . ")");


if (! defined ($rows)) { print ("error inserting
$DBI::errstr<br>
\
n"); }

}

$st = $dbh
-
>prepare("SELECT * from studentTemp");

$st
-
>execute();

Murali Mani

Perl with HTML forms (contd..)

print ("<table border=1>
\
n");

print ("<tr><th>number</th><th>name</th></tr>
\
n");

while ($data = $st
-
>fetchrow_hashref()) {


print "<tr><td> $data
-
>{NUM} </td><td> $data
-
>{NAME}
</td></tr>
\
n";

}

print ("</table>
\
n");

print <<ENDHTML;

<br><br>

Num: <input type="text" name="a1"><br>

Name: <input type="text" name="a2"><br><br>

<input type="Submit" name="submit" value="Enter
Information"/>

ENDHTML

print ("</form></body></html>
\
n");

$st
-
>finish(); $dbh
-
>disconnect();

Murali Mani

Java Servlets


Steps


Install a web server, such as Apache Tomcat


Learn about servlets


Learn about HTML forms


Learn how to use JDBC


Integrate them into your project.

Murali Mani

Installing a web server


Download it from jakarta.apache.org/tomcat


You might need about 50 MB of space for the
installation


For example, get the .tar.gz file (You may
want to keep it in the temp directory, rather
than your personal disk space).


tar

xvzf file.tar.gz (untar it directly without
unzipping it to save space).

Murali Mani

Setting up the webserver


I will call the root of the installation $TOMCAT_DIR


In your .cshrc


setenv TOMCAT_DIR /home/mmani/jakarta
-
tomcat
-
5.0.18


Check the file $TOMCAT_DIR/conf/server.xml


You will see a line <Connector port=“8080”


You can renumber the port, say between 1200 and 20000


For your .cshrc


setenv PATH ${PATH}:${TOMCAT_DIR}/bin


setenv CLASSPATH
${CLASSPATH}:${TOMCAT_DIR}/common/lib/servlet
-
api.jar

Murali Mani

Test the webserver


Run the script
startup.sh


Open the page: http://ccc2.wpi.edu:1200


You ran the
startup.sh

from ccc2


Your web server is configured to port 1200
(default was 8080)


To check for errors etc, check
$TOMCAT_DIR/logs


To shut down, run the script
shutdown.sh


Check what processes are running:
ps
-
u mmani


Kill unnecessary Java processes:
killall java

Murali Mani

Servlets: Introduction


Write the java code, and compile it.


Configure the web server to recognize the
servlet class.


Restart the web server

Murali Mani

First Java Servlet


Check the directory


$TOMCAT_DIR/webapps/servlets
-
examples/WEB
-
INF/classes


There exist example servlets here


Create a test servlet with the method doGet


Compile it, let our test servlet be
TestServlet.class

Murali Mani

Configuring the web server


Check $TOMCAT_DIR/webapps/servlets
-
examples/WEB
-
INF/web.xml


Add the declarations

<servlet>


<servlet
-
name>MyTestServlet</servlet
-
name>


<servlet
-
class>TestServlet</servlet
-
class>

</servlet>

<servlet
-
mapping>


<servlet
-
name>MyTestServlet</servlet
-
name>


<url
-
pattern>/servlet/FirstTestServlet</url
-
pattern>

</servlet
-
mapping>

Murali Mani

Test the servlet


Restart the web server


Go to the URL


http://ccc2.wpi.edu:1200/servlets
-
examples/servlet/FirstTestServlet

Murali Mani

JDBC: CLI (Call Level
Interface)


JDBC (Java Database Connetivity) is a
standard API for connecting to databases
from Java programs (such as servlets).


Different vendors provide JDBC drivers
implementing the JDBC API for different
DBMS: Oracle, mySQL etc

Murali Mani

Java Code with JDBC


Steps


import java.sql.*


Load a driver instance


Establish Connection


Create a Statement


Query


Murali Mani

JDBC with Oracle


JDBC driver comes with database server


Check $ORACLE_HOME/jdbc/Readme.txt


setenv CLASSPATH
${CLASSPATH}:${ORACLE_HOME}/jdbc/lib/
ojdbc6.jar

Murali Mani

JDBC: Oracle


Loading a Driver


Class.forName (“oracle.jdbc.driver.OracleDriver”);


Establishing a Connection

Connection conn =
DriverManager.getConnection(“jdbc:oracle:thin:@
oracle.wpi.edu:1521:WPIDBR2”, <userName>,
<password>);


Create a Statement

Statement stmt = conn.createStatement ();

Murali Mani

JDBC with mySQL


You need to install the driver mySQL
Connector/J from www.mysql.com


Setenv CLASSPATH <dir>/mysql
-
connector
-
java
-
3.1.0
-
stable
-
bin.jar

Murali Mani

JDBC: mySQL


Loading a Driver


Class.forName (“com.mysql.jdbc.Driver”);


Establishing a Connection

Connection conn = DriverManager.getConnection
(“jdbc:mysql://mysql.wpi.edu/<dbName>”,
<userName>, <password>);


Create a Statement

Statement stmt = conn.createStatement ();

Murali Mani

Queries using JDBC


Queries: SQL DDL

String sql = “CREATE TABLE a (a1 int, a2 int)”;

stmt.executeUpdate (sql)


Queries: SQL DML (Updates)

String sql = “INSERT INTO a values (1, 1)”;

stmt.executeUpdate (sql)


Queries: SQL DML (Retrieval)

String sql = “SELECT * FROM a”;

ResultSet r = stmt.executeQuery (sql);

Murali Mani

JDBC Result Set: Iteration


We can iterate over a result set, r as:

/* fetch the next tuple from r and ensure that it is not
empty */

while (r.next ()) {


System.out.println (“a1 = “ + r.getString (“a1”));

}


Murali Mani

Close the statement and
connection

try {


stmt.close ();

} catch (SQLException sqlEx) {


System.out.println (“Could not close statement:” +
sqlEx.toString ());

try {


conn.close ();

} catch (SQLException sqlEx) {


System.out.println (“Could not close connection:”
+ sqlEx.toString ());

Murali Mani

Using Servlets with JDBC


Ensure that the JDBC driver can be
downloaded by our servlet.


The servlet sees only the classes available at


$TOMCAT_DIR/shared/lib


$TOMCAT_DIR/common/lib


Create a symbolic link, for example, for
Oracle JDBC driver, from the directory
$TOMCAT_DIR/shared/lib

ln

s $ORACLE_HOME/jdbc/lib/ojdbc6.jar ojdbc6.jar