Assume the following exception arising due to an INSERT INTO that violated a constraint:
CDbException Object ( [message:protected] => CDbCommand failed to execute the SQL statement: SQLSTATE: Unique violation: 7 ERROR: duplicate key value violates unique constraint "location__region_location_id_key" [string:private] => [code:protected] => 0 [file:protected] => /Users/rawtaz/Sites/.../library/yii-1.1.3.r2247/framework/db/CDbCommand.php [line:protected] => 228 [trace:private] => ... }
I would like to be able to handle this situation gracefully by trapping the exception, checking if the problem is a duplicate key violation, and if so either fire off another query or simply just ignore the conflict.
So my question is; Is there a cleaner way to inspect the exception and retrieve some kind of code for what the actual/detailed cause of the exception is, other than to parse the error message? Indeed the message contains an error code, but parsing it isn’t the cleanest and most reliable way of doing this.
I do know that MySQL have INSERT IGNORE INTO and REPLACE INTO, but they are not of interest whatsoever because they are MySQL-specific and thereby does not work with other databases.
Obviously I can do a SELECT before this query and fire the insert only if there was no result from the select, but that’s another story. For now I would like to do this insert first of all, and then handle any problems that arise during the query.