4

I have a database (mysql) table that contains a unique key in barcode column.

What I intend is this:

  • I have a form that has a barcode field.
  • When user submits the form and the barcode entered already exists in the database table, I should just give an error message.
  • Currently, it gives me PDOException: ... Duplicate entry and the page contents does not load.

This is how I did the insert:

$insert = db_insert('mymodule_mytable')->fields($fieldarray)->execute();

At that line, Drupal 7 returns PDO error so I cannot execute the lines after it. Thus, I cannot also check the return value in $insert.

Is there workaround to avoid the PDO error? I need to do something like:

if($insert === FALSE) {
  drupal_set_message(t('Error'), 'error');
}
5

PHP has exception handling in the form of try...catch blocks:

try {
  $insert = db_insert('mymodule_mytable')->fields($fieldarray)->execute();
}
catch (PDOException $e) {
  drupal_set_message(t('Error: %message', array('%message' => $e->getMessage())), 'error');
}

So rather than ignore the error completely, you can actually respond to different types of exception and react accordingly.

  • This works. Thanks. Will mark check after 4 minutes. – Neigyl R. Noval Apr 17 '13 at 17:43
2

I'm not sure if one should rely on exception handling here as the accepted answer suggests. Instead of introducing a try...catch block, a better approach could be to explicitly check if the record with the ID already exists before inserting it:

if (db_query("SELECT 1 FROM {mymodule_mytable} WHERE mid = :mid", array(':mid' => $mid))->fetchField()) {
  drupal_set_message(t('ID already exists!'), 'error');
}
else {
  // Perform the insertion.
}

Edit: As mentioned by kiamlaluno in the comments, the added advantage of this approach is that checking for a duplicate ID can take place separately in the form validation handler, where the user input is supposed to be checked.

  • 1
    In a case like this, I would do as suggested in this answer, considering also that verifying the user input is a task of the form validation handler. – kiamlaluno Apr 17 '13 at 22:44
  • 1
    I would also agree into this. But barcode is a unique_key in the table. So I expect MySQL does not accept duplicate entries and returns. Considering this answer, it would be like double defense (check if barcode already exists and the unique_key in MySQL) and the power of unique_key may become unused. What would be the advantage of checking first if barcode exists than using try...catch? – Neigyl R. Noval Apr 18 '13 at 1:55
  • 1
    @Jamix Exception handling is always the preferred approach - trying to pre-empt exceptions is fine but it can very quickly get out of hand and clog up the codebase with unnecessary code and (in this case) database calls. Far better to let the exception workflow work exactly as the language dictates. Who's to say the exception would definitely be thrown by a duplicate key constraint? What about all the other things that can go wrong with a database statement? We use exceptions to handle these exceptional cases so we don't have to test for every possible error before we run a line of code – Clive Apr 18 '13 at 7:51
  • @Clive That's a valid point, but let's not forget the consideration of verifying the user input separately in the form validation handler as mentioned by kiamlaluno. – jamix Apr 18 '13 at 8:40
  • 1
    That isn't a safe workflow though...no db transaction is started across the validation/submit handler calls so you introduce a potential race condition by presuming that the db result from the validate handler is still valid. I guess you could argue it's wise to use both methods, but the exception handler is an absolute must (as in any code, not just when dealing with db exceptions). I would agree with Neigyl R. Noval, though, that would be an unnecessary duplication of logic in this particular case (and it saves an extra db call in an already db-hungry Drupal app) – Clive Apr 18 '13 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.