Handling SQL Errors in PDO
To give you an example, consider the query below (the correct tablename is country, so this SQL will fail):
$db = new PDO('mysql:host=localhost;dbname=sakila', 'user', 'pass');
$sql = 'select * from countrt';
$stmt = $db->query($sql);
while(($row = $stmt->fetch()) != false) {
echo $row['country'] . "\n";
}
The script will output an error because $stmt is not an object.
You have a few options here – you can check that you got an object back before you try to do anything with it, for example. Alternatively you can prepare() and then execute() the statement, which means that you’ll have a statement object and if there are any errors, they’ll be available. This gives code that looks something like:
$db = new PDO('mysql:host=localhost;dbname=sakila', 'user', 'pass');
$sql = 'select * from countrt';
$stmt = $db->prepare($sql);
$stmt->execute();
if($stmt->errorCode() == 0) {
while(($row = $stmt->fetch()) != false) {
echo $row['country'] . "\n";
}
} else {
$errors = $stmt->errorInfo();
echo($errors[2]);
}
This way, we can check if we got errors and examine what they are if we did. (if you’re coming on my database course next week, you’ll see this example and also a demonstration of how to change this silent behaviour. I will endeavour to write about changing it here as well at some point).
The main thing to look out for is that it is easy to miss the difference between getting no results because there aren’t any and getting no results because there’s an error that you can’t see – and I hope this code example shows how you can interrogate PDO to find out which one it was!
Add this line after you connect and sql errors will behave as all other php errors:
$PDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
To catch them by yourself add your own error handling using set_error_handler().
You even can use PDO::ERRMODE_EXCEPTION as the second argument. It will fire exceptions and let you cathc them when you need.
Thanks for commenting :) I usually use the exceptions setting, and it’s this that I realised I should write a follow-up post about one day.
On that line:
while(($row = $stmt->fetch()) != false) {
the “!= false” is redundant.
It would make a difference when it was not identical “!==”
So you could write it like this, and it will make no difference at all:
while($row = $stmt->fetch()) {
It’s easier to read.
In many cases PDO methods return false on error rather than null when nothing is returned. So my code does…
$stmt = $dbh->prepare(“Some SQL”);
if (($stmt !== false) && $stmt->execute(…)) {
$rows = $stmt->fetch(…);
if ($rows !== false) {
while …
Unfortunately I’ve found one place where a null is returned when it should be false. :-(
using exceptions for errors is wrong…
exceptions and errors are very different things…
I get sick when I see code that throw exceptions rigth and left for ordinary errors…
http://www.artima.com/weblogs/viewpost.jsp?thread=331407
An Exception is exactly what it says on the tin, as in a state has occurred in which the code in context doesn’t know how to proceed, or is outside of the scope of its intended purpose. What’s an error if it’s not exactly that?
no, failing to open a file is an ordinary error, not an exception…
There’s no difference between ‘an ordinary error’ and an exception. I think the issue is that PHP is mixing two kinds of error reporting. The return status code for the more traditional non OO APIs and exceptions for the rest. If you look at other languages, like say Java, you’ll see they don’t use the return status code at all. They just use exceptions. An exception is where code has operated abnormally. I.e. an error has occurred. Exceptions are the ideal mechanism for reporting a problem in DB access.
If there is an error in the syntax or in a column name, I see no problem in throwing exceptions: it’s a programming error, not something that could be reached by a user under certain conditions.
Interesting. I usually prefer to have PDO throw exceptions mostly because I think they are the most appropriate solution. Once I’ve finished developing and debugging an application database errors normally only happen in exceptional circumstances such as not being able to connect to the db. For me that would be the definition of an exceptional circumstance. I also find the code to be much cleaner if I’m using a try catch block. I can assume that unless an exception is thrown the database operations are proceeding as expected. Of course, it all comes down to the definition of an ‘exceptional circumstance’…
If the code cannot continue, it is an exception.
I wouldn’t expect an exception to be thrown for, say, input validation. We expect someone will type X in a numeric input and handle it without throwing an exception.
If the database barfs, I say exception.
IMO Exceptions are only obtained when you can’t do anything about it. E.g. if i wrote a wrong SQL I should get an error because a can fix it as a programmer. Usually we write the whole stack of code in MVC like patterns, so we have control over every layer. In that case I won’t use Exceptions, because i’m fully in control over every layer.
I think an exception should only be used when you can’t influence the code that tells something went wrong. But from that “read-only” code point of view something needs to be reported because something unexpected happened. So what i CAN influence is its following behaviour after the exceptions (because I can catch it right?). From that point I can still generate an error or give a message or do something else. So the programmer is still in control
That implies using Exceptions is almost always used in API’s and components. In little isolated islands of independant code. In that case you write software for other programmers. You can still report something went wrong but leave it to those who implement your code what to do with it.
Pingback: Lorna Mitchell’s Blog: Handling SQL Errors in PDO | Scripting4You Blog
I think Exceptions aren’t the worst move for database errors. While developing, an exception is just as descriptive as a usual error message. While in production, if *anything* fails in the database, it means that my query is malformed, my database connection is lost, I’m querying a table that doesn’t exist, etcetera. All of those cases are cases I don’t want to show a user, yet, I want to be informed, as they seem important enough. That’s why I think exceptions are in the right place here, but in the end, this whole discussion is on semantics.
The thing I am amazed by is that people still use the plain PDO interface, instead of a wrapper. There are so many wrappers that give PDO a “better” API and set sensible defaults, that I can’t grasp why someone still wants to work with $pdo->setAttribute( ) :)
My reply came sort of theoretical (and using a wrong sql statement as an example probably gave it a kind of false assumption). So in practice; it depends on where this piece of code is used, within a fully maintable code stack (error) or within an API/component (exception)?
Also helpful to debug statements before preparing them. Simple example by @dshafik
http://daveyshafik.com/archives/605-debugging-pdo-prepared-statements.html
Pingback: Handling SQL Errors in PDO | LornaJane | La veille du WebDeveloper | Scoop.it
more about exceptions vs errors:
http://stackoverflow.com/questions/1736146/why-is-exception-handling-bad
I have a cool example how to detect duplicate records without having to perform a SELECT on the database:
try{
$pdo->query(“INSERT INTO `table`(`name`,`value`)`VALUES(‘name’,’value’)”);
} catch (PDOException $e) {
if($e->errorInfo[0] == ‘23000’ && $e->errorInfo[1] == ‘1062’){
throw new CustomException(“Bla bla already exists”);
} else {
throw $e;
}
}
I use it in all my API’s and it helps a lot.
That’s a neat trick Thijs, thanks for sharing :)
Great post. I have been spending the last two days trying all the different ways of handling PDO errors and this one is the most simple and effective I have found. Well it’s the only method I could get to actually work!
Pingback: Programowanie w PHP » Blog Archive » Lorna Mitchell’s Blog: Handling SQL Errors in PDO
nice example….thanks for this
I searched for ages to find this. Excellent piece if work :)