ProblemYou 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. SolutionUse the appropriate capabilities provided by your API. DiscussionFor 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. PerlUsing the Perl DBI interface, you can obtain result sets two ways. These differ in the scope of result set metadata available to your scripts:
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.
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};
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. RubyRuby 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.)
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. PHPIn 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.
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 PythonFor 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 JavaJDBC 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. |