[SOLVED] Handling errors from the database (key violation)

[eluser]jeffersonrpn[/eluser]
Hello. I'm a newbie in CI and I'm trying to create my first application. Check the error below, it's occurs because a primary key in my database has duplicated:

Code:

An Error Was Encountered
Error Number: 1062
Duplicate entry '57-1' for key 1
INSERT INTO user_has_movie SET id_movie=57, id_user=1

How I can catch this Database error and throws an exception? Thanks.

P.S. Sorry if my english is not good.

[eluser]gtech[/eluser]

Code:

$dbRet = $this->db->insert($table, $data_array);
    if (!$dbRet) {
      $ret['ErrorMessage'] = $this->db->_error_message();
      $ret['ErrorNumber'] = $this->db->_error_number();
      log_message('error', "DB Error: (".$ret['ErrorNumber'].") ".$ret['ErrorMessage']);
    } else {

this is how I catch db errors in CI.

[eluser]jeffersonrpn[/eluser]
I can't catch the error. The execution stops in this line:

Code:

$dbRet = $this->db->insert($table, $data_array);

This function don't throws any Exception? That way I can use a try-catch to handle the error. Remember, the SQL query is correct, but can sometimes makes a key violation in the primary key of the table (Duplicate key entry).

Thanks for help.

[eluser]Yauhen_S[/eluser]
Hi! You can try catch the error in CI error template in /system/application/errors/error_db.php. I think this is not a good way, but i still not found more correct solution. Also by this way you can catch other errors. I was trying to change this templates to generate correct JSON for catch errors in AJAX application, but this is not works good

[eluser]gtech[/eluser]
the reason the execution stops in your case is because db_debug is on.. you need to set db_debug to FALSE in the database config file, to catch db errors like my example above. (which is tested on 1.6.2 with MYSQL).

[eluser]Yauhen_S[/eluser]
sounds good, but in this case you are needed make checks for errors after each db query. it would be nice to have some feature for handle errors like hooks

[eluser]gtech[/eluser]
yeah I have created wrapper code to do this for the db functions I use. so rather than call $this->db I call $this->mydb which then calls $this->db.

I might post up this code when I modify it for general use.

[eluser]jeffersonrpn[/eluser]
Thanks guys. With the db_debug set to FALSE I can get the exception.

[eluser]adc[/eluser]
Hello everybody,

[quote author="jeffersonrpn" date="1213335600"]Thanks guys. With the db_debug set to FALSE I can get the exception.[/quote]

I have a similar issue. I've set the db_debug set to FALSE, and the error message is displayed no more, but I cannot get the exception. The try-catch block is the following:

Code:

$str_insert = $this->db->insert_string('customers',$data);
try{
    $this->db->query($str_insert);
}
catch(Exception $e){
    $data['msg'] = $e;
    $this->load->view('customers_edit_view',$data);
}

The catch block doesn't run
Any clue?
ADC

[eluser]gtech[/eluser]
When a query fails the db code will NOT throw an execption. the call $this->db->query will return true if it succeeds and null if the query fails.

Code:

$data = array('name' => 'John', 'email' => '[email protected]');
$str = $this->db->insert_string('customers', $data);

// returns null if SQL error occurs.
$res = $this->db->query($str);

if (!$res) {
  // if query returns null
  $msg = $this->db->_error_message();
  $num = $this->db->_error_number();

  $data['msg'] = "Error(".$num.") ".$msg;
  $this->load->view('customers_edit_view',$data);
}

Ensure DB_DEBUG is set to FALSE in the database config file, or execution will halt when a mysql error occurs (it does not get thrown, it justs exits from the php interpreter)