Re: DELETE ... RETURNING

Thomas Kellerer wrote:

> The following code will print "deleted: -1" and nothing more.

> stmt.execute("delete from test_delete returning id");
> System.out.println("deleted: " + stmt.getUpdateCount());
> if (stmt.getMoreResults())
> {
>   System.out.println("has result");
>   rs = stmt.getResultSet();
>   while (rs.next())
>   {
>     System.out.println(rs.getObject(1));
>   }
> }

I took another look at this, and your code assumes that the update count
is the first result. It's not. In the case where both a resultset and an
update count are present in a single query, the driver puts the
resultset result first (so that executeQuery() works nicely).

So your first call to getUpdateCount() returns -1 because the current
result is a resultset, not an update count (see the javadoc). Then you
call getMoreResults() which moves to the 2nd result (the update count)
and returns false because there's no resultset (again, see the javadoc).

If you want a general-purpose result processing loop, you want something
like this:

boolean hasResultSet = stmt.execute("...");
while (hasResultSet || stmt.getUpdateCount() != -1) {
  if (hasResultSet) {
    ResultSet rs = stmt.getResultSet();
    // .. process it ..
  } else {
    int updateCount = stmt.getUpdateCount();
    // .. process it ..
  }
  hasResultSet = stmt.getMoreResults();
}

-O