SQL-PL Interface - PowerPoint PPT Presentation

About This Presentation
Title:

SQL-PL Interface

Description:

SQL-PL Interface – PowerPoint PPT presentation

Number of Views:50
Avg rating:3.0/5.0
Slides: 35
Provided by: pcguest
Learn more at: http://web.cs.wpi.edu
Category:

less

Transcript and Presenter's Notes

Title: SQL-PL Interface


1
SQL-PL Interface
2
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

3
Perl Features (from Mike Ciaraldis slides)
  • Created around 1987 by Larry Wall.
  • A scripting language.
  • Widely used.
  • Utilities
  • Web server programs (CGI).
  • Quick reference to Perl

4
Perl Hello World
  • !/usr/local/bin/perl
  • use CGICarp qw(fatalsToBrowser
    warningsToBrowser)
  • print "Content-type text/html\n\n"
  • print ("lthtmlgtltheadgtlttitlegtHello World
    Scriptlt/titlegtlt/headgt\n")
  • print ("ltbodygt\n")
  • print ("lth1gtHello World !!!!lt/h1gt\n")
  • print ("lt/bodygtlt/htmlgt\n")

5
Perl Hello World (Again !!)
  • !/usr/local/bin/perl
  • use CGICarp qw(fatalsToBrowser
    warningsToBrowser)
  • print "Content-type text/html\n\n"
  • print ltltENDHTML
  • lthtmlgtltheadgtlttitlegtHello World Scriptlt/titlegtlt/hea
    dgt
  • ltbodygt
  • lth1gtHello World !!!!lt/h1gt
  • lt/bodygtlt/htmlgt
  • ENDHTML

6
Perl printing out environment variables
  • !/usr/local/bin/perl -w
  • use CGICarp qw(fatalsToBrowser
    warningsToBrowser)
  • print "Content-type text/html\n\n"
  • foreach key (keys ENV)
  • print ("key ENVkeyltbrgt\n")

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

8
How does Perl DBI work? (Image from OReilly)
9
Perl DBI Main functions
  • !/usr/local/bin/perl
  • use CGICarp qw(fatalsToBrowser
    warningsToBrowser)
  • use DBI
  • use configOracle
  • print "Content-type text/html\n\n"
  • print ("lthtmlgtltheadgtlttitlegtTest Oracle -
    1lt/titlegtlt/headgt\n")
  • print ("ltbodygt\n")
  • if (ENVHTTP_ACCEPT)
  • ENVORACLE_HOME "/usr/local/oracle/product/1
    1.1.0/db_1"
  • dbh DBI-gtconnect ("DBIOraclehosthostsids
    idportport", userName, passwd) die
    "Database connection not made DBIerrstr"

10
Perl DBI (contd)
  • Note the usage of DBI-gtconnect
  • Also note configOracle.pm which looks like
  • !/usr/local/bin/perl
  • package configOracle
  • use Exporter
  • _at_ISA ('Exporter')
  • _at_EXPORT qw(host sid port userName passwd)
  • host "oracle.wpi.edu"
  • sid "WPIDBR2"
  • port "1521"
  • userName "mmani"
  • passwd "mmani"

11
Perl DBI (contd)
  • dropTable dbh-gtdo ("DROP TABLE studentTemp")
  • if (!defined (dropTable))
  • print ("error in dropping table studentTemp
    DBIerrstrltbrgt\n")
  • crTable dbh-gtdo ("CREATE TABLE studentTemp
    (num int, name varchar (10))")
  • if (!defined (crTable))
  • print ("error in creating table studentTemp
    DBIerrstrltbrgt\n")
  • rows dbh-gtdo ("INSERT INTO studentTemp VALUES
    (1," . dbh-gtquote ("Matt") . ")")
  • rows dbh-gtdo ("INSERT INTO studentTemp VALUES
    (2," . dbh-gtquote ("Greg") . ")")

12
Perl DBI (contd)
  • st dbh-gtprepare("SELECT from studentTemp")
  • st-gtexecute()
  • print ("lttablegt\n")
  • while (data st-gtfetchrow_hashref())
  • print "lttrgtlttdgt data-gtNUM lt/tdgtlttdgt
    data-gtNAME lt/tdgtlt/trgt\n"
  • print ("lt/tablegtlt/bodygtlt/htmlgt\n")
  • st-gtfinish()
  • dbh-gtdisconnect()

13
Perl with mySQL
  • Everything stays the same except for the connect
    string
  • dbh DBI-gtconnect ("DBImysqlschemaserver",
    userName, passwd) die "Database connection
    not made DBIerrstr"

14
Perl with HTML forms and Oracle
  • !/usr/local/bin/perl
  • use CGICarp qw(fatalsToBrowser
    warningsToBrowser)
  • use DBI
  • use configOracle
  • use CGI "standard"
  • print "Content-type text/html\n\n"
  • print ("lthtmlgtltheadgtlttitlegtTest Oracle -
    2lt/titlegtlt/headgt\n")
  • print ("ltbodygt\n")
  • print ('ltform methodpost actiontestOracle2.plgt')
  • if (ENVHTTP_ACCEPT)
  • ENVORACLE_HOME "/usr/local/oracle/product/1
    1.1.0/db_1"
  • dbh DBI-gtconnect ("DBIOraclehosthostsids
    idportport", userName,passwd) die
    "Database connection not made DBIerrstr"

15
Perl with HTML forms (contd..)
  • if (defined (param ("submit")))
  • rows dbh-gtdo ("INSERT INTO studentTemp
    VALUES (" .
  • dbh-gtquote (param ("a1")) . ", " .
  • dbh-gtquote (param ("a2")) . ")")
  • if (! defined (rows)) print ("error
    inserting DBIerrstrltbrgt\n")
  • st dbh-gtprepare("SELECT from studentTemp")
  • st-gtexecute()

16
Perl with HTML forms (contd..)
  • print ("lttable border1gt\n")
  • print ("lttrgtltthgtnumberlt/thgtltthgtnamelt/thgtlt/trgt\n")
  • while (data st-gtfetchrow_hashref())
  • print "lttrgtlttdgt data-gtNUM lt/tdgtlttdgt
    data-gtNAME lt/tdgtlt/trgt\n"
  • print ("lt/tablegt\n")
  • print ltltENDHTML
  • ltbrgtltbrgt
  • Num ltinput type"text" name"a1"gtltbrgt
  • Name ltinput type"text" name"a2"gtltbrgtltbrgt
  • ltinput type"Submit" name"submit" value"Enter
    Information"/gt
  • ENDHTML
  • print ("lt/formgtlt/bodygtlt/htmlgt\n")
  • st-gtfinish() dbh-gtdisconnect()

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

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

19
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.1
    8
  • Check the file TOMCAT_DIR/conf/server.xml
  • You will see a line ltConnector port8080
  • You can renumber the port, say between 1200 and
    20000
  • For your .cshrc
  • setenv PATH PATHTOMCAT_DIR/bin
  • setenv CLASSPATH CLASSPATHTOMCAT_DIR/common
    /lib/servlet-api.jar

20
Test the webserver
  • Run the script startup.sh
  • Open the page http//ccc2.wpi.edu1200
  • 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

21
Servlets Introduction
  • Write the java code, and compile it.
  • Configure the web server to recognize the servlet
    class.
  • Restart the web server

22
First Java Servlet
  • Check the directory
  • TOMCAT_DIR/webapps/servlets-examples/WEB-INF/cla
    sses
  • There exist example servlets here
  • Create a test servlet with the method doGet
  • Compile it, let our test servlet be
    TestServlet.class

23
Configuring the web server
  • Check TOMCAT_DIR/webapps/servlets-examples/WEB-IN
    F/web.xml
  • Add the declarations
  • ltservletgt
  • ltservlet-namegtMyTestServletlt/servlet-namegt
  • ltservlet-classgtTestServletlt/servlet-classgt
  • lt/servletgt
  • ltservlet-mappinggt
  • ltservlet-namegtMyTestServletlt/servlet-namegt
  • lturl-patterngt/servlet/FirstTestServletlt/url-patte
    rngt
  • lt/servlet-mappinggt

24
Test the servlet
  • Restart the web server
  • Go to the URL
  • http//ccc2.wpi.edu1200/servlets-examples/servle
    t/FirstTestServlet

25
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

26
Java Code with JDBC
  • Steps
  • import java.sql.
  • Load a driver instance
  • Establish Connection
  • Create a Statement
  • Query

27
JDBC with Oracle
  • JDBC driver comes with database server
  • Check ORACLE_HOME/jdbc/Readme.txt
  • setenv CLASSPATH CLASSPATHORACLE_HOME/jdbc/
    lib/ojdbc6.jar

28
JDBC Oracle
  • Loading a Driver
  • Class.forName (oracle.jdbc.driver.OracleDriver)
  • Establishing a Connection
  • Connection conn DriverManager.getConnection(jdb
    coraclethin_at_oracle.wpi.edu1521WPIDBR2,
    ltuserNamegt, ltpasswordgt)
  • Create a Statement
  • Statement stmt conn.createStatement ()

29
JDBC with mySQL
  • You need to install the driver mySQL Connector/J
    from www.mysql.com
  • Setenv CLASSPATH ltdirgt/mysql-connector-java-3.1.0-
    stable-bin.jar

30
JDBC mySQL
  • Loading a Driver
  • Class.forName (com.mysql.jdbc.Driver)
  • Establishing a Connection
  • Connection conn DriverManager.getConnection
    (jdbcmysql//mysql.wpi.edu/ltdbNamegt,
    ltuserNamegt, ltpasswordgt)
  • Create a Statement
  • Statement stmt conn.createStatement ()

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

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

33
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 ())

34
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
Write a Comment
User Comments (0)
About PowerShow.com