Handling SQLExceptions

When you execute a statement that results in an error or an informational message, SQL Server might respond differently depending whether it is able to generate an execution plan. The error message can be thrown immediately after statement execution or it might require a separate result set. In the latter case, the applications need to parse the result set to retrieve the exception.

When SQL Server is unable to generate an execution plan, the exception is thrown immediately.

String SQL = "SELECT * FROM nonexistentTable;";
try (Statement statement = connection.createStatement();) {
    statement.execute(SQL);
} catch (SQLException e) {
    e.printStackTrace();
}

When SQL Server returns an error message in a result set, the result set needs to be processed to retrieve the exception.

String SQL = "SELECT 1/0;";
try (Statement statement = connection.createStatement();) {
    boolean hasResult = statement.execute(SQL);
    if (hasResult) {
        try (ResultSet rs = statement.getResultSet()) {
            // Exception is thrown on next().
            while (rs.next()) {}
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

If statement execution generates multiple result sets, each result set needs to be processed until the one with the exception is reached.

String SQL = "SELECT 1; SELECT * FROM nonexistentTable;";
try (Statement statement = connection.createStatement();) {
    // Does not throw an exception on execute().
    boolean hasResult = statement.execute(SQL);
    while (hasResult) {
        try (ResultSet rs = statement.getResultSet()) {
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }
        }
        // Moves the next result set that generates the exception.
        hasResult = statement.getMoreResults();
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Note that in case of String SQL = "SELECT * FROM nonexistentTable; SELECT 1;";, exception is thrown immediately on execute() and SELECT 1 is not executed at all.

If the error from SQL Server has severity of 0 to 9, it is considered as an informational message and returned as SQLWarning.

String SQL = "RAISERROR ('WarningLevel5', 5, 2);";
try (Statement statement = connection.createStatement();) {
    boolean hasResult = statement.execute(SQL);
    SQLWarning warning = statement.getWarnings();
    System.out.println(warning);
}