Dabbling in Live Databases: MSAccess - Page 6

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.

• $sth->{NUM_OF_FIELDS};

  This attribute of the DBI statement handle can let you in on how many fields are being returned for each row. Of course, you may already know this information if you constructed the SQL statement yourself (as we did in our earlier example), but if your script programmatically constructed the SQL statement from user input you may find this information useful.

  Speaking of foreknowledge, you might be wondering how to know the number of rows returned for a query -- before you retrieve the data for each one. This may or may not be possible depending on which database product you use; in our testing, Microsoft Access did not seem to return this information for the sample database.

• $sth->rows;

  This method may return -1 if your database does not report this information, but you can give it a try.

  An easier way to process incoming fields is to return each row into a hash reference rather than an array. In a hash you can access each field by its name. Recall our A Simple Query example, where we want to receive a row which contains ClientName and ClientEmail:

  $row=$sth->fetchrow_hashref;

  Now, we can access each returned field by name:

  while ($row=$sth->fetchrow_hashref)
{print "Client's Name: $row->{ClientName}\t
E-mail address: $row->{ClientEmail}\n"}

  The hash reference is a convenient way to format incoming fields into formatted output such as an HTML document. Sometimes you may want to spit out a quick dump of all returned data, perhaps for testing purposes. You could write a simple routine to do this, or:

  $sth->dump_results;

  Which simply retrieves all returned rows and outputs the data simply formatted.