Recipe 2.4. Issuing Statements and Retrieving Results
ProblemYou want your program to send an SQL statement to the MySQL server and retrieve whatever result it produces. SolutionSome statements return only a status code; others return a result set (a set of rows). Some APIs provide different methods for issuing each type of statement. If so, use the method that's appropriate for the statement to be executed. DiscussionThere are two general categories of SQL statements that you can execute. Some statements retrieve information from the database; others make changes to that information. These two types of statements are handled differently. In addition, some APIs provide several different routines for issuing statements, which complicates matters further. Before we get to the examples demonstrating how to issue statements from within each API, I'll show the database table that the examples use, and then further discuss the two statement categories and outline a general strategy for processing statements in each category. In Chapter 1, we created a table named limbs to try some sample statements. 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 definition: 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 indicates the things that are important to us about each buddy: name, age, favorite color, favorite foods, and number of catsobviously one of those goofy tables that are used only for examples in a book![
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. (Section 6.11 discusses age calculations.) 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, and 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, and then restore it if you change its contents by running the script again. (See the last recipe of this chapter on the importance of restoring the profile table.) The initial contents of the profile table as 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 | +----+---------+------------+-------+-----------------------+------+ Although most of the columns in the profile table allow NULL values, none of the rows in the sample dataset actually contain NULL yet. That is because NULL values complicate statement processing a bit and I don't want to deal with those complications until we get to Recipes Section 2.5 and Section 2.7. SQL statement categoriesSQL statements can be divided into two broad categories:
The first step in processing an SQL statement is to send it to the MySQL server for execution. Some APIs (those for Perl, Ruby, and Java, for example) recognize a distinction between the two categories of statements and provide separate calls for executing them. Other APIs (such as those for PHP and Python) have a single call that can be used for any statement. However, one thing all APIs have in common is that you don't use any special character to indicate the end of the statement. No terminator is necessary because the end of the statement string implicitly terminates the statement. This differs from the way that you issue statements in the mysql program, where you terminate statements using a semicolon (;) or \g. (It also differs from the way that I normally show the syntax for SQL statements in examples, because usually I include semicolons to make it clear where statements end.) When you send a statement to the server, you should be prepared to handle errors if it did not execute successfully. Do not neglect to do this! If a statement fails and you proceed on the basis that it succeeded, your program won't work. For the most part, error-checking code is not shown in this section, but that is for brevity. The sample scripts in the recipes distribution from which the examples are taken do include error handling, based on the techniques illustrated in Section 2.2. If a statement does execute without error, your next step depends on the type of statement 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 statement. If the statement does return a result set, you can fetch its rows, and then close the result set. If you are executing a statement in a context where you don't necessarily know whether it returns a result set, Section 9.3 discusses how to tell. PerlThe Perl DBI module provides two basic approaches to SQL statement execution, depending on whether you expect to get back a result set. To issue a statement such as INSERT or UPDATE that returns no result set, use the database handle do( ) method. It executes the statement and returns the number of rows affected by it, or undef if an error occurs. For example, if Fred gets a new cat, the following statement 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 "Number of rows updated: $count\n"; } If the statement executes successfully but affects no rows, do( ) returns a special value, "0E0" (that is, the value zero in scientific notation, expressed as a string). "0E0" can be used for testing the execution status of a statement because it is true in Boolean contexts (unlike undef). For successful statements, 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 that doesn't happen: add zero to the value to explicitly coerce 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 cats = cats+1 WHERE name = 'Fred'"); if ($count) # print row count if no error occurred { printf "Number of rows updated: %d\n", $count; } If RaiseError is enabled, your script terminates automatically if a DBI-related error occurs, so you don't need to bother checking $count to see whether do( ) failed. In that case, you can simplify the code somewhat: my $count = $dbh->do ("UPDATE profile SET cats = cats+1 WHERE name = 'Fred'"); printf "Number of rows updated: %d\n", $count; To process a statement such as SELECT that does return a result set, use a different approach that involves several steps:
The following example illustrates these steps, using fetchrow_array( ) as the row-fetching method and assuming that RaiseError is enabled so that errors terminate the script: 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 "Number of rows returned: $count\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 you. The only time it's important to invoke finish( ) explicitly is when you don't fetch the entire result set. Thus, the example could have omitted the finish( ) call without ill effect. 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 rows( ) 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 methods that fetch a row at a time. 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. Array elements are accessed as $val[0], $val[1], ..., and are present in the array in the same order they are named in the SELECT statement. The size of the array tells you how many columns the result set has. The fetchrow_array( ) method is most useful for statements that explicitly name the columns to select. (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. Array elements are accessed as $ref->[0], $ref->[1], and so forth. As with fetchrow_array( ), the values are present in the order named in the statement: 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 "Number of rows returned: $count\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 "Number of rows returned: $count\n"; To access the elements of the hash, use the names of the columns that are selected by the statement ($ref->{id}, $ref->{name}, and so forth). fetchrow_hashref( ) is particularly useful for SELECT * statements 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 statement 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 statement retrieves the same columns as the previous statement, but gives them the distinct names id and id2: SELECT id, id AS id2 FROM profile Admittedly, this statement is pretty silly. However, if you're retrieving columns from multiple tables, you can 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 Section 12.16. In addition to the methods for performing the statement execution process just described, DBI provides several high-level retrieval methods that issue a statement and return the result set in a single operation. All of these are database handle methods that create and dispose 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:
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 statements 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 statements that return a single value: my $buddy_count = $dbh->selectrow_array ("SELECT COUNT(*) FROM profile"); If a statement 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 ($stmt); my $ncols = (defined ($ref) ? @{$ref} : 0); my $nrows = ($ncols ? 1 : 0); my $ref = $dbh->selectrow_hashref ($stmt); 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 ($stmt); 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 ($stmt); 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 row 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 various selectall_ XXX ( ) methods are useful when you need to process a result set more than once because Perl DBI provides no way to "rewind" a result set. By assigning the entire result set to a variable, you can iterate through its elements multiple times. Take care when using the high-level methods if you have RaiseError disabled. In that case, a method's return value may not always enable 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, $DBI::err, or $DBI::state. RubyAs with Perl DBI, Ruby DBI provides two approaches to SQL statement execution. With either approach, if a statement-execution method fails with an error, it raises an exception. For statements such as INSERT or UPDATE that return no result set, invoke the do database handle method. Its return value indicates the number of rows affected: count = dbh.do("UPDATE profile SET cats = cats+1 WHERE name = 'Fred'") puts "Number of rows updated: #{count}" For statements such as SELECT that return a result set, invoke the execute database handle method. execute returns a statement handle to use for fetching the rows of the result set. The statement handle has several methods of its own that enable row fetching to be done in different ways. After you are done with the statement handle, invoke its finish method. (Unlike Perl DBI, where it is necessary to invoke finish only if you do not fetch the entire result set, in Ruby you should call finish for every statement handle that you create.) To determine the number of rows in the result set, count them as you fetch them. The following example executes a SELECT statement and uses the statement handle's fetch method in a while loop: count = 0 sth = dbh.execute("SELECT id, name, cats FROM profile") while row = sth.fetch do printf "id: %s, name: %s, cats: %s\n", row[0], row[1], row[2] count += 1 end sth.finish puts "Number of rows returned: #{count}" You can also use fetch as an iterator that returns each row in turn: count = 0 sth = dbh.execute("SELECT id, name, cats FROM profile") sth.fetch do |row| printf "id: %s, name: %s, cats: %s\n", row[0], row[1], row[2] count += 1 end sth.finish puts "Number of rows returned: #{count}" In iterator context (such as just shown), the each method is a synonym for fetch. The fetch method returns DBI::Row objects. As just shown, you can access column values within the row by position (beginning with 0). DBI::Row objects also provide access to columns by name: sth.fetch do |row| printf "id: %s, name: %s, cats: %s\n", row["id"], row["name"], row["cats"] end To fetch all rows at once, use fetch_all, which returns an array of DBI::Row objects: sth = dbh.execute("SELECT id, name, cats FROM profile") rows = sth.fetch_all sth.finish rows.each do |row| printf "id: %s, name: %s, cats: %s\n", row["id"], row["name"], row["cats"] end row.size tells you the number of columns in the result set. To fetch each row as a hash keyed on column names, use the fetch_hash method. It can be called in a loop or used as an iterator. The following example shows the iterator approach: count = 0 sth = dbh.execute("SELECT id, name, cats FROM profile") sth.fetch_hash do |row| printf "id: %s, name: %s, cats: %s\n", row["id"], row["name"], row["cats"] count += 1 end sth.finish puts "Number of rows returned: #{count}" The preceding examples invoke execute to get a statement handle and then invoke finish when that handle is no longer needed. Another way to invoke execute is with a code block. In this case, it passes the statement handle to the block and invokes finish on that handle automatically. For example: count = 0 dbh.execute("SELECT id, name, cats FROM profile") do |sth| sth.fetch do |row| printf "id: %s, name: %s, cats: %s\n", row[0], row[1], row[2] count += 1 end end puts "Number of rows returned: #{count}" Ruby DBI has some higher-level database handle methods for executing statements to produce result sets:
PHPIn PHP, PEAR DB doesn't have separate methods for issuing statements that return result sets and those that do not. Instead, the query( ) method executes any kind of statement. query( ) takes a statement string argument and returns a result value that you can test with PEAR::isError( ) to determine whether the statement failed. If PEAR::isError( ) is true, it means that your statement was bad: it was syntactically invalid, you didn't have permission to access a table named in the statement, or some other problem prevented the statement from executing. If the statement executed properly, what you do at that point depends on the type of statement. For statements such as INSERT or UPDATE that don't return rows, the statement has completed. If you want, you can call the connection object's affectedRows( ) method to find out how many rows were changed: $result =& $conn->query ("UPDATE profile SET cats = cats+1 WHERE name = 'Fred'"); if (PEAR::isError ($result)) die ("Oops, the statement failed"); printf ("Number of rows updated: %d\n", $conn->affectedRows ()); For statements such as SELECT that return a result set, the query( ) method returns a result set object. Generally, you use this object to call a row-fetching method in a loop, and then call its free( ) method to release the result set. Here's an example that shows how to issue a SELECT statement and use the result set object to fetch the rows: $result =& $conn->query ("SELECT id, name, cats FROM profile"); if (PEAR::isError ($result)) die ("Oops, the statement failed"); while ($row =& $result->fetchRow (DB_FETCHMODE_ORDERED)) print ("id: $row[0], name: $row[1], cats: $row[2]\n"); printf ("Number of rows returned: %d\n", $result->numRows ()); $result->free (); The example demonstrates several methods that result set objects have:
The fetchRow( ) method returns the next row of the result set or NULL when there are no more rows. fetchRow( ) takes an argument that indicates what type of value it should return. As shown in the preceding example, with an argument of DB_FETCHMODE_ORDERED, fetchRow( ) returns an array with elements that correspond to the columns selected by the statement and that are accessed using numeric subscripts. The size of the array indicates the number of columns in the result set. With an argument of DB_FETCHMODE_ASSOC, fetchRow( ) returns an associative array containing values that are accessed by column name: $result =& $conn->query ("SELECT id, name, cats FROM profile"); if (PEAR::isError ($result)) die ("Oops, the statement failed"); while ($row =& $result->fetchRow (DB_FETCHMODE_ASSOC)) { printf ("id: %s, name: %s, cats: %s\n", $row["id"], $row["name"], $row["cats"]); } printf ("Number of rows returned: %d\n", $result->numRows ()); $result->free (); With an argument of DB_FETCHMODE_OBJECT, fetchRow( ) returns an object having members that are accessed using the column names: $result =& $conn->query ("SELECT id, name, cats FROM profile"); if (PEAR::isError ($result)) die ("Oops, the statement failed"); while ($row =& $result->fetchRow (DB_FETCHMODE_OBJECT)) print ("id: $row->id, name: $row->name, cats: $row->cats\n"); printf ("Number of rows returned: %d\n", $result->numRows ()); $result->free (); If you invoke fetchRow( ) without an argument, it uses the default fetch mode. Normally, this is DB_FETCHMODE_ORDERED unless you have changed it by calling the setFetchMode( ) connection object method. For example, to use DB_FETCHMODE_ASSOC as the default fetch mode for a connection, do this: $conn->setFetchMode (DB_FETCHMODE_ASSOC); PythonThe Python DB-API interface does not have distinct calls for SQL statements that return a result set and those that do not. To process a statement in Python, use your database connection object to get a cursor object. Then use the cursor's execute( ) method to send the statement to the server. If the statement fails with an error, execute( ) raises an exception. Otherwise, if there is no result set, the statement is completed, and you can use the cursor's rowcount attribute to determine how many rows were changed: cursor = conn.cursor () cursor.execute ("UPDATE profile SET cats = cats+1 WHERE name = 'Fred'") print "Number of rows updated: %d" % cursor.rowcount Note that rowcount is an attribute, not a method. Refer to it as rowcount, not rowcount( ), or an exception will be raised. NOTE
The Python DB-API specification indicates that database connections should begin with auto-commit mode disabled, so MySQLdb disables auto-commit when it connects to the MySQL server. One implication is that if you use transactional tables, modifications to them will be rolled back when you close the connection unless you commit the changes first. Changes to nontransactional tables such as MyISAM tables are committed automatically, so this issue does not arise. For more information on auto-commit mode, see Chapter 15 (Section 15.7 in particular). If the statement does return a result set, fetch its rows, and then 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): 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 "Number of rows returned: %d" % cursor.rowcount cursor.close () As you can see from the preceding example, the rowcount attribute is useful for SELECT statements, too; it indicates the number of rows in the result set. len(row) tells you the number of columns in the result set. Another row-fetching method, fetchall( ), returns the entire result set as a sequence of row sequences. You can iterate through the sequence to access the rows: 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 "Number of rows returned: %d" % cursor.rowcount cursor.close () 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: 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 "Number of rows returned: %d" % cursor.rowcount cursor.close () The preceding example also demonstrates how to use fetch_all( ) directly as an iterator. JavaThe JDBC interface provides specific object types for the various phases of SQL statement processing. Statements are issued in JDBC by using Java objects of one type. The results, if there are any, are returned as objects of another type. To issue a statement, the first step is to get a Statement object by calling the createStatement( ) method of your Connection object: Statement s = conn.createStatement (); Now use the Statement object to send the statement 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. Each method raises an exception if the statement fails with an error. The executeUpdate( ) method sends a statement 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: Statement s = conn.createStatement (); int count = s.executeUpdate ( "UPDATE profile SET cats = cats+1 WHERE name = 'Fred'"); s.close (); // close statement System.out.println ("Number of rows updated: " + count); 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 the result set and statement objects: 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 ("Number of rows returned: " + count); 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 get XXX ( ) 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, which means 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. To access column values, use methods such as getInt( ), getString( ), getFloat( ), and geTDate( ). To obtain the column value as a generic object, use getObject( ). The get XXX ( ) calls can be invoked with an argument that indicates 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 get XXX ( ) call that makes sense for the data 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 example 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 the result set, access the result set's metadata: ResultSet rs = s.getResultSet (); ResultSetMetaData md = rs.getMetaData (); // get result set metadata int ncols = md.getColumnCount (); // get column count from metadata The third JDBC statement-execution method, execute( ), works for either type of statement. It's particularly useful when you receive a statement string from an external source and don't know whether it generates a result set. The return value from execute( ) indicates the statement 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 stmtStr represents an arbitrary SQL statement: Statement s = conn.createStatement (); if (s.execute (stmtStr)) { // 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 ("Number of rows affected: " + s.getUpdateCount ()); } s.close (); // close statement |
] The table also includes an id column containing unique values so that we can distinguish rows from each other, even if two buddies have the same name. id and name are declared as NOT NULL because they're each required to have a value. The other columns are allowed to be NULL (and that is implicitly their default value) because we might not know the value to put into them for any given individual. That is, we'll use NULL to signify "unknown." 
] Actually, its not that goofy. The table uses several different data types for its columns, and these come in handy later for illustrating how to solve problems that pertain to specific data types.