Setting up DBI (Determining the Port)
On a standard NuSphere installation, the documentation for the DBI
library is at http://localhost:9000/perldoc/site/lib/DBI.html. Take a
look at this file. (And hint: bookmark it. You'll be coming back here.)
The SYNOPSIS link will take you to a list of all the main methods
provided by DBI. It's handy when you forget the exact name of a method.
For now, follow the Class Methods link. The first method described is connect. This is the method you use to
establish a session with the MySQL database server. The format is pretty
simple:
$dbh = DBI->connect($data_source, $username, $password) or die
$DBI::errstr;
The variables $username and $password are what they appear to be.
$data_source is a bit trickier. DBI uses this string to find the right
driver, database, etc. for your database server. Again, the
documentation gives examples of typical $data_source values:
dbi:DriverName:database_name
dbi:DriverName:database_name@hostname:port
dbi:DriverName:database=database_name;host=hostname;port=portBut note the emphasized text just after these examples: "There is no standard for the text following the driver name." This is one of the
things you definitely will have to change if you switch from one
database to another. So, what do you use with MySQL? The way to find out is to look at the
documentation for DBD::mysql. This is the Perl library that is the
interface between DBI and MySQL. (It's also the library for the mSQL
database, so be careful when you read it that you're looking at the
appropriate parts.) And this is what you're looking for:
# Connect to the database.
my $dbh = DBI->connect("DBI:mysql:database=test;host=localhost",
"joe", "joe's password",
{'RaiseError' => 1});RaiseError is a flag to the DBI 'connect' method that will cause your
script to die if there are any database errors. It defaults to a value
of 0 (off). You can just omit this if you don't want to set it.
TOP
Verifying Your Connection
You can use the Sample1.pl script
to test out your installation. You can run it from the nsperl-bin
directory that's set up by default on your server. (I'll show you how to
set up other directories to run mod_perl later.) You can run the script
by entering this URL in your browser:
http://localhost:9000/perl-bin/script1.pl Walking through it a bit at a time, we begin:
#!perl
print "Content-type: text/plain\n\r\n\r";
print "Hello. This is a test file to see if DBI is working.\n";
use DBI;
my $driver = "mysql";
my $hostname = "localhost";
my $database = "mysql";
my $port = "3306";
my $user = "root";
my $password = "";
my $dsn = "dbi:$driver:database=$database;host=$hostname;port=$port";The database mysql is a good one for this test because it's available in
every working MySQL installation. The 'root' user is defined by default.
In my example, I haven't changed root's MySQL password from the default
of an empty string. If you have, use the new password you set up. By default, MySQL runs on port 3306. You don't even have to specify the
port unless you're using a different value. An easy way to check on a
NuSphere installation is to use the phpMyAdmin tool. From the Home page
of phpMyAdmin, click on the Show MySQL System Variables link. In the
list of system variables displayed, you'll find a listing with the port
number. Now that I've created a data source string, I can connect to MySQL:
my $dbh = DBI->connect($dsn, $user, $password);
if (!$dbh)
{
print "Error connecting: ".$DBI::errstr;
exit;
}
If you don't see the error message when you run this, you should be OK.
But I've included a query in the script to prove everything's working.
It's a simple 'show tables' query; it's a good test, because it doesn't
depend on anything in the database.
my $query = "show tables";
print "query=$query\n";
my $result = $dbh->selectall_hashref($query) or print $dbh->errstr;The DBI method 'selectall_hashref' returns a reference to an array
containing all of the rows returned by the query. Each element of the
array is a hash, with the keys set to the column names and the values
set to the column values for that row. The script walks through the
array:
print "results:\n";
foreach my $row (@$result)
{
foreach my $f (keys %{$row})
{
print "$f=$$row{$f}\t";
}
print "\n";
}The following screenshot shows the result you should get from this
script.
 If you want to know what happens when a query goes wrong, try running
