Dabbling in Live Databases: MSAccess

Monday Sep 13th 1999 by Aaron Weiss
Share:

Many words have been written about databases and Web sites, and these are some more--but with a twist, because we cover what you need to know. Databases can be a complex and hairy subject, with a vocabulary seemingly all their own. The good news is that a lot of database hoo-hah is verbal smoke-and-mirrors, simple concepts that we've seen a thousand times before in programming and computing, but with new names so that database specialists can claim a professional niche.

Data-basics

We need databases because we need a way to structure a big mess of data. The very simplest database is concerned with only one category, or "field", of data, such as a list of client names. But a list of names on its own is not very useful, unless you simply enjoy looking at a bunch of names. What we need is more information to relate to these names. For instance, the total bill on our last invoice. Now we have a database with two fields--two fields which are related to one another:

Loganberry Inc. 50.00
Smart Corp. 450.00
Arlene Von Smitten 500.00
Sanford & Son 75.00
B. Gates 25000.00
Fanny Farmer 55.50

If you read any article about databases you'll undoubtedly see the term "relational database" -- which is exactly what we are building above. Relational databases are extremely common and the most likely database you will want to query from a web page. All of the major database software packages, including Microsoft Access, FileMaker Pro, Oracle, Informix, and so on are geared towards creating and managing relational databases.

In real life, our database above would likely contain even more fields -- more information we can relate, the richer the database. For instance, it would make sense to store an e-mail contact for the client, the invoice number, and whether or not the client has paid up. In fact, we can visually represent this database as a table ... literally:

billing: a sample database table
ClientName ClientEmail Invoice Total Paid
Loganberry Inc. logan@pluc.net 099050101 50.00 Yes
Smart Corp. smarties@pants.net 099050102 450.00  
Arlene Von Smitten arly@frau.net 099060101 500.00 Yes
Sanford & Son rfoxx@pickup.com 099060102 75.00  
B. Gates bill@me.com 099060103 25000.00  
Fanny Farmer ffarmer@sausagelinks.net 099070101 55.50 Yes

The above is a table, both on the screen and in database lingo. Each record in the database is called a row, which makes a whole lot of sense. And, of course, each row is made up of fields, which are in some way (mathematical, conceptual, philosophical) related to one another.

We can quickly see how we might formulate requests of this database, for example:

  • "Please list all client names and their e-mail addresses".
  • "Please list all client names who were billed at least $500."
  • "Please list all client names and e-mail addresses who are unpaid."

Pretty simple so far. Of course, the more fields in each row, the more complex your requests could become. It shouldn't be tough to imagine applying this example to many types of databases, such as basic retail inventory, or birthdays, and so on.

Thus far we've created a basic database with one table -- in fact, a single database can contain several or more tables. Because this is a relational database, the tables should be related to each other somehow. Technically speaking, the tables should relate to each other via a common, unique field. Consider a second table that we might include in this database:

invoices: a sample database table
Invoice Hours Rate
099050101 1 50
099050102 9 50
099060101 12.5 40
099060102 1 75
099060103 50 500
099070101 3.7 15

The first table, billing, contains a unique invoice code for each row, which relates the client name, address, total, and paid status to that invoice. Yet, in the invoices table, we store more detailed information about each individual invoice ... namely, the number of hours worked and the rate of pay. We can not only relate data fields within a table, but we can relate data across two tables because they share a common unique key (the invoice code). Now we can formulate requests such as:

There you have it -- the basic ins and outs of your garden variety relational database. Our example database, which we could call clients, consists of two tables, billing and invoices. We've seen the types of requests you could foist upon this beast -- but how to foist them? Ultimately, we're going to use Perl to request data from the database, receive the data, and even format it for output to a Web page. However to do that we need to understand how general requests are made from a database, Perl notwithstanding.

Data-Basics II: the SeQueL

Relational databases, according to professional database lingo, are managed. That means that some piece of software is used to create, modify, save, and query the database. They call these software packages relational database management systems, or RDBMS, but that's a big mouthful of nothing. It's as if Microsoft Word were called a textual processing modification system, or the way they call shampoo a scientifically formulated hair therapy system. You only need to know which database package you will be using to manage your database -- Microsoft Access is a very popular application for Windows 95/98 systems, of course, and is the one we will focus on first.

