Perl DBI/MySQL
— print (last updated: Jun 13, 2009) — print

Select font size:
Personalize this document by entering your login into the form in order to replace the usage of the generic LOGIN whenever relevant.
   

The examples in this document are meant to be keyed in or entered by copy/paste. Remember that the .pl and .cgi files must be made executable. Some features are presented as shell-based commands and others as web applications. Create an empty directory: 
$ mkdir ~/public_html/mysql-dbi
Then follow the steps from Perl CGI to install this directory as the Eclipse project of the same name mysql-dbi. Start a terminal shell to and change to this directory.

DBI Package

The Perl DBI package provides an abstract interface which can manipulate virtually every SQL DBMS. What is needed is the specific DBD driver package. The situation is completely analogous to Java/JDBC if you are familiar with that.

MySQL driver installation

$ sudo apt-get install libdbd-mysql-perl
Open a shell and navigate to the directory. The examples assume that you have a MySQL test database accessible by the guest user with empty password. Test by accessing: 
$ mysql -u guest test

Database Connection

Making a database connection to a DBMS always involves two steps: load the driver and make the connection. In Perl this is usually a single statement like this: 
$cx = DBI->connect( $url, $user, $password )
The $url component is a string representing the target data source. For a MySQL DBMS is of the form: 
$url = "DBI:mysql:$database:$server"
The $database field can be empty, this representing the user-level connection to the MySQL DBMS without specifying the database. A fourth argument can be provided; this argument is a hash reference specifying (mostly boolean) options used for the connection. Often it is more efficient in database intensive operations to hold off on "committing" to the operations until they're "complete" in some sense. The AutoCommit flag (0 or 1) can be used to control this.

In practice, one also wants to ensure that the connection goes through, and so it is common to test the connection immediately in a statement like this: 
my $cx = DBI->connect($url, $user, $password) or die;
If we fail to make the connection, it is automatically reported. If we don't want the error reported, we must provide the boolean option PrintError "turned off." If this is the case, we can manage the error reporting ourselves by printing the value of the package variable $DBI::errstr.

On success, the result, $cx, is a DBI::db object (the package is DB/db.pm). It is used to establish all other necessary entities for delivering SQL statements.

Create the following test program to test connections: 





test-connect.pl




#!/usr/bin/perl -w

use strict;
use DBI;
use File::Basename;

die "usage: " . basename($0) . " USER PASSWORD [ DATABASE [ SERVER ] ]\n"
   unless (@ARGV >= 2 && @ARGV <= 4);

my ($user, $password, $database, $server) = @ARGV;

$database = "" unless defined $database;
$server = "localhost" unless defined $server;

my $url = "DBI:mysql:$database:$server";

print qq(Connect: user="$user" to database="$database" on server="$server"\n);

my $opts = { PrintError => 0 };

my $cx = DBI->connect($url, $user, $password, $opts) or 
  die "*** Error: $DBI::errstr\n";

print "Success!\n";
print "cx=$cx\n";
Try these calls: 
$ test-connect.pl
$ test-connect.pl guest ""
$ test-connect.pl guest "" test
$ test-connect.pl guest "" mysql
$ test-connect.pl root "" mysql

Statement Execution

The simplest way to execute an SQL statement uses the DBI do function: 
$cx->do( "some SQL statement" );
This must be an SQL statement which expects no results (e.g., not a SELECT). For everything else, Perl execution uses a combination of prepare and execute. The prepare operation creates a prepared statement as follows: 
$st = $cx->prepare( "an SQL statement, possibly with ?'s" );
The result, $st, is a DBI::st object. The SQL statement can have "?" characters which serve as values to be specified during execution, which is: 
$st->execute( ARG1, ARG2, ... );
where each ARGi value fills in the appropriate "?" entry in the statement. If the arguments exist as an array @ARG_ARRAY, we can use this syntax: 
$st->execute( @ARG_ARRAY );
If there are no "?" characters in the statement then the call is simply: 
$st->execute();
If the statement only updates the table, then the result of this operation is a numeric value which counts the number of affected rows.

Accessing the result set for SELECT statements

