MySQL Cookbook (2007)

---

^[7^] If you make a copy of Connect.java to use as the basis for a new program, you’ll need to change the class name in the class statement to match the name of your new file.

Checking for Errors

Problem

Something went wrong with your program, and you don’t know what.

Solution

Everyone has problems getting programs to work correctly. But if you don’t anticipate difficulties by checking for errors, you make the job a lot harder. Add some error-checking code so that your programs can help you figure out what went wrong.

Discussion

After working through Connecting, Selecting a Database, and Disconnecting, you now know how to connect to the MySQL server. It’s also a good idea to know how to check for errors and how to retrieve specific error information from the API, so that’s what we’ll cover next. You’re probably anxious to see how to do more interesting things (such as issuing statements and getting back the results), but error checking is fundamentally important. Programs sometimes fail, especially during development, and if you don’t know how to determine why failures occur, you’ll be flying blind.

When errors occur, MySQL provides three values:

§ A MySQL-specific error number

§ A MySQL-specific descriptive text error message

§ An SQLSTATE error code that is a five-character value defined according to the ANSI and ODBC standards

Various sections in this recipe in this section show how to access this information. Most of the later recipes in this book that display error information print only the MySQL-specific values, but the recipes here show how to access the SQLSTATE value as well, for those APIs that expose it.

The example programs demonstrate how to check for errors but will in fact execute without any problems if your MySQL account is set up properly. Thus, you may have to modify the examples slightly to force errors to occur so that the error-handling statements are triggered. That is easy to do. For example, you can change a connection-establishment call to supply a bad password.

A general debugging aid that is not specific to any API is to check the MySQL server’s query log to see what statements the server actually is receiving. (This requires that you have query logging enabled and that you have access to the log.) The query log often will show you that a statement is malformed in a particular way and give you a clue that your program is not constructing the proper statement string. If you’re running a script under a web server and it fails, check the web server’s error log.

DON’T SHOOT YOURSELF IN THE FOOT: CHECK FOR ERRORS

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 people 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 when they occur.

Perl

The DBI module provides two attributes that control what happens when DBI method invocations fail:

§ PrintError, if enabled, causes DBI to print an error message using warn().

§ RaiseError, if enabled, causes DBI to print an error message usingdie(). This terminates your script.

By default, PrintError is enabled, and RaiseError is disabled, so a script continues executing after printing a message if an error occurs. Either or both attributes can be specified in the connect() call. Setting an attribute to 1 or 0 enables or disables it, respectively. To specify either or both attributes, pass them in a hash reference as the fourth argument to the connect() call.

The following code sets only the AutoCommit attribute and uses the default settings for the error-handling attributes. If the connect() call fails, this results in a warning message, but the script continues to execute:

my %conn_attrs = (AutoCommit => 1);

my $dbh = DBI->connect ($dsn, "cbuser", "cbpass", \%conn_attrs);

However, because you really can’t do much if the connection attempt fails, it’s often prudent to exit instead after DBI prints a message:

my %conn_attrs = (AutoCommit => 1);

my $dbh = DBI->connect ($dsn, "cbuser", "cbpass", \%conn_attrs)

or exit;

To print your own error messages, leave RaiseError disabled, and disable PrintError as well. Then test the results of DBI method calls yourself. When a method fails, the $DBI::err, $DBI::errstr, and $DBI::state variables contain the MySQL error number, a descriptive error string, and the SQLSTATE value, respectively:

my %conn_attrs = (PrintError => 0, AutoCommit => 1);

my $dbh = DBI->connect ($dsn, "cbuser", "cbpass", \%conn_attrs)

or die "Connection error: "

. "$DBI::errstr ($DBI::err/$DBI::state)\n";

If no error occurs, $DBI::err will be 0 or undef, $DBI::errstr will be the empty string or undef, and $DBI::state will be empty or 00000.

When you check for errors, access these variables immediately after invoking the DBI method that sets them. If you invoke another method before using them, their values will be reset.

The default settings (PrintError enabled, RaiseError disabled) are not so useful if you’re printing your own messages. In this case, DBI prints a message automatically, and then your script prints its own message. This is at best redundant, and at worst confusing to the person using the script.

If you enable RaiseError, you can call DBI methods without checking for return values that indicate errors. If a method fails, DBI prints an error and terminates your script. If the method returns, you can assume it succeeded. This is the easiest approach for script writers: let DBI do all the error checking! However, if PrintError and RaiseError both are enabled, DBI may call warn() and die() in succession, resulting in error messages being printed twice. To avoid this problem, it’s best to disable PrintError whenever you enable RaiseError. That’s the approach generally used in this book, as illustrated here:

my %conn_attrs = (PrintError => 0, RaiseError => 1, AutoCommit => 1);

my $dbh = DBI->connect ($dsn, "cbuser", "cbpass", \%conn_attrs);

If you don’t want the all-or-nothing behavior of enabling RaiseError for automatic error checking versus having to do all your own checking, you can adopt a mixed approach. Individual handles have PrintError and RaiseError attributes that can be enabled or disabled selectively. For example, you can enable RaiseError globally by turning it on when you call connect(), and then disable it selectively on a per-handle basis. Suppose that you have a script that reads the username and password from the command-line arguments, and then loops while the user enters statements to be executed. In this case, you’d probably want DBI to die and print the error message automatically if the connection fails (there’s not much you can do if the user doesn’t provide a valid name and password). After connecting, on the other hand, you wouldn’t want the script to exit just because the user enters a syntactically invalid statement. It would be better for the script to trap the error, print a message, and then loop to get the next statement. The following code shows how this can be done. The do() method used in the example executes a statement and returnsundef to indicate an error:

my $user_name = shift (@ARGV);

my $password = shift (@ARGV);

my %conn_attrs = (PrintError => 0, RaiseError => 1, AutoCommit => 1);

my $dbh = DBI->connect ($dsn, $user_name, $password, \%conn_attrs);

$dbh->{RaiseError} = 0; # disable automatic termination on error

print "Enter queries to be executed, one per line; terminate with Control-D\n";

while (<>) # read and execute queries

{

$dbh->do ($_) or warn "Query failed: $DBI::errstr ($DBI::err)\en";

}

$dbh->{RaiseError} = 1; # re-enable automatic termination on error

If RaiseError is enabled, you can trap errors without terminating your program by executing code within an eval block. If an error occurs within the block, eval fails and returns a message in the $@ variable. Typically, you use eval something like this:

eval

{

# statements that might fail go here...

};

if ($@)

{

print "An error occurred: $@\n";

}

This technique is commonly used to implement transactions. For an example, see Using Transactions in Perl Programs.

Using RaiseError in combination with eval differs from using RaiseError alone in the following ways:

§ Errors terminate only the eval block, not the entire script.

§ Any error terminates the eval block, whereas RaiseError applies only to DBI-related errors.

When you use eval with RaiseError enabled, be sure to disable PrintError. Otherwise, in some versions of DBI, an error may simply cause warn() to be called without terminating the eval block as you expect.

In addition to using the error-handling attributes PrintError and RaiseError, you can get lots of useful information about your script’s execution by turning on DBI’s tracing mechanism. Invoke the trace() method with an argument indicating the trace level. Levels 1 to 9 enable tracing with increasingly more verbose output, and level 0 disables tracing:

DBI->trace (1); # enable tracing, minimal output

DBI->trace (3); # elevate trace level

DBI->trace (0); # disable tracing

Individual database and statement handles have trace() methods, too. That means you can localize tracing to a single handle if you want.

Trace output normally goes to your terminal (or, in the case of a web script, to the web server’s error log). You can write trace output to a specific file by providing a second argument indicating a filename:

DBI->trace (1, "/tmp/trace.out");

If the trace file already exists, trace output is appended to the end; the file’s contents are not cleared first. Beware of turning on a file trace while developing a script, and then forgetting to disable the trace when you put the script into production. You’ll eventually find to your chagrin that the trace file has become quite large. Or worse, a filesystem will fill up, and you’ll have no idea why!

Ruby

Ruby signals errors by raising exceptions and Ruby programs handle errors by catching exceptions in a rescue clause of a begin block. Ruby DBI methods raise exceptions when they fail and provide error information by means of a DBI::DatabaseError object. To get the MySQL error number, error message, and SQLSTATE value, access the err, errstr, and state methods of this object. The following example shows how to trap exceptions and access error information in a DBI script:

begin

dsn = "DBI:Mysql:host=localhost;database=cookbook"

dbh = DBI.connect(dsn, "cbuser", "cbpass")

puts "Connected"

rescue DBI::DatabaseError => e

puts "Cannot connect to server"

puts "Error code: #{e.err}"

puts "Error message: #{e.errstr}"

puts "Error SQLSTATE: #{e.state}"

exit(1)

end

PHP

A PEAR DB method indicates success or failure by means of its return value. If the method fails, the return value is an error object. If the method succeeds, the return value is something else:

§ The connect() method returns a connection object for interacting with the database server.

§ The query() method for executing SQL statements returns a result set object for statements such as SELECT that return rows, or the DB_OK value for statements such as INSERT, UPDATE, or DELETE that modify rows.

To determine whether a method return value is an error object, pass it to the PEAR::isError() method, check the PEAR::isError() result, and take action accordingly. For example, the following code prints “Connected” if connect() succeeds and exits with a generic error message if not:

$dsn = "mysqli://cbuser:cbpass@localhost/cookbook";

$conn =& DB::connect ($dsn);

if (PEAR::isError ($conn))

