PS_13_Perl - Database Access in Perl

Database Access in Perl

Database Access

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.

DBM

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:

use DB_File;

tie(%ARRAYNAME, “DB_File”, “dbmfilename”);

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/DBD

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:

$db_handle = DBI->connect(‘DBI:mysql:dbname:hostname’,

$username, $password);

and destroyed by the disconnect() method:

$result = $db_handle->disconnect();

The first argument to the connect() method is a string describing the data source, typically written in the form:

DBI:driver_name:database_name:host_name

Statement handles are created by the prepare() method

$st_handle = $db_handle->prepare($sql)

where $sql is a valid SQL statement, and “destroyed” using the finish() method.

The SQL statement is then executed using the execute() method

$result = $st_handle->execute();

and the results obtained using any of the fetch() methods:

@ary = $st_handle->fetchrow_array(); # fetch a single row of the

# query results

$hashref = $st_handle->fetchrow_hashref();

%hash = %$hashref;

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:

use DBI:

use strict:

my($dsn) = ‘DBI:mysql:test:localhost’; # Data source name

my($username) = ‘user’; # User name

my($password) = ‘secret’; # Password

my($dbh,$sth); # Database and statement handles

my(@ary); # array for rows returned by query

# connect to database

$dbh = DBI->connect($dsn, $username, $password);

# issue query

$sth = $dbh->prepare(‘SELECT * FROM tablename’);

$sth->execute();

# read results of query, then clean up

while(@ary = $sth->fetchrow_array()) {

print join(“\t”, @ary), “\n”;

}

$sth->finish();

$dbh->disconnect();