how can an application distinguish between sql errors?

[Sol Mayer-Orn]

Hi,

I'd greatly appreciate any advice on the following. It seems a common-sense requirement, but how would you solve it with JDBC ?

We have an Oracle 9i database with some simple constraints (unique keys, not null, foreign keys).
When an sql update failes due to constraint violation, the application must take action depending on the actual problem (e.g the action for "duplicate key" is different from the action for "non null" violation).
How should this be done ?
I have considered:

1) Using SQLException.getSQLState() : vendor-independent, but not detailed enough. For instance, the same code (23000) indicates both "duplicate key" and "null not allowed".

2) Using Oracle specific codes through SQLException.getErrorCode() : we can live with vendor-specific code (not expecting to change the vendor). But it appears to be a nightmare to map. For instance, integrity constraints fall under ORA-00001, 40002, and the range 02290..02299 .

3) A desparate solution, which is not preferrable, would be to re-query the database. For instance, if you expect logical errors to be either "duplicate key" or "foreign key points to non existing department", you can run SQL queries to see whether the key or the department already exist.

How would you go about it ?
Thank you very much in advance

[Pratibha Malhotra]

Hi

getErrorCode() method of SQLException returns the vendor-specific exception code for this SQLException object.

Similarly getMessage() returns the error message string of throwable object.

Check these methods in API

[Sonny Gill]

Yup that's something I have been grappling with too