MySQL Cookbook, 2nd Edition

Obtaining Result Set Metadata

Problem

You already know how to retrieve the rows of a result set (Issuing Statements and Retrieving Results). Now you want to know things about the result set, such as the column names and data types, or how many rows and columns there are.

Solution

Use the appropriate capabilities provided by your API.

Discussion

For statements such as SELECT that generate a result set, you can get a number of types 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 statement (SELECT name, foods FROM profile). One of the simplest uses for this information 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 Perl DBI interface, you can obtain result sets two ways. These differ in the scope of result set metadata available to your scripts:

Process the statement using a statement handle

In this case, you invoke prepare() to get the statement handle. This handle has an execute() method that you invoke to generate the result set, and then you 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 ...