DBI and mySQL and Perl Oh My!

4 days ago the boss tells me that I need to set up a robust database that can be viewed, searched, and updated from the web. The program should be able to support over 20 concurrent users, and "Oh yeah, we might be marketing this to other companies later on, so try to make it portable."

Just a few months ago, I would have laughed/cried hysterically at such a concept. But today, we live in the wonderful world of something called the DBI. DBI (database-independent) is an installable module for Perl that gives you a consistent, easy, portable interface to a wide variety of relational database systems (RDBMS's). I can write my program to work with the DBI and it can talk to each database in its own language by using a DBD (database dependent) module.

I looked around on Usenet, spent some time looking in various bulletin boards, and chose MySQL as my relational database program. The program is free to install and use if you meet a few criteria. Luckily, I'm running a Linux box as my web server and I will not be redistributing the database itself, so I can use it for free.

Next, you need to install the appropriate DBI modules on your system. Head over to http://www.cpan.org/modules/by-module/DBD/ download and install the latest DBI-1.xx.tar.gz file. This is where you will also download the appropriate DBD module for the database you are going to be interacting with. The MySQL modules are a little different from most of the other database systems. If you are looking for them, scroll all the way to the bottom of the list and grab the latest Msql-Mysql-modules-xxxx.tar.gz file.

Or, you could just cross your fingers and go with a little more automated process : ) After attempting to install all of the correct modules yourself (as I did the first time) you will bless the name of Jochen Wiedmann. He put together a handy little bundle that installs all of the correct DBI and MySQL modules on your system in one fell swoop by typing:

perl -MCPAN -e 'install Bundle::DBD::mysql'

Sit back and watch the magic.

Resources:

This will be your biggest source of information and, ironically, the most frustrating to navigate.

http://forteviot.symbolstone.org/technology/perl/DBI/index.html

An article about MySQL, datatypes, and a few basic database functions.

http://www.devshed.com/Server_Side/MySQL/Intro/page3.html

The MySQL home page. I would recommend downloading the 'big' documentation file to your harddrive, you are going to be looking at it a lot.

http://www.mysql.com/

I'll be back later with information on how to work with the database in your perl program.