Miscellaneous: Perl and DBI
(last edit: 2000-11-22)
                   Database Interface Module
DBI provides an interface between your perl program and the database driver. This has as
advantage that when you switch database you only have to use an other database driver and
don't have to change your code. For more explanation see DBI at perl.com

Installing DBI for perl
Login as root (duh?) and go to the 'p5-DBI' ports dir.

root@host:/usr/porst/databases/p5-DBI#make 
....
root@host:/usr/porst/databases/p5-DBI#make install
...
root@host:/usr/porst/databases/p5-DBI#make clean

So now DBI is installed and you have the DBI interface on your machine. Now you have to 
choose a database driver and you have to install it.

Installing the mySQL driver
First of all: you have to get the source. I'll explain how to get it via ftp but it is also
possible to get it via http.

Open an ftp connection to 'ftp.mysql.org' and go to the dir '/pub/mysql/Contrib':

ncftp /pub/mysql/Contrib > get Msql-Mysql-modules-1.2209.tar.gz

The file you need to get is the 'Msql-Mysql-modules-1.2209.tar.gz' file. You'll have to 
unpack the file. I've put it in my /tmp dir and unpacked it as follows:

root@host:/tmp#tar -xvzf Msql-Mysql-modules-1.2209.tar.gz
...

Now go to the new created dir and type:

root@host:/tmp/Msql-Mysql-modules-1.2209#perl Makefile.PL 
Which drivers do you want to install?

    1)  MySQL only
    2)  mSQL only (either of mSQL 1 or mSQL 2)
    3)  MySQL and mSQL (either of mSQL 1 or mSQL 2)

    4)  mSQL 1 and mSQL 2
    5)  MySQL, mSQL 1 and mSQL 2

Enter the appropriate number:  [3] 1


Do you want to install the MysqlPerl emulation? You might keep your old
Mysql module (to be distinguished from DBD::mysql!) if you are concerned
about compatibility to existing applications! [n] 
Where is your MySQL installed? Please tell me the directory that
contains the subdir 'include'. [/usr/local] 
Which database should I use for testing the MySQL drivers? [test] 
On which host is database test running (hostname, ip address
or host:port) [localhost] 
or host:port) [localhost] 
User name for connecting to database test? [undef] 
Password for connecting to database test? [undef] 
Creating files for MySQL ....................
Checking if your kit is complete...
Looks good
Warning: prerequisite Data::ShowTable 0 not found at (eval 14) line 219.
Using DBI 1.13 installed in /usr/local/lib/perl5/site_perl/5.005/i386-freebsd/auto/DBI
Writing Makefile for DBD::mysql
Writing Makefile for Msql-Mysql-modules
----

The above settings are just examples. Make sure you set them to match you system/security
model.

Now do the standard 'make', 'make install' and 'make clean'.

If the installation was completed successfull you should be able to find the file 'mysql.pm'
in the following dir:

/usr/local/lib/perl5/site_perl/5.005/i386-freebsd/DBD/

Example perl file:
Note: This example perl file prints its output to STDOUT and not to a browser, so run it
from the command line.

---
#!/bin/perl5.005_03 -w

use strict;
use DBI;

my ($i, @row, $database, $query);

sub exitScript;

sub exitScript {
  print "\n$_[0]\n\n";
  $database->disconnect;
  exit(1);
}

$database = DBI->connect("DBI:mysql:freebsd", [ENTER USER NAME HERE], [AND PASSWORD]) || exit(1);

$query = $database->prepare([SQL STATEMENT]);

$query->execute || exitScript($database->errstr);

while(@row = $query->fetchrow_array) {
  for($i=0; $i<=$#row; $i++) {
    print "$row[$i]\t";
  }
  print "\n";
}

$database->disconnect;

print "\n";
---