A Simple Query

"Please list all client names and their e-mail addresses" -- that is the first request we aim to please, premised on the sample clients database created earlier in this article. We begin our Perl program by including the DBI module:

#Windows-based Perl/DBI/MS Access example
	use DBI;

Next, we use the DBI connect method to open a connection to the MS Access database:

#open connection to Access database
	$dbh = DBI->connect('dbi:ODBC:Clients');

The variable $dbh represents the database handle (of course you can use any variable name you wish). We will soon need to access the database handle to make more specific requests of the database. The parameter passed to connect specifies which database module to use (ODBC) and the registered name of the database to open (Clients). If our database was password protected then we'd need to supply those additional parameters as well:

$dbh = DBI->connect('dbi:ODBC:Clients',username,password);

The database is now standing at attention and waiting to serve. Next we construct the SQL statement, tell DBI to "prepare" the statement for execution (sort of like compiling the statement), and finally to go ahead an execute the statement.

#construct SQL statement
$sqlstatement="SELECT ClientName,ClientEmail FROM billing";

#prepare and execute SQL statement
$sth = $dbh->prepare($sqlstatement);
$sth->execute || 
      die "Could not execute SQL statement, maybe invalid?";

The SQL statement string is assigned to the variable $sqlstatement. Another variable, $sth or statement handle, calls a method of the database handle, to prepare the statement. Exactly what preparation consists of is not our concern -- that knowledge lives within the DBD:ODBC module, or whatever database module you are using. Finally, the statement handle is instructed to execute the SQL statement.

At this point, the database now takes the SQL statement into its own hands. It will parse the statement using its own knowledge of SQL and, if valid, return results from the database. If the database cannot execute the SQL statement -- perhaps the syntax is wrong or there is a system problem (lack of memory) -- the execute method will return a false value and, in our example, the die clause will dump an error message to the screen.

Results don't grow on trees, or fall off them either. The database may be ready with its answer, but we on the Perl side must actively stick out our arms and retrieve these results. We recommend you retrieve these results with DBI's fetchrow_array method. When we call this method the database will return the first row of results, where each returned field is an element in the array.

@row=$sth->fetchrow_array;

Because our query requested two returned fields -- ClientName and ClientEmail -- the array @row should contain two elements, $row[0] and $row[1]. A cute little Perl trick as well as a more efficient way to receive these fields would be:

($ClientName,$ClientEmail)=$sth->fetchrow_array;

Remember that each call of fetchrow_array yields the next row of results, until there are no more results and the call yields a value of undef. Thus, we can use a while loop to retrieve and, for example, print out each row of results.

#output database results
	while (@row=$sth->fetchrow_array)
	 { print "@row\n" }

Putting together the pieces, we have a small little Perl script which executes a single specific SQL statement and outputs the results:

#Windows-based Perl/DBI/MS Access example use DBI; #open connection to Access database $dbh = DBI->connect('dbi:ODBC:Clients'); #prepare and execute SQL statement $sqlstatement="SELECT ClientName,ClientEmail FROM billing"; $sth = $dbh->prepare($sqlstatement); $sth->execute || die "Could not execute SQL statement ... maybe invalid?"; #output database results while (@row=$sth->fetchrow_array) { print "@row\n" }

Hint: if Microsoft Access is open and running while you try to execute these Perl scripts you may run into resource conflicts. If so, simply shutdown Microsoft Access before using Perl.

Perl Module Mania: DBI and DBD
The Perl You Need to Know
Query Know How