Introducing Sequences - PHP

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

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.