Regression: getMoreResults() Behavior Differs in Batched Statements

[Ananya2]

I am trying to implement the suggested pattern for iterating through results using getMoreResults(), but I am observing misaligned results when batching multiple statements. This looks like a bug.

PreparedStatement ps = conn.prepareStatement( "delete from TestTable; " + "INSERT INTO TestTable (c1,c2) VALUES (?, ?);" + "INSERT INTO TestTable (c1,c2) VALUES (?, ?);" + "update TestTable set C1=1;" + "INSERT INTO TestTable (c1,c2) VALUES (?, ?);" + "select * from TestTable" ); boolean hasResults; do { hasResults = ps.getMoreResults(); System.out.println("update count: " + ps.getUpdateCount() + ", has result: " + hasResults); } while (hasResults || ps.getUpdateCount() != -1);
Observed behavior:

With old JDBC driver (v12.8):
update count: 3, has result: false
update count: 1, has result: false
update count: 1, has result: false
update count: 2, has result: false
update count: 1, has result: false
update count: -1, has result: true

With new JDBC driver (v12.10):
update count: 3, has result: false
update count: 2, has result: false
update count: 1, has result: false
update count: -1, has result: true

Expected behavior:
The behavior should remain consistent across driver versions. Based on v12.8, I expect each update/insert/delete statement to return the correct update count, followed by the final SELECT returning a result set.

Environment:

JDBC Driver: 12.8 vs 12.10

SQL Server version: [please fill in]

JDK version: [please fill in]

Additional context:
It seems like some update counts are being skipped or collapsed in v12.10. This regression makes it hard to correctly process batched statements.

[git-ai-bot-test]

[AI Summary]

Summary for Maintainer:

The issue highlights a regression in the behavior of getMoreResults() when processing batched statements using JDBC driver v12.10. Compared to v12.8, certain update counts appear skipped or collapsed, resulting in inconsistent results and making it difficult to handle batched operations properly. The user expects consistent behavior across driver versions, where each statement's update count is reported correctly, followed by the final SELECT returning a result set.

[Draft Resolution Steps by Git.AI]

COMPREHENSIVE TECHNICAL ANALYSIS

---

1. Root Cause Analysis

The issue described in Issue #27 pertains to a regression in the behavior of PreparedStatement.getMoreResults() when executing batched SQL statements using the Microsoft JDBC Driver for SQL Server. Specifically, the update counts returned by the driver in version 12.10 differ from those in version 12.8, leading to skipped or collapsed update counts. This inconsistency makes it difficult to correctly process batched statements, especially when relying on getMoreResults() to iterate through results.

Core Issue

The root cause lies in changes introduced in the handling of update counts for multi-statement queries in JDBC Driver v12.10. A highly relevant commit, c734710, explicitly addresses "update count handling for multi-statement queries executed via PreparedStatement." This suggests that the regression is likely tied to modifications made in this commit, where the logic for processing batched statements and their associated update counts was altered.

Behavioral Analysis

In v12.8, each individual statement in the batch correctly returns its update count, followed by the final SELECT statement returning a result set (hasResult = true). In v12.10, however:

1. Some update counts are collapsed or skipped.
2. The sequence of results deviates from the expected behavior, making it impossible to reliably iterate through all statements using getMoreResults().

This regression likely stems from an optimization or refactoring in how the driver processes batched statements internally. The changes may have inadvertently introduced a bug where certain update counts are aggregated or ignored.

Connection to Related Issues

The regression aligns with patterns observed in other issues related to multi-statement handling in the Microsoft JDBC Driver:

• #2722: Concatenated select SQL statements returning null due to improper handling of result sets.
• #2737: Fixes for update count handling in multi-statement queries.
  These issues highlight recurring challenges with managing batched queries and result sets, particularly after updates to the driver.

---

2. Technical Solution Steps

To resolve this issue, we need to ensure consistent behavior across driver versions and restore the correct handling of update counts and result sets for batched statements. Below are the detailed steps:

Step 1: Verify Driver Behavior

• Test the behavior of getMoreResults() and getUpdateCount() using both v12.8 and v12.10.
• Use a simple multi-statement query to isolate discrepancies:

  DELETE FROM TestTable;
  INSERT INTO TestTable (c1, c2) VALUES (1, 'A');
  UPDATE TestTable SET c1 = 2;
  SELECT * FROM TestTable;

• Log all intermediate results (update count and has result) for comparison.

