This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
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 Murach's Java Servlets and JSP this week in the Servlets 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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how can an application distinguish between sql errors?
 
Similar Threads
FOREIGN KEY
[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
SQL Performance Tuning - Release Announcement - Addison-Wesley