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

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. :)

Answer 2

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 3

This doesn't explain why it should be like that, but it explains why it could happen. The following byte-code sets -1 to the internal updateCount flag in the SQLServerStatement constructor:

// Method descriptor #401 (Lcom/microsoft/sqlserver/jdbc/SQLServerConnection;II)V
// Stack: 5, Locals: 8
SQLServerStatement(
  com.microsoft.sqlserver.jdbc.SQLServerConnection arg0, int arg1, int arg2) 
throws com.microsoft.sqlserver.jdbc.SQLServerException;

// [...]

34 aload_0 [this]
35 iconst_m1
36 putfield com.microsoft.sqlserver.jdbc.SQLServerStatement.updateCount:int [27]

Now, I will not analyse all possible control-flows, but I'd just say that this is the internal default initialisation value that somehow leaks out to client code. Note, this is also done in other methods:

// Method descriptor #383 ()V
// Stack: 2, Locals: 1
final void resetForReexecute() 
throws com.microsoft.sqlserver.jdbc.SQLServerException;

// [...]

10 aload_0 [this]
11 iconst_m1
12 putfield com.microsoft.sqlserver.jdbc.SQLServerStatement.updateCount:int [27]

// Method descriptor #383 ()V
// Stack: 3, Locals: 3
final void clearLastResult();
0 aload_0 [this]
1 iconst_m1
2 putfield com.microsoft.sqlserver.jdbc.SQLServerStatement.updateCount:int [27]

In other words, you're probably safe interpreting -1 as being the same as 0. If you rely on this result value, maybe stay on the safe side and do your checks as follows:

// No rows affected
if (stmt.executeUpdate() <= 0) {
}
// Rows affected
else {
}

UPDATE: While reading Mark Rotteveel's answer, I tend to agree with him, assuming that -1 is the JDBC-compliant value for "unknown update counts". Even if this isn't documented on the relevant method's Javadoc, it's documented in the JDBC specs, chapter 13.1.2.3 Returning Unknown or Multiple Results. In this very case, it could be said that an IF .. INSERT .. statement will have an "unknown update count", as this statement isn't SQL-standard compliant anyway.

Answer 4

For executeUpdate statements against a DB2 for z/OS server, the value that is returned depends on the type of SQL statement that is being executed:

For an SQL statement that can have an update count, such as an INSERT, UPDATE, or DELETE statement, the returned value is the number of affected rows. It can be:

A positive number, if a positive number of rows are affected by the operation, and the operation is not a mass delete on a segmented table space.

0, if no rows are affected by the operation.

-1, if the operation is a mass delete on a segmented table space.

For a DB2 CALL statement, a value of -1 is returned, because the DB2 database server cannot determine the number of affected rows. Calls to getUpdateCount or getMoreResults for a CALL statement also return -1. For any other SQL statement, a value of -1 is returned.

Answer 5

I haven't seen this anywhere, either, but my instinct would be that this means that the IF prevented the whole statement from executing.

Try to run the statement with a database where the IF passes.

Also check if there are any triggers involved which might change the result.

[EDIT] When the standard says that this function should never return -1, that doesn't enforce this. Java doesn't have pre and post conditions. A JDBC driver could return a random number and there was no way to stop it.

If it's important to know why this happens, run the statement against different database until you have tried all execution paths (i.e. one where the IF returns false and one where it returns true).

If it's not that important, mark it off as a "clever trick" by a Microsoft engineer and remember how much you liked it when you feel like being clever yourself next time.