Recipe 9.2. Obtaining Result Set Metadata


Problem

You already know how to retrieve the rows of a result set (Section 2.4). 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 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 statement using a database handle method that returns the result set in a single operation

With this method, any metadata generated while processing the statement 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 statement, 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. For each such type of metadata, the 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, with individual column names available as elements of this array:

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

Or you can access the entire array like this:

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

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 nonportable; the kinds of information they provide might be available in other database systems, but under different attribute names.

Attribute nameArray element meaning
NAME Column name
NAME_lc Column name in lowercase
NAME_uc Column name in uppercase
NULLABLE 0 or empty string = column values cannot be NULL
 1 = column values can be NULL
 2 = unknown
PRECISION Column width
SCALE Number of decimal places (for numeric columns)
TYPE Data type (numeric DBI code)
mysql_is_blob True if column has a BLOB (or TEXT) type
mysql_is_key True if column is part of a 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 Data type (numeric internal MySQL code)
mysql_type_name Data type name


Some types of metadata, listed in the following table, are accessible as references to hashes rather than arrays. These hashes have one element per column value. The element key is the column name and its value is the position of the column within the result set. For example:

$col_pos = $sth->{NAME_hash}->{col_name}; 

Attribute nameHash element meaning
NAME_hash Column name
NAME_hash_lc Column name in lowercase
NAME_hash_uc Column name in uppercase


The number of columns in a result set and the number of placeholders in a prepared statement are available as scalar values:

$num_cols = $sth->{NUM_OF_FIELDS}; $num_placeholders = $sth->{NUM_OF_PARAMS}; 

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

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

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

Statement: 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 explicitly 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⁠(⁠ ⁠ ⁠). These methods provide no access to column metadata. That information already will have been disposed of by the time the method returns, and is unavailable to your scripts. However, you can 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 Section 2.4.

Ruby

Ruby DBI provides result set metadata after you execute a statement with execute, and access to metadata is possible until you invoke the statement handle finish method. The column_names method returns an array of column names (which is empty if there is no result set). If there is a result set, the column_info method returns an array of ColumnInfo objects, one for each column. A ColumnInfo object is similar to a hash and has the elements shown in the following table. Element names that begin with an underscore are MySQL-specific and may not be present for other database engines. (Most of these elements are not present unless you use Ruby DBI 0.1.1 or higher.)

Member nameMember meaning
name Column name
sql_type XOPEN type number
type_name XOPEN type name
precision Column width
scale Number of decimal places (for numeric columns)
nullable True if column allows NULL values
indexed True if column is indexed
primary True if column is part of a primary key
unique True if column is part of a unique index
mysql_type Data type (numeric internal MySQL code)
mysql_type_name Data type name
mysql_length Column width
mysql_max_length Actual maximum length of column values in result set
mysql_flags Data type flags


Here's some example code that shows how to execute a statement and display the result set metadata values for the columns:

stmt = "SELECT name, foods FROM profile" puts "Statement: " + stmt sth = dbh.execute(stmt) # metadata information becomes available at this point ... puts "Number of columns: #{sth.column_names.size}" puts "Note: statement has no result set" if sth.column_names.size == 0 sth.column_info.each_with_index do |info, i|   printf "--- Column %d (%s) ---\n", i, info["name"]   printf "sql_type:         %s\n", info["sql_type"]   printf "type_name:        %s\n", info["type_name"]   printf "precision:        %s\n", info["precision"]   printf "scale:            %s\n", info["scale"]   printf "nullable:         %s\n", info["nullable"]   printf "indexed:          %s\n", info["indexed"]   printf "primary:          %s\n", info["primary"]   printf "unique:           %s\n", info["unique"]   printf "mysql_type:       %s\n", info["mysql_type"]   printf "mysql_type_name:  %s\n", info["mysql_type_name"]   printf "mysql_length:     %s\n", info["mysql_length"]   printf "mysql_max_length: %s\n", info["mysql_max_length"]   printf "mysql_flags:      %s\n", info["mysql_flags"] end sth.finish 

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

Statement: SELECT name, foods FROM profile Number of columns: 2 --- Column 0 (name) --- sql_type:         12 type_name:        VARCHAR precision:        20 scale:            0 nullable:         false indexed:          false primary:          false unique:           false mysql_type:       254 mysql_type_name:  VARCHAR mysql_length:     20 mysql_max_length: 7 mysql_flags:      4097 --- Column 1 (foods) --- sql_type:         12 type_name:        VARCHAR precision:        42 scale:            0 nullable:         true indexed:          false primary:          false unique:           false mysql_type:       254 mysql_type_name:  VARCHAR mysql_length:     42 mysql_max_length: 21 mysql_flags:      2048 

To get a row count from a result set generated by calling execute, fetch the rows and count them yourself. The sth.rows method is not guaranteed to work for result sets.

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 select_one or select_all. These methods provide no access to column metadata. That information already will have been disposed of by the time the method returns, and is unavailable to your scripts. However, you can derive column and row counts by examining the result set itself.

PHP

In PHP, metadata information for SELECT statements is available from PEAR DB after a successful call to query⁠(⁠ ⁠ ⁠) and remains accessible up to the point at which you free the result set.

To check whether metadata is available, verify that the query result is a result set, and then pass it to the connection object tableInfo⁠(⁠ ⁠ ⁠) method , which returns a structure containing an array of column information. Each array element contains the members shown in the following table.

Member nameMember meaning
name Column name
type Data type name
len Data length
flags Data type flags


PEAR DB also makes row and column counts for a result set available via the result's numRows⁠(⁠ ⁠ ⁠) and numCols⁠(⁠ ⁠ ⁠) methods.

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

$stmt = "SELECT name, foods FROM profile"; print ("Statement: $stmt\n"); $result =& $conn->query ($stmt); if (PEAR::isError ($result))   die ("Statement failed\n"); # metadata information becomes available at this point ... if (is_a ($result, "DB_result"))  # statement generates a result set {   $nrows = $result->numRows ();   $ncols = $result->numCols ();   $info =& $conn->tableInfo ($result); } else                              # statement generates no result set {   $nrows = 0;   $ncols = 0; } print ("Number of rows: $nrows\n"); print ("Number of columns: $ncols\n"); if ($ncols == 0)   print ("Note: statement has no result set\n"); for ($i = 0; $i < $ncols; $i++) {   $col_info = $info[$i];   printf ("--- Column %d (%s) ---\n", $i, $col_info["name"]);   printf ("type:         %s\n", $col_info["type"]);   printf ("len:          %s\n", $col_info["len"]);   printf ("flags:        %s\n", $col_info["flags"]); } if ($ncols > 0)   # dispose of result set, if there is one   $result->free (); 

The output from the program looks like this:

Statement: SELECT name, foods FROM profile Number of rows: 10 Number of columns: 2 --- Column 0 (name) --- type:         char len:          7 flags:        not_null --- Column 1 (foods) --- type:         char len:          21 flags:        set 

Python

For statements that produce a result set, Python's DB-API makes row and column counts available, as well as a few pieces of information about individual columns.

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. (If the statement generates no result set, such as for UPDATE, 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:

stmt = "SELECT name, foods FROM profile" print "Statement: ", stmt cursor = conn.cursor () cursor.execute (stmt) # 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: statement has no result set" for i in range (ncols):   col_info = cursor.description[i]   # print name, and 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:

Statement:  SELECT name, foods FROM profile Number of rows: 10 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 

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. For JDBC, column indexes begin at 1 rather than 0, which differs from the other APIs covered here.

String stmt = "SELECT name, foods FROM profile"; System.out.println ("Statement: " + stmt); Statement s = conn.createStatement (); s.executeQuery (stmt); 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: statement 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:

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

The row count of the result set is not available directly; you must fetch the rows and count them.

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




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

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