Database transaction error handling

I am aware of the discussions on this topic in the CI forum, StackOverflow and elsewhere. I tried to do my research carefully but it seems to me, that I did not find a proper answer.

The question is how to handle database transaction errors and not to loose the error details.

The CI guide suggested way is

PHP Code:

$this->db->trans_start();
$this->db->query('AN SQL QUERY...');
$this->db->query('ANOTHER QUERY...');
$this->db->trans_complete();

if ($this->db->trans_status() === FALSE)
{
    // generate an error... or use the log_message() function to log your error
} 


However if 'db_debug' in config/database.php is TRUE, the execution fails on the first failing query and the error handling code is never run.
OK, so let's set 'db_debug' to FALSE. In this case the error handling code runs, but how do I find out more about the DB error (error code and message)? Again, the recommended way is to use $this->db->_error_message() and $this->db->_error_number(), but they return nothing when 'db_debug' is FALSE. And this is my problem. It is not enough to detect an error, I need to know more about it.

I found only this solution to the problem, but I don't like it because it is quite verbose:

PHP Code:

try {
  $this->db->trans_begin();
  $res = $this->db->query('AN SQL QUERY...');
  if(!$res) throw new Exception($this->db->_error_message(), $this->db->_error_number());
  $res = $this->db->query('ANOTHER QUERY...');
  if(!$res) throw new Exception($this->db->_error_message(), $this->db->_error_number());
  $this->db->trans_commit();
}
catch (Exception $e) {
  $this->db->trans_rollback();
  log_message('error', sprintf('%s : %s : DB transaction failed. Error no: %s, Error msg:%s, Last query: %s', __CLASS__, __FUNCTION__, $e->getCode(), $e->getMessage(), print_r($this->main_db->last_query(), TRUE)));
} 


Please not that
- I am using the "manual transaction" mode of CI,
- I have to call $this->db->_error_message(), $this->db->_error_number() after each query, because it returnes an ampty response in the catch block.
Only this way I can achieve the detailed error handling but I find it clunky.

Is there a more elegant way?
Thank you

you could write your own library/helper for $res that takes the $db and $sql as arguments and returns either the results set if successful or the error number and message if not.

you would then call it with something like

if $res($conn,$sql)->success == true {
Do some neat stuff here
} else {
error handling goes here
}

Thank you for your comment. However I think that it would be quite the same as in my case; moreover for error handling I prefer exceptions. Anyway, your comment made me consider a possibility to wrap the CI database class so that it would use exceptions for error handling. Then the schema would be clean:

PHP Code:

try {
 $this->db->trans_begin();
 $this->db->query('AN SQL QUERY...');
 $this->db->query('ANOTHER QUERY...');
 $this->db->trans_commit();
}
catch (Exception $e) {
 $this->db->trans_rollback();
 log_message('error', sprintf('%s : %s : DB transaction failed. Error no: %s, Error msg:%s, Last query: %s', __CLASS__, __FUNCTION__, $e->getCode(), $e->getMessage(), print_r($this->main_db->last_query(), TRUE)));
} 


The only problem is that the CI DB class cannot by extended... I'll have to think about it.
Thank you anyway.

Thank you for your comment. However I think that it would be quite the same as in my case; moreover for error handling I prefer exceptions. Anyway, your comment made me consider a possibility to wrap the CI database class so that it would use exceptions for error handling. Then the schema would be clean:

PHP Code:

try {
 $this->db->trans_begin();
 $this->db->query('AN SQL QUERY...');
 $this->db->query('ANOTHER QUERY...');
 $this->db->trans_commit();
}
catch (Exception $e) {
 $this->db->trans_rollback();
 log_message('error', sprintf('%s : %s : DB transaction failed. Error no: %s, Error msg:%s, Last query: %s', __CLASS__, __FUNCTION__, $e->getCode(), $e->getMessage(), print_r($this->main_db->last_query(), TRUE)));
} 


The only problem is that the CI DB class cannot by extended... I'll have to think about it.
Thank you anyway.

Try catch blocks, although messy, are pretty standard approaches. You can hide the transactions (begin/commit/rollback) inside stored procedures, which can return success and failure codes. But then you have to dig in the database to find the errors--and you still have to surround your db calls with try catch just as you do with any I/O access that can fail for reasons beyond your control. The benefit of try catch, log, and rollback is that you have control of the transaction and you have control of what and how you log the errors.