Catching DB exceptions

[eluser]Andrewkha[/eluser]
Hi!

I have a DB using foreign keys with restrict on delete settings. So if I delete from the DB the record which is not used by other tables, everything is OK. But if I try to delete a record that is tied to records in other tables (using a foreign key), my web application shows the error:

Code:

A Database Error Occurred

Error Number: 1451

Cannot delete or update a parent row: a foreign key constraint fails (`cms`.`cm_photos`, CONSTRAINT `photoid` FOREIGN KEY (`cat`) REFERENCES `cm_photo_cat` (`id`) ON UPDATE CASCADE)

DELETE FROM `cm_photo_cat` WHERE `id` IN ('2')

Filename: Z:\home\cms\www\system\database\DB_driver.php

Line Number: 330

How can I capture this in my code?
I use the following commands:

Code:

$this->db->where_in($this->idkey, $id);
            $this->db->delete($this->table)

Making smth like this

Code:

if(!$this->db->delete($this->table))

doesn't work. Any ideas?

[eluser]jonez[/eluser]
It depends on how the action is run. If it's a normal page change you could try/catch the delete and handle the error gracefully, or if it's an AJAX call you can catch the error event and alert the user.

[eluser]Andrewkha[/eluser]
Hi

Thanks for your response. However the question is which condition should I check in the try/catch statement? When the exception should be thrown?

If I do smth like
try
if(!$this->db->delete($this->table)) {
throw new Exception()}
catch (...)

it doesn't generate any exception. Just get the error as in my initial post...

[eluser]CroNiX[/eluser]
I believe you need to turn db_debug to FALSE in order to bypass CI's automatic db error handling. Otherwise it will use CI's exception class to handle them before you get a chance to. However, I don't think the db class throws its own exceptions, so you need to test the result of the query and manually grab the error. It's been awhile since I attempted this but you can try something like

Code:

if ( ! $this->db->delete($this->table))
{
  $msg = $this->db->_error_message();
  $num = $this->db->_error_number();
}

All db methods return TRUE upon success.

[eluser]jonez[/eluser]
[quote author="Andrewkha" date="1390410732"]Hi

Thanks for your response. However the question is which condition should I check in the try/catch statement? When the exception should be thrown?

If I do smth like
try
if(!$this->db->delete($this->table)) {
throw new Exception()}
catch (...)

it doesn't generate any exception. Just get the error as in my initial post...[/quote]
You would write it like this:

Code:

try {
  $deleted = true;
  $this->db->where( ... );
  $this->db->delete( ... );
}
catch ( Exception $e ) {
  $deleted = false;
}

if ( $deleted == false ) {
  //show page with error msg
}
else {
  //show normal page
}

If that doesn't allow you to bypass the exception I'd look into the db debug flag CroNiX mentioned, I've never had to use it so I can't provide any insight on it.

[eluser]Andrewkha[/eluser]
Jonez,

I'm afraid this will not work. I do not see throw statement in your example...

[eluser]jonez[/eluser]
[quote author="Andrewkha" date="1390458634"]Jonez,

I'm afraid this will not work. I do not see throw statement in your example...[/quote]
Did you try it? I thought the point was to not throw an exception..? If an exception occurs your code will halt. If you catch it you can redirect the user to a page explaining why you couldn't delete the record vs. showing them an error (the exception) they surely won't understand.

If you did want to throw an exception you would put that in the catch statement not the try block. You do not have to throw an exception in a try/catch. A lot of time people will to handle generic errors (using a global exception handler like CI has) but if you want to bypass that handler and handle the error on your own you'd do something like what I wrote above.