aspose file tools*
The moose likes JDBC 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


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how can an application distinguish between sql errors?" Watch "how can an application distinguish between sql errors?" New topic
Author

how can an application distinguish between sql errors?

Sol Mayer-Orn
Ranch Hand

Joined: Nov 13, 2002
Posts: 311
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
Ranch Hand

Joined: Dec 21, 2003
Posts: 199
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


~ 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
 
Don't get me started about those stupid light bulbs.
 
subject: how can an application distinguish between sql errors?
 
Similar Threads
[CMP] no default value for nullable foreign key
Design Decision: Database User List
Id field is a PK but is also a FK pointing towards a PK of a main table
FOREIGN KEY
SQL Performance Tuning - Release Announcement - Addison-Wesley