There are two primary means of accessing databases under Perl. The first (and oldest) makes use of the DBM (Database Management) libraries available for most flavors of UNIX. The second (and more powerful) allows for a platform-independent interaction with more sophisticated database management systems (DBMS’s) such as Oracle, Sybase, Informix, and MySQL.
A DBM is a simple database management facility for most UNIX systems. It allows programs to store a collection of key-value pairs in binary form, thus providing rudimentary database support for Perl. To use DBM databases in Perl, you can associate a hash with a DBM database through a process similar to opening a file:
Once the database is opened, anything you do to the hash is immediately written to the database. To break the association of the hash with the file, use the untie() function.
DBI is a module that provides a consistent interface for interaction with database solutions. The DBI approach relies on database-specific drivers (DBD’s) to translate the DBI calls as needed for each database. Further, actual manipulation of the contents of the database is performed by composing statements in Structured Query Language (SQL) and submitting them to the database server.
DBI methods make use of two different types of handles
1. Database handles (like filehandles)
2. Statement handles (provide means of executing statements and manipulating their results)
Database handles are created by the connect() method:
and destroyed by the disconnect() method:
The first argument to the connect() method is a string describing the data source, typically written in the form:
Statement handles are created by the prepare() method
where $sql is a valid SQL statement, and “destroyed” using the finish() method.
The SQL statement is then executed using the execute() method
and the results obtained using any of the fetch() methods:
Note that you do not directly access the results the SQL statement, but obtain them one row at a time via the fetch() methods.
The following script connects to a MySQL database and prints the contents of one of its tables: