PERL Tutorial #5

PERL was principally designed as a string manipulation and report generating language but it is extremely flexible and can make database access that much easier and now having laid most of the foundation, we can begin to build simple database applications. A database is simply a collection of data that is assembled in a structured way (consisting of tables of data) for easy viewing using a language called Structured Query Language (SQL). But, I hear you ask, I thought we were using PERL. We are but it is sometimes easier to talk to databases in their own language and this can be achieved using PERL although PERL provides other comands so we are not reliant totally on SQL. However, the first thing to do is become familiar with using MySQL. If you have followed this tutorial then you should be able to run the MySQL monitor and get the mysql> prompt. However, this will be in root mode and it is wise to create a user. This is achieved by logging into root mode and typing at the prompt:-

grant all privileges on *.* to user-name@domain identified by "password" with grant option;

The user name will be your current user name and the domain is the local host. For example on my machime it would be linux@localhost with the password being anything you can easily remember more than 4 characters long and must be entered in quotes. Ensure that you add the semi-colon at the end otherwise you'll get an error. Login as your current user and enter at the mysql prompt:-

mysql -u user-name -p

and you will be prompted for the password. Once completed, you can try a few commands. Try this:-

SHOW DATABASES;

If all is well then you will get a list of names including mysql. The try:-

USE mysql;

followed by:-

SHOW TABLES;

remebering to end the line with a semi-colon each time. This will then list all the tables in the database named mysql. Typing QUIT will get you out. If you make a mistake then type /c as prompted. With those few tasks completed, it's back to PERL. Create a new file (called dbconn for example) and enter this into it:-

#! /usr/bin/perl

use DBI;
use strict;

my ($dsn)="DBI:mysql:mysql:localhost";
my ($user_name)="linux";
my ($password)="linux";
my ($dbh, $sth);
my (@ary);

$dbh=DBI->connect($dsn,$user_name, $password)
	or die "Could not connect";

print "You are connected\n";

$dbh->disconnect;
exit(0);

When you run this file, it should show that you are connected to the mysql database. This is similar to previous programs but has some slight differences. The use DBI is important because it tells PERL that you want to access databases. The $dsn variable holds references to the following information:

DBI - the database library
mysql - the database driver
mysql - the name of the current database
localhost - the name of the current host

and all seperated by : semi-colons. The user name and password is then defined plus two more variables used to connect to the database and an array denoted by the @ infront of the variable @ary. Finally we connect using all the information contained in the above variables or stop the program and print a message if we can't connect. All being well, you'll get a message saying you are connected and the program exits in the normal way. So now we can connect to the database with our new user name. Now to display all the database names as we did before in the mysql monitor. This is how it's done:-

#! /usr/bin/perl

use DBI;
use strict;

my ($dsn)="DBI:mysql:mysql:localhost";
my ($user_name)="linux";
my ($password)="linux";
my ($dbh, $sth);
my (@ary);

$dbh=DBI->connect($dsn,$user_name, $password)
	or die "Could not connect";

$sth=$dbh->prepare("SHOW DATABASES");
$sth->execute();

while(@ary=$sth->fetchrow_array())
{
	print join("\t",@ary),"\n";
}

$sth->finish();
$dbh->disconnect;
exit(0);

which on my system displayed:-

mysql
test
tmp

which are the databases currently available on my system. Now for the tables inside the databases.

#! /usr/bin/perl

use DBI;
use strict;

my ($dsn)="DBI:mysql:mysql:localhost";
my ($user_name)="linux";
my ($password)="linux";
my ($dbh, $sth);
my (@ary);

$dbh=DBI->connect($dsn,$user_name, $password)
	or die "Could not connect";

$sth=$dbh->prepare("SHOW TABLES");
$sth->execute();

while(@ary=$sth->fetchrow_array())
{
	print join("\t",@ary),"\n";
}

$sth->finish();
$dbh->disconnect;
exit(0);

Here we are selecting the database to use in the $dsn variable (in this case mysql) then listing alll the tables within it. The loop cycles throught the tables (held in rows) and constructs a list of them for displaying. On my system, the output looks something like this:

mysql
- columns_priv
- db
- func
- host
- tables_priv
- user

That is probably enough for you to be getting on with. Next time we'll create and delete some databases. Until then...

Back