MySQL Cookbook

Obtaining Result Set Metadata

Problem

You know how to retrieve the rows of a result set, but you want to know things about the result, such as the column names and data types, or the number of rows and columns there are.

Solution

Use the appropriate capabilities provided by your API.

Discussion

For queries that generate a result set, you can get a number of kinds of metadata. This section discusses the information provided by each API, using programs that show how to display the result set metadata available after issuing a sample query (SELECT name, foods FROM profile). The section also discusses some applications for this information. One of the simplest uses is illustrated by several of the example programs: When you retrieve a row of values from a result set and you want to process them in a loop, the column count stored in the metadata serves as the upper bound on the loop iterator.

Perl

Using the DBI interface, you can obtain result sets two ways. These differ in the scope of result set metadata available to your scripts:

• Process the query using a statement handle.

  In this case, you invoke prepare( ) to get the statement handle, then call its execute( ) method to generate the result set, then fetch the rows in a loop. With this approach, access to the metadata is available while the result set is active—that is, after the call to execute( ) and until the end of the result set is reached. When the row-fetching method finds that there are no more rows, it invokes finish( ) implicitly, ...