a bad query. Replace the show tables query with something like:
$query = "select * from ajdf"; Unless you happen to have a table named 'ajdf' in your database, this is
what you should see:
TOP
Getting Ready to Write a Simple Application Now that you've proved that everything is working OK, it's time to try
something a little more complicated. I'll go through a simple website
that will let you record information about your stack of CDs. It's not
the most likely thing you'd want to do, I'll grant you, but hey - it's
not recipes. First, though, let's set up Apache to let us run our code in a little
sandbox area of the NuSphere installation. By default, Apache is set up
to run mod_perl scripts from the 'nsperl-bin' directory. I want to put
my scripts in a separate directory, but I don't want to mess around with
the main installation's setup. So instead, I added some lines to the
'httpd.conf' file (located in \nusphere\apache\conf) to set up a new
VirtualHost on my machine, on port 8080:
# VirtualHost for my site
Listen 8080
<VirtualHost _default_:8080>
DocumentRoot "C:/Program Files/nusphere/apache/htdocs"
DirectoryIndex index.html index.htm index.cgi index.pl
AddHandler cgi-script .cgi
<Location /myscripts>
Options All MultiViews
</Location>
<FilesMatch "*.pl">
SetHandler perl-script
PerlHandler Apache::Registry
PerlSendHeader On
</FilesMatch>
</VirtualHost>The code will run in the myscripts directory which is in the normal
Apache document root, the htdocs folder. Rather than make every file in
a directory run as a Perl script, the FilesMatch config directive lets
you tell Apache to run any file ending in .pl as Perl code. Something else worth noting up there is the DirectoryIndex config
directive.
This tells Apache what file names to look for when the user asks for a
URL ending in a directory name. By adding 'index.pl' to the more usual
values of index.html and 'index.htm', we can have a Perl script that
runs automatically when the user goes to a directory. This makes for
cleaner-looking URLs. So, if you were to take the test script seen above, name it index.pl, and
put it in the 'myscripts' directory, it could be accessed from the URL: http://localhost:8080/myscripts/index.pl
or, just
http://localhost:8080/myscripts/ Next, you can create a database for this simple application. I called
mine 'cds'. It has four tables, 'bands', 'cds', 'tracks', and 'genres'.
It's about what you'd imagine: a band can have multiple CDs (if they're
lucky), and a CD usually has more than one song on it. The genres table
is a lookup table to store some descriptive words about the bands and/or
the CDs. Here's the SQL code to create the tables:
CREATE TABLE bands (
band_id int NOT NULL auto_increment,
band_name varchar(255) default NULL,
band_prefix varchar(50) default NULL,
band_genre varchar(20) default NULL,
PRIMARY KEY (band_id),
UNIQUE KEY band_name (band_name)
);
CREATE TABLE cds (
cd_id int NOT NULL auto_increment,
band_id int NOT NULL default '0',
cd_title varchar(255) default NULL,
cd_release_year smallint default NULL,
cd_genre varchar(20) default NULL,
PRIMARY KEY (cd_id)
);
CREATE TABLE tracks (
cd_id int NOT NULL default '0',
track_no tinyint NOT NULL default '0',
track_name varchar(255) default NULL,
track_mins tinyint default NULL,
track_secs tinyint default NULL,
PRIMARY KEY (cd_id,track_no)
);
CREATE TABLE genres (
genre varchar(20) NOT NULL default '',
PRIMARY KEY (genre),
);These are all pretty simple, though there is something to say about the
'bands' table. Instead of just one band_name field, it also has a field
named band_prefix. This is so we can sort the names properly. You've
probably seen this kind of list before:
ABBA
Guided By Voices
Julieta Venegas
Sugar
The Archies Every ex-record-store-clerk knows it ought to be:
ABBA
The Archies
Guided By Voices
Sugar
Julieta Venegas So you should store artist names slightly differently, depending on if
it's
a band name, a band name starting with "The", or the name of a person:
band_prefix | band_name | | ABBA | The | Archies | | Guided By Voices | | Sugar | Julieta | Venegas |
A query can retrieve those rows ordered by band_name, band_prefix and
get them in the right order.
TOP
Creating a Simple Application Now that I have a database, I need some code to put data into it and get
it back out again. I've created a directory named cds in the myscripts directory, and the
code will run from a file there placed there. You can find the entire
script here. Just copy the contents to the
appropriate directory and name it as you wish. All the code for the
application is in the one file. Partly, this is because it's not that
complex and doesn't require multiple files. But partly it's because
there are functions that are shared among the multiple screens that the
code presents to the users, and using a library file to store those
functions presents us with something of a problem - at least during
development. If my script calls a library file named 'myfuncs.pm', say, like so:
require "$my_full_path/myfuncs.pm"; that file is loaded into Apache just once during the lifetime of the
server process, and not over and over again every time a script that
calls the library is run - as with a CGI script. So it runs much faster
and this is great when your applications are live. But, if you're fixing bugs and generally making it up as you go, the
only way to make your changes take effect is to restart Apache. That's
fine, for about the first 50 times. Then it gets tiresome.
If I was going to actually use this application, now that it's done, I
would take the functions out of the index.pl file and put them into a
library file in the directory, to take advantage of the way that
mod_perl works.
The structure of the script is not complex:
- connect to the database
- get static lookup info (the list of genres) that gets used in a lot
of different places
- print out HTML header information and some navigation links
- print out the content of the page, based (possibly) on fields passed
in from a form, or in the URL
- print out HTML footer information
- disconnect from the database
- exit
The fourth bullet point requires some more explanation. If you look at
the script you'll see, starting on line 60, the do_something function.
This function goes through a list of actions that the script might take.
The function evaluates information that comes from either from data from
a parameter in URL that I've called action.
For example, if the URL that accesses the script looks like this:
http://localhost/myscripts/cds/index.pl?action=edit_cd&cd_id;=0
the following portion of the do_something() function (on line 112) will
test true
if ($action eq "edit_cd")
{
print edit_cd($cgi,$dbh,$genres);
}and the edit_cd() function will run, the results of which will be
printed. I've also included the action parameter in the values of the
submit buttons of the forms used to insert and edit CDs. If you look
through the do_something() function, reading the comments as you go, you
should see that the other functions in the script are called in a manner
similar to the above example.
TOP
Writing an Input/Error Form
If you continue with the example given above, where the action is
edit_cd and the cd_id is 0, you end up at the edit_cd() function, which
starts on line 154. The first if branch looks for the existence of a
cd_id:
if ($cd_id)
{But since the cd_id is 0, this will test false. As you can see in the
script, if it had tested true, information regarding the CD would have
been retrieved from the database, and that data would have been added to
the forms displayed in the HTML page.
Output from the form data starts on line 245. From that line to the end
of the function, you can see that there is a lot of conditional
information that can be included in the form, depending on what data are
available. Finally, on line line 360, a couple of submit buttons are
printed. These will carry the parameter action and will have a value of
either "Delete CD" or "Save CD". Note that the Delete CD submit button
will be available only if a cd_id is available. That is, if there is no
cd_id, then this must be a new entry for the database and therefore
there's nothing to delete.
Wrapping Up
The above section covered just one portion of the script. The best way
to see the rest of what this small application can do -- and learn a few
things about mod_perl -- is to use this application with your NuSphere
install. Then look through the source code, including the comments, to
see how the script is reacting to input from the user and responses from
the database.
Though this is a small example, it's a pretty good one. Your own Perl
scripts might be larger but for the most part, they'll be doing the same
kinds of things.
Good luck and good coding.
TOP
About NuSphere Corporation NuSphere delivers the first Internet Application Platform (IAP) based on open source compo-nents, providing an integrated foundation that allows companies to deploy reliable, cost-effective, enterprise-class applications across Windows, UNIX and Linux environments. NuSphereŽ Advantage is an integrated software suite that pairs the reliability and cost-effectiveness of PHP, Apache, Perl and open source databases with new technology for build-ing business-critical web applications and web services. The company's commercial software services include technical support, consulting and training. For more information, visit www.nusphere.com.
| 
