What does it mean when Statement.executeUpdate() returns -1?

Question

A query that works in management studio and in the executeUpdatemakes that same executeUpdate return -1, which is undefined in any documentation we can find. Its supposed to return only the rowcount or 0. What does this mean? The driver is the JDBC-ODBC bridge if that matters.

Example:

String query = "IF NOT EXISTS (SELECT * FROM animals WHERE animal_name ='" + a +"') INSERT INTO " + table + " (animal_name, animal_desc, species_id) VALUES ('" + a + "', '" + b + "', " + c + ")"; int result = statement.executeUpdate(query); System.out.println(result); 

The query works, as the row is added to the database, it's just strange that it returns -1 where the documentation says it will only return 0 or the rowcount (as I've been corrected).

UPDATE:

Running this in Management Studio results with "Command completed successfully."

IF NOT EXISTS (SELECT * FROM animals WHERE animal_name = 'a')  INSERT INTO animals(animal_name, animal_desc, species_id) VALUES ('a', 'a', 1) 

That should mean the method should return 0 because it doesn't return anything, correct?

Answer 1

As the statement executed is not actually DML (eg UPDATE, INSERT or EXECUTE), but a piece of T-SQL which contains DML, I suspect it is not treated as an update-query.

Section 13.1.2.3 of the JDBC 4.1 specification states something (rather hard to interpret btw):

When the method execute returns true, the method getResultSet is called to retrieve the ResultSet object. When execute returns false, the method getUpdateCount returns an int. If this number is greater than or equal to zero, it indicates the update count returned by the statement. If it is -1, it indicates that there are no more results.

Given this information, I guess that executeUpdate() internally does an execute(), and then - as execute() will return false - it will return the value of getUpdateCount(), which in this case - in accordance with the JDBC spec - will return -1.

This is further corroborated by the fact 1) that the Javadoc for Statement.executeUpdate() says:

Returns: either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing

And 2) that the Javadoc for Statement.getUpdateCount() specifies:

the current result as an update count; -1 if the current result is a ResultSet object or there are no more results

Just to clarify: given the Javadoc for executeUpdate() the behavior is probably wrong, but it can be explained.

Also as I commented elsewhere, the -1 might just indicate: maybe something was changed, but we simply don't know, or we can't give an accurate number of changes (eg because in this example it is a piece of T-SQL that is executed).

Answer 2

So 4 years later, Microsoft has open sourced their JDBC driver on Github. I got a notification about this question today, and went and had a look, and I believe I have found the culprit here, mssql-jdbc/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerStatement.java:1713.

Basically, the driver tries to understand what SQL Server sends back if it is not a definite result set. According to the comments, it goes like this:

1. Check for errors first. (ln 1669)

2. Not an error. Is it a result set? (ln 1680)

3. Not an error or a result set. Maybe a result from a T-SQL statement? That is, one of the following:

   • a positive count of the number of rows affected (from INSERT, UPDATE, or DELETE),
   • a zero indicating no rows affected, or the statement was DDL, or
   • a -1 indicating the statement succeeded, but there is no update count information available (translates to Statement.SUCCESS_NO_INFO in batch update count arrays). (ln 1706)

4. None of the above. Last chance here... Going into the parser above, we know moreResults was initially true. If we come out with moreResults false, the we hit a DONE token (either DONE (FINAL) or DONE (RPC in batch)) that indicates that the batch succeeded overall, but that there is no information on individual statements' update counts. This is similar to the last case above, except that there is no update count. That is: we have a successful result (return true), but we have no other information about it (updateCount = -1). (ln 1693)

5. Only way to get here (moreResults is still true, but no apparent results of any kind) is if the TDSParser didn't actually parse anything. That is, we are at EOF in the response. In that case, there truly are no more results. We're done. (ln 1717)

(Emphasis mine)

So you guys were right in the end. SQL simply can't tell how many rows are affected, and defaults to -1. :)