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.
I recommend that you compile each one of these programs, rather than install from rpm. Unfortunately, no set of rpms I tried worked with different versions of Linux, even from the same distribution, e.g. RedHat 6.2 rpm does not work with 7.0. If you did not compile programs in Linux before, don't get scared. They come with programs which run in your machine (the Makefile script), see what you have in terms of software and hardware and create a set of instructions (an executable script/program) which takes care of the compilation.
Once you download the DBI module file, you will need to extract (it is compressed) and compile it, as follows:
#!/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.
Once you download the ShowTable module file, you will need to extract (it is compressed) and compile it, as follows:
Once you download the MySQL DBD module file, you will need to extract (it is compressed) and compile it, as follows:
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:
#!/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;
Lets see some basic concepts and a series of examples to illustrate them.
#!/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
#!/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
#!/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.