die ("Cannot connect to server\n");

print ("Connected\n");

To obtain more specific information when a PEAR DB method fails, use the methods provided by the error object:

§ getCode()and getMessage() return an error number and message, respectively. These are standard values provided by PEAR that are not MySQL specific.

§ getUserInfo()and getDebugInfo() return MySQL-specific information.

The following listing shows how each method displays the error information returned by PEAR DB when a connect error occurs:

$dsn = "mysqli://cbuser:cbpass@localhost/cookbook";

$conn =& DB::connect ($dsn);

if (PEAR::isError ($conn))

{

print ("Cannot connect to server.\n");

printf ("Error code: %d\n", $conn->getCode ());

printf ("Error message: %s\n", $conn->getMessage ());

printf ("Error debug info: %s\n", $conn->getDebugInfo ());

printf ("Error user info: %s\n", $conn->getUserInfo ());

exit (1);

}

Python

Python signals errors by raising exceptions, and Python programs handle errors by catching exceptions in the except clause of a try statement. To obtain MySQL-specific error information, name an exception class, and provide a variable to receive the information. Here’s an example:

try:

conn = MySQLdb.connect (db = "cookbook",

host = "localhost",

user = "cbuser",

passwd = "cbpass")

print "Connected"

except MySQLdb.Error, e:

print "Cannot connect to server"

print "Error code:", e.args[0]

print "Error message:", e.args[1]

sys.exit (1)

If an exception occurs, the first and second elements of e.args are set to the error number and error message, respectively. (Note that the Error class is accessed through the MySQLdb driver module name.)

Java

Java programs handle errors by catching exceptions. If you simply want to do the minimum amount of work, print a stack trace to inform the user where the problem lies:

try

{

/* ... some database operation ... */

}

catch (Exception e)

{

e.printStackTrace ();

}

The stack trace shows the location of the problem but not necessarily what the problem is. It may not be all that meaningful except to you, the program’s developer. To be more specific, you can print the error message and code associated with an exception:

§ All Exception objects support the getMessage() method. JDBC methods may throw exceptions using SQLException objects; these are like Exception objects but also support getErrorCode() and getSQLState() methods. getErrorCode() and getMessage() return the MySQL-specific error number and message string. getSQLState() returns a string containing the SQLSTATE value.

§ You can also get information about nonfatal warnings, which some methods generate using SQLWarning objects. SQLWarning is a subclass of SQLException, but warnings are accumulated in a list rather than thrown immediately, so they don’t interrupt your program, and you can print them at your leisure.

The following example program, Error.java, demonstrates how to access error messages by printing all the error information it can get its hands on. It attempts to connect to the MySQL server and prints exception information if the attempt fails. Then it issues a statement and prints exception and warning information if the statement fails:

// Error.java - demonstrate MySQL error-handling

import java.sql.*;

public class Error

{

public static void main (String[] args)

{

Connection conn = null;

String url = "jdbc:mysql://localhost/cookbook";

String userName = "cbuser";

String password = "cbpass";

try

{

Class.forName ("com.mysql.jdbc.Driver").newInstance ();

conn = DriverManager.getConnection (url, userName, password);

System.out.println ("Connected");

tryQuery (conn); // issue a query

}

catch (Exception e)

{

System.err.println ("Cannot connect to server");

System.err.println (e);

if (e instanceof SQLException) // JDBC-specific exception?

{

// print general message, plus any database-specific message

// (e must be cast from Exception to SQLException to

// access the SQLException-specific methods)

System.err.println ("SQLException: " + e.getMessage ());

System.err.println ("SQLState: "

+ ((SQLException) e).getSQLState ());

System.err.println ("VendorCode: "

+ ((SQLException) e).getErrorCode ());

}

}

finally

{

if (conn != null)

{

try

{

conn.close ();

System.out.println ("Disconnected");

}

catch (SQLException e)

{

// print general message, plus any database-specific message

System.err.println ("SQLException: " + e.getMessage ());

System.err.println ("SQLState: " + e.getSQLState ());

System.err.println ("VendorCode: " + e.getErrorCode ());

}

}

}

}

public static void tryQuery (Connection conn)

{

try

{

// issue a simple query

Statement s = conn.createStatement ();

s.execute ("USE cookbook");

s.close ();

// print any accumulated warnings

SQLWarning w = conn.getWarnings ();

while (w != null)

{

System.err.println ("SQLWarning: " + w.getMessage ());

System.err.println ("SQLState: " + w.getSQLState ());

System.err.println ("VendorCode: " + w.getErrorCode ());

w = w.getNextWarning ();

}

}

catch (SQLException e)

{

// print general message, plus any database-specific message

System.err.println ("SQLException: " + e.getMessage ());

System.err.println ("SQLState: " + e.getSQLState ());

System.err.println ("VendorCode: " + e.getErrorCode ());

}

}

}

Writing Library Files

Problem

You notice that you’re writing the same code to perform common operations in multiple programs.

Solution

Put routines to perform those operations in a library file, and have your programs access the library. Then write the code only once. You might need to set an environment variable so that your scripts can find the library.

Discussion

This section describes how to put code for common operations in library files. Encapsulation (or modularization) isn’t really a “recipe” so much as a programming technique. Its principal benefit is that you don’t have to repeat code in each program you write. Instead, you just call a routine that’s in the library. For example, by putting the code for connecting to the cookbook database into a library routine, you need not write out all the parameters associated with making that connection. Simply invoke the routine from your program, and you’re connected.

Connection establishment isn’t the only operation you can encapsulate, of course. Later sections in this book develop other utility functions to be placed in library files. All such files, including those shown in this section, can be found under the lib directory of the recipes distribution. As you write your own programs, you’ll probably identify several operations that you perform often and that are good candidates for inclusion in a library. The techniques demonstrated in this section will help you write your own library files.

Library files have other benefits besides making it easier to write programs. They can help portability. For example, if you write connection parameters directly into each program that connects to the MySQL server, you have to change all those programs if you move them to another machine that uses different parameters. If instead you write your programs to connect to the database by calling a library routine, you localize the changes that need to be made: it’s necessary to modify only the affected library routine, not all the programs that use it.

Code encapsulation also can improve security in some ways. If you make a private library file readable only to yourself, only scripts run by you can execute routines in the file. Or suppose that you have some scripts located in your web server’s document tree. A properly configured server will execute the scripts and send their output to remote clients. But if the server becomes misconfigured somehow, the result can be that your scripts are sent to clients as plain text, thus displaying your MySQL username and password. (And you’ll probably realize it too late. Oops.) If the code for establishing a connection to the MySQL server is placed in a library file that’s located outside the document tree, those parameters won’t be exposed to clients.

WARNING

Be aware that if you install a library file to be readable by your web server, you don’t have much security should you share the web server with other developers. Any of those developers can write a web script to read and display your library file because, by default, the script runs with the permissions of the web server and thus will have access to the library.

The examples of programs that follow demonstrate how to write, for each API, a library file that contains a routine for connecting to the cookbook database on the MySQL server. The calling program can use the error-checking techniques discussed in Checking for Errors to determine whether a connection attempt fails. The connection routine for each language except PHP returns a database handle or connection object when it succeeds or raises an exception if the connection cannot be established. The PHP routine returns an object that represents a connection or an error, because that is what the PEAR DB connection method does (it does not raise an exception).

Libraries are of no utility in themselves, so each one’s use is illustrated by a short “test harness” program. You can use any of these harness programs as the basis for creating new programs of your own: make a copy of the file and add your own code between the connect and disconnect calls.

Library file writing involves not only the question of what to put in the file but also subsidiary issues such as where to install the file so it can be accessed by your programs, and (on multiuser systems such as Unix) how to set its access privileges so its contents aren’t exposed to people who shouldn’t see it.

Choosing a library file installation location

If you install a library file in a directory that a language processor searches by default, programs written in that language need do nothing special to access the library. However, if you install a library file in a directory that the language processor does not search by default, you’ll have to tell your scripts how to find the library. There are two common ways to do this:

§ Most languages provide a statement that can be used within a script to add directories to the language processor search path. This requires that you modify each script that needs the library.

§ You can set an environment or configuration variable that changes the language processor search path. This approach requires that each user who uses scripts that require the library to set the appropriate variable. Alternatively, if the language processor has a configuration file, you might be able to set a parameter in the file that affects scripts globally for all users.

We’ll use the second approach. For our API languages, the following table shows the relevant variables. In each case, the variable value is a directory or list of directories.

Language	Variable name	Variable type
Perl	PERL5LIB	Environment variable
Ruby	RUBYLIB	Environment variable
PHP	include_path	Configuration variable
Python	PYTHONPATH	Environment variable
Java	CLASSPATH	Environment variable

For general information on setting environment variables, see Appendix B. You can use those instructions to set environment variables to the values in the following discussion.

Suppose that you want to install library files in a directory that language processors do not search by default. For purposes of illustration, let’s use /usr/local/lib/mcb on Unix or C:\lib\mcb on Windows. (To put the files somewhere else, adjust the pathnames in the variable settings accordingly. For example, you might want to use a different directory, or you might want to put libraries for each language in separate directories.)

Under Unix, if you put Perl library files in the /usr/local/lib/mcb directory, you can set the PERL5LIB environment variable. For a shell in the Bourne shell family (sh, bash, ksh), set the variable like this in the appropriate startup file:

export PERL5LIB=/usr/local/lib/mcb

NOTE

If you are using the original Bourne shell, sh, you may need to split this into two commands:

PERL5LIB=/usr/local/lib/mcb

export PERL5LIB

For a shell in the C shell family (csh, tcsh), set PERL5LIB like this in your .login file:

setenv PERL5LIB /usr/local/lib/mcb

Under Windows, if you put Perl library files in C:\lib\mcb, you can set PERL5LIB as follows:

PERL5LIB=C:\lib\mcb

In each case, the variable setting tells Perl to look in the specified directory for library files, in addition to whatever other directories it would search by default. If you set PERL5LIB to name multiple directories, the separator character between directory pathnames is colon (:) on Unix orsemicolon (;) on Windows.

The other environment variables (RUBYLIB, PYTHONPATH, and CLASSPATH) are specified using the same syntax.

NOTE

Setting these environment variables as just discussed should suffice for scripts that you run from the command line. But for scripts that are intended to be executed by a web server, you’ll likely have to configure the server as well so that it can find the library files. See Using Apache to Run Web Scripts for details on how to do this.

For PHP, the search path is defined by the value of the include_path variable in the php.ini PHP initialization file. On Unix, the file’s pathname is likely to be /usr/lib/php.ini or /usr/local/lib/php.ini. Under Windows, the file is likely to be found in the Windows directory or under the main PHP installation directory. The value of include_path is defined with a line like this:

include_path = "value"

value is specified using the same syntax as for environment variables that name directories. That is, it’s a list of directory names, with the names separated by colons on Unix or semicolons on Windows. For example, on Unix, if you want PHP to look for include files in the current directory and in /usr/local/lib/mcb, set include_path like this:

include_path = ".:/usr/local/lib/mcb"

On Windows, to search the current directory and C:\lib\mcb, set include_path like this:

include_path = ".;C:\lib\mcb"

If you modify the php.ini file, and PHP is running as an Apache module, you’ll need to restart Apache to make your changes take effect.

Setting library file access privileges

Questions about file ownership and access mode are issues about which you’ll need to make decisions if you’re using a multiple-user system such as Unix:

§ If a library file is private and contains code to be used only by you, the file can be placed under your own account and made accessible only to you. Assuming that a library file mylib is already owned by you, you can make it private like this:

%chmod 600 mylib

§ If the library file is to be used only by your web server, you can install it in a server library directory and make the file owned by and accessible only to the server user ID. You may need to be root to do this. For example, if the web server runs as wwwusr, the following commands make the file private to that user:

§ #chown wwwusr mylib

# chmod 600 mylib

§ If the library file is public, you can place it in a location that your programming language searches automatically when it looks for libraries. (Most language processors search for libraries in some default set of directories.) You may need to be root to install files in one of these directories. Then you can make the file world-readable:

#chmod 444 mylib

Now let’s construct a library for each API. Each section here demonstrates how to write the library file itself and discusses how to use the library from within programs.

Perl

In Perl, library files are called modules, and typically have an extension of .pm (“Perl module”). Here’s a sample module file, Cookbook.pm, that implements a module named Cookbook. (It’s conventional for the basename of a Perl module file to be the same as the identifier on the packageline in the file.)

package Cookbook;

# Cookbook.pm - library file with utility method for connecting to MySQL

# via Perl DBI module

use strict;

use warnings;

use DBI;

my $db_name = "cookbook";

my $host_name = "localhost";

my $user_name = "cbuser";

my $password = "cbpass";

my $port_num = undef;

my $socket_file = undef;

# Establish a connection to the cookbook database, returning a database

# handle. Raise an exception if the connection cannot be established.

sub connect

{

my $dsn = "DBI:mysql:host=$host_name";

my %conn_attrs = (PrintError => 0, RaiseError => 1, AutoCommit => 1);

$dsn .= ";database=$db_name" if defined $db_name;

$dsn .= ";mysql_socket=$socket_file" if defined $socket_file;

$dsn .= ";port=$port_num" if defined $port_num;

return (DBI->connect ($dsn, $user_name, $password, \%conn_attrs));

}

1; # return true

The module encapsulates the code for establishing a connection to the MySQL server into a connect() method, and the package identifier establishes a Cookbook namespace for the module, so you invoke the connect() method using the module name:

$dbh = Cookbook::connect ();

The final line of the module file is a statement that trivially evaluates to true. This is needed because Perl assumes that something is wrong with a module and exits after reading it if the module doesn’t return a true value.

Perl locates library files by searching through the directories named in its @INC array. This array contains a default list of directories. To check the value of this variable on your system, invoke Perl as follows at the command line:

%perl -V

The last part of the output from the command shows the directories listed in the @INC array. If you install a library file in one of those directories, your scripts will find it automatically. If you install the module somewhere else, you need to tell your scripts where to find it by setting thePERL5LIB environment variable, as discussed earlier in the introduction to this recipe.

