Database Integrity Messages

MickB

Hi,

What is the easiest way to handle database integrity errors? I am trying to write a generic editor view which I just point at a table and it allows editing.

For example, I try to delete a record in my selectors table which is a foreign key in my records table. I get this message:

QLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (DEVselection.records, CONSTRAINT records_selector_id_foreign FOREIGN KEY (selector_id) REFERENCES selectors (id))

How can I translate this to a more user friendly message?

Catch the AJAX error or catch somewhere In PHP?

I am using Editor and the PHP Libs.

Mick

allan

Hi Mick,

The best way of doing this is to catch the error before the SQL error is thrown. For the delete case you'd need to use the preRemove server-side event make the query and if it fails then return JSON in the format:

{
  "error": "...."
}

Allan

allan

Actually - I've just had a thought about this - in the release version of Editor you can't cancel the remove action with preRemove. You'd need to perform this validation before the Editor->process() method is called:

if ( Editor::action( $_POST ) === Editor::ACTION_DELETE ) {
  // query the database to check if the delete can happen

  if ( $error ) {
    echo json_encode( [ "error" => "...." ] );
    exit;
  }
}

Not ideal I realise - I'll look at how validation can be improved for removal of data!

Allan