dcsimg
Java code to retrieve error messages from an SQL Server stored procedure
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   neil_thompson
Posted On:   Wednesday, October 8, 2003 01:58 AM

Hello I have a stored procedure to update a table that I am calling from my Java DAO object using the java.sql.CallableStatement execute method. Within the stored procedure I raise an error if the update is unsuccessful like this: IF @@ROWCOUNT <> 1 RAISERROR 99999 'update unsuccessful' Is there a way to catch just the error message String (e.g. 'update unsuccessful') so I can display a nice error message? At the moment, the error message gets embedded into the exception as shown below and stripping it out manually does not seem like the most elegant solution: Exception: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer] up   More>>

Hello


I have a stored procedure to update a table that I am calling from my Java DAO object using the java.sql.CallableStatement execute method.



Within the stored procedure I raise an error if the update is unsuccessful like this:



IF @@ROWCOUNT <> 1

RAISERROR 99999 'update unsuccessful'


Is there a way to catch just the error message String (e.g. 'update unsuccessful') so I can display a nice error message? At the moment, the error message gets embedded into the exception as shown below and stripping it out manually does not seem like the most elegant solution:



Exception: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer] update unsuccessful at ....sqlserver.SQLServerDepartmentDAO.update(SQLServerDepartmentDAO.java:314)



Many thanks!

   <<Less

Re: Java code to retrieve error messages from an SQL Server stored procedure

Posted By:   Christopher_Koenigsberg  
Posted On:   Wednesday, October 8, 2003 07:56 AM

SQLExceptions have a hierarchical structure, unlike ordinary generic Exceptions, and you can often get more information from a SQLException via something like:



// SQLException ex;
while (null != ex) {
StringBuffer mystr = new StringBuffer(" SQLState: ");
mystr.append( ex.getSQLState() );
mystr.append(", Message: ");
mystr.append( ex.getMessage() );
mystr.append(", Vendor: ");
mystr.append( ex.getErrorCode() );
System.out.println(mystr.toString());
ex = ex.getNextException();
}


I think you'll have to experiment in your situation to see what you get for these various fields, and figure out the best way to parse/extract what you need from them, to recognize the common situations you encounter.


For instance we found that with our Oracle db and jdbc driver (8.1.7), once in a while we would get a strange SQLException for some unknown reason, trying to connect.


Adding this code to get more specific details from a SQLException, we found that the "getMessage()" would say "Protocol violation", in this one situation.


Eventually we figured out that it happened only when the db login username had expired, in Oracle, which our connection pool was trying to use.


So when catching a SQLException in our authentication code, if the getMessage says "Procol violation", we figure that we need to notify the DBA (even if it is ourselves wearing a different hat :-) to unlock the application's db username, change its password, etc.

About | Sitemap | Contact