Database-independent applications make it easy to change and upgrade your database or use different brands on remote or local hosts without extensively changing your code. The key to creating such apps is the Perl5 Database Interface (DBI) module. Read on to learn more about this module and to work through several examples. How DBI can do what it does
The DBI module is a generic database interface that provides an API that enables scripts to use database drivers (DBD) produced by various database manufacturers. DBI comes bundled with a few initial drivers, such as ODBC and Proxy (used for client-server connections), and an example for driver developers. However, I've found that the included drivers vary on different platforms, and some may need to be reinstalled or updated depending on your Perl5 distribution. To obtain DBDs not included with DBI, contact your database manufacturer or look in the downloads section of its Web site. DBI includes support for several proprietary databases, including Oracle, Sybase, Informix, and SQL Server, and a number of open source servers, as would be expected. You can even use the DBI module to access files in a variety of formats. Use your normal method of module installation to include support files for your database. You can find a number of drivers at CPAN, but I recommend looking on manufacturers' sites for the most recent versions. Getting started
If you maintain your own server, it's helpful to know how to determine what drivers are available on your system. First, use your modules manager to find out if DBI is installed; if it isn't, get it from CPAN. Once you're set, use the DBI class methods available_drivers() and data_sources() to find out what drivers and databases currently exist. The simple script in Listing A should suffice. After you run the script, pay attention to the output. If you've already installed the appropriate DBD and your database is running, you'll see output similar to: Driver name: mysql
Data Source -> DBI:mysql:mysql
Data Source -> DBI:mysql:test
In this output, two MySQL databases exist, "mysql" and "test." Any additional databases you have created will also be listed. DBI will need the case-sensitive driver name shown in your output to create a connection to your database. If the driver you are expecting isn't listed, try reinstalling the appropriate DBD module or looking for a more recent or alternate version. Or you might just stick to the ODBC driver, available from CPAN. If the driver shows up but not the databases you've created, check to make sure your database server is running and accepting connections. Your next course of action is to connect to your database and manipulate it. The sections below describe how to do this and provide examples along the way. If you're a big-picture kind of person and would like to see a complete sample script, you can refer to Listing B.