File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes how can an application distinguish between sql errors? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "how can an application distinguish between sql errors?" Watch "how can an application distinguish between sql errors?" New topic

how can an application distinguish between sql errors?

Sol Mayer-Orn
Ranch Hand

Joined: Nov 13, 2002
Posts: 311

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
Ranch Hand

Joined: Dec 21, 2003
Posts: 199

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

~ Pratibha Malhotra<br /> <br />Sun Certified Java Programmer<br />SCEA 1.4 (In Progress)<br />~~~~~~~~~~~~~~~~~~~~~~~~~~~<br />"Many of life's failures are people who did not realize how close they were to success when they gave up!!"
Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

Yup that's something I have been grappling with too
I agree. Here's the link:
subject: how can an application distinguish between sql errors?
It's not a secret anymore!