DBI is a very popular Perl module. No wonder. It's the gateway between Perl and SQL-driven databases. It lets you perform database-administration functions from within Perl programs and, more importantly, issue SQL commands from within your Perl source code. For each database, there is a DataBase Driver (DBD) that links the generic DBI interface to your specific database server. This way, Perl programs written with DBI are quite portable. You can go from using one database server to another without having to change more than a few lines of Perl code.
To gain access to the magic of DBI, you just need a line that says
use DBI;
at the beginning of your Perl program. If you have your mod_perl/Apache combination set up the way I indicated yesterday, then you don't actually have to include the use DBI; statement in each ePerl file, since the start-up.perl file I provided you with gives this facility to all Perl/ePerl programs that run through mod_perl.
Actual DBI Perl programming can get rather repetitive and algorithmic, which makes it easier for me to explain.
First, create an object which will act as a 'database handle.' This object gives you something to reference all future SQL queries against, since it defines your database.
$dbh = DBI->connect('DBI:mysql:test:localhost', '','')
or die $DBI::errstr;
The three parameters involved with DBI->connect are $database, $username,
and $password. Since we're using the test database - which we set up yesterday - we don't need to specify a username and password. You can use the string constant DBI:mysql:test:localhost as your $database parameter, but only in this very limited case. If you start doing different things with different databases, you'll need to refer to DBI and DBD documentation to help you decide on a new $database string.
Next, write some SQL code and put it into a variable. For instance, using one of my examples from yesterday:
$SQL = <<'EOT';
select title, released
from albums
where artist = 'Genesis'
order by released
EOT
This variable will become the core of a client-side cursor. A cursor is a special kind of advanced SQL query which is executed one row at a time. That's not really what's going on here; the query is indeed executed all at once, but our Perl program only has the ability to step through the results of the query row by row, so it 'feels' like a cursor to applications programmers like us. The client-side cursor is declared and executed with:
$cursor = $dbh->prepare($SQL);
$cursor->execute;
Now, we step through it one row at a time:
while ( @columns = $cursor->fetchrow ) {
print ( ( map { '[$_]' } @columns ) , '');
}
All that I'm doing with this Perl code is printing out each entry in the columns array - the values of which are extracted from the $cursor row with the $cursor->fetchrow method - surrounded by square brackets, []. Obviously, I could have put anything in the while loop, not just print statements.
Lastly, to recycle system resources and make clean disconnections, we want to close off our cursor and database handle.
$cursor->finish;
$dbh->disconnect;
If the particular SQL command you wanted to execute was something other than a select statement, you wouldn't have to bother with the while ( $cursor->fetchrow ) { ... } loop. Since you didn't actually request any returned information, no rows for you to loop through are here.
Let's say I didn't delete my Genesis information in the albums database yesterday. I could take all of these ideas and turn them into a Web-ready ePerl program.
connect('DBI:mysql:test:localhost', '','')
or die $DBI::errstr;
my $SQL = <<'EOT';
select title, released
from albums
where artist = 'Genesis'
order by released
EOT
my $cursor = $dbh->prepare($SQL);
$cursor->execute;
!>//
<HTML>
<HEAD><TITLE>ePerl/DBI/HTML Integration Example</TITLE>
</HEAD>
<BODY>
<P>The results returned from the database query regarding
Genesis albums in my database are ...</P>
<HR>
<TABLE BORDER>
<TR>
<TH COLSPAN=2>Albums by Genesis</TH>
</TR>
<TR>
<TH>Title</TH><TH>Release Date</TH>
</TR>
<?
my @columns;
while ( @columns = $cursor->fetchrow ) {
print ( '<TR>',( map { '<TD>$_</TD>' }
@columns ) , '</TR>');
}
!>//
</TABLE>
<HR>
<P>... and that's it!</P>
</BODY>
</HTML>
<?
$cursor->finish;
$dbh->disconnect;
!>//
