Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS Access

Posted Sep 13, 1999

Dabbling in Live Databases: MSAccess - Page 6

By Aaron Weiss

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.



MS Access Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM