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.

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:

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.