Issuing Queries and Retrieving Results

2.5.1 Problem

You want your program to send a query to the MySQL server and retrieve the result.

2.5.2 Solution

Some statements only return a status code, others return a result set (a set of rows). Most APIs provide different functions for each type of statement; if so, use the function that's appropriate for your query.

2.5.3 Discussion

This section is the longest of the chapter because there are two categories of queries you can execute. Some statements retrieve information from the database; others make changes to that information. These two types of queries are handled differently. In addition, some APIs provide several different functions for issuing queries, which complicates matters further. Before we get to the examples demonstrating how to issue queries from within each API, I'll show the table used for examples, then discuss the general statement categories and outline a strategy for processing them.

In Chapter 1, we created a table named limbs to use for some sample queries. In this chapter, we'll use a different table named profile. It's based on the idea of a "buddy list," that is, the set of people we like to keep in touch with while we're online. To maintain a profile about each person, we can use the following table:

CREATE TABLE profile
(
 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 name CHAR(20) NOT NULL,
 birth DATE,
 color ENUM('blue','red','green','brown','black','white'),
 foods SET('lutefisk','burrito','curry','eggroll','fadge','pizza'),
 cats INT,
 PRIMARY KEY (id)
);

The profile table reflects that the things that are important to us are each buddy's name, age, favorite color, favorite foods, and number of catsobviously one of those goofy tables that are used only for examples in a book![3] The table includes an id column containing unique values so that we can distinguish records from each other, even if two buddies have the same name. id and name are NOT NULL because they're each required to have a value. The other columns are allowed to be NULL because we might not know the value to put into them for any given individual. (We'll use NULL to signify "unknown.") Notice that although we want to keep track of age, there is no age column in the table. Instead, there is a birth column of DATE type. That's because ages change, but birthdays don't. If we recorded age values, we'd have to keep updating them. Storing the birth date is better because it's stable, and we can use it to calculate age at any given moment. (Age calculations are discussed in Recipe 5.20.) color is an ENUM column; color values can be any one of the listed values. foods is a SET, which allows the value to be chosen as any combination of the individual set members. That way we can record multiple favorite foods for any buddy.

[3] Actually, it's not that goofy. The table uses several different data types for its columns, and these will come in handy later for illustrating how to solve particular kinds of problems that pertain to specific column types.

To create the table, use the profile.sql script in the tables directory of the recipes distribution. Change location into that directory, then run the following command:

% mysql cookbook < profile.sql

Another way to create the table is to issue the CREATE TABLE statement manually from within the mysql program, but I recommend that you use the script, because it also loads sample data into the table. That way you can experiment with the table, then restore it after changing it by running the script again.[4]

[4] See the note at the very end of this chapter on the importance of restoring the profile table.

The initial contents of the profile table loaded by the profile.sql script look like this:

mysql> SELECT * FROM profile;
+----+---------+------------+-------+-----------------------+------+
| id | name | birth | color | foods | cats |
+----+---------+------------+-------+-----------------------+------+
| 1 | Fred | 1970-04-13 | black | lutefisk,fadge,pizza | 0 |
| 2 | Mort | 1969-09-30 | white | burrito,curry,eggroll | 3 |
| 3 | Brit | 1957-12-01 | red | burrito,curry,pizza | 1 |
| 4 | Carl | 1973-11-02 | red | eggroll,pizza | 4 |
| 5 | Sean | 1963-07-04 | blue | burrito,curry | 5 |
| 6 | Alan | 1965-02-14 | red | curry,fadge | 1 |
| 7 | Mara | 1968-09-17 | green | lutefisk,fadge | 1 |
| 8 | Shepard | 1975-09-02 | black | curry,pizza | 2 |
| 9 | Dick | 1952-08-20 | green | lutefisk,fadge | 0 |
| 10 | Tony | 1960-05-01 | white | burrito,pizza | 0 |
+----+---------+------------+-------+-----------------------+------+

Most of the columns in the profile table allow NULL values, but none of the rows in the sample dataset actually contain NULL yet. This is because NULL values complicate query processing a bit and I don't want to deal with those complications until we get to Recipe 2.8 and Recipe 2.9.

2.5.4 SQL Statement Categories

SQL statements can be divided into two broad categories:

• Statements that do not return a result set (that is, a set of rows). This statement category includes INSERT, DELETE, and UPDATE. As a general rule, statements of this type generally change the database in some way. There are some exceptions, such as USE db_name, which changes the current (default) database for your session without making any changes to the database itself.
• Statements that return a result set, such as SELECT, SHOW, EXPLAIN, and DESCRIBE. I refer to such statements generically as SELECT statements, but you should understand that category to include any statement that returns rows.

The first step in processing a query is to send it to the MySQL server for execution. Some APIs (Perl and Java, for example) recognize a distinction between the two categories of statements and provide separate calls for executing them. Others (such as PHP and Python) do not and have a single call for issuing all statements. However, one thing all APIs have in common is that you don't use any special character to indicate the end of the query. No terminator is necessary because the end of the query string implicitly terminates the query. This differs from the way you issue queries in the mysql program, where you terminate statements using a semicolon ( ;) or g. (It also differs from the way I normally show the syntax for SQL statements, because I include semicolons to make it clear where statements end.)

After sending the query to the server, the next step is to check whether or not it executed successfully. Do not neglect this step. You'll regret it if you do. If a query fails and you proceed on the basis that it succeeded, your program won't work. If the query did execute, your next step depends on the type of query you issued. If it's one that returns no result set, there's nothing else to do (unless you want to check how many rows were affected by the query). If the query does return a result set, you can fetch its rows, then close the result set.

Don t Shoot Yourself in the Foot Check for Errors

Apparently, the principle that you should check for errors is not so obvious or widely appreciated as one might hope. Many messages posted on MySQL-related mailing lists are requests for help with programs that fail for reasons unknown to the people that wrote them. In a surprising number of cases, the reason these developers are mystified by their programs is that they put in no error checking, and thus gave themselves no way to know that there was a problem or to find out what it was! You cannot help yourself this way. Plan for failure by checking for errors so that you can take appropriate action if they occur.

Now we're ready to see how to issue queries in each API. Note that although the scripts check for errors as necessary, for brevity they just print a generic message that an error occurred. You can display more specific error messages using the techniques illustrated in Recipe 2.3.

2.5.5 Perl

The Perl DBI module provides two basic approaches to query execution, depending on whether or not you expect to get back a result set. To issue a query such as INSERT or UPDATE that returns no result set, use the do( ) method. It executes the query and returns the number of rows affected by the query, or undef if an error occurs. For example, if Fred gets a new kitty, the following query can be used to increment his cats count by one:

my $count = $dbh->do ("UPDATE profile SET cats = cats+1 WHERE name = 'Fred'");
if ($count) # print row count if no error occurred
{
 $count += 0;
 print "$count rows were updated
";
}

If the query executes successfully but affects no rows, do( ) returns a special value, the string "0E0" (that is, the value zero in scientific notation). "0E0" can be used for testing the execution status of a query because it is true in Boolean contexts (unlike undef). For successful queries, it can also be used when counting how many rows were affected, because it is treated as the number zero in numeric contexts. Of course, if you print that value as is, you'll print "0E0", which might look kind of weird to people who use your program. The preceding example shows one way to make sure this doesn't happen: adding zero to the value explicitly coerces it to numeric form so that it displays as 0. You can also use printf with a %d format specifier to cause an implicit numeric conversion:

my $count = $dbh->do ("UPDATE profile SET color = color WHERE name = 'Fred'");
if ($count) # print row count if no error occurred
{
 printf "%d rows were updated
", $count;
}

If RaiseError is enabled, your script will terminate automatically if a DBI-related error occurs and you don't need to bother checking $count to see if do( ) failed:

my $count = $dbh->do ("UPDATE profile SET color = color WHERE name = 'Fred'");
printf "%d rows were updated
", $count;

To process queries such as SELECT that do return a result set, use a different approach that involves four steps:

• Specify the query by calling prepare( ) using the database handle. prepare( ) returns a statement handle to use with all subsequent operations on the query. (If an error occurs, the script terminates if RaiseError is enabled; otherwise, prepare( ) returns undef.)
• Call execute( ) to execute the query and generate the result set.
• Perform a loop to fetch the rows returned by the query. DBI provides several methods you can use in this loop, which we'll describe shortly.
• Release resources associated with the result set by calling finish( ).

The following example illustrates these steps, using fetchrow_array( ) as the row-fetching method and assuming RaiseError is enabled:

my $sth = $dbh->prepare ("SELECT id, name, cats FROM profile");
$sth->execute ( );
my $count = 0;
while (my @val = $sth->fetchrow_array ( ))
{
 print "id: $val[0], name: $val[1], cats: $val[2]
";
 ++$count;
}
$sth->finish ( );
print "$count rows were returned
";

The row-fetching loop just shown is followed by a call to finish( ), which closes the result set and tells the server that it can free any resources associated with it. You don't actually need to call finish( ) if you fetch every row in the set, because DBI notices when you've reached the last row and releases the set for itself. Thus, the example could have omitted the finish( ) call without ill effect. It's more important to invoke finish( ) explicitly if you fetch only part of a result set.

The example illustrates that if you want to know how many rows a result set contains, you should count them yourself while you're fetching them. Do not use the DBI rows( ) method for this purpose; the DBI documentation discourages this practice. (The reason is that it is not necessarily reliable for SELECT statementsnot because of some deficiency in DBI, but because of differences in the behavior of various database engines.)

DBI has several functions that can be used to obtain a row at a time in a row-fetching loop. The one used in the previous example, fetchrow_array( ), returns an array containing the next row, or an empty list when there are no more rows. Elements of the array are accessed as $val[0], $val[1], ..., and are present in the array in the same order they are named in the SELECT statement. This function is most useful for queries that explicitly name columns to selected. (If you retrieve columns with SELECT *, there are no guarantees about the positions of columns within the array.)

fetchrow_arrayref( ) is like fetchrow_array( ), except that it returns a reference to the array, or undef when there are no more rows. Elements of the array are accessed as $ref->[0], $ref->[1], and so forth. As with fetchrow_array( ), the values are present in the order named in the query:

my $sth = $dbh->prepare ("SELECT id, name, cats FROM profile");
$sth->execute ( );
my $count = 0;
while (my $ref = $sth->fetchrow_arrayref ( ))
{
 print "id: $ref->[0], name: $ref->[1], cats: $ref->[2]
";
 ++$count;
}
print "$count rows were returned
";

fetchrow_hashref( ) returns a reference to a hash structure, or undef when there are no more rows:

my $sth = $dbh->prepare ("SELECT id, name, cats FROM profile");
$sth->execute ( );
my $count = 0;
while (my $ref = $sth->fetchrow_hashref ( ))
{
 print "id: $ref->{id}, name: $ref->{name}, cats: $ref->{cats}
";
 ++$count;
}
print "$count rows were returned
";

The elements of the hash are accessed using the names of the columns that are selected by the query ($ref->{id}, $ref->{name}, and so forth). fetchrow_hashref( ) is particularly useful for SELECT * queries, because you can access elements of rows without knowing anything about the order in which columns are returned. You just need to know their names. On the other hand, it's more expensive to set up a hash than an array, so fetchrow_hashref( ) is slower than fetchrow_array( ) or fetchrow_arrayref( ). It's also possible to "lose" row elements if they have the same name, because column names must be unique. The following query selects two values, but fetchrow_hashref( ) would return a hash structure containing a single element named id:

SELECT id, id FROM profile

To avoid this problem, you can use column aliases to ensure that like-named columns have distinct names in the result set. The following query retrieves the same columns as the previous query, but gives them the distinct names id and id2:

SELECT id, id AS id2 FROM profile

Admittedly, this query is pretty silly, but if you're retrieving columns from multiple tables, you may very easily run into the problem of having columns in the result set that have the same name. An example where this occurs may be seen in Recipe 12.4.

In addition to the methods for performing the query execution process just described, DBI provides several high-level retrieval methods that issue a query and return the result set in a single operation. These all are database handle methods that take care of creating and disposing of the statement handle internally before returning the result set. Where the methods differ is the form in which they return the result. Some return the entire result set, others return a single row or column of the set, as summarized in the following table:[5]

[5] selectrow_arrayref( ) and selectall_hashref( ) require DBI 1.15 or newer. selectrow_hashref( ) requires DBI 1.20 or newer (it was present a few versions before that, but with a different behavior than it uses now).

Method	Return value
selectrow_array( )	First row of result set as an array
selectrow_arrayref( )	First row of result set as a reference to an array
selectrow_hashref( )	First row of result set as a reference to a hash
selectcol_arrayref( )	First column of result set as a reference to an array
selectall_arrayref( )	Entire result set as a reference to an array of array references
selectall_hashref( )	Entire result set as a reference to a hash of hash references

Most of these methods return a reference. The exception is selectrow_array( ), which selects the first row of the result set and returns an array or a scalar, depending on how you call it. In array context, selectrow_array( ) returns the entire row as an array (or the empty list if no row was selected). This is useful for queries from which you expect to obtain only a single row:

my @val = $dbh->selectrow_array (
 "SELECT name, birth, foods FROM profile WHERE id = 3");

When selectrow_array( ) is called in array context, the return value can be used to determine the size of the result set. The column count is the number of elements in the array, and the row count is 1 or 0:

my $ncols = @val;
my $nrows = ($ncols ? 1 : 0);

You can also invoke selectrow_array( ) in scalar context, in which case it returns only the first column from the row. This is especially convenient for queries that return a single value:

my $buddy_count = $dbh->selectrow_array ("SELECT COUNT(*) FROM profile");

If a query returns no result, selectrow_array( ) returns an empty array or undef, depending on whether you call it in array or scalar context.

selectrow_arrayref( ) and selectrow_hashref( ) select the first row of the result set and return a reference to it, or undef if no row was selected. To access the column values, treat the reference the same way you treat the return value from fetchrow_arrayref( ) or fetchrow_hashref( ). You can also use the reference to get the row and column counts:

my $ref = $dbh->selectrow_arrayref ($query);
my $ncols = (defined ($ref) ? @{$ref} : 0);
my $nrows = ($ncols ? 1 : 0);

my $ref = $dbh->selectrow_hashref ($query);
my $ncols = (defined ($ref) ? keys (%{$ref}) : 0);
my $nrows = ($ncols ? 1 : 0);

With selectcol_arrayref( ), a reference to a single-column array is returned, representing the first column of the result set. Assuming a non-undef return value, elements of the array are accessed as $ref->[i] for the value from row i. The number of rows is the number of elements in the array, and the column count is 1 or 0:

my $ref = $dbh->selectcol_arrayref ($query);
my $nrows = (defined ($ref) ? @{$ref} : 0);
my $ncols = ($nrows ? 1 : 0);

selectall_arrayref( ) returns a reference to an array, where the array contains an element for each row of the result. Each of these elements is a reference to an array. To access row i of the result set, use $ref->[i] to get a reference to the row. Then treat the row reference the same way as a return value from fetchrow_arrayref( ) to access individual column values in the row. The result set row and column counts are available as follows:

my $ref = $dbh->selectall_arrayref ($query);
my $nrows = (defined ($ref) ? @{$ref} : 0);
my $ncols = ($nrows ? @{$ref->[0]} : 0);

selectall_hashref( ) is somewhat similar to selectall_arrayref( ), but returns a reference to a hash, each element of which is a hash reference to a row of the result. To call it, specify an argument that indicates which column to use for hash keys. For example, if you're retrieving rows from the profile table, the PRIMARY KEY is the id column:

my $ref = $dbh->selectall_hashref ("SELECT * FROM profile", "id");

Then access rows using the keys of the hash. For example, if one of the rows has a key column value of 12, the hash reference for the row is accessed as $ref->{12}. That value is keyed on column names, which you can use to access individual column elements (for example, $ref->{12}->{name}). The result set row and column counts are available as follows:

my @keys = (defined ($ref) ? keys (%{$ref}) : ( ));
my $nrows = scalar (@keys);
my $ncols = ($nrows ? keys (%{$ref->{$keys[0]}}) : 0);

The selectall_XXX( ) methods are useful when you need to process a result set more than once, because DBI provides no way to "rewind" a result set. By assigning the entire result set to a variable, you can iterate through its elements as often as you please.

Take care when using the high-level methods if you have RaiseError disabled. In that case, a method's return value may not always allow you to distinguish an error from an empty result set. For example, if you call selectrow_array( ) in scalar context to retrieve a single value, an undef return value is particularly ambiguous because it may indicate any of three things: an error, an empty result set, or a result set consisting of a single NULL value. If you need to test for an error, you can check the value of $DBI::errstr or $DBI::err.

2.5.6 PHP

PHP doesn't have separate functions for issuing queries that return result sets and those that do not. Instead, there is a single function mysql_query( ) for all queries. mysql_query( ) takes a query string and an optional connection identifier as arguments, and returns a result identifier. If you leave out the connection identifier argument, mysql_query( ) uses the most recently opened connection by default. The first statement below uses an explicit identifier; the second uses the default connection:

$result_id = mysql_query ($query, $conn_id);
$result_id = mysql_query ($query);

If the query fails, $result_id will be FALSE. This means that an error occurred because your query was bad: it was syntactically invalid, you didn't have permission to access a table named in the query, or some other problem prevented the query from executing. A FALSE return value does not mean that the query affected 0 rows (for a DELETE, INSERT, or UPDATE) or returned rows (for a SELECT).

If $result_id is not FALSE, the query executed properly. What you do at that point depends on the type of query. For queries that don't return rows, $result_id will be TRUE, and the query has completed. If you want, you can call mysql_affected_rows( ) to find out how many rows were changed:

$result_id = mysql_query ("DELETE FROM profile WHERE cats = 0", $conn_id);
if (!$result_id)
 die ("Oops, the query failed");
print (mysql_affected_rows ($conn_id) . " rows were deleted
");

mysql_affected_rows( ) takes the connection identifier as its argument. If you omit the argument, the current connection is assumed.

For queries that return a result set, mysql_query( ) returns a nonzero result identifier. Generally, you use this identifier to call a row-fetching function in a loop, then call mysql_free_result( ) to release the result set. The result identifier is really nothing more than a number that tells PHP which result set you're using. This identifier is not a count of the number of rows selected, nor does it contain the contents of any of those rows. Many beginning PHP programmers make the mistake of thinking mysql_query( ) returns a row count or a result set, but it doesn't. Make sure you're clear on this point and you'll save yourself a lot of trouble.

Here's an example that shows how to run a SELECT query and use the result identifier to fetch the rows:

$result_id = mysql_query ("SELECT id, name, cats FROM profile", $conn_id);
if (!$result_id)
 die ("Oops, the query failed");
while ($row = mysql_fetch_row ($result_id))
 print ("id: $row[0], name: $row[1], cats: $row[2]
");
print (mysql_num_rows ($result_id) . " rows were returned
");
mysql_free_result ($result_id);

The example demonstrates that you obtain the rows in the result set by executing a loop in which you pass the result identifier to one of PHP's row-fetching functions. To obtain a count of the number of rows in a result set, pass the result identifier to mysql_num_rows( ). When there are no more rows, pass the identifier to mysql_free_result( ) to close the result set. (After you call mysql_free_result( ), don't try to fetch a row or get the row count, because at that point $result_id is no longer valid.)

Each PHP row-fetching function returns the next row of the result set indicated by $result_id, or FALSE when there are no more rows. Where they differ is in the data type of the return value. The function shown in the preceding example, mysql_fetch_row( ), returns an array whose elements correspond to the columns selected by the query and are accessed using numeric subscripts. mysql_fetch_array( ) is like mysql_fetch_row( ), but the array it returns also contains elements that can be accessed using the names of the selected columns. In other words, you can access each column using either its numeric position or its name:

$result_id = mysql_query ("SELECT id, name, cats FROM profile", $conn_id);
if (!$result_id)
 die ("Oops, the query failed");
while ($row = mysql_fetch_array ($result_id))
{
 print ("id: $row[0], name: $row[1], cats: $row[2]
");
 print ("id: $row[id], name: $row[name], cats: $row[cats]
");
}
print (mysql_num_rows ($result_id) . " rows were returned
");
mysql_free_result ($result_id);

Despite what you might expect, mysql_fetch_array( ) is not appreciably slower than mysql_fetch_row( ), even though the array it returns contains more information.

The previous example does not quote the non-numeric element names because they appear inside a quoted string. Should you refer to the elements outside of a string, the element names should be quoted:

printf ("id: %s, name: %s, cats: %s
",
 $row["id"], $row["name"], $row["cats"]);

mysql_fetch_object( ) returns an object having members that correspond to the columns selected by the query and that are accessed using the column names:

$result_id = mysql_query ("SELECT id, name, cats FROM profile", $conn_id);
if (!$result_id)
 die ("Oops, the query failed");
while ($row = mysql_fetch_object ($result_id))
 print ("id: $row->id, name: $row->name, cats: $row->cats
");
print (mysql_num_rows ($result_id) . " rows were returned
");
mysql_free_result ($result_id);

PHP 4.0.3 adds a fourth row-fetching function, mysql_fetch_assoc( ), that returns an array containing elements that are accessed by name. In other words, it is like mysql_fetch_array( ), except that the row does not contain the values accessed by numeric index.

Don t Use count() to Get a Column Count in PHP 3

PHP programmers sometimes fetch a result set row and then use count($row) to determine how many values the row contains. It's preferable to use mysql_num_fields( ) instead, as you can see for yourself by executing the following fragment of PHP code:

if (!($result_id = mysql_query ("SELECT 1, 0, NULL", $conn_id)))
 die ("Cannot issue query
");
$count = mysql_num_fields ($result_id);
print ("The row contains $count columns
");
if (!($row = mysql_fetch_row ($result_id)))
 die ("Cannot fetch row
");
$count = count ($row);
print ("The row contains $count columns
");

If you run the code under PHP 3, you'll find that count( ) returns 2. With PHP 4, count( ) returns 3. These differing results occur because count( ) counts array values that correspond to NULL values in PHP 4, but not in PHP 3. By contrast, mysql_field_count( ) uniformly returns 3 for both versions of PHP. The moral is that count( ) won't necessarily give you an accurate value. Use mysql_field_count( ) if you want to know the true column count.

2.5.7 Python

The Python DB-API interface does not have distinct calls for queries that return a result set and those that do not. To process a query in Python, use your database connection object to get a cursor object.[6] Then use the cursor's execute( ) method to send the query to the server. If there is no result set, the query is completed, and you can use the cursor's rowcount attribute to determine how many records were changed:[7]

[6] If you're familiar with the term "cursor" as provided on the server side in some databases, MySQL doesn't really provide cursors the same way. Instead, the MySQLdb module emulates cursors on the client side of query execution.

[7] Note that rowcount is an attribute, not a function. Refer to it as rowcount, not rowcount( ), or an exception will be raised.

try:
 cursor = conn.cursor ( )
 cursor.execute ("UPDATE profile SET cats = cats+1 WHERE name = 'Fred'")
 print "%d rows were updated" % cursor.rowcount
except MySQLdb.Error, e:
 print "Oops, the query failed"
 print e

If the query does return a result set, fetch its rows and close the set. DB-API provides a couple of methods for retrieving rows. fetchone( ) returns the next row as a sequence (or None when there are no more rows):

try:
 cursor = conn.cursor ( )
 cursor.execute ("SELECT id, name, cats FROM profile")
 while 1:
 row = cursor.fetchone ( )
 if row == None:
 break
 print "id: %s, name: %s, cats: %s" % (row[0], row[1], row[2])
 print "%d rows were returned" % cursor.rowcount
 cursor.close ( )
except MySQLdb.Error, e:
 print "Oops, the query failed"
 print e

As you can see from the preceding example, the rowcount attribute is useful for SELECT queries, too; it indicates the number of rows in the result set.

Another row-fetching method, fetchall( ), returns the entire result set as a sequence of sequences. You can iterate through the sequence to access the rows:

try:
 cursor = conn.cursor ( )
 cursor.execute ("SELECT id, name, cats FROM profile")
 rows = cursor.fetchall ( )
 for row in rows:
 print "id: %s, name: %s, cats: %s" % (row[0], row[1], row[2])
 print "%d rows were returned" % cursor.rowcount
 cursor.close ( )
except MySQLdb.Error, e:
 print "Oops, the query failed"
 print e

Like DBI, DB-API doesn't provide any way to rewind a result set, so fetchall( ) can be convenient when you need to iterate through the rows of the result set more than once or access individual values directly. For example, if rows holds the result set, you can access the value of the third column in the second row as rows[1][2] (indexes begin at 0, not 1).

To access row values by column name, specify the DictCursor cursor type when you create the cursor object. This causes rows to be returned as Python dictionary objects with named elements:

try:
 cursor = conn.cursor (MySQLdb.cursors.DictCursor)
 cursor.execute ("SELECT id, name, cats FROM profile")
 for row in cursor.fetchall ( ):
 print "id: %s, name: %s, cats: %s" 
 % (row["id"], row["name"], row["cats"])
 print "%d rows were returned" % cursor.rowcount
 cursor.close ( )
except MySQLdb.Error, e:
 print "Oops, the query failed"
 print e

2.5.8 Java

The JDBC interface provides specific object types for the various phases of query processing. Queries are issued in JDBC by passing SQL strings to Java objects of one type. The results, if there are any, are returned as objects of another type. Problems that occur while accessing the database cause exceptions to be thrown.

To issue a query, the first step is to get a Statement object by calling the createStatement( ) method of your Connection object:

Statement s = conn.createStatement ( );

Then use the Statement object to send the query to the server. JDBC provides several methods for doing this. Choose the one that's appropriate for the type of statement you want to issue: executeUpdate( ) for statements that don't return a result set, executeQuery( ) for statements that do, and execute( ) when you don't know.

The executeUpdate( ) method sends a query that generates no result set to the server and returns a count indicating the number of rows that were affected. When you're done with the statement object, close it. The following example illustrates this sequence of events:

try
{
 Statement s = conn.createStatement ( );
 int count = s.executeUpdate ("DELETE FROM profile WHERE cats = 0");
 s.close ( ); // close statement
 System.out.println (count + " rows were deleted");
}
catch (Exception e)
{
 Cookbook.printErrorMessage (e);
}

For statements that return a result set, use executeQuery( ). Then get a result set object and use it to retrieve the row values. When you're done, close both the result set and statement objects:

try
{
 Statement s = conn.createStatement ( );
 s.executeQuery ("SELECT id, name, cats FROM profile");
 ResultSet rs = s.getResultSet ( );
 int count = 0;
 while (rs.next ( )) // loop through rows of result set
 {
 int id = rs.getInt (1); // extract columns 1, 2, and 3
 String name = rs.getString (2);
 int cats = rs.getInt (3);
 System.out.println ("id: " + id
 + ", name: " + name
 + ", cats: " + cats);
 ++count;
 }
 rs.close ( ); // close result set
 s.close ( ); // close statement
 System.out.println (count + " rows were returned");
}
catch (Exception e)
{
 Cookbook.printErrorMessage (e);
}

The ResultSet object returned by the getResultSet( ) method of your Statement object has a number of methods of its own, such as next( ) to fetch rows and various getXXX( ) methods that access columns of the current row. Initially the result set is positioned just before the first row of the set. Call next( ) to fetch each row in succession until it returns false, indicating that there are no more rows. To determine the number of rows in a result set, count them yourself, as shown in the preceding example.

Column values are accessed using methods such as getInt( ), getString( ), getFloat( ), and getDate( ). To obtain the column value as a generic object, use getObject( ). The getXXX( ) calls can be invoked with an argument indicating either column position (beginning at 1, not 0) or column name. The previous example shows how to retrieve the id, name, and cats columns by position. To access columns by name instead, the row-fetching loop of that example can be rewritten as follows:

while (rs.next ( )) // loop through rows of result set
{
 int id = rs.getInt ("id");
 String name = rs.getString ("name");
 int cats = rs.getInt ("cats");
 System.out.println ("id: " + id
 + ", name: " + name
 + ", cats: " + cats);
 ++count;
}

You can retrieve a given column value using any getXXX( ) call that makes sense for the column type. For example, you can use getString( ) to retrieve any column value as a string:

String id = rs.getString ("id");
String name = rs.getString ("name");
String cats = rs.getString ("cats");
System.out.println ("id: " + id
 + ", name: " + name
 + ", cats: " + cats);

Or you can use getObject( ) to retrieve values as generic objects and convert the values as necessary. The following code uses toString( ) to convert object values to printable form:

Object id = rs.getObject ("id");
Object name = rs.getObject ("name");
Object cats = rs.getObject ("cats");
System.out.println ("id: " + id.toString ( )
 + ", name: " + name.toString ( )
 + ", cats: " + cats.toString ( ));

To find out how many columns are in each row, access the result set's metadata. The following code uses the column count to print each row's columns as a comma-separated list of values:

try
{
 Statement s = conn.createStatement ( );
 s.executeQuery ("SELECT * FROM profile");
 ResultSet rs = s.getResultSet ( );
 ResultSetMetaData md = rs.getMetaData ( ); // get result set metadata
 int ncols = md.getColumnCount ( ); // get column count from metadata
 int count = 0;
 while (rs.next ( )) // loop through rows of result set
 {
 for (int i = 0; i < ncols; i++) // loop through columns
 {
 String val = rs.getString (i+1);
 if (i > 0)
 System.out.print (", ");
 System.out.print (val);
 }
 System.out.println ( );
 ++count;
 }
 rs.close ( ); // close result set
 s.close ( ); // close statement
 System.out.println (count + " rows were returned");
}
catch (Exception e)
{
 Cookbook.printErrorMessage (e);
}

The third JDBC query-executing method, execute( ), works for either type of query. It's particularly useful when you receive a query string from an external source and don't know whether or not it generates a result set. The return value from execute( ) indicates the query type so that you can process it appropriately: if execute( ) returns true, there is a result set, otherwise not. Typically you'd use it something like this, where queryStr represents an arbitrary SQL statement:

try
{
 Statement s = conn.createStatement ( );
 if (s.execute (queryStr))
 {
 // there is a result set
 ResultSet rs = s.getResultSet ( );

 // ... process result set here ...

 rs.close ( ); // close result set
 }
 else
 {
 // there is no result set, just print the row count
 System.out.println (s.getUpdateCount ( )
 + " rows were affected");
 }
 s.close ( ); // close statement
}
catch (Exception e)
{
 Cookbook.printErrorMessage (e);
}

Closing JDBC Statement and Result Set Objects

The JDBC query-issuing examples in this section close the statement and result set objects explicitly when they are done with those objects. Some Java implementations close them automatically when you close the connection. However, buggy implementations may fail to do this properly, so it's best not to rely on that behavior. Close the objects yourself when you're done with them to avoid difficulties.