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.
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.
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.
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:
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:
Now, we proceed to create a table that we will test with:
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');
Now that our database has some data, we can start playing around with MySQL.
The next thing we will try to do is connect to the database via DBI from Perl.
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:
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.
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):
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:
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.