PDO, wrong update still no exception

Question

try {
     $query = 'UPDATE keywords SET value = :keyvalue WHERE keyword = :keyname AND document_id = :docId';
     $pdo   = _openConnection();
     $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     $pdo->beginTransaction();
     $pdoStatement = $pdo->prepare($query);
     foreach ($keywords as $keyname => $keyval) {
        $pdoStatement->bindParam(':docId', $id, PDO::PARAM_STR);
        $pdoStatement->bindParam(':keyname', $keyname, PDO::PARAM_STR);
        $pdoStatement->bindParam(':keyvalue', $keyval, PDO::PARAM_STR);
        $pdoStatement->execute();
     }
     $res = $pdo->commit();
     var_dump('retornando true', $res);
     return true;
} catch (PDOException $e) {
     $pdo->rollBack();
     echo $e->getMessage();
     return false;
}

The sentence updates a given row identified by KEYWORDNAME and DOCUMENT_ID. I am sending a wrong keyword name (non-existent) but existent document id.

Shouldn't it throw an exception for record not found and rollback the operation? It is always succeeding and returning true (also I see the var_dump)

PS: this is the last portion of the code.

Why do you think it should throw an exception? Query tries to update value which is not found, I don't see something "exceptional" here — u_mulder, Jan 14 '15 at 17:56
possible duplicate of [MySQL record UPDATE should fail but does not. Why?](http://stackoverflow.com/questions/5526308/mysql-record-update-should-fail-but-does-not-why) — Sean, Jan 14 '15 at 17:59
It will throw an exception if a foreign key constraint fails on the update. Probably not the best way but it is an option. — MKC, Mar 30 '16 at 13:02

jeroen · Answer 1 · 2015-01-14T18:02:00.293

3

No, you are simply updating 0 rows when the WHERE condition is not matched. Updating, selecting, etc. 0 rows is not an error, these are normal database operations.

Check the rowCount() to see how many rows are updated and handle that accordingly.

edited Jan 14 '15 at 18:02

answered Jan 14 '15 at 17:56

jeroen

91,079
21
114
132

I see... if I need to throw an error here, how would you manage it? Checking for the row first? – JorgeeFG Jan 14 '15 at 17:57
2

@Jorge Use [PDO->rowCount()](http://php.net/manual/en/pdostatement.rowcount.php) to see the number of affected rows from the previous operation. If the count is 0, throw an error... – War10ck Jan 14 '15 at 17:58
@Jorge I see an answer for your updated question has already been posted :-) – jeroen Jan 14 '15 at 17:59
@jeroen Shouldn't there be a way to distinguish between having `0` results because there were no matching values vs. having `0` results because we entered column names incorrectly? (e.g. columns that don't even exist) – veich Apr 26 '17 at 12:06
@veich non-existent column names leads to a invalid query and that will throw an exception. – jeroen Apr 26 '17 at 15:28
@jeroen I'm using Sequelize in Node.js and no error was thrown when updating non-existent column names. So I wasn't sure is it because of Sequelize or something more fundamental (PostgreSQL in this case). – veich May 01 '17 at 11:13
@veich Are you sure that PDO is set up to throw exceptions on errors like in the OP's example? – jeroen May 01 '17 at 11:14
@jeroen Like I said I'm using Node.js so I'm not sure what would equate to PDO in this environment. Basically, I just wanted to pinpoint a problem (wether it's DB or Sequelize issue) before posting a random question. – veich May 02 '17 at 09:23

score 3 · Accepted Answer · answered Jan 14 '15 at 17:57

As far as the database (and thus PDO) is concerned - this is not an error. You performed an update statement, and it successfully updated 0 rows.

If you want to handle this as an error, you'd have to do it manually:

$res = $pdo->commit();
if ($pdo->rowCount() == 0) {
    # some exception treatment
}

score 2 · Answer 3 · answered Jan 14 '15 at 17:59

A query which doesn't match any records is NOT an error. It's just an empty result set, which is a perfectly valid result.

The only time you'd get an exception from the query is if there was an actual problem with the query itself, the connection to the db, etc... e.g. A syntax error in the DB, connection failure, permission denied on whatever table(s) you're accessing, etc...

PDO, wrong update still no exception

3 Answers3