1 / 34

SQL-PL Interface

This document delves into the use of Perl and its DBI (Database Interface) module, highlighting options for managing databases like Oracle and MySQL. It offers insights into various interfaces, including web and non-web options, and provides sample scripts to demonstrate database connectivity, data manipulation, and form handling. With historical context on Perl's development and its widespread adoption, readers will gain a quick reference to Perl's capabilities alongside SQL for web application development.

roland
Télécharger la présentation

SQL-PL Interface

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL-PL Interface Murali Mani

  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 Murali Mani

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

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

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

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

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

  8. How does Perl DBI work? (Image from OReilly) Murali Mani

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

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

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

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

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

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

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

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

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

  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). Murali Mani

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

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

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

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

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

  24. Test the servlet • Restart the web server • Go to the URL http://ccc2.wpi.edu:1200/servlets-examples/servlet/FirstTestServlet Murali Mani

  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 Murali Mani

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

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

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

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

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

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

  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”)); } Murali Mani

  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 ()); Murali Mani

  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 Murali Mani

More Related