Additional notes to be read in conjunction with Lecture 19. Not examinable.

• Introduction to SQL
• Architectures for Database Access
• Database Access with Perl and MySQL
• Database Access with JDBC and MySQL
• Review Questions

• Some supplementary notes on SQL that are assumed to have been covered in CS-219.
• SQL is a standard language to create, query, and modify databases that is supported by all major database vendors.
• More like structured English than a programming language.
• We cover only six basic commands: CREATE TABLE, SELECT, INSERT, UPDATE, DELETE, and DROP.
• SQL reserved words are not case sensitive

• The CREATE TABLE command
• The SELECT Command
• The INSERT Command
• The UPDATE Command
• The DELETE Command
• The DROP Command

  CREATE TABLE TABLE_NAME (
     column_name_1  data_type  constraints,
     ...
     column_name_n  data_type  constraints)

• There are many different data types (INTEGER, FLOAT, CHAR(length), …)
• There are several constraints possible e.g., NOT NULL, PRIMARY KEY.

• Used to specify queries
• Three clauses: SELECT, FROM, and WHERE
• General form:

  SELECT COLUMN names
  FROM TABLE names
  WHERE condition

  INSERT INTO TABLE_NAME (col_name_1, ..., col_name_n)
  VALUES (value_1, ..., value_n)

• The correspondence between column names and values is positional.

• To change one or more values of a row in a table:

  UPDATE TABLE_NAME
  SET col_name_1 = value_1,
  ...
      col_name_n = value_n
  WHERE col_name = VALUE

• Example:

  DELETE FROM Corvettes
  WHERE Vette_id = 27

• The WHERE clause could specify more than one row of the table

• To delete whole databases or complete tables:

  DROP (TABLE | DATABASE) [IF EXISTS] name

• A tool to access any common database structure
• Use either the Jet database engine, or go through the Open Database Connectivity (ODBC) standard
• ODBC is an API for a set of objects and methods that are an interface to different databases
• Database vendors provide ODBC drivers for their products - the drivers implement the ODBC objects and methods
• An application can include SQL statements that work for any database for which a driver is available

• Database Interface (DBI) provides methods & attributes for generic SQL commands
• Database Driver (DBD) is an interface to a specific database system (MySQL, Oracle, etc.)
• Convenient for Web access to databases, because the Perl program can be run as CGI on the Web server system

• An API for each specific database system
• Also convenient for Web access to databases, because PHP is run on the Web server

• Related to both embedded languages and to ODBC
• JDBC is a standard protocol that can be implemented as a driver for any database system
• JDBC allows SQL to be embedded in Java applications, applets, and servlets
• JDBC has the advantage of portability over embedded SQL
• A JDBC application will work with any database system for which there is a JDBC driver

The structure illustrated here is common to many three-tier database applications, including web applications.

• Needed:
  1. DBI – a standard object-oriented module
  2. A DBD for the specific database system

• DBI Module
• Get complete documentation from perldoc DBI
• Interface is similar to Perl's interface to external files – through a filehandle
• To provide access to DBI and create a DBI object:

  use DBI;

• Access to the object is through the reference variable, DBI
• To connect to the database:

  $dbh = DBI->connect(
      "DBI:driver_name:database_name"
         [, username]
         [, password]);

• Example DBI→connect();

  $dbh = DBI->connect("DBI:mysql:cars"); 

• Creates the db handle
• Assumes the user name of the person logged in
• Assumes the db does not need a password
• The connect method is usually used with die
• A Perl program can have connections to any number of databases
• To create a query, we usually compile the SQL command first, then use it against the database