Step 2: Analyze Driver Source Code

• Review changes introduced in c734710 and related commits.
• Focus on modifications to PreparedStatement and SQLServerStatement classes, particularly methods handling batched queries and update counts.

Step 3: Implement Workaround

Until an official fix is provided, implement a workaround to manually track update counts:

• Use Statement.executeBatch() instead of relying on getMoreResults() for batched execution.
• Parse individual statements and execute them sequentially if batching is critical.

Step 4: Modify Query Execution Logic

If batching cannot be avoided:

• Add explicit markers (e.g., dummy SELECT statements) between each statement to ensure proper separation of results.
• Example:

  DELETE FROM TestTable; SELECT 'marker';
  INSERT INTO TestTable (c1, c2) VALUES (?, ?); SELECT 'marker';
  UPDATE TestTable SET c1 = ?; SELECT 'marker';
  SELECT * FROM TestTable;

Step 5: Adjust Result Iteration Logic

• Use a combination of getUpdateCount() and ResultSet checks to reliably iterate through results:

  boolean hasResults;
  do {
      hasResults = ps.getMoreResults();
      int updateCount = ps.getUpdateCount();
      if (updateCount != -1) {
          System.out.println("Update count: " + updateCount);
      } else if (hasResults) {
          ResultSet rs = ps.getResultSet();
          while (rs.next()) {
              System.out.println(rs.getString("c1") + ", " + rs.getString("c2"));
          }
      }
  } while (hasResults || ps.getUpdateCount() != -1);

Step 6: Validate Against SQL Server Version

• Ensure compatibility with your SQL Server version by testing with different configurations (SELECT @@VERSION).
• Confirm that server-side settings (e.g., batch processing optimizations) do not interfere with driver behavior.

Step 7: Report Issue

• Submit a detailed bug report to the Microsoft JDBC Driver repository, referencing c734710 and your findings.
• Include reproducible test cases and logs from both driver versions.

Step 8: Monitor Updates

• Track related issues such as #2737 for potential fixes.
• Update your application once a patch is released.

---

3. Code Examples & Configuration

Example: Multi-statement Execution

PreparedStatement ps = conn.prepareStatement(
    "DELETE FROM TestTable; " +
    "INSERT INTO TestTable (c1, c2) VALUES (?, ?); " +
    "UPDATE TestTable SET c1 = ?; " +
    "SELECT * FROM TestTable;"
);
ps.setInt(1, 1);
ps.setString(2, "A");
ps.setInt(3, 2);

boolean hasResults;
do {
    hasResults = ps.getMoreResults();
    int updateCount = ps.getUpdateCount();
    if (updateCount != -1) {
        System.out.println("Update count: " + updateCount);
    } else if (hasResults) {
        ResultSet rs = ps.getResultSet();
        while (rs.next()) {
            System.out.println(rs.getString("c1") + ", " + rs.getString("c2"));
        }
    }
} while (hasResults || ps.getUpdateCount() != -1);

Example: Explicit Markers

DELETE FROM TestTable; SELECT 'marker';
INSERT INTO TestTable (c1, c2) VALUES (?, ?); SELECT 'marker';
UPDATE TestTable SET c1 = ?; SELECT 'marker';
SELECT * FROM TestTable;

---

4. Technical Context

Recent Developments

The regression appears tied to optimizations introduced in c734710, which aimed to improve handling of multi-statement queries but inadvertently caused inconsistencies in how update counts are processed. Similar issues have been reported in other contexts, such as concatenated SQL statements (#2722).

Version Compatibility

While v12.8 exhibits expected behavior, v12.10 introduces regressions that may impact applications relying on precise update counts. Until a fix is provided, use v12.8 for critical applications or implement workarounds as described above.

---

References

• c734710: Fix update count handling for multi-statement queries.
• #2722: Concatenated select SQL Statement returns null.
• #2737: Fixes for update count handling.

By following these steps, you can mitigate the impact of this regression while awaiting an official fix from Microsoft JDBC Driver maintainers.

[Triage Priority]
High

Auto-applied Label: bug

Similar Issues (semantic match):

• Inconsistent getMoreResults() behavior with batched statements between v12.8 and v12.10 (score: 0.979)
• Inconsistent getMoreResults() behavior with batched statements between v12.8 and v12.10 (score: 0.979)
• Inconsistent getMoreResults() behavior with batched statements between v12.8 and v12.10 (score: 0.979)