1. DBQuery Object
Now, our DBQuery object simply emulates a stored procedure - once executed, it returns a result resource that must be saved; and if you want to use functions on the result set (such as num_rows() or fetch_row() )), you must pass the MySqlDB object. So, what is the effect if the DBQuery object implements the functions implemented by the MySqlDB object (which is designed to operate on the results of an executed query)? Let's continue using the code from the previous example; and let's assume that our result resources are now managed by a DBQuery object. The source code of the DBQuery class is shown in Listing 1.
Listing 1. Using the DBQuery class.
require 'mysql_db.php';
require_once 'query.php';
$db = new MySqlDb;
$db->connect('host', 'username', 'pass');
$db->query('use content_management_system');
$query = new DBQuery($db);
$query->prepare('SELECT fname，sname FROM users WHERE username=:1S AND pword=:2S AND expire_time<:3I');
try {
if($query->execute("visualad", "apron", time()))->num_rows() == 1) {
echo('Correct Credentials');
} else {
echo('Incorrect Credentials / Session Expired');
}
} catch (QueryException $e) {
echo('Error executing query: ' . $e);
}
What we are most interested in in the modified code above are the catch statement and execute statement.
· The execute statement no longer returns a result resource, it now returns the DBQuery object itself.
· The DBQuery object now implements the num_rows() function—which we are already familiar with from the DB interface.
· If the query execution fails, it throws an exception of type QueryException. When converted to a string, it returns the details of the error that occurred.
To do this, you need to use a proxy. In fact, you are already using proxies in our DBQuery object, but now you will use it in more depth to tightly bind it to the MySqlDB object. The DBQuery object has been initialized with an object that implements the DB interface, and it already contains a member function execute—which calls the query() method of the DB object to execute the query. The DBQuery object itself does not actually query the database, it leaves this task to the DB object. This is a proxy, which is a process by which an object can implement a specific behavior by sending messages to another object that implements the same or similar behavior.
To do this, you need to modify the DBQuery object to include all functions that operate on a result resource from the DB object. You need to use the stored results when executing a query to call the corresponding function of the DB object and return its results. The following functions will be added:
Listing 2: Extending the DBQuery class using proxies.
classDBQuery
{
.....
public function fetch_array()
{
if (! is_resource($this->result)) {
throw new Exception('Query not executed.');
}
return $this->db->fetch_array($this->result);
}
public function fetch_row()
{
if (! is_resource($this->result)) {
throw new Exception('Query not executed.');
}
return $this->db->fetch_row($this->result);
}
public function fetch_assoc()
{
if (! is_resource($this->result)) {
throw new Exception('Query not executed.');
}
return $this->db->fetch_assoc($this->result);
}
public function fetch_object()
{
if (! is_resource($this->result)) {
throw new Exception('Query not executed.');
}
return $this->db->fetch_object($this->result);
}
public function num_rows()
{
if (! is_resource($this->result)) {
throw new Exception('Query not executed.');
}
return $this->db->num_rows($this->result);
}
}
The implementation of each function is quite simple. It first checks to make sure the query has been executed, then delegates the task to the DB object, returning its results as if it were the query object itself (called a basic database function).
2. Type Hinting
In order for the proxy to work, we need to ensure that the $db variable of the DBQuery object is an instance of an object that implements the DB interface. Type hints are a new feature in PHP 5 that enable you to coerce function parameters into objects of a specific type. Prior to PHP 5, the only way to ensure that a function parameter was a specific object type was to use the type checking function provided in PHP (that is, is_a()). Now, you can simply cast an object type—by prefixing the function parameter with the type name. You've already seen type hints from our DBQuery object, which ensures that an object implementing the DB interface is passed into the object constructor.
public function __construct(DB $db)
{
$this->db = $db;
}
When using type hints, you can specify not only object types, but also abstract classes and interfaces.
3. Throw exceptions
You may have noticed from the above code that what you catch is an exception called QueryException (we will implement this object later). An exception is similar to an error, but more general. The best way to describe an exception is to use emergency. Although an emergency may not be "fatal," it must still be dealt with. When an exception is thrown in PHP, the current scope of execution is quickly terminated, whether it is a function, try..catch block or the script itself. The exception then traverses the call stack—terminating each execution scope—until it is either caught in a try..catch block or it reaches the top of the call stack—at which point it generates a fatal error.
Exception handling is another new feature in PHP 5. When used in conjunction with OOP, it can achieve good control over error handling and reporting. A try..catch block is an important mechanism for handling exceptions. Once caught, script execution will continue from the next line of code where the exception was caught and handled.
If the query fails, you need to change your execute function to throw an exception. You will throw a custom exception object called QueryException - the DBQuery object that caused the error is passed to it.
Listing 3. Throws an exception.
/**
*Execute current query
*
* Execute the current query—replacing any dots with the provided arguments
* .
*
* @parameters: mixed $queryParams,... query parameters
* @return: Resource A—reference describing the resource on which the query is executed.
*/
public function execute($queryParams = '')
{
//For example: SELECT * FROM table WHERE name=:1S AND type=:2I AND level=:3N
$args = func_get_args();
if ($this->stored_procedure) {
/*Call the compile function to get the query*/
$query = call_user_func_array(array($this, 'compile'), $args);
} else {
/*A stored procedure has not been initialized, therefore, it is executed as a standard query*/
$query = $queryParams;
}
$result = $this->db->query($query);
if (! $result) {
throw new QueryException($this);
}
$this->result = $result;
/* Notice how now we return the object itself, which allows us to call member functions from the return result of this function */
return $this;
}
4. Use inheritance to throw custom exceptions
In PHP, you can throw any object as an exception; however, first, the exception should inherit from PHP's built-in exception class. By creating your own custom exception, you can log other information about the error, create an entry in a log file, or do whatever you like. Your custom exception will do the following:
· Log the error message from the DB object generated by the query.
· Give precise details of the line of code where the query error occurred—by examining the call stack.
· Display error messages and query text—when converted to a string.
In order to get the error message and query text, several changes need to be made to the DBQuery object.
1. A new protected attribute—compiledQuery—needs to be added to the class.
2. The compile() function updates the query's compiledQuery property with the query text.
3. A function should be added to retrieve the compiled query text.
4. A function should also be added - it gets the current DB object associated with the DBQuery object.
Listing 4. Throw an exception.
classDBQuery
{
/**
*Store the compiled version of the query after calling compile() or execute()*
* @var string $compiledQuery
*/
protected $compiledQuery;
/**
* Returns the compiled query without executing it.
* @parameters: mixed $params,...query parameters* @return: string—compiled query*/
public function compile($params='')
{
if (! $this->stored_procedure) {
throw new Exception("The stored procedure has not been initialized.");
}
/*replacing parameters*/
$params = func_get_args(); //Get function parameters $query = preg_replace("/(?compile_callback($params, 1, "2")', $this->query);
return ($this->compiledQuery = $this->add_strings($query)); //Put the string back into the query}
public function getDB()
{
return $this->db;
}
public function getCompiledQuery()
{
return $this->compiledQuery;
}
}
Now, you can implement the QueryException class. Notice how you walk through the call stack to find the location in the script that actually caused the error. This is exactly the case when the DBQuery object that throws the exception is a subclass that inherits from the DBQuery object.
Listing 5: QueryException class.
/**
*Query exception
*
*When trying to execute a query, if an error occurs, an error will be thrown by the {@link DBQuery} object
*/
class QueryException extends Exception
{
/**
*Query text*
* @var string $QueryText;
*/
protected $QueryText;
/**
*Error number/code from database*
* @var string $ErrorCode
*/
protected $ErrorNumber;
/**
*Error message from database*
* @var string $ErrorMessage
*/
protected $ErrorMessage;
/**
*Class constructor*
* @Parameter: DBQuery $db, which is the query object that throws the exception */
public function __construct(DBQuery $query)
{
/*Get the call stack*/
$backtrace = $this->GetTrace();
/*Set the line and file to the location where the error actually occurred*/
if (count($backtrace) > 0) {
$x = 1;
/*If the query class is inherited, then we need to ignore calls made by subclasses*/
while((! isset($backtrace[$x]['line'])) ||
(isset($backtrace[$x]['class']) && is_subclass_of($backtrace[$x]['class'], 'DBQuery')) ||
(strpos(strtolower(@$backtrace[$x]['function']), 'call_user_func')) !== false ) {
/*Loop execution, as long as there is no line number or the function called is a subclass of the DBQuery class*/
++$x;
/*If we reach the bottom of the stack, then we use the first caller*/
if (($x) ＞= count($backtrace)) {
$x = count($backtrace);
break;
}
}
/*If the above loop executes at least once, then we can decrement it by 1 to find the actual line of code that caused the error*/
if ($x != 1) {
$x -= 1;
}
/*Finally, we can set the file and line numbers, which should reflect the SQL statement that caused the error*/
$this->line = $backtrace[$x]['line'];
$this->file = $backtrace[$x]['file'];
}
$this->QueryText = $query->getCompiledQuery();
$this->ErrorNumber = $query->getDB()->errno();
$this->ErrorMessage = $query->getDB()->error();
/*Call the superclass's exception constructor*/
parent::__construct('Query Error', 0);
}
/**
*get query text*
* @return string query text */
public function GetQueryText()
{
return $this->QueryText;
}
/**
*got error number*
* @return string error number */
public function GetErrorNumber()
{
return $this->ErrorNumber;
}
/**
*got error message*
* @return string error message */
public function GetErrorMessage()
{
return $this->ErrorMessage;
}
/**
* Called when the object is converted to a string.
* @return string */
public function __toString()
{
$output = "Query Error in {$this->file} on line {$this->line}nn";
$output .= "Query: {$this->QueryText}n";
$output .= "Error: {$this->ErrorMessage} ({$this->ErrorNumber})nn";
return $output;
}
}
Now the code you saw at the beginning of this section works.
5. Conclusion
In this article, you saw how the agent maps the DB interface associated with the query to operations on a specific query result. DBQuery objects expose the same functions, such as fetch_assoc(), as DB objects. However, these all work for a single query. You also learned how to use custom exceptions to give detailed information about when and where an error occurred, and how they can better control error handling.