After installing the Cookbook.pm module, try it from a test harness script harness.pl written as follows:

#!/usr/bin/perl

# harness.pl - test harness for Cookbook.pm library

use strict;

use warnings;

use Cookbook;

my $dbh;

eval

{

$dbh = Cookbook::connect ();

print "Connected\n";

};

die "$@" if $@;

$dbh->disconnect ();

print "Disconnected\n";

harness.pl has no useDBI statement. It’s not necessary because the Cookbook.php library file itself imports the DBI module, so any script that uses Cookbook also gains access to DBI.

If you don’t want to bother catching connection errors explicitly, you can write the body of the script more simply. In this case, Perl will catch any connection exception and terminate the script after printing the error message generated by the connect() method:

my $dbh = Cookbook::connect ();

print "Connected\n";

$dbh->disconnect ();

print "Disconnected\n";

Ruby

The following Ruby library file, Cookbook.rb, defines a Cookbook class that implements a connect method:

# Cookbook.rb - library file with utility method for connecting to MySQL

# via Ruby DBI module

require "dbi"

# Establish a connection to the cookbook database, returning a database

# handle. Raise an exception if the connection cannot be established.

class Cookbook

@@host = "localhost"

@@db_name = "cookbook"

@@user_name = "cbuser"

@@password = "cbpass"

# class method for connecting to server to access

# cookbook database; returns database handle object.

def Cookbook.connect

return DBI.connect("DBI:Mysql:host=#{@@host};database=#{@@db_name}",

@@user_name, @@password)

end

end

The connect method is defined in the library as Cookbook.connect because Ruby class methods are defined as class_name.method_name.

Ruby locates library files by searching through the directories named in its $LOAD_PATH variable (also known as $:), which is an array that contains a default list of directories. To check the value of this variable on your system, use Ruby to execute this statement:

puts $LOAD_PATH

If you install a library file in one of those directories, your scripts will find it automatically. If you install the file somewhere else, you need to tell your scripts where to find it by setting the RUBYLIB environment variable, as discussed earlier in the introduction to this recipe.

After installing the Cookbook.rb library file, try it from a test harness script harness.rb written as follows:

#!/usr/bin/ruby -w

# harness.rb - test harness for Cookbook.rb library

require "Cookbook"

begin

dbh = Cookbook.connect

print "Connected\n"

rescue DBI::DatabaseError => e

puts "Cannot connect to server"

puts "Error code: #{e.err}"

puts "Error message: #{e.errstr}"

exit(1)

end

dbh.disconnect

print "Disconnected\n"

harness.rb has no require statement for the DBI module. It’s not necessary, because the Cookbook module itself imports DBI, so any script that imports Cookbook also gains access to DBI.

If you just want a script to die if an error occurs without checking for an exception yourself, write the body of the script like this:

dbh = Cookbook.connect

print "Connected\n"

dbh.disconnect

print "Disconnected\n"

PHP

The contents of PHP library files are written like regular PHP scripts. You can write such a file, Cookbook.php, that implements a Cookbook class with a connect() method as follows:

<?php

# Cookbook.php - library file with utility method for connecting to MySQL

# via PEAR DB module

require_once "DB.php";

class Cookbook

{

# Establish a connection to the cookbook database, returning a

# connection object, or an error object if an error occurs.

function connect ()

{

$dsn = array (

"phptype" => "mysqli",

"username" => "cbuser",

"password" => "cbpass",

"hostspec" => "localhost",

"database" => "cookbook"

);

return (DB::connect ($dsn));

}

} # end Cookbook

?>

Although most PHP examples throughout this book don’t show the <?php and ?> tags, I’ve shown them as part of Cookbook.php here to emphasize that library files must enclose all PHP code within those tags. The PHP interpreter doesn’t make any assumptions about the contents of a library file when it begins parsing it because you might include a file that contains nothing but HTML. Therefore, you must use <?php and ?> to specify explicitly which parts of the library file should be considered as PHP code rather than as HTML, just as you do in the main script.

PHP looks for libraries by searching the directories named in the value of the include_path variable in the PHP initialization file, as described earlier in the introduction to this recipe. Assuming that Cookbook.php is installed in one of those directories, you can access it from a test harness script harness.php written as follows:

<?php

# harness.php - test harness for Cookbook.php library

require_once "Cookbook.php";

$conn =& Cookbook::connect ();

if (PEAR::isError ($conn))

die ("Cannot connect to server: " . $conn->getMessage () . "\n");

print ("Connected\n");

$conn->disconnect ();

print ("Disconnected\n");

?>

harness.php has no statement to include DB.php. It’s not necessary because the Cookbook module itself includes DB.php, which gives any script that includes Cookbook.php access to DB.php.

WHERE SHOULD PHP LIBRARY FILES BE INSTALLED?

PHP scripts often are placed in the document tree of your web server, and clients can request them directly. For PHP library files, I recommend that you place them somewhere outside the document tree, especially if (like Cookbook.php) they contain usernames and passwords. This is particularly true if you use a different extension such as .inc for the names of include files. If you do that and install include files in the document tree, they might be requested directly by clients and be displayed as plain text, exposing their contents. To prevent that from happening, reconfigure Apache so that it treats files with the .inc extension as PHP code to be processed by the PHP interpreter rather than being displayed literally.

Python

Python libraries are written as modules and referenced from scripts using import or from statements. To create a method for connecting to MySQL, we can write a module file Cookbook.py:

# Cookbook.py - library file with utility method for connecting to MySQL

# via MySQLdb module

import MySQLdb

host_name = "localhost"

db_name = "cookbook"

user_name = "cbuser"

password = "cbpass"

# Establish a connection to the cookbook database, returning a connection

# object. Raise an exception if the connection cannot be established.

def connect ():

return MySQLdb.connect (db = db_name,

host = host_name,

user = user_name,

passwd = password)

The filename basename determines the module name, so the module is called Cookbook. Module methods are accessed through the module name; thus you would invoke the connect() method of the Cookbook module like this:

conn = Cookbook.connect ();

The Python interpreter searches for modules in directories named in the sys.path variable. You can find out what the default value of sys.path is on your system by running Python interactively and entering a couple of commands:

%python

>>> import sys

>>> sys.path

If you install Cookbook.py in one of the directories named by sys.path, your scripts will find it with no special handling. If you install Cookbook.py somewhere else, you’ll need to set the PYTHONPATH environment variable, as discussed earlier in the introduction to this recipe.

After installing the Cookbook.py library file, try it from a test harness script harness.py written as follows:

#!/usr/bin/python

# harness.py - test harness for Cookbook.py library

import sys

import MySQLdb

import Cookbook

try:

conn = Cookbook.connect ()

print "Connected"

except MySQLdb.Error, e:

print "Cannot connect to server"

print "Error code:", e.args[0]

print "Error message:", e.args[1]

sys.exit (1)

conn.close ()

print "Disconnected"

NOTE

The Cookbook.py file imports the MySQLdb module, but a script that imports Cookbook does not thereby gain access to MySQLdb. If the script needs MySQLdb-specific information (such as MySQLdb.Error), the script must also import MySQLdb.

If you just want a script to die if an error occurs without checking for an exception yourself, write the body of the script like this:

conn = Cookbook.connect ()

print "Connected"

conn.close ()

print "Disconnected"

Java

Java library files are similar to Java programs in most ways:

§ The class line in the source file indicates a class name.

§ The file should have the same name as the class (with a .java extension).

§ You compile the .java file to produce a .class file.

Java library files also differ from Java programs in some ways:

§ Unlike regular program files, Java library files have no main() function.

§ A library file should begin with a package identifier that specifies the location of the class within the Java namespace.

A common convention for Java package identifiers is to begin them with the reverse domain of the code author; this helps make identifiers unique and avoids conflict with classes written by other authors. Domain names proceed right to left from more general to more specific within the domain namespace, whereas the Java class namespace proceeds left to right from general to specific. Thus, to use a domain as the prefix for a package name within the Java class namespace, it’s necessary to reverse it. In my case, the domain is kitebird.com, so if I write a library file and place it under mcb within my domain’s namespace, the library begins with a package statement like this:

package com.kitebird.mcb;

Java packages developed for this book are placed within the com.kitebird.mcb namespace to ensure their uniqueness in the package namespace.

The following library file, Cookbook.java, defines a Cookbook class that implements a connect() method for connecting to the cookbook database. connect() returns a Connection object if it succeeds, and throws an exception otherwise. To help the caller deal with failures, theCookbook class also defines getErrorMessage() and printErrorMessage() utility methods that return the error message as a string or print it to System.err.

// Cookbook.java - library file with utility method for connecting to MySQL

// via MySQL Connector/J

package com.kitebird.mcb;

import java.sql.*;

public class Cookbook

{

// Establish a connection to the cookbook database, returning

// a connection object. Throw an exception if the connection

// cannot be established.

public static Connection connect () throws Exception

{

String url = "jdbc:mysql://localhost/cookbook";

String user = "cbuser";

String password = "cbpass";

Class.forName ("com.mysql.jdbc.Driver").newInstance ();

return (DriverManager.getConnection (url, user, password));

}

// Return an error message as a string

public static String getErrorMessage (Exception e)

{

StringBuffer s = new StringBuffer ();

if (e instanceof SQLException) // JDBC-specific exception?

{

// print general message, plus any database-specific message

s.append ("Error message: " + e.getMessage () + "\n");

s.append ("Error code: " + ((SQLException) e).getErrorCode () + "\n");

}

else

{

s.append (e + "\n");

}

return (s.toString ());

}

// Get the error message and print it to System.err

public static void printErrorMessage (Exception e)

{

System.err.println (Cookbook.getErrorMessage (e));

}

}

