Parsing the results

Download JDBC driver

This article describes how SQL Server expects users to fully process results returned from any query.

Update counts and result sets

This section will talk about the two most common results returned from SQL Server: Update Count and ResultSet. In general, any query a user executes will cause one or more of these results to be returned. Users are expected to handle both when processing results.

The following code is an example of how a user could iterate through all results from the server:

Java

try (Connection connection = DriverManager.getConnection(URL); Statement statement = connection.createStatement()) {
    boolean resultsAvailable = statement.execute(USER_SQL);
    int updateCount = -2;
    while (true) {
        updateCount = statement.getUpdateCount();
        if (!resultsAvailable && updateCount == -1)
            break;
        if (resultsAvailable) {
            // handle ResultSet
        } else {
            // handle Update Count
        }
        resultsAvailable = statement.getMoreResults();
    }
}

Exceptions

When you execute a statement that results in an error or an informational message, SQL Server can respond differently. For example, if it can't generate an execution plan, the error message will be thrown immediately on execute(). If an error is related to statement processing, the application needs to parse the result set to see the exception.

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

Java

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. Furthermore, this processing must be done before any additional operations involving the result set (e.g. retrieval of output parameters).

Java

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. Additionally, not only does each result set need to be processed, they need to be processed before retrieving any output parameters from the stored procedures.

Java

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();
}

For example, String SQL = "SELECT * FROM nonexistentTable; SELECT 1;";, throws an exception immediately on execute() and SELECT 1 isn't executed at all.

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

Java

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

See also

Overview of the JDBC driver