Logan Logan - 1 year ago 171
Java Question

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

A query that works in management studio and in the

makes that same
, which is undefined in any documentation we can find. Its supposed to return only the rowcount or
. What does this mean? The driver is the JDBC-ODBC bridge if that matters.


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

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


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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download