Notes 0012

Databases¿

All data is stored in either files or some sort of a database.

(note that most database maintain data in files as well, so the statement above kind of doesn't make sense)

We've already seen how we can open and read/write files. We also saw how we can manipulate directories. The next thing we will look at is how to work with databases.

DBI

Most database access from Perl happens via the DBI (Database Interface). It is similar to JDBC (if you're familiar with that) in a sense that it is an interface that can be used with any (or at least most) database. What that means, is that there is a need for DBD (Database Driver).

You can find a lot of DBI information here: http://dbi.perl.org/

Most databases have a DBD. The database we will examine in these notes is MySQL (http://www.mysql.com/) because it is free, open source, is simple and easy to use, doesn't take up too many resources, and is very widely deployed. Just about every hosting provider that gives you database access on UNIX has support for MySQL. Also, most Linux distribution come with MySQL preinstalled.

Installation DBI

As it is, Perl doesn't come with DBI, nor with DBD. You will need to download and install them.

If you're working under Windows, you can run that program named: "Perl Package Manager" (that was installed when you installed ActivePerl - it's in the Windows start menu). At that prompt, you need to type: install DBI and then install DBD-Mysql. These two commands will install DBI and DBD-Mysql packages, which are required to access MySQL from Perl.

Installing MySQL

You go to http://www.mysql.com/ and download the latest stable distribution. It is around 10-15MB. If for Linux, it's best to download the source code and compile your own, if under Windows, just get the installer.

Once installed, MySQL has a default root account (super user) that has no password (so you can login).

You are greatly encouraged to read the MySQL manual. The manual has information on how to install MySQL, administer it, use it, etc., even on how to access it using Perl, Java, C/C++, and a bunch of other languages and interfaces.

In our configuration, we will create a database for testing purposes. Let's call it: perldb.

We enter commands into MySQL using mysql client (just type mysql at command prompt) ie:

mysql -u root

(if you already setup a root password, this will be a bit different...)

You can now create databases by simply issuing commands like this:

mysql> create database perldb;

To create a user (and give them privileges on that new database), we do grant:

grant all on perldb.* to perldb identified by 'perldb';

From now on, we can just login to the database using our perldb user:

C:\temp>mysql -u perldb -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.53-max-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Now that we're logged in, we can do anything, but we can only do it to perldb database. We must switch to use that database:

mysql> use perldb;
Database changed

Now, we proceed to create a table that we will test with:

CREATE TABLE PERSON (
   ID INT AUTO_INCREMENT,
   FNAME VARCHAR(30),
   LNAME VARCHAR(30),
   ADDRESS VARCHAR(50),
   CITY VARCHAR(20),
   STATE CHAR(2),
   ZIP CHAR(5),
   PRIMARY KEY (ID)
);

If you are not familiar with SQL, I suggest learning it ASAP (you really can't get anywhere without knowing it).

Now we need some data in the database for our tests. Here's some data from an actual doctor database used at Pfizer (basically stuff you can manually type in from the yellow pages).

INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('MILTON','WHITE','505 Fairburn Rd SW Ste 208','Atlanta','GA','30331');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('HAN','LO','1136 Cleveland Ave','East Point','GA','30344');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('VALENCIA','BURRUSS','1001 Thornton Rd','Lithia Springs','GA','30122');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('BENJAMIN','CAMP','705 Dixie St','Carrollton','GA','30117');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('DENISE','NAKOS','1664 Mulkey Rd','Austell','GA','30106');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('JAMES','SEXSON','1680 Hospital South Dr','Austell','GA','30106');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('SHARON','HAYNES','705 Dallas Hwy','Villa Rica','GA','30180');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('SUZANNE','LOWRY','1001 Thornton Rd Ste 213','Lithia Springs','GA','30122');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('JAMES','LAMAR','9280 Highway 5 A','Douglasville','GA','30134');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('MICHEAL','BROOKS','868 York Ave SW','Atlanta','GA','30310');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('CHRISTINE','ZANDER','6095 Professional Pkwy','Douglasville','GA','30134');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('JANE','ZELLER','109 Professional Pl','Carrollton','GA','30117');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('RHODA','ROGERS','100 Professional Pl','Carrollton','GA','30117');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('LISA','WALKER','870 Crestmark Dr','Lithia Springs','GA','30122');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('KAREN','WELDON','4586 Timber Ridge Dr Ste 140','Douglasville','GA','30135');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('MONICA','WATTS','1636 Connally Dr','East Point','GA','30344');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('WILLIAM','WEAVER','1136 Cleveland Ave','East Point','GA','30344');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('ROBERT','WILLIAMS','505 Fairburn Rd','Atlanta','GA','30331');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('DANIEL','WILLIAMS','1128 Southpark St','Carrollton','GA','30119');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('THOMAS','VARUGHESE','3872 Highway 5','Douglasville','GA','30135');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('GEORGE','VELLANI-KARAN','58 Hospital Rd','Newnan','GA','30263');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('MUHAMMAD','UDDIN','3280 Howell Mill Rd NW','Atlanta','GA','30327');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('TASSEW','TESFAYE','868 York Ave SW','Atlanta','GA','30310');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('ANURADHA','THOPU','1700 Hospital South Dr','Austell','GA','30106');
INSERT INTO PERSON (FNAME,LNAME,ADDRESS,CITY,STATE,ZIP) VALUES ('SONYA','THOMPSON','6095 Professional Pkwy','Douglasville','GA','30134');

Now that our database has some data, we can start playing around with MySQL.

Using DBI

The next thing we will try to do is connect to the database via DBI from Perl.

use DBI;

# connect to the database
$dbh = DBI->connect("DBI:mysql:perldb",
                    "perldb",   # user name
                    "perldb",   # password
                    {RaiseError => 1});

# prepare a SQL statement for execution
$sth = $dbh->prepare("SELECT LNAME,FNAME FROM PERSON");

# execute the SQL statement
$sth->execute();

# fetch results, once at a time.
while($arr = $sth->fetch()){
   print join(', ',@{$arr}),"\n";
}

# inform DBI that we're done with the statement.
$sth->finish();

# disconnect from the database
$dbh->disconnect();

The code above is reasonably commented so that you can figure out the details by just reading it. Provided our data above, this code above produces:

WHITE, MILTON
LO, HAN
BURRUSS, VALENCIA
CAMP, BENJAMIN
NAKOS, DENISE
SEXSON, JAMES
HAYNES, SHARON
LOWRY, SUZANNE
LAMAR, JAMES
BROOKS, MICHEAL
ZANDER, CHRISTINE
ZELLER, JANE
ROGERS, RHODA
WALKER, LISA
WELDON, KAREN
WATTS, MONICA
WEAVER, WILLIAM
WILLIAMS, ROBERT
WILLIAMS, DANIEL
VARUGHESE, THOMAS
VELLANI-KARAN, GEORGE
UDDIN, MUHAMMAD
TESFAYE, TASSEW
THOPU, ANURADHA
THOMPSON, SONYA

You use a similar idea when accessing the database from a CGI script.

I'll leave all the details for you to explore on your own.

Connecting (updated)

I realized I didn't explain one very important thing; so I'm updating the notes.

Normally, when you write database applications, you wouldn't want to have connection parameters as part of your code. These things should be in a configuration file.

MySQL's DBI allows us to have the hostname, username and password as part of a separate configuration file which MySQL loads itself. So if somehow your script does fall into wrong hands, they don't get automatic access to the database.

The idea is that you create a file, let's say my.cfg (for my Windows based example). [Under UNIX, you might want to make the file $HOME/.my.cnf as that is a supposed standard. Then you must pass this file as part of the connection string...

The file contents would look like this (for our database we created and used in these notes):

[client]
host=localhost
user=perldb
password=perldb

Notice that if we wanted to port out application to run on a different machine using a different database account, we wouldn't have to modify the source code. These configuration files are VERY useful when you have many such programs that connect can reference a single configuration files (sure beats going through a dozen files just to modify where the application is getting its data).

I'll append a modified version of our earlier example to illustrate a more 'proper' way of connecting to the database:

use DBI;

my ($host_name, $user_name, $password) = (undef,undef,undef);
my ($db_name) = "perldb";

my ($dsn) = "DBI:mysql:$db_name";
$dsn .= ";hostname=$host_name" if $host_name;
$dsn .= ";mysql_read_default_file=\\projects\\desktop\\my.cfg";

my $attr = {RaiseError => 1, PrintError => 1 };

# connect to the database
$dbh = DBI->connect($dsn,$user_name,$password,$attr);

# prepare a SQL statement for execution
$sth = $dbh->prepare(qq{SELECT * FROM PERSON});

# execute the SQL statement
$sth->execute();

# fetch results, once at a time.
while($arr = $sth->fetch()){
   print join(',',@{$arr}),"\n";
}

# inform DBI that we're done with the statement.
$sth->finish();

# disconnect from the database
$dbh->disconnect();

Notice that if we wanted to reuse this code, it is a simple matter of cut & paste and modify some of the scalar values, and that's it. Also notice that we never encode the username or password in the code.

Anyway, there are many minute details that are useful when writing database applications, and there is no way I can cover them all in a single document.

© 2006, Particle