Our Java code uses Statement thus:
Statement stmt = _CONN.createStatement();
boolean isRst = stmt.execute(sql);
if (isRst) {
do {
ResultSet rs = stmt.getResultSet();
setColumnInfo(rs);
while (rs.next())
outputOneRecord(rs);
rs.close();
} while (fullerror && stmt.getMoreResults());
} else {
int r = stmt.getUpdateCount();
if (r != -1)
logger.info("Update count: {}", r);
}
Nothing unusual; it has worked for years. Recently, we changed the JDBC provider JAR from the old:
<dependency>
<groupId>com.microsoft</groupId>
<artifactId>sqljdbc4</artifactId>
<version>4.0.2206.100</version>
</dependency>
to the new:
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>12.4.1.jre11</version>
</dependency>
Almost everything continues to work as before, except for one stored procedure, which performs some updates first, and then ends with SELECT-statements.
The old JDBC implementation used to return the results of those SELECTs, but, when using the new JAR, our stmt.execute(sql) returns false -- the code reports the update-count and exits without outputting any rows.
Replacing the JDBC JAR-path in CLASSPATH, putting the old dependency in without even recompiling anything, fixes the problem.
Is this a documented change in stmt.execute("EXEC sp_Foo") behavior, or is it a bug in Microsoft's new code?
Nothing unusual
That's quite unusual and possibly a gaping security hole. Does sql contain any input that isn't directly hardcoded in a source file? Any user input, entry from a config file, and so forth? You should almost never use Statement; use PreparedStatement. See OWASP: SQL Injection. If it's all hardcoded, in basis no worries, but you still aren't entirely out of the woods. Does everybody know that the moment you start calling this method with user-provided input, your system is now completely compromisable and easily so? As far as 'security problem' goes, 'vulnerable to SQL injection' is a five alarm fire. You lost, completely, if a malicious agent finds such a hole. They control your entire DB.
Sounds like either a bug or the inevitability of Hyrum: Perhaps the behaviour of executing a call to a stored procedure that ends in a SELECT does not actually specwise require that true is returned, so, it'll be a finger pointing game: File a bug but MS will just deny it saying 'it does not violate any specs'.
The somewhat odd nature of JDBC is that any given callout to the DB can results in a series of 'results'. The vast, vast majority of calls you ever make produces a single result and that result comes in two completely different flavours: Either [A] an update count, or [B] a ResultSet.
When you have multiple results, each individual result can be either of the flavour 'update count' or of the flavour 'resultset'.
This 'multiple results' is explicitly added to JDBC due to stored procedures on some DB engines being capable of producing multiple results and that sounds precisely like what you are running into here.
The API has been kludged; in general the JDBC API is not at all suitable for direct use (use JDBI or JOOQ instead1, some abstraction - it was designed as the low level glue; we don't write in direct machine code either). In this case, support for multi-result was somewhat hamfistedly injected into existing aspects of the API. Hence, why it works in the following, bizarre fashion:
Call execute on your statement. This will indicate the flavour of the first of potentially many results that the execute has produced by a boolean. false means the first result is of the 'update count' flavour, true means it is of the ResultSet flavour. But that refers only to the flavour of the first result, it says zilch about any further results!
Deal with the first result as you wish; presumably, by calling getResultSet() on the Statement only if the flavour of the first result is the ResultSet flavour.
You're not done yet! Call getMoreResults() which returns a boolean. This boolean is the weirdest boolean you're likely to ever see in an API. true means:
So far that's halfway sane. But, false means:
So now you need to disambiguate those 2! You do that by invoking getUpdateCount(). If this returns -1, then it is the "there is not another result". If returns anything else, there may still be more results.
Yes, you can invoke getMoreResults(), get false, then invoke it again, and get true. If your stored proc returns:
5 (update count) A result set 3 (update count) Another result set
Then the sequence of events is:
statement.execute(); // false (5 is UpdCount flavour)
statement.getUpdateCount(); // 5
statement.getMoreResults(); // true
statement.getResultSet(); // results retrievable here
statement.getMoreResults(); // false
statement.getUpdateCount(); // 3
statement.getMoreResults(); // true. true? What the.. yes.
statement.getResultSet(); // results retrievable here
statement.getMoreResults(); // false
statement.getUpdateCount(); // -1
Update your code accordingly by not just going: Oh, exec returned false therefore no result sets. Instead, you need to write some loops.
You should write whole heaps of comments in this code because it is not reasonable to expect whatever engineer sees this code a month from now to know that JDBC's 'multi result' API is this bizarre.
Here's the thing to remember: hasMoreResults() is lying to you. If it says false that does not mean there are no more results.
I would very much like it if you can give us feedback: Is this the answer? I'm posting it as one because it seems relevant (due to you using stored procedures) and as per the spec this is highly likely to be the explanation, but, I have partly based the decision to post it as an answer due to not being aware of any other non-utterly-exotic explanation. However, that might simply be lack of knowledge of MSSQL's JDBC driver.
[1] JDBI and JOOQ mostly expose all aspects of JDBC, but in order to provide an API that is suitable for the task at hand (which is, to let app developers get on with the job of talking in SQL to their db engines), they have removed some extremely exotic dusty corners of JDBC, and this (multi-result support) might be one of them, in which case, this is very mean advice - this specific job can't be done with JOOQ or JDBI. I simply don't know - this footnote exists merely to highlight I am making no guarantees that either JDBI or JOOQ can do this.