Using Perl to Access Database Systems
This document was written by CS 290W TA David Corcoran and was last
modified
The ability to make queries to a database is essential in today's
world of electronic commerce. Search engines, credit card
verification, Web dictionaries, etc. all use databases and have a
means of accessing them. What are these databases? Typical ones
include Oracle, Sybase, Access, Solid, and Msql/Mysql.
Those are just a few. They all have something in common though. Each
can be accessed remotely. They all support either a full or subset
implementation of
SQL (Structured Query Language). SQL (pronounced "sequel") is a
standard that allows you to communicate using the same commands to any
database that supports them. Another standard ODBC (Open Database
Connectivity) is commonly used by Microsoft Access. But, we will
be focusing on SQL. The following are examples of SQL commands:
- CREATE - Create a new database
- DROP - Destroy an existing database
- INSERT - Insert (add) data into a database
- DELETE - Remove data from a database
- UPDATE - Change existing data in a database
- SELECT - Find certain values in the database
With the six commands above you can do virtually any type of database
management function with most databases. Once communications are
established to the database server, sending SQL commands will work on
most databases. Unfortunately, actually talking to the database
server differs greatly between all databases. Fortunately, there are
modules written for Perl called DBI and DBD modules.
- DBI - Database Interface, a common interface for talking to
different databases.
- DBD - Database Driver, allows the DBI interface to communicate
with a specific database server.
DBI and DBD modules can be downloaded off the Perl website
http://www.perl.com under the CPAN
site. PUCC has installed DBI and DBD::Oracle for use with your
accounts. We will not be going into great detail on Web databasing
besides making simple queries. For more information you might try the
following:
- Rowe, Jeff. Building Internet Databases Servers with CGI New Riders
Publishing: Indianapolis 1997.
- News Group: comp.databases
- Build Your Own for FREE (How do I do it?)
You will need the following:
- Linux (Redhat, Caldera, Debian, Slackware)
- Apache Web Server
- MySQL Database
- Perl 5.004 or greater
- DBI::Perl DBD::MySQL
Note: the above is all free. You can save yourself some time by
getting a copy of RedHat Linux (~$49) and downloading MySQL (~1meg)
and the DBI/DBD (500k).