Installing DBI and Using MySQL with Perl in Windows

You will also see in this tutorial the basics of using DBI to perform queries, and display the results. Once you learn the basics you can use Perl to program the other SQL commands we saw briefly in our class on MySQL, and you should have learned in more details in the DB course. Please note that we have on reserve at the Library the book Programming the Perl DBI, the authoritative reference to DBI.

Installation

The installation in Windows relies in CPAN (Comprehensive Perl Archive Network) archives and PPM (Programmer's Package Manager) from ActiveState.

1. When you installed Perl, PPM was installed automatically and it allows you to install and manages modules, originally available in CPAN.
2. you open a Command Prompt Window and type ppm. An interactive session starts (requires you to be connected to the Internet to search the modules repository) and you can search for the module you want to install, and later install it.
3. finally at the ppm prompt type install package-name (package-name is the name you want to install after you found it) and PPM will install the module in the Perl/lib directory.

Install DBI

Install DB_File first. At the ppm prompt type search DB_File and if it is found (it will), then type install DB_FIle (see it done). Proceed to download DBI, the Perl module to interface with Ddatabases. You will only need to download one version of DBI to use with any database: MySQL, Oracle, Informix, etc.

Once more at the PPM prompt type search DBI and you will see a variety of modules you can install, related to DBI. Install the main DBI module as shown here. The following script let you test if your installation is working fine:

#!/usr/bin/perl -w
use strict;
use DB_File;
my %database;
tie %database, 'DB_File', "createdb.dat" or die "Can't initialize database: $!\n";
untie %database;
exit;

If all went well a file createdb.dat will be created by the script.




Install Data-Table

You will only need to install one version of Table to use with any database: MySQL, Oracle, Informix, etc. Again, at the ppm prompt type search Data-Table and when found type install Data-Table, as shown here.




Install the MySQL module

You will need to download one version of the DBD module to use with each database: MySQL, Oracle, Informix, etc. These instructions refer to downloading and installing the DBD module for MySQL, but you can install more than one of the modules to access different types of database software. The same Perl commands wich work for one database type will work for the other types: the code is portable not only across platforms, but also across database software.

Once more, at the ppm prompt type search DBD and see the variety of modules available. You are only interested in installing the driver for Mysql and at the ppm prompt type install DBD-Mysql and this is it.

This completes the installation of Perl modules to manipulate MySQL databases. The following script let you test if your installation is working fine with MySQL, assuming you have created a MYTEST database and the root password is rootpass:

#!/usr/bin/perl  -w
use DBI;
#definition of variables
$db="MYTEST";
$host="localhost";
$user="root";
$password="rootpass";  # the root password

#connect to MySQL database
my $dbh   = DBI->connect ("DBI:mysql:database=$db:host=$host",
                           $user,
                           $password) 
                           or die "Can't connect to database: $DBI::errstr\n";

#disconnect  from database 
$dbh->disconnect or warn "Disconnection error: $DBI::errstr\n";

exit;

Using MySQL with the Perl DBI Interface

The basic documentation for the MySQL module is the reference table and methods for the DBI Interface, including specific commands only available in MySQL.

Lets see some basic concepts and a series of examples to illustrate them.

Basic concepts

Perl uses three levels of handlers to deal with databases:
• $drh -- the driver handle = DBD::mysql, which is transparent for you once is installed. You will not need regularly to include $drh in your script.

• $dbh -- the database handle = what you will need to create a connection to the database, it is different for each type of database software. It has the following format in MySQL:
  my $dbh = DBI->connect ("DBI:mysql:database=$db:host=$host",$user,$password);

  $sth -- the statement handle = children of the $dbh, and will let the various statements be declared. The types of statements will depend on the SQL commands you are using. SELECT uses three statements in order to perform the query and display the results: prepare, execute and fetch (see examples below). INSERT,UPDATE and DELETE do not use fetch, only prepare and execute. See the above on-line references and the book on reserve for more details.

Example 1: Connecting, quering and displaying results of query.

#!/usr/bin/perl  -w
use DBI;
#definition of variables
$db="MYTEST";
$host="localhost";
$user="root";
$password="rootpass";

#connect to MySQL database
my $dbh   = DBI->connect ("DBI:mysql:database=$db:host=$host",
                          $user,
                          $password) 
                          or die "Can't connect to database: $DBI::errstr\n";

#prepare the query
my $sth = $dbh->prepare( "
              SELECT *
              FROM people");

#execute the query
$sth->execute( );
## Retrieve the results of a row of data and print
print "\tQuery results:\n================================================\n";

while ( my @row = $sth->fetchrow_array( ) )  {
         print "@row\n";
}
warn "Problem in retrieving results", $sth->errstr( ), "\n"
        if $sth->err( );

exit;

Once you run the script you will see the following results:

[root@alaptop dbi]# ./queryall
        Query results:
================================================
347 Al Bento 1420 N. Charles St, Baltimore, MD 21201
458 John Morris 1420 N. Charles St, Baltimore, MD 21201
569 Milt Jenkins 1420 N. Charles St, Baltimore, MD 21201




Example 2: select fields and place results in variables

This is a modification of the example 1, where we select some fields and we split the query results into variables before printing.

#!/usr/bin/perl  -w
use DBI;
#definition of variables
$db="MYTEST";
$host="localhost";
$user="root";
$password="rootpass";

#connect to MySQL database
my $dbh   = DBI->connect ("DBI:mysql:database=$db:host=$host",
                          $user,
                          $password) 
                          or die "Can't connect to database: $DBI::errstr\n";

#prepare the query
my $sth = $dbh->prepare( "
              SELECT name,address
              FROM people");

#execute the query
$sth->execute( );
## Retrieve the results of a row of data and print
print "\tQuery results:\n================================================\n";

while ( ($name,$address) = $sth->fetchrow_array( ) )  {
         print "Name: $name, Address: $address\n";
}
warn "Problem in retrieving results", $sth->errstr( ), "\n"
        if $sth->err( );

exit;

Once you run the script you will see the following results:

[root@alaptop dbi]# ./querynames
        Query results:
================================================
Name: Al Bento, Address: 1420 N. Charles St, Baltimore, MD 21201
Name: John Morris, Address: 1420 N. Charles St, Baltimore, MD 21201
Name: Milt Jenkins, Address: 1420 N. Charles St, Baltimore, MD 21201

Example 3: another way to query and obtain results

#!/usr/bin/perl  -w
use DBI;
#definition of variables
$db="MYTEST";
$host="localhost";
$user="root";
$password="rootpass";

#connect to MySQL database
my $dbh   = DBI->connect ("DBI:mysql:database=$db:host=$host",
                         $user,
                         $password) 
                         or die "Can't connect to database: $DBI::errstr\n";

#prepare the query
my $sql = "SELECT * FROM people";
my $sth = $dbh->prepare( $sql);

#execute the query
$sth->execute( );

## Retrieve the results of a row of data and print

my ( $userid,$name,$address);
$sth->bind_columns ( undef,\$userid, \$name, \$address );

print "\tQuery results:\n================================================\n";

while ( $sth->fetch( ) )  {      
      print "Userid is $userid, the name is $name, and the address is $address\n";
}
$sth->finish( );
$dbh->disconnect( );
exit;

Once you run the script you will see the following results:

[root@alaptop dbi]# ./queryallfancy
        Query results:
================================================
Userid is 347, the name is Al Bento, and the address is 1420 N. Charles St, Baltimore, MD 21201
Userid is 458, the name is John Morris, and the address is 1420 N. Charles St, Baltimore, MD 21201
Userid is 569, the name is Milt Jenkins, and the address is 1420 N. Charles St, Baltimore, MD 21201

You are not limited to queries when using DBI. You can create and drop tables, insert, update, etc.

---

This page is maintained by Al Bento who can be reached at abento@ubmail.ubalt.edu. This page was last updated on October 28, 2002. Although we will attempt to keep this information accurate, we can not guarantee the accuracy of the information provided.