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