I have run into an unexpected fatal error while testing an app that uses my database utility class (see code below). Here is a rundown of the test that was performed:
Results:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]The DELETE statement conflicted with the REFERENCE constraint "FK_Orders_Products". The conflict occurred in database "AppTEST", table "dbo.Orders", column 'productID'.' in C:\xampp\htdocs\App\classes\utility\DB.php:140 Stack trace: ...
$stmt->execute();
, which is in the query()
method in the code below.Here are the pertinent parts of my database utility class for this question:
namespace classes\utility;
use \PDO,
\Exception,
\PDOException;
class DB
{
private $_pdo, # stores PDO object when it's instantiated
$_error, # stores whether query failed or not
$_results, # stores dataset returned by query
$_count = 0; # stores number of data rows returned
/*connect to database*/
private function __construct()
{
try
{
$connection = new \PDO('sqlsrv:Server=' . DB_HOST . ';Database=' . DB_NAME);
$connection->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION); //to capture error messages returned by db
$this->setPdo($connection);
}
catch(\PDOException $e)
{
exit((DEBUGMODE) ? $e->getMessage() : 'There was a problem connecting to the database.');
}
}
# generic query method - uses named placeholders for parameter binding
private function query($sql, $parameters = [])
{
try
{
$this->setError(false);
$stmt = $this->getPdo()->prepare($sql); //assign to a variable with a short name
if ($stmt):
if (count($parameters)):
foreach($parameters as $name => $parameter):
$stmt->bindValue(':'.$name, $parameter);
endforeach;
endif;
$stmt->execute();
$this->setCount($stmt->rowCount());
if ($stmt->rowCount() == -1): //conditionally do the following if SQL stmt was a SELECT
$this->setResults($stmt->fetchAll(\PDO::FETCH_OBJ));
$this->setCount($stmt->rowCount()); //overwrite count attribute with number of rows returned by SELECT stmt
endif;
endif;
return $this;
}
catch (\PDOException $e)
{
$this->setError(true);
throw $e;
}
}
// setter and getter methods for private variables not shown
}
EDIT: Here is the code that calls the query()
method. This method is also part of the DB
class.
public function runQuery($operation, $sql, $parameters)
{
try
{
$this->query($sql, $parameters); //call query method all $operation types (read, write)
if ($operation == 'read'):
if ($this->getCount()):
if ($this->getCount() > 1): //for "read all"
$data = $this->getResults();
else: //for "read one"
$data = $this->getFirstResult();
endif;
$data = Arr::objectToArray($data);
return $data;
else:
throw new \Exception("No data to display."); //throw an exception if query runs fine but returns zero rows
endif;
endif;
}
catch (\PDOException $e)
{
throw (DEBUGMODE) ? $e : new \Exception("A database error occurred. The $operation operation failed.");
}
}
The problem turned out to be missing a backslash in a calling method. I had prepended the backslash in all references to global classes in the DB class, but not in other classes in other namespaces that had catch blocks.
There were actually 2 different ways to resolve this problem.
One approach was to change catch (Exception $e)
to catch (\Exception $e)
.
Alternatively, leaving catch (Exception $e)
as-is throughout the code and adding use \Exception;
after the namespace declaration and before the class definition worked as well.