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 Capturing SQLSTATE Messages 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 "Capturing SQLSTATE Messages" Watch "Capturing SQLSTATE Messages" New topic

Capturing SQLSTATE Messages

Gabriel Cane
Ranch Hand

Joined: Mar 27, 2001
Posts: 39
I'm coding an application that does an INSERT into a database that triggers a delete on another record. Currently, the application has no way of letting the user know whether or not the triggered delete worked, only whether the INSERT failed.
The coworker who will be coding the trigger said that he would set the trigger to send a SQLSTATE message indicating whether or not the triggered delete (after the successful insert) worked.
My question is how do I capture this SQLSTATE message so that I can send it back to the user as an error/notification message? Will the SQLSTATE trigger an exception, or will I have to use some part of the JDBC API to capture this message.

Sun Certified Programmer for the Java 2 Platform
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1141

Hi Gabriel,
I don't know what database you are working with (I didn't see any mention of it in your post), but with the Oracle 8i database that I work with, database triggers _always_ (as far as I know) execute in the same transaction context as their triggering event.
In other words, you cannot have a situation where the DELETE operation worked, but the INSERT operation failed. If the INSERT succeeded, then the DELETE also succeeded (and vice-versa).
However, a DELETE operation may not delete any rows (since there were no rows matching the DELETE criteria). In this case -- for all the databases I have worked with -- this is _not_ considered an error.
[That's why the "executeUpdate()" method -- in the "java.sql.Statement" class -- returns an "int" indicating how many rows were affected.]
So if you want to know how many rows the DELETE operation actually deleted, then I don't think that this information is available via SQLSTATE, anyway.
So unless I've totally misunderstood your question, I think you're barking up the wrong tree, and you need to rethink this issue/requirement/problem.
Hope this helps.
Good Luck,
I agree. Here's the link:
subject: Capturing SQLSTATE Messages
It's not a secret anymore!