The routines within the class are declared using the static keyword, which makes them class methods rather than instance methods. That is done here because the class is used directly rather than creating an object from it and invoking the methods through the object.

To use the Cookbook.java file, compile it to produce Cookbook.class, and then install the class file in a directory that corresponds to the package identifier. This means that Cookbook.class should be installed in a directory named com/kitebird/mcb (or com\kitebird\mcb under Windows) that is located under some directory named in your CLASSPATH setting. For example, if CLASSPATH includes /usr/local/lib/mcb under Unix, you can install Cookbook.class in the /usr/local/lib/mcb/com/kitebird/mcb directory. (See the Java discussion in Connecting, Selecting a Database, and Disconnecting for more information about the CLASSPATH variable.)

To use the Cookbook class from within a Java program, import it, and then invoke the Cookbook.connect() method. The following test harness program, Harness.java, shows how to do this:

// Harness.java - test harness for Cookbook library class

import java.sql.*;

import com.kitebird.mcb.Cookbook;

public class Harness

{

public static void main (String[] args)

{

Connection conn = null;

try

{

conn = Cookbook.connect ();

System.out.println ("Connected");

}

catch (Exception e)

{

Cookbook.printErrorMessage (e);

System.exit (1);

}

finally

{

if (conn != null)

{

try

{

conn.close ();

System.out.println ("Disconnected");

}

catch (Exception e)

{

String err = Cookbook.getErrorMessage (e);

System.out.println (err);

}

}

}

}

}

Harness.java also shows how to use the error message utility methods from the Cookbook class when a MySQL-related exception occurs:

§ printErrorMessage() takes the exception object and uses it to print an error message to System.err.

§ getErrorMessage() returns the error message as a string. You can display the message yourself, write it to a logfile, or whatever.

Issuing Statements and Retrieving Results

Problem

You want your program to send an SQL statement to the MySQL server and retrieve whatever result it produces.

Solution

Some 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.

Discussion

There 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 cats—obviously one of those goofy tables that are used only for examples in a book!^[8^] 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.”

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. (Calculating Ages 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 and .

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). Statements in this category include 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 default (current) database for your session without making any changes to the database itself. The example data-modifying statement used in this section is an UPDATE:

UPDATE profile SET cats = cats+1 WHERE name = 'Fred'

We’ll cover how to issue this statement and determine the number of rows that it affects.

§ 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 example row-retrieval statement used in this section is a SELECT:

SELECT id, name, cats FROM profile

We’ll cover how to issue this statement, fetch the rows in the result set, and determine the number of rows and columns in the result set. (If you need information such as the column names or data types, you must access the result set metadata. Obtaining Result Set Metadata shows how to do this.)

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 Checking for Errors.

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, Determining Whether a Statement Produced a Result Set discusses how to tell.

Perl

The 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:

1. Specify the statement to be executed by calling prepare() using the database handle. If prepare() is successful, it returns a statement handle to use with all subsequent operations on the statement. (If an error occurs, the script terminates if RaiseError is enabled; otherwise,prepare() returns undef.)

2. Call execute() to execute the statement and generate the result set.

3. Perform a loop to fetch the rows returned by the statement. DBI provides several methods that you can use in this loop; we cover them shortly.

4. If you don’t fetch the entire result set, release resources associated with it by calling finish().

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 statements—not 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 Referring to Join Output Column Names in Programs.

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:

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 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() orfetchrow_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, anundef 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.

Ruby

As 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:

§ select_oneexecutes a query and returns the first row as an array (or nil if the result is empty):

row = dbh.select_one("SELECT id, name, cats FROM profile WHERE id = 3")

§ select_allexecutes a query and returns an array of DBI::Row objects, one per row of the result set. The array is empty if the result is empty:

rows = dbh.select_all( "SELECT id, name, cats FROM profile")

The select_all method is useful when you need to process a result set more than once because Ruby DBI provides no way to “rewind” a result set. By fetching the entire result set as an array of row objects, you can iterate through its elements multiple times. If you need to run through the rows only once, you can apply an iterator directly to select_all:

dbh.select_all("SELECT id, name, cats FROM profile").each do |row|

printf "id: %s, name: %s, cats: %s\n",

row["id"], row["name"], row["cats"]

end

PHP

In 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 SELECTstatement 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:

§ To obtain the rows in the result set, execute a loop in which you invoke the fetchRow() method.

§ To obtain a count of the number of rows in the result set, invoke numRows().

§ When there are no more rows, invoke the free() method.

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);

Python

The 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 (Using Transactions in Python Programs 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.

Java

The 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