2.5 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 cats�obviously
one of those goofy tables that are used only for examples in a
book! 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.
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.
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.
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\n";
}
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\n", $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\n", $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]\n";
++$count;
}
$sth->finish ( );
print "$count rows were returned\n";
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
statements�not 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]\n";
++$count;
}
print "$count rows were returned\n";
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}\n";
++$count;
}
print "$count rows were returned\n";
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:
|
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\n");
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]\n");
print (mysql_num_rows ($result_id) . " rows were returned\n");
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]\n");
print ("id: $row[id], name: $row[name], cats: $row[cats]\n");
}
print (mysql_num_rows ($result_id) . " rows were returned\n");
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\n",
$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\n");
print (mysql_num_rows ($result_id) . " rows were returned\n");
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.
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\n");
$count = mysql_num_fields ($result_id);
print ("The row contains $count columns\n");
if (!($row = mysql_fetch_row ($result_id)))
die ("Cannot fetch row\n");
$count = count ($row);
print ("The row contains $count columns\n");
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. 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:
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);
}
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.
|
| 
No comments:
Post a Comment