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

User · Answer

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

User · Answer

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

User · Answer

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    lt   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

User · Answer

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

User · Answer

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         Check for errors first   ln 1669    Not an error   Is it a result set   ln 1680    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       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    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

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

Examples related to java

Examples related to sql

Examples related to sql-server-2008

Examples related to jdbc