Pear::DB Primer

Often I have been a victim of badly designed code or by changes made to the initial specification of a project. Sometimes my personal pet project changes hosting providers and because of that, it needs to use a different database server, or a corporate project changes its direction and consequently its platform. In any case, I end up changing database servers.

Things like these happen very frequently in the IT world. People start out thinking that platform A is the best one and out of the blue, a business need or a strategic partnership necessitates a change to the project plan. Most of the source code will need to be reviewed to check for problems caused by the database switch.

That is, unless the project was designed to work with any database server that has a significant market share. This is the objective of the PEAR::DB library, which is a part of the PEAR project.

In the PHP community, there are several other good projects that aim to do pretty much the same thing as PEAR::DB, such as PHPLIB, Metabase, and ADODB. However, I believe PEAR::DB has the most modern interface to talk to database servers, as it is fully object oriented and provides the most general error-handling class that exists today. Besides that, several PHP Core developers work on PEAR to make it the best organized and cleanest distribution of reusable libraries for the PHP world.

Because of this heavy project backing, it will probably continue to be developed and improved. Stig Bakken, the originator of the project and also a PHP Core developer since the PHP 2.0 era, is even working on porting the PEAR::DB framework to C. This will boost the performance of the library and provide a standard way to interact with databases. This C version of PEAR is still in the very early stages of development, and doesn't contain any database-related routines. It currently only contains the main PEAR class and its error-handling class, PEAR_Error.

PEAR::DB certainly has problems and will continue to improve. Many critics of PEAR::DB, such as Manuel Lemos the author of Metabase, say PEAR::DB is too slow and doesn't provide a true abstraction to the interaction with database servers. I believe the most important part of a database abstraction library is its design -- I want to be able to use a high-quality library on my code; differences of 0.02% are not so important.

Connecting to a database server

In PEAR::DB you connect to a database server by passing an array of parameters that contain the connnection information such as the host name of the database server, the user name and password to use on the connection, and also which database to use.

The following code connects to MySQL running on host presidio.impleo.net using "anonymous" as the user name and "hellokitty" as the password. I specified the database name as "mydb".

<?php
include("DB.php");
$dsn = array(
    'phptype'  => "mysql",
    'hostspec' => "presidio.impleo.net",
    'database' => "mydb",
    'username' => "anonymous",
    'password' => "hellokitty"
);
$dbh = DB::connect($dsn);
?>

The phptype parameter tells PEAR::DB which driver to use for this connection. In this case, we are using MySQL. Several different database drivers are available to use, and on my last visit to PEAR's CVS repository, the following ones were available: MySQL, PostgreSQL, Oracle, MS SQL Server, Frontbase, Informix, Sybase, Interbase, mSQL, and ODBC. Like any other open-source project, some of these drivers are not as up-to-date as the MySQL or PostgreSQL drivers, but they are being mantained actively.

The DB::connect() line returns an object that can be used to run queries, fetch result sets, and such. Like all other PEAR software, it can return an object of type PEAR_Error to send back error messages and full debugging descriptions. This is a standard way for PEAR to handle error situations gracefully. In this case, one could use the following checks to see if an error happened when PEAR::DB tried to connect to the database:

<?php
if (PEAR::isError($dbh)) {
    echo "An error occurred while trying to connect to the database server.<br>\n";
    echo "Error message: " . $dbh->getMessage() . "<br>\n";
    echo "A more detailed error description: " . $dbh->getDebugInfo() . "<br>\n";
}
?>

The connect() method of the DB class contains code that according to the parameter phptype, includes the appropriate driver script and tries to run the driver's connect() method. This way, you can use the same piece of code (DB::connect()) and still connect to different database servers just by changing one parameter. This is a piece of the abstraction and code re-use that I was talking about.

So connect() tries to use the driver's connect() method to actually connect to the database, and if any error happens on this process, a PEAR_Error object is created and returned. This PEAR_Error instance will contain the normal error message and a detailed version of it for debugging purposes. This is how the connect() method deals with error situations, and all other methods do it pretty much in the same way. This is a piece of the standardization of error handling of PEAR libraries.

Selecting something from the database

Now that we can connect to a database server using PEAR::DB, let's run a query and select some rows from it. As always, there are a set of standard methods to use in such cases as seen below:

<?php
$stmt = "SELECT
            id,
            first_name,
            last_name
         FROM
            persons
         LIMIT
            0, 3";
$rows = $dbh->getAll($stmt, DB_FETCHMODE_ASSOC);
?>

The example above runs a query and returns the full result set as one nested array. The DB_FETCHMODE_ASSOC constant passed to the method tells PEAR::DB that I want the resulting array with a nested associative array, with the field name as the key. Here you see its structure:

array(
  0 => array(
          "id" => 12,
          "first_name" => "Rasmus",
          "last_name" => "Lerdorf"
       ),
  1 => array(
          "id" => 13,
          "first_name" => "Stig",
          "last_name" => "Bakken"
       ),
  2 => array(
          "id" => 14,
          "first_name" => "Joao",
          "last_name" => "Prado Maia"
       )
)

There are several alternatives to this method, such as the fetchRow() method, which does the same thing as the mysql_fetch_* family of functions. Similar methods like getOne() and getRow() are very useful for those cases where you are selecting just one value from the only row being returned, or even selecting the first full row of data being returned.

<?php
$stmt = "SELECT first_name FROM persons WHERE id=14";
$first_name = $dbh->getOne($stmt);
if (PEAR::isError($first_name)) {
    echo "An error occurred while trying to run your query.<br>\n";
    echo "Error message: " . $first_name->getMessage() . "<br>\n";
    echo "A more detailed error description: " . $first_name->getDebugInfo() . "<br>\n";
}

$stmt = "SELECT id, first_name, last_name FROM persons WHERE id=14";
$row = $dbh->getRow($stmt, DB_FETCHMODE_ASSOC);
if (PEAR::isError($row)) {
    echo "An error occurred while trying to run your query.<br>\n";
    echo "Error message: " . $row->getMessage() . "<br>\n";
    echo "A more detailed error description: " . $row->getDebugInfo() . "<br>\n";
}
?>

As always, every returned value from PEAR::DB can eventually contain a PEAR_Error object, so checking for that is appropriate.

Updating, deleting, and inserting into the database

Now that I covered how to select information from a table using the PEAR::DB get* family of methods, let's look on how easy it is to update the contents of an existing row, or even delete or insert a new row.

<?php
$stmt = "INSERT INTO
            persons
         (
            first_name,
            last_name,
            birth_date
         ) VALUES (
            'Zeev',
            'Suraski',
            '0000-00-00 00:00:00'
         )";
$result = $dbh->query($stmt);
if (PEAR::isError($result)) {
    echo "An error occurred while trying to run your query.<br>\n";
    echo "Error message: " . $result->getMessage() . "<br>\n";
    echo "A more detailed error description: " . $result->getDebugInfo() . "<br>\n";
} else {
    echo "Thank you, the new row was inserted successfully.";
}
?>

Simple, isn't it? Queries like UPDATE and DELETE behave in exactly the same way. With these types of queries, the method to use is query(), and it will return a standard PEAR_Error instance on error situations or "true" for success.

Now you are probably thinking that constantly checking every PEAR_Error message is a bit excessive. A more automatic way to check for errors should surely exist, correct? Here's an example of how to automate error handling:

<?php
function handleErrors($error)
{
    echo "An error occurred while trying to run your query.<br>\n";
    echo "Error message: " . $error->getMessage() . "<br>\n";
    echo "A more detailed error description: " . $error->getDebugInfo() . "<br>\n";
}
include("DB.php");
PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, 'handle_pear_error');
$dbh = DB::connect($dsn);
// the next line triggers the automatic error handling function
$rows = $dbh->getAll('SELECT unknown_field FROM persons');
?>

The example above runs the handleError() function automatically whenever an error happens inside the PEAR::DB code. This is quite useful and will save a few thousand keystrokes if you are developing a big project.

I hope this article gave a little more attention to the PEAR project. Detailed information on the current developments can be seen on the PEAR-DEV and PEAR-GENERAL mailing lists.

The PEAR project is also beginning to develop its own site, with lots of information about the libraries and a roadmap for the future, so please be sure to visit the site.

Joao Prado Maia is a web developer living in Houston with more than four years of experience developing web-based applications and loves learning new technologies and programming languages.

oreillynet.com Copyright © 2003 O'Reilly & Associates, Inc.