Home arrow PHP arrow Page 7 - Accessing Databases with DB

Introducing Sequences - PHP

This chapter discusses the database abstraction layer PEAR DB. This package supplies a standard set of functions for talking to many different kinds of databases (from Essential PHP Modules, Extensions, Tools, by David Sklar, 2004, Apress, ISBN: 1590592808).

TABLE OF CONTENTS:
  1. Accessing Databases with DB
  2. Introducing DSNs
  3. Understanding Quoting and Placeholders
  4. Examining Data Retrieval Convenience Methods
  5. Understanding Query Information
  6. Running a Query Multiple Times
  7. Introducing Sequences
By: David Sklar
Rating: starstarstarstarstar / 14
November 30, 2004

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

A sequence is a source of unique integer identifiers. When you need an ID for an item in your database, ask a sequence for its next available ID. This ID is guaranteed to be unique within the sequence. If two queries ask the same sequence for an ID at the same time, each query gets a different answer.

The easiest way to use a sequence is just to call the DB::nextID() method. This creates the specified sequence if it doesn’t already exist and returns the next available ID in the sequence:

$flavor_id = $dbh->nextID('flavors');
$dbh->query('INSERT INTO ice_cream (id,flavor) VALUES
      (?,?)', array($flavor_id,'Walnut'));

To create sequences explicitly, use createSequence():

$dbh->createSequence('flavors');

If you are creating your sequences with createSequence(), you can tell nextID() not to create sequences automatically by passing false as a second argument:

$flavor_id = $dbh->nextID('flavors',false);
if (DB::isError($flavor_id)) { 
     die("Can't get sequence ID");
}
$dbh->query('INSERT INTO ice_cream (id,flavor) VALUES 
      (?,?)', array($flavor_id,'Walnut'));

Whether they are created automatically by nextID() or explicitly with createSequence(), the dropSequence() method deletes a sequence:

$dbh->dropSequence('flavors');

Introducing Error Handling

DB methods return a DB_Error object if they fail. The DB_Error object contains fields that describe the error condition. The “Sending Queries and Receiving Results” section describes the basic use of DB::isError(). This function returns true when the object passed to it is a DB_Error object. To comprehensively catch errors with this test, use it each time you call a DB method that may return an error:

$dbh = DB::Connect('mysql://user:pwd@localhost/dbname');
if (DB::isError($dbh)) { 
     die("Can't connect: ".$dbh->getMessage());
}
$sth = $dbh->query('SELECT flavor,price FROM ice_cream');
if (DB::isError($dbh)) {
     die("Can't SELECT: ".$sth->getMessage());
}
while($res = $sth->fetchRow()) {
     if (DB::isError($res)) {
          die("Can't get row: ".$res->getMessage());
     }
     print "Flavor: $res[0], Price: $res[1]";
}

Instead of testing each call to DB::isError() with if(), you can use the and logical operator instead, which is slightly more concise:

$dbh = DB::Connect('mysql://test:@localhost/test');
DB::isError($dbh) and die("Can't connect: "
    .$dbh->getMessage());
$sth = $dbh->query('SELECT flavor,price FROM ice_cream');
DB::isError($sth) and die("Can't SELECT: "
    .$sth->getMessage());
while($res = $sth->fetchRow()) {
     DB::isError($res) and die("Can't get row: "
         .$res->getMessage());
     print "Flavor: $res[0], Price: $res[1]\n";
}

Still, using DB::isError() after each relevant method call is cumbersome and error prone. If you forget to check the results of one method and it fails, subsequent operations won’t work properly. The DB::setErrorHandling() method allows you to tell DB to automatically take an action whenever a DB method call returns an error. To print the error and exit the program immediately, pass the constant PEAR_ERROR_DIE to setErrorHandling():

$dbh = DB::Connect('mysql://test:@localhost/test');
DB::isError($dbh) and die($dbh->getMessage());
$dbh->setErrorHandling(PEAR_ERROR_DIE);
$sth = $dbh->query('SELECT flavor,price FROM ice_cream');
while($res = $sth->fetchRow()) {
     print "Flavor: $res[0], Price: $res[1]\n";
}

To print the error but continue program execution, use PEAR_ERROR_PRINT instead of PEAR_ERROR_DIE. You can also use setErrorHandling() to have a custom function called each time there’s a DB error. Pass PEAR_ERROR_CALLBACK as the first argument to setErrorHandling() and the name of the function to call as the second argument:

$dbh = DB::Connect('mysql://test:@localhost/test');
DB::isError($dbh) and die($dbh->getMessage());
$dbh->setErrorHandling(PEAR_ERROR_CALLBACK,'db_error');
$sth = $dbh->query('SELECT flavor_name,price FROM
       ice_cream');
while($res = $sth->fetchRow()) {
     print "Flavor: $res[0], Price: $res[1]\n";
}
function db_error($err_obj) {
     print "Error! [$err_obj->code] $err_obj->userinfo";
}

Because this example has an unknown field in the SQL query (flavor_name instead of flavor), the db_error() error callback is called, and it prints this:

Error! [-19] SELECT flavor_name,price FROM ice_cream
[nativecode=1054 ** Unknown column 'flavor_name' in
'field list']

Error callbacks are useful for queries inside of a transaction. If there’s an error, the callback can automatically roll back the transaction and print a message or return the proper value to indicate that the transaction failed. 

This chapter is from Essential PHP Modules, Extensions, Tools, by David Sklar (Apress, 2004, ISBN: 1590592808). Check it out at your favorite bookstore today.

Buy this book now.



 
 
>>> More PHP Articles          >>> More By David Sklar
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: