Re: DELETE ... RETURNING

Oliver Jowett wrote on 13.07.2009 23:42:
> Thomas Kellerer wrote:
>
>> At least executeUpdate() should not throw an exception.
>
> Wrong, see the javadoc:
Oops ;)

> Your SQL statement is returning a ResultSet object, so executeUpdate
> correctly throws an exception.
>
> Use executeQuery() or execute().

But execute() will not give the information about the returned IDs as
getMoreResults() always returns false.

And in my test getUpdateCount() returned -1 even though rows were deleted.

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

But my understanding is, that it should print "deleted: 3", and then iterate
over the returned ids (but at least show the correct update count)

Statement stmt = con.createStatement();

stmt.executeUpdate(
"CREATE TABLE test_delete (id integer primary key, some_data varchar(100))"
);
stmt.executeUpdate("insert into test_delete values (1, 'first row')");
stmt.executeUpdate("insert into test_delete values (2, 'second row')");
stmt.executeUpdate("insert into test_delete values (3, 'third row')");
con.commit();

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

Thomas