Dynamic Web

Class 5 - Perl Talks to MySQL: The Web to the Database and Back

The Perl Interface To MySQL
So now that we know how variables that are sent by the web browser can be manipulated by Perl, and how we can set up our database to store and manipulate these values, we need to connect Perl to the database. The interface of Perl to MySQL is a two-level approach consisting of the following:

* DBI (DataBase Interface): a generic interface for Perl
* DBD (DataBase Driver): database-specific interface that talks to the Perl DBI

Thus, when Perl sends a SQL query to MySQL, the query is sent via the DBI to the DBD that is specific to MySQL. MySQL performs the query and sends the results back through its DBD to Perl's DBL interface such that Perl can obtains the results of the query. Visually, the chain is:

Perl -> DBI -> DBD for MySQL -> MySQL -> DBD for MySQL -> DBI -> Perl

Practical Considerations on Stage
In order to connect to your MySQL database via Perl, you need to use your MySQL login and password that was assigned to you. Because this is sensitive information, you should keep it in its own separate script that is only readable and writable by you (i.e. permissions set to "-rw-------" or "600" instead of "-rwxr-xr-x" or "755"). I named mine "info.pl" and it looks something like this:

#########################################################
# info.pl
#########################################################

$mySqlUsername = "your MySQL username - usually your Net ID";
$mySqlPassword = "your MySQL password";

1;

Then, you can reference this information at any time by issuing the statement:

# Get Login Info
require "info.pl";

However, in some environments, you may need to make sure that your main script can find the location of your login info script, so you can use the following to derive the the full directory path to your main script. The result is put in the var "$execDir":

if ($0=~m#^(.*)\\#){ $execDir = "$1"; } # Win/DOS
elsif ($0=~m#^(.*)/# ){ $execDir = "$1"; } # Unix
else {`pwd` =~ /(.*)/; $execDir = "$1"; } # Unix

If you put your login info script in the same directory as your main script, then you know the exact location of your login info script and can reference by its full pathname like this:

# Get Login Info
require "$execDir/info.pl";

Common Perl DBI Methods
You can achieve a lot with only a few commands using the Perl DBI. The main things to keep in mind are that you need to be able to connect to your database, you need to be able to make SQL queries, and you need to be able to disconnect. You connect to the DB once and only once at the beginning of your script, and you disconnect once and only once at the end of your script.

connect
Establishes a database connection, or session, to the requested $data_source. Returns a database handle object if the connection succeeds. Use $dbh->disconnect to terminate the connection.

$dbh = DBI->connect($data_source, $username, $password);

Examples of $data_source values are:

dbi:DriverName:database_name
dbi:DriverName:database_name@hostname:port
dbi:DriverName:database=database_name;host=hostname;port=port

In the real world, the connection goes something like this:

use DBI();

$database = $mySqlUsername;
$data_source = "dbi:mysql:$database";
$username = $mySqlUsername;
$password = $mySqlPassword;

#########################################################
# Connect to the database.
#########################################################
# This only needs to be done once per script
my $dbh = DBI->connect($data_source, $username, $password,
{'RaiseError' => 1, 'PrintError' => 1});

disconnect
Disconnects the database from the database handle. disconnect is typically only used before exiting the program.

#########################################################
# Disconnect from the database.
#########################################################
# Always the last thing you do before exiting your script
$dbh->disconnect();

exit(0);

do
The do method is used to prepare, execute, and finish a single statement. It returns the number of rows affected or undef on error. A return value of -1 means the number of rows is not known, not applicable, or not available.

$rows = $dbh->do($statement)

my $rows_deleted = $dbh->do('DELETE FROM table WHERE status = ?')
or die $dbh->errstr;

The main things about "do" is that it is great for INSERT, UPDATE, and DELETE queries but not for SELECT queries. More on that later. Here's some real world examples of "do":

#########################################################
# Inserting
#########################################################
$myName = "Morgan";
$myName = &escape_quotes($myName);
$SqlStatement = "INSERT INTO class05_table1 (name) ";
$SqlStatement .= "VALUES ('$myName') ";
$affected_rows = $dbh->do($SqlStatement);

#########################################################
# Updating
#########################################################
$myName = "Nancy";
$myName = &escape_quotes($myName);
$SqlStatement = "UPDATE class05_table1 ";
$SqlStatement .= "SET name='$myName' ";
$SqlStatement .= "WHERE id=1 ";
$affected_rows = $dbh->do($SqlStatement);

#########################################################
# Deleting
#########################################################
$myName = "Morgan";
$myName = &escape_quotes($myName);
$SqlStatement = "DELETE FROM class05_table1 ";
$SqlStatement .= "WHERE name='$myName' ";
$affected_rows = $dbh->do($SqlStatement);

Note that there is very little difference in how you make INSERT, UPDATE, and DELETE statements using the "do" method. It's all in how you construct the string that holds the actual SQL statement. In the above examples, we have put this statement into the scalar "$SqlStatement".

prepare, execute, finish
The reality is that "do" is an alias for executing the above three methods and in that order. "Prepare" takes a SQL statement and hands it to the DBD for pre-processing. Once the statement has been "prepared", a statement handle is returned. This handle has a method called "execute" which actual performs the query and returns a result set. Once we are done with this result set, the "finish" method is called to tidy up memory and system resources.

$SqlStatement = "SELECT * FROM class05_table1";
$sth = $dbh->prepare($SqlStatement);
$sth->execute();

# This is where we would obtain the results of our query

$sth->finish();

However, because "do" calls all three methods in a row, we don't ever get access to our result set because "do" has already called "finish", which has relinquished the very results in which we were interested. Thus, you have to explicitly "prepare" your SQL statement, then "execute" it, then obtain your result set using the two methods outlined below, and then "finish" it.

fetchrow_array
The method "fetchrow_array" returns a normal array that contains all the values for the fields requested. It returns one row at a time. We can use the while loop structure in Perl to obtain all the rows from this query.

#########################################################
# SELECT using fetchrow_array()
# Array containing one row of resultset
#########################################################
# $SqlStatement = "SELECT * FROM class05_table1"; # We can use wildcards to get all fields
$SqlStatement = "SELECT id, name FROM class05_table1"; # Or explicitly label fields
$sth = $dbh->prepare($SqlStatement);
$sth->execute();
while (@row_array = $sth->fetchrow_array())
{ $current_id = $row_array[0];
$current_name = $row_array[1];
}
$sth->finish();

Note that the columns in your request match up with the indexes in your array in the order that you request them. Thus, "id" is at index 0 in your array, "name" is at index 1. If our select statement had been:

$SqlStatement = "SELECT name, id FROM class05_table1";

then "name" would be at index 0 in your array, and "id" would be at index 1. Here is a variety of examples that manipulate a table of user firstnames.