• To create a compiled query, use prepare, as in:

    $sth = $dbh->prepare("SELECT Vette_id,
               Body_style, Year, States.State
       FROM Corvettes, States
       WHERE Corvettes.State = States.State_id
          AND States.State = 'California'"); 

• To execute a compiled query, use execute, as in:

    $sth->execute() or
       die "Error -query: $dbh->errstr\n"; 

• The $sth object now has the result of the query
• To display the results, we would like column names, which are stored in a hash:

    $col_names = $sth->{NAME}; 

• Rows of the result are available with the fetchrow_array method, which returns a reference to an array that has the next row of the result (returns false if there are no more rows)
• Note Putting query results directly into an HTML document can cause trouble (text may contain >, <, “, and &)
• Avoid the problem by using the CGI function, escapeHTML (converts to entities: >, <, " and &)

• XHTML Form for entering SQL query: carscgi.html (carscgi.html @ localhost )

<?xml version = "1.0" encoding = "utf-8"?>
<!DOCTYPE html PUBLIC "-//w3c//DTD XHTML 1.1//EN"
 "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
 
<!-- carsdata.html
     Uses a form to collect a query against the cars
     database.
     Calls the PHP script, access_cars.php to perform
     the given query and display the results
    -->
<html xmlns = "http://www.w3.org/1999/xhtml" xml:lang="en">
  <head><title> Access to the corvettes database </title>
  </head>
  <body>
    <p>
      Try:
<pre>
SELECT Vette_id, Body_style, Year, States.State
FROM Corvettes, States
WHERE Corvettes.State = States.State_id AND States.State = 'California'
</pre>
      Please enter your query
      <br />
      <form action  = "/~eechris/cgi-bin/access_cars.cgi" method = "post">
<textarea  rows = "2"  cols = "80" name = "query" >
        </textarea>
        <br /><br />
        <input type = "reset"  value = "Reset" />
        <input type = "submit"  value = "Submit request" />
      </form>    
    </p>
  </body>
</html>

• The CGI program to illustrate using MySQL from Perl access_cars.cgi

#!c:\Perl\bin\perl.exe -w
# access_cars.cgi
# A CGI program to illustrate using MySQL from Perl
 
# Get access to DBI and CGI
 
use DBI;
use CGI ":standard";
 
print header();
print start_html("CGI-Perl MySQL database access");
# Create the connection to the database, cars
 
my $dbh = DBI->connect("DBI:mysql:cars", "eg-259", "");
 
if (!$dbh) {
    print "Error connecting to database; $dbh->errstr\n";
}
 
# Get the query and display it
 
my $query = param("query");
print "<p> <b> The query is: </b>", $query, "</p>";
 
# Build a statement object for a SELECT SQL command
 
my $sth = $dbh->prepare($query);
 
# Execute the statement
 
$sth->execute or
    die "Error - unable to execute query: $dbh->errstr\n";
 
# Get a reference to the column names in the returned value and
#  display the column names as the first table row
 
print "<table> <caption> <h2> Query Results </h2> </caption>",
      "<tr align = 'center'>";
my $col_names = $sth->{NAME};
foreach $field_name (@$col_names) {
    print "<th> $field_name </th>";
}
print "</tr>";
 
# Get the rows of the result and display them in the table
 
while (@result_rows = $sth->fetchrow_array) {
    print "<tr align = 'center'>";
 
    while ($#result_rows >= 0) {
        $field = shift @result_rows;
 
# Replace the HTML special characters with their entities
$field = escapeHTML($field);
        print "<td>  $field </td>";
    }
 
    print "</tr>";
}
 
print "</table>";
$sth->finish;
$dbh->disconnect;
print end_html(); 

• Result:

Explanations of how to use JDBC to connect to MySQL and how to use this to re-implement the cars query using servlets are covered in Robert W. Sebasta, Programming the World-Wide Web, 3rd Edition, Addison Wesley, 2006. Many of the details are analogous to the Perl/PHP interface. Not examinable.

1. What is JDBC and why is it useful?
2. What does the Perl DBI module provide?
3. What is the relationship between ODBC and JDBC?
4. What is the form of the first parameter to the Perl DBI method connect?
5. What does the Perl DBI prepare method do?
6. What is a Perl DBI statement handle?

If you want to look into JDBC, these questions may help to guide your study.

1. Explain the two ways of using JDBC.
2. What advantage does a third-tier computer provide when using JDBC?
3. What method of what class is used to connect to a database when using JDBC?
4. Explain the two ways to register a JDBC driver.
5. What purpose does a Statement object serve when using SQL through JDBC?
6. What method of what class is used to execute a SQL action command?
7. What method of what class is used to execute a SELECT command?
8. What class of object is returned from the executeQuery method?
9. How can a program iterate through the object returned by executeQuery?
10. What is the form of the methods used to extract values from the object returned by executeQuery?

Go back to Lecture 19.