eg-259:extras:dbandsql
Table of Contents
Supplementary Material on SQL and Databases for the Web
Additional notes to be read in conjunction with Lecture 19. Not examinable.
Contents
Introduction to SQL
- 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
, andDROP
. - SQL reserved words are not case sensitive
SQL commands
The CREATE TABLE 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
.
The SELECT Command
- Used to specify queries
- Three clauses:
SELECT
,FROM
, andWHERE
- General form:
SELECT COLUMN names FROM TABLE names WHERE condition
The INSERT Command
INSERT INTO TABLE_NAME (col_name_1, ..., col_name_n) VALUES (value_1, ..., value_n)
- The correspondence between column names and values is positional.
The UPDATE Command
- 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
The DELETE Command
- Example:
DELETE FROM Corvettes WHERE Vette_id = 27
- The
WHERE
clause could specify more than one row of the table
The DROP Command
- To delete whole databases or complete tables:
DROP (TABLE | DATABASE) [IF EXISTS] name
Architectures for Database Access
Microsoft Access Architecture
- 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
The Perl DBI/DBD Architecture
- 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
PHP & Database Access
- An API for each specific database system
- Also convenient for Web access to databases, because PHP is run on the Web server
The Java JDBC Architecture
- 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
Common Database Access Architecture
The structure illustrated here is common to many three-tier database applications, including web applications.
Database Access with Perl/MySQL
- Needed:
- DBI – a standard object-oriented module
- A DBD for the specific database system
The Perl Database Interface (DBI)
- 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;
DBI: Connect to Database
- 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
DBI: Prepare
- 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'");
DBI: Execute
- To execute a compiled query, use execute, as in:
$sth->execute() or die "Error -query: $dbh->errstr\n";
DBI Results
- 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&
)
Perl/MySQL: Example
- 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:
JDBC and MySQL
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.
Review Questions
- What is JDBC and why is it useful?
- What does the Perl DBI module provide?
- What is the relationship between ODBC and JDBC?
- What is the form of the first parameter to the Perl DBI method
connect
? - What does the Perl DBI
prepare
method do? - What is a Perl DBI statement handle?
Optional Extras
If you want to look into JDBC, these questions may help to guide your study.
- Explain the two ways of using JDBC.
- What advantage does a third-tier computer provide when using JDBC?
- What method of what class is used to connect to a database when using JDBC?
- Explain the two ways to register a JDBC driver.
- What purpose does a Statement object serve when using SQL through JDBC?
- What method of what class is used to execute a SQL action command?
- What method of what class is used to execute a
SELECT
command? - What class of object is returned from the
executeQuery
method? - How can a program iterate through the object returned by
executeQuery
? - What is the form of the methods used to extract values from the object returned by
executeQuery
?
Go back to Lecture 19.
eg-259/extras/dbandsql.txt · Last modified: 2011/01/14 13:00 by 127.0.0.1