SQL statements which generate results (typically the SELECT statement, although the same is true of SHOW, DESCRIBE and EXPLAIN statements) must be executed as prepared statements: 
$st = $cx->prepare( "SELECT ..." );
$st->execute();
Afterwards, the result set can be accessed, row by row, in a while loop by a number of different operations, the most common being these: 
while ( @row = $st->fetchrow() ) { ... 
while ( $href = $st->fetchrow_hashref() ) { ...
The former delivers the row as an array of values; the latter delivers it as a reference to a hash mapping the column (field) name to the value. The value of a column col_name is typically accessed like this: 
$href->{col_name};

Return value

The return value of execute or do statements may be useful. For non-SELECT statements, the value is the number of affected rows. For SELECT statements, the value is the number of rows returned.

Error handling

If an error occurs in any of these operations, the value returned by the operation is a false value and the error message is available in the package variable: 
$DBI::errstr
For many situations we do not want to proceed further in the code, and so it is common to use the statement like this: 
sql_operation  or  die "$DBI::errstr\n";
If the operation is INSERT where the key may be duplicate, we probably do not want to use the "do-or-die" approach. In this case we may just want to report back to the user and go on.

Blog example

We want to create is an example of generating and displaying "blog entries" in a very simplistic, yet illustrative manner. Begin by creating this Perl package: 





Blog.pm




package Blog;

use strict;
use DBI;

sub connect {
  my $database = "test";
  my $server   = "localhost";
  my $user     = "guest";
  my $password = "";
  my $url = "DBI:mysql:$database:$server";
  return DBI->connect( $url, $user, $password ) or die "$DBI::errstr\n";
}

sub table() {
  return "blog_entries";
}

return 1;
Then create this Perl script file to create the desired table: 





create-table.pl




#!/usr/bin/perl

use strict;
use DBI;
use Blog;

my $cx = Blog->connect();
my $table = Blog->table();
my $table_def = "
id INT AUTO_INCREMENT NOT NULL,
content TEXT,
created DATETIME,
PRIMARY KEY(id)
";

$cx->do("DROP TABLE IF EXISTS $table") or die "$DBI::errstr\n";
$cx->do("CREATE TABLE $table( $table_def )") or die "$DBI::errstr\n";
print "Success!\n";
After you've created it and made it executable, create the table and get some feedback about what you've done by: 
$ ./create-table.pl
$ echo "DESCRIBE blog_entries" | mysql -u guest test
Now create the three Web files. Make show-add.cgi executable. 





blog.html




<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 
              "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>My Blog</title>
<script type="text/javascript" src="/javascript/jquery.js"></script>
<script type="text/javascript" src="blog.js"></script>
<style type="text/css">
pre {
  margin: 0;
}
</style>
</head>
<body>
<b style="font-size:x-large">My Blog</b>
<button id="show">Show All Entries</button>
<table>
<tr>
<td><button id="add">Add Entry</button></td>
<td><textarea cols="50" rows="5" id="content"></textarea></td>
</tr>
</table>
<hr />
<div id="entries"></div>
</body>
</html>








blog.js




$(document).ready( function() {
   $("#show").click( function() {
	  $.get(
          "show-add.cgi", 
          {}, 
          function(data) {
            $("#entries").html(data)
          }
	  )
   })

   $("#add").click( function() {
	  $.post(
	    "show-add.cgi", 
	    { content : $("#content").val() }, 
	    function(data) {
	      $("#content").val("")
	      $("#entries").html(data)
	    }
	  )
   })
})








show-add.cgi




#!/usr/bin/perl

use strict;
use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser);
use DBI;
use Blog;

my $cx = Blog->connect();
my $table = Blog->table();

print header;

my $content = param('content');

my $st;

if ($content) {
  $st = $cx->prepare("INSERT INTO $table (created,content) VALUES (NOW(),?)");
  $st->execute($content) or die "$DBI::errstr\n";
}

$st = $cx->prepare("SELECT id,created,content FROM $table");
my $num = $st->execute() or die "$DBI::errstr\n";

if ($num == 0) {
  print h4 "no entries";
  exit;
}

my @all;
while ( my @row = $st->fetchrow() ) {
  push @all, \@row;
}

print qq(<table border="1" cellpadding="5" cellspacing="0">);
print qq(<th>id</th><th>created</th><th>content</th>);
foreach my $rowref (@all) {
  my ( $id, $created, $content ) = @$rowref;
  $content = escapeHTML $content;
  print qq(
    <tr valign="top">
    <td>$id</td><td>$created</td><td><pre>$content</pre></td>
    </tr>
  );
}
print qq(</table>);
First test the URL:
http://localhost/~LOGIN/mysql-dbi/show-add.cgi
Then try the HTML file:
http://localhost/~LOGIN/mysql-dbi/blog.html
Create some text in the text area and press the Add Entry button. Refresh the browser and press the Show All Entries button.

Programming Notes

Here are some points about the Web Database program:
  1. The JavaScript file, blog.js, contains two event handlers, one for the "show only" button, the other for the "add and show" button. The single server-side script file, show-add.cgi, serves for both. This is achieved by seeing if the content parameter is defined and non-empty, and if so, adding a new entry into the database table.
  2. The "show" event is a GET query which passes no parameters and sets the content of the div to the output of show-add.cgi, which is normally a table displaying the entry information.
  3. The "add" event passes the contents of the textarea as the content parameter. The textarea's contents, like the textfield, is treated as the val() funtion in jQuery. A POST query is used because the content may be too large for a GET query.
  4. The "add" event, in addition to displaying the table, clears the textarea's contents by a call to val() with an empty string parameter.
  5. The MySQL command for adding a new entry uses INSERT with a prepared statement which expects the content to be provided upon execution. The current date/time is added to the record in the created field using the built-in MySQL NOW() function.

© Robert M. Kline