Why Access, which is a Windows-only product? Many Web servers run in Unix environments, in which there is no easy way to "access" an Access database, so our choice may seem counterintuituve. On the other hand, in the interests of accessibility, the reality is that many readers may already have their database in Access format and wish to at least practice generating Perl-based queries within the Windows environment. Access is also an easy path to familiarity with relational databases. Next month, the power hungry among you should feel more sated with our focus on a Unix-based database, MySQL.

Regardless of which database software you choose, it is likely to (and had better) support SQL, or the structured query language. (You may pronounce SQL as "sequel" if you wish but there is no official rule). It is SQL which defines the syntax and grammar, a sort of mini-programming language, with which you construct queries of the database.

This article isn't an appropriate place for a full-fledged course in SQL programming, but you will need to know how to construct SQL statements. Ultimately, you'll be passing these statements from your Perl script to the database. A good web-based tutorial for SQL is James Hoffman's Introduction to Structured Query Language which will take you beyond the simple introduction to SQL presented here.

The primary SQL statement you will use to pull data from the database is the select statement, which roughly follows this template:

SELECT fields_to_return FROM table_name
	WHERE field_to_test condition

Recall a row from our billing database:

ClientName ClientEmail Invoice Total Paid
Loganberry Inc. logan@pluc.net 099050101 50.00 Yes

Using the knowledge of the SQL statement template, we can translate our earlier requests into valid SQL queries:

"Please list all client names and their e-mail addresses."

SELECT ClientName,ClientEmail FROM billing

"Please list all client names who were billed at least $500."

SELECT ClientName FROM billing
	 WHERE Total>=500

"Please list all client names and e-mail addresses who are unpaid."

SELECT ClientName,ClientEmail FROM billing
	 	WHERE Paid=No

Because this is a relational database, we can also construct slightly more complex SQL statements which relate data across both tables, billing and invoices.

"Please list the names of all unpaid clients for whom I worked at least 10 hours."

SELECT billing.ClientName FROM billing,invoices
 WHERE invoices.Hours>=10 AND billing.Invoice=invoices.Invoice

 "Please list the names and e-mail addresses of all clients for whom I charged at least $70 per hour."

SELECT billing.ClientName,billing.ClientEmail FROM billing,invoices
 WHERE invoices.Rate>=70 AND billing.Invoice=invoices.Invoice

This is about as advanced as our SQL queries will become in this article, which is enough to illustrate queries from Perl. Additional SQL resources, listed at the end of this article, can provide you with far more detailed insight into the strange and quirky world of the Structured Query Language.

Preparing Microsoft Access

Our focus on MS Access this month is not an endorsement, per se, but because it is an easy-to-learn and popular database application. Of course, we're not going to spend time on the ins and outs of using the Access interface ... suffice it to say, this article will have to assume that you have or can figure out how to create a database within Access. To help follow along, you might consider re-creating the sample database we featured earlier in this article, named clients, with two tables, billing and invoices.

Now, let's cut to the chase: you have a database ready-to-go in Microsoft Access and you're chomping at the bit to start coding a Perl-based query script. First, you'll have to register your Access database with Windows' ODBC, or Open Database Connectivity, control. This will allow our Perl modules to "speak" with the Access database, as they share a common communication protocol. You can find the ODBC control in the Windows Control Panel. Launch the ODBC Data Sources control panel and select the System DSN tab.

You can see that we've already added our clients database to ODBC; you can do this by clicking "Add", selecting the "Microsoft Access Driver (*.mdb)", and then Finish. The next window lets you select a Data Source Name, or DSN, which is the name of this database as you'll refer to it from your Perl script. We chose Clients (Windows systems are generally case insensitive, although Perl itself is case sensitive), as well as the actual database file you wish to register. Voila! -- Perl will now be able to talk to your Access database courtesy of the ODBC protocol.

Now that your Access database has been created and registered with Windows' ODBC control panel, we can turn our attention away from Access-specific information and look to Perl and all the wonders within....

Perl Module Mania: DBI and DBD

Given our focus on a Windows-based database in this particular article, we will also work with the Windows version of Perl -- the most popular of which is the ActiveState port, also known as ActivePerl. In almost all respects that interest us, using ActivePerl is much the same as Perl 5 in a Unix environment.

One of the nice perks of ActivePerl is that it is easy to install additional Perl modules. ActivePerl's PPM, or Perl Package Manager, is a command-line interface with which you can simply request modules from ActivePerl's Internet site and they are automatically downloaded and installed. That's good because we need some Perl modules! We'll detail the process for Unix users next month, but if you are a Unix user with experience installing Perl modules already, this month's coverage will probably suffice.

No matter what database you intend to work with from Perl, be it Access or Oracle or MySQL and so on, you will want to install and use the Perl module DBI, or Database Independent Interface (but it's not called DBII!). The DBI module provides a simple and consist means of querying a database regardless of the source format of that database. This allows us Perl coders to query Oracle databases with the same Perl script that we query Access databases, for instance.

We'll look more closely at using DBI in Perl code soon -- but first you need to retrieve and install the DBI module. Assuming you have installed ActivePerl, launch an MS-DOS prompt in Windows and type "ppm" to launch the Perl Package Manager. From there you simply enter the command "install DBI" and PPM should take care of the rest.

PPM interactive shell (1.0.0) - type 'help' for available commands.
	PPM>install DBI
	Install package 'DBI?' (y/N): y
	Retrieving package 'DBI'...

Just how smart is this DBI module? How can it possibly know how to talk to Oracle databases, Informix databases, MS Access, and so on? In fact, DBI doesn't know any of this. DBI only knows how to talk to DBD. What??

DBD modules contain the "brains" for DBI ... thus there is a DBI module specifically written for each brand of database that you can query with DBI. For example, any database that can communicate via the ODBC protocol can be queried with DBI and the DBD::ODBC module. Alternatively, you could query an Oracle database with DBI and DBD::Oracle, a Sybase database with DBI and DBD::Sybase, and so on. If you're using a database we haven't mentioned here, check the current list of DBI Module Availability.

Since MS Access supports ODBC, we want to install the DBD::ODBC module. To do so, return to the Perl Package Manager:

PPM interactive shell (1.0.0) - type 'help' for available commands.
	PPM>install DBD-ODBC
	Install package 'DBD-ODBC?' (y/N): y
	Retrieving package 'DBD-ODBC'...

That's all ... finally, we are ready to code some Perl!

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.

Query Know How

The basis of the previous section, A Simple Query, may be all you need to know to generate SQL queries and receive the results. Because SQL is a programming language in its own right, our technique essentially boils down to using one programming language (Perl) to send the code of another language (SQL) to an interpreter, and turning back to Perl to receive and process the results. So, even a complex SQL query can be sent and processed with simple Perl code, since most of the effort takes place inside the database engine.

In our example query script we used a simple technique for retrieving the returned data: dump each row into an array. For many applications this works perfectly well. But there are some alternative capabilities of DBI that you may find helpful in receiving database results.

Where We Stand

By now you've learned how to construct a basic SQL statement, pass it to your database with Perl, and receive the results. That's 90% of the ballgame. As we've noted several times, this article has focused on Microsoft Access, and there are a few -- very few -- different twists in using these techniques in a Unix environment. Next month we'll look at the popular Unix-friendly MySQL database.

You may have noticed that our examples have so far been oriented solely towards reading data from a database. This is probably the goal of most readers and is certainly the easiest and safest interaction. Sometimes, though, you might wish to use Perl to write data to a database, either adding new records or updating existing ones. Here things become slightly tricker, as we must be concerned with safety -- both security and data integrity -- a topic we'll focus on later in this series on using Perl with databases.

Resources

James Hoffman's Introduction to Structured Query Language

SQL Reference Page

DBI Module Documentation

DBI Frequently Asked Questions

Selena Sol's Introduction to Databases on the Web

Ask the SQL Server Pro

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved