How do you retrieve multiple SSMS query error messages from a Java Exception object in a catch statement?

Question

I am developing a JSON Exception HTTP response builder based on error messages from executing a select query in SQL Server Management Studio. I have a try/catch block with the select query execution happening in the try and the response building with exception message happening in the catch.

My problem is that my exception response builder currently can only capture the first error message from a failed SSMS query. If there is more than 1 error message from a single query it ignores everything after the first. I would like to improve the exception response builder so it returns every SSMS error message from a single query execution in the same response. So if I misspell two column names in a unit test, I would like the response to give me 'Invalid column name' message twice. Does anyone know if this is possible? Is there a way to iterate through a Exception object and get multiple errors? Not a stack trace for a single error but just the "sys.messages" error message 'text' value for multiple independent errors from a query?

My catch looks like this:

    catch (Exception e) {
        response = myresponse.exceptionResponse(e);
    }

I am not sure whether the Exception object contains multiple error message instances from a single SSMS query, or if it would just hold the first one and maybe this is the reason why my exception response builder only returns a single error message. Also the code where my exception response is built looks like this:

 public MyResponse exceptionResponse(Exception exception){
    JsonObjectBuilder response = Json.createObjectBuilder();
    if (exception != null) {
        msg = exception.getMessage();
        if (msg == null) {
         msg = NULL_EXCEPTION;
     }
 }
 response.add("response", msg);
 response.add("id",Integer.toString(counter++));
 isError = true;
 responseArray.add(response);
 return this;

}`

If there is a way to iterate through Exceptions, as there is with SQLExceptions, I could definitely do that but I can't find any information on iterating over Exception objects. Any advice would be greatly appreciated!

Answer 1

If you misspell two column names, SQL Server will return only one message with the first column name that was misspelled. I believe it is is not possible to catch all errors that happen because there is only one error that happen at any given time. SQL Server will not keep trying to go ahead with the query execution if it determined that something is wrong with your query.