Constraint Exceptions

[Gregg Bolinger]

Apologies if this is a silly question..

Say I have a constraint on a couple ot tables so that you can't delete from one without deleting from the other. If I issue a DELETE statement on one of the tables, I'll obviously get an SQLException. How would I go about translating that exception into a meaningful message to the user?

Or, is this just something I need to handle in business logic outside of the DB? The reason I ask is because I want the user to be able to delete from Table A if the id in a column has never been used, which would be ok if there is a constraint. And I'd rather not have to do a query to determine if it has been used before issuing the delete, which is what I have done in the past when constraints weren't an option.

Thanks.
[ May 01, 2006: Message edited by: Gregg Bolinger ]

[Maximilian Xavier Stocker]

Kind of depends how meaningful you mean by meaningful.

Generally the vendor code or better yet SQL state of the SQL exception will give you an idea of what generally happened. So you would be able to know for example that the query failed for a constraint/key violation and not because the connection dropped or something.

If that's enough for your needs you're rocking. If you need to know what constraint, what column, etc then it gets not so good.

My personal method for dealing with this is to use whatever error codes and map them to a properties file in a resource bundle. Then it's easy enough to plug in new messages as needs be.

[Gregg Bolinger]

Thanks. It would be enough to know that there was a constraint error. I just didn't want to have to parse the stacktrace for it. But I should be able to grab the SQL Code relatively easily, correct?

[Maximilian Xavier Stocker]

Yes.

// returns an int with the vendor specific code
java.sql.SQLException.getErrorCode()
 
// returns a String with the SQL state for the exception
java.sql.SQLException.getSQLState()

Here are a couple of links for SQL state codes.

http://developer.mimer.se/documentation/Mimer_SQL_Reference_Manual/App_return_status2.html#1110406

http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html

As far as I know you have to pay money for the actual spec. Hence the links to various vendor implementations.

[comment on edit of edit: Well that was an unexpected "feature" ]
[ May 01, 2006: Message edited by: Maximilian Xavier Stocker ]

[Gregg Bolinger]

Thanks for the info. Truly appreciated.