Check error codes in application or database

[Andy123]

Hello guys,

 

 

I am building an application where I am using a stored procedure in MySQL. In this stored procedure, some errors can occur, such as duplicate entry on a unique column. This would give an error with the code 1062. My question is if I should be checking on this code in my application like below or use signalling in the database (or something else).

 

try {
// Call stored procedure
}

catch (Exception $e) {
if ($e->getCode() == 1062) {
// Duplicate entry
}
}

 

The above is convenient because it allows me to easily determine what went wrong so I can either log it or give the user a precise error message. My worry is that the error code (1062 in this example) is specific to MySQL, so if I were to change database vendor in the future, I would have to refactor my application (update error codes) and my stored procedures. It is unlikely that I will change vendor, but I like to build my application to support future changes.

 

Alternatively, I believe that I could use signalling in my stored procedure. That is, if I detect an error 1062, I can signal an error with my own error code. This allows me to create error codes that are independent of my database vendor, such that if I were to change vendor, all I would have to do would be to change my stored procedure and thus leave my application intact (hopefully). The idea is that I could use error codes that are not already used by the various vendors. I have not checked which ranges are available yet.

 

Perhaps there are other ways to go about it. I would love to hear your thoughts on this.

 

 

Thanks,

Andy

[Andy123]

I just had an idea. Using signalling would make my stored procedures more complicated. I am using a Data Access Layer (DAL) for interacting with my database, so what I am thinking of doing is to have an abstract base class (which I have already) where error codes are mapped to keys that I can use in all of my DAOs/mappers. This would centralize the refactoring required if I were to change database vendor. Something like this:

 

class AbstractDAO {
protected $error_codes = array(
'duplicate_entry' => 1062,
'another_error' => 1234
);

// Other stuff
}

 

class UserDAO extends AbstractDAO {
public function register(User $user) {
try {
// Call stored procedure
}

catch (Exception $e) {
if ($e->getCode() == $this->error_codes['duplicate_entry']) {
// Duplicate entry
}
}
}
}

 

This would probably (?) save me from over complicating my stored procedures. If I change database vendor, I just have to update the error codes in one class. Better yet, the codes are defined within my data access layer.

 

Any comments? Ideas? Suggestions? Recommendations?

 

Thanks.