Working with Metadata - MySQL - page 341

When the row-fetching method finds that there are no more rows, it invokes fin
ish() implicitly, which causes the metadata to become unavailable. (That also hap‐
pens 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.
• Using a database-handle method that returns the result set in a single operation
With this approach, any metadata generated while processing the statement will
have been disposed of by the time the method returns. You can still determine the
number of rows and columns from the size of the result set.
When you use a statement handle 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. For each such type of metadata, the array
has one element per column in the result set. Access these array references 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 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:
Attribute name Array 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