Obtaining Result Set Metadata

9.3.1 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.

9.3.2 Solution

Use the appropriate capabilities provided by your API.

9.3.3 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.

9.3.4 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 activethat 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, which causes the metadata to become unavailable. (That also happens if you explicitly call finish( ) yourself.) Thus, normally it's best to access the metadata immediately after calling execute( ), making a copy of any values that you'll need to use beyond the end of the fetch loop.

  • Process the query using a database handle method that returns the result set in a single operation.

    With this method, any metadata generated while processing the query will have been disposed of by the time the method returns, although you can still determine the number of rows and columns from the size of the result set.

When you use the statement handle approach to process a query, DBI makes result set metadata available after you invoke the handle's execute( ) method. This information is available primarily in the form of references to arrays. There is a separate array for each type of metadata, and each array has one element per column in the result set. Array references are accessed as attributes of the statement handle. For example, $sth->{NAME} points to the column name array. Individual column names are available as elements of this array:

$name = $sth->{NAMES}->[$i];

Or you can access the entire array like this:

@names = @{$sth->{NAMES}};

The following table lists the attribute names through which you access array-based metadata and the meaning of values in each array. Names that begin with uppercase are standard DBI attributes and should be available for most database engines. Attribute names that begin with mysql_ are MySQL-specific and non-portable; the kinds of information they provide may be available in other databases, but under different attribute names.

Attribute name

Array element meaning

NAME

Column name

NAME_lc

Column name, lowercased

NAME_uc

Column name, uppercased

NULLABLE

1 if column values can be NULL, empty string if not

PRECISION

Column width

SCALE

Number of decimal places (for numeric columns)

TYPE

Numeric column type (DBI value)

mysql_is_blob

True if column has a BLOB (or TEXT) type

mysql_is_key

True if column is part of a non-unique key

mysql_is_num

True if column has a numeric type

mysql_is_pri_key

True if column is part of a primary key

mysql_max_length

Actual maximum length of column values in result set

mysql_table

Name of table the column is part of

mysql_type

Numeric column type (internal MySQL value)

mysql_type_name

Column type name

The exception to array-based metadata is that the number of columns in a result set is available as a scalar value:

$num_cols = $sth->{NUM_OF_FIELDS};

Here's some example code that shows how to execute a query and display the result set metadata:

my $query = "SELECT name, foods FROM profile";
printf "Query: %s
", $query;
my $sth = $dbh->prepare ($query);
$sth->execute( );
# metadata information becomes available at this point ...
printf "NUM_OF_FIELDS: %d
", $sth->{NUM_OF_FIELDS};
print "Note: query has no result set
" if $sth->{NUM_OF_FIELDS} == 0;
for my $i (0 .. $sth->{NUM_OF_FIELDS}-1)
{
 printf "--- Column %d (%s) ---
", $i, $sth->{NAME}->[$i];
 printf "NAME_lc: %s
", $sth->{NAME_lc}->[$i];
 printf "NAME_uc: %s
", $sth->{NAME_uc}->[$i];
 printf "NULLABLE: %s
", $sth->{NULLABLE}->[$i];
 printf "PRECISION: %s
", $sth->{PRECISION}->[$i];
 printf "SCALE: %s
", $sth->{SCALE}->[$i];
 printf "TYPE: %s
", $sth->{TYPE}->[$i];
 printf "mysql_is_blob: %s
", $sth->{mysql_is_blob}->[$i];
 printf "mysql_is_key: %s
", $sth->{mysql_is_key}->[$i];
 printf "mysql_is_num: %s
", $sth->{mysql_is_num}->[$i];
 printf "mysql_is_pri_key: %s
", $sth->{mysql_is_pri_key}->[$i];
 printf "mysql_max_length: %s
", $sth->{mysql_max_length}->[$i];
 printf "mysql_table: %s
", $sth->{mysql_table}->[$i];
 printf "mysql_type: %s
", $sth->{mysql_type}->[$i];
 printf "mysql_type_name: %s
", $sth->{mysql_type_name}->[$i];
}
$sth->finish ( ); # release result set, since we didn't fetch its rows

If you use the preceding code to execute the query SELECT name, foods FROM profile, the output looks like this:

Query: SELECT name, foods FROM profile
NUM_OF_FIELDS: 2
--- Column 0 (name) ---
NAME_lc: name
NAME_uc: NAME
NULLABLE:
PRECISION: 20
SCALE: 0
TYPE: 1
mysql_is_blob:
mysql_is_key:
mysql_is_num: 0
mysql_is_pri_key:
mysql_max_length: 7
mysql_table: profile
mysql_type: 254
mysql_type_name: char
--- Column 1 (foods) ---
NAME_lc: foods
NAME_uc: FOODS
NULLABLE: 1
PRECISION: 42
SCALE: 0
TYPE: 1
mysql_is_blob:
mysql_is_key:
mysql_is_num: 0
mysql_is_pri_key:
mysql_max_length: 21
mysql_table: profile
mysql_type: 254
mysql_type_name: char

To get a row count from a result set generated by calling execute( ), you must fetch the rows and count them yourself. (The use of $sth->rows( ) to get a count for SELECT statements is specifically deprecated in the DBI documentation.)

You can also obtain a result set by calling one of the DBI methods that uses a database handle rather than a statement handle, such as selectall_arrayref( ) or selectall_hashref( ). For these methods, no access to column metadata is provided. That information already will have been disposed of by the time the method returns, and is unavailable to your scripts. However, you can still derive column and row counts by examining the result set itself. The way you do this depends on the kind of data structure a method produces. These structures and the way you use them to obtain result set row and column counts are discussed in Recipe 2.5.

9.3.5 PHP

In PHP, metadata information is available after a successful call to mysql_query( ) and remains accessible up to the point at which you call mysql_free_result( ). To access the metadata, pass the result set identifier returned by mysql_query( ) to the function that returns the information you want. To get a row or column count for a result set, invoke mysql_num_rows( ) or mysql_num_fields( ). Metadata information for a given column in a result set is packaged up in a single object. You get the object by passing the result set identifier and a column index to mysql_fetch_field( ), then access the various metadata attributes as members of that object. These members are summarized in the following table:

Member name

Member meaning

blob

1 if column has a BLOB (or TEXT) type, 0 otherwise

max_length

Actual maximum length of column values in result set

multiple_key

1 if column is part of a non-unique key, 0 otherwise

name

Column name

not_null

1 if column values cannot be NULL, 0 otherwise

numeric

1 if column has a numeric type, 0 otherwise

primary_key

1 if column is part of a primary key, 0 otherwise

table

Name of table the column is part of

type

Column type name

unique_key

1 if column is part of a unique key, 0 otherwise

unsigned

1 if column has the UNSIGNED attribute, 0 otherwise

zerofill

1 if column has the ZEROFILL attribute, 0 otherwise

The following code shows how to access and display result set metadata:

$query = "SELECT name, foods FROM profile";
print ("Query: $query
");
$result_id = mysql_query ($query, $conn_id);
if (!$result_id)
 die ("Query failed
");
# metadata information becomes available at this point ...
# @ is used below because mysql_num_rows( ) and mysql_num_fields( ) print
# a message if there is no result set (under PHP 4, at least)
$nrows = @mysql_num_rows ($result_id);
print ("Number of rows: $nrows
");
$ncols = @mysql_num_fields ($result_id);
print ("Number of columns: $ncols
");
if ($ncols == 0)
 print ("Note: query has no result set
");
for ($i = 0; $i < $ncols; $i++)
{
 $col_info = mysql_fetch_field ($result_id, $i);
 printf ("--- Column %d (%s) ---
", $i, $col_info->name);
 printf ("blob: %s
", $col_info->blob);
 printf ("max_length: %s
", $col_info->max_length);
 printf ("multiple_key: %s
", $col_info->multiple_key);
 printf ("not_null: %s
", $col_info->not_null);
 printf ("numeric: %s
", $col_info->numeric);
 printf ("primary_key: %s
", $col_info->primary_key);
 printf ("table: %s
", $col_info->table);
 printf ("type: %s
", $col_info->type);
 printf ("unique_key: %s
", $col_info->unique_key);
 printf ("unsigned: %s
", $col_info->unsigned);
 printf ("zerofill: %s
", $col_info->zerofill);
}
if ($ncols > 0) # dispose of result set, if there is one
 mysql_free_result ($result_id);

The output from the program looks like this:

Query: SELECT name, foods FROM profile
Number of rows: 10
Number of columns: 2
--- Column 0 (name) ---
blob: 0
max_length: 7
multiple_key: 0
not_null: 1
numeric: 0
primary_key: 0
table: profile
type: string
unique_key: 0
unsigned: 0
zerofill: 0
--- Column 1 (foods) ---
blob: 0
max_length: 21
multiple_key: 0
not_null: 0
numeric: 0
primary_key: 0
table: profile
type: string
unique_key: 0
unsigned: 0
zerofill: 0

9.3.6 Python

Python's DB-API is more limited than the other APIs in providing result set metadata. The row and column counts are available, but the information about individual columns is not as extensive.

To get the row count for a result set, access the cursor's rowcount attribute. The column count is not available directly, but after calling fetchone( ) or fetchall( ), you can determine the count as the length of any result set row tuple. It's also possible to determine the column count without fetching any rows by using cursor.description. This is a tuple containing one element per column in the result set, so its length tells you how many columns are in the set. (However, be aware that if the query generates no result set, such as for an UPDATE statement, the value of description is None.) Each element of the description tuple is another tuple that represents the metadata for the corresponding column of the result. There are seven metadata values per column; the following code shows how to access them and what they mean:

query = "SELECT name, foods FROM profile"
print "Query: ", query
cursor = conn.cursor ( )
cursor.execute (query)
# metadata information becomes available at this point ...
print "Number of rows:", cursor.rowcount
if cursor.description == None: # no result set
 ncols = 0
else:
 ncols = len (cursor.description)
print "Number of columns:", ncols
if ncols == 0:
 print "Note: query has no result set"
for i in range (ncols):
 col_info = cursor.description[i]
 # print name, then other information
 print "--- Column %d (%s) ---" % (i, col_info[0])
 print "Type: ", col_info[1]
 print "Display size: ", col_info[2]
 print "Internal size:", col_info[3]
 print "Precision: ", col_info[4]
 print "Scale: ", col_info[5]
 print "Nullable: ", col_info[6]
cursor.close

The output from the program looks like this:

Query: SELECT name, foods FROM profile
Number of rows: 10L
Number of columns: 2
--- Column 0 (name) ---
Type: 254
Display size: 7
Internal size: 20
Precision: 20
Scale: 0
Nullable: 0
--- Column 1 (foods) ---
Type: 254
Display size: 21
Internal size: 42
Precision: 42
Scale: 0
Nullable: 1

9.3.7 Java

JDBC makes result set metadata available through a ResultSetMetaData object, which you obtain by calling the getMetaData( ) method of your ResultSet object. The metadata object provides access to several kinds of information. Its getColumnCount( ) method returns the number of columns in the result set. Other types of metadata, illustrated by the following code, provide information about individual columns and take a column index as their argument. Note that for JDBC, column indexes begin at 1, not 0, which differs from DBI, PHP, and DB-API.

String query = "SELECT name, foods FROM profile";
System.out.println ("Query: " + query);
Statement s = conn.createStatement ( );
s.executeQuery (query);
ResultSet rs = s.getResultSet ( );
ResultSetMetaData md = rs.getMetaData ( );
// metadata information becomes available at this point ...
int ncols = md.getColumnCount ( );
System.out.println ("Number of columns: " + ncols);
if (ncols == 0)
 System.out.println ("Note: query has no result set");
for (int i = 1; i <= ncols; i++) // column index values are 1-based
{
 System.out.println ("--- Column " + i
 + " (" + md.getColumnName (i) + ") ---");
 System.out.println ("getColumnDisplaySize: " + md.getColumnDisplaySize (i));
 System.out.println ("getColumnLabel: " + md.getColumnLabel (i));
 System.out.println ("getColumnType: " + md.getColumnType (i));
 System.out.println ("getColumnTypeName: " + md.getColumnTypeName (i));
 System.out.println ("getPrecision: " + md.getPrecision (i));
 System.out.println ("getScale: " + md.getScale (i));
 System.out.println ("getTableName: " + md.getTableName (i));
 System.out.println ("isAutoIncrement: " + md.isAutoIncrement (i));
 System.out.println ("isNullable: " + md.isNullable (i));
 System.out.println ("isCaseSensitive: " + md.isCaseSensitive (i));
 System.out.println ("isSigned: " + md.isSigned (i));
}
rs.close ( );
s.close ( );

The output from the program looks like this:

Query: SELECT name, foods FROM profile
Number of columns: 2
--- Column 1 (name) ---
getColumnDisplaySize: 20
getColumnLabel: name
getColumnType: 1
getColumnTypeName: CHAR
getPrecision: 0
getScale: 0
getTableName: profile
isAutoIncrement: false
isNullable: 0
isCaseSensitive: true
isSigned: false
--- Column 2 (foods) ---
getColumnDisplaySize: 42
getColumnLabel: foods
getColumnType: 1
getColumnTypeName: CHAR
getPrecision: 0
getScale: 0
getTableName: profile
isAutoIncrement: false
isNullable: 1
isCaseSensitive: true
isSigned: false

As with DBI, the row count is not available directly; you must fetch the rows and count them.

There actually are several other JDBC result set metadata calls than the ones shown in the preceding example, but many of them provide no useful information for MySQL. If you want to try them out, get a JDBC reference to see what the calls are and modify the program to see what, if anything, they return.

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Similar book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net