Обсуждение: DELETE ... RETURNING
Hi,
when using the RETURNING clause in a DELETE statement the driver throws an error:
org.postgresql.util.PSQLException: A result was returned when none was expected.
The code that I'm using is as follows:
Statement stmt = connection.createStatement();
stmt.executeUpdate("delete from person where firstname like 'A%' returning id");
Am I missing something or is the "RETURNING" feature not supported by the driver?
I'm using postgresql-8.4-701.jdbc3.jar with Java5
Regards
Thomas
On Mon, Jul 13, 2009 at 9:44 PM, Thomas Kellerer<spam_eater@gmx.net> wrote:
> Statement stmt = connection.createStatement();
> stmt.executeUpdate("delete from person where firstname like 'A%' returning
> id");
>
> Am I missing something or is the "RETURNING" feature not supported by the
> driver?
executeUpdate by definitionem can only return an int, the number of
rows affected.
You probably can use stmt.executeQuery instead
Dennis Brakhane wrote on 13.07.2009 21:51: >> Am I missing something or is the "RETURNING" feature not supported by the >> driver? > > executeUpdate by definitionem can only return an int, the number of > rows affected. True, but the JDBC API also defines Statement.getMoreResults() and Statement.getResultSet() which could be used to return that information At least executeUpdate() should not throw an exception. > You probably can use stmt.executeQuery instead Actually execute() works without throwing an exception, but getMoreResults() and getResultSet() do not return the information about the deleted rows. Additionally when using execute(), getUpdateCount() will return -1 even if rows were deleted. Thomas
Thomas Kellerer wrote: > At least executeUpdate() should not throw an exception. Wrong, see the javadoc: > Throws: > SQLException - if a database access error occurs or the SQL statement returns a ResultSet object Your SQL statement is returning a ResultSet object, so executeUpdate correctly throws an exception. Use executeQuery() or execute(). -O
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
Thomas Kellerer wrote: > 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) You might want to turn on logLevel=2 and see what the server's actually telling us. The update count is usually just derived from the command status string, maybe something odd is happening there with DELETE .. RETURNING -O
Oliver Jowett wrote on 13.07.2009 23:57:
> Thomas Kellerer wrote:
>
>> 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)
>
> You might want to turn on logLevel=2 and see what the server's actually
> telling us. The update count is usually just derived from the command
> status string, maybe something odd is happening there with DELETE ..
> RETURNING
The log output looks fine as far as I can tell:
00:06:22.037 (1) simple execute,
handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@a20892,
maxRows=0, fetchSize=0, flags=1
00:06:22.037 (1) FE=> Bind(stmt=S_1,portal=null)
00:06:22.037 (1) FE=> Execute(portal=null,limit=0)
00:06:22.037 (1) FE=> Parse(stmt=null,query="delete from test_delete returning
id",oids={})
00:06:22.037 (1) FE=> Bind(stmt=null,portal=null)
00:06:22.037 (1) FE=> Describe(portal=null)
00:06:22.037 (1) FE=> Execute(portal=null,limit=0)
00:06:22.037 (1) FE=> Sync
00:06:22.053 (1) <=BE BindComplete [null]
00:06:22.053 (1) <=BE CommandStatus(BEGIN)
00:06:22.053 (1) <=BE ParseComplete [null]
00:06:22.053 (1) <=BE BindComplete [null]
00:06:22.053 (1) <=BE RowDescription(1)
00:06:22.053 (1) <=BE DataRow
00:06:22.053 (1) <=BE DataRow
00:06:22.053 (1) <=BE DataRow
00:06:22.053 (1) <=BE CommandStatus(DELETE 3)
00:06:22.069 (1) <=BE ReadyForQuery(T)
And it still doesn't explain why getMoreResults() is not working either.
Regards
Thomas
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
Oliver Jowett, 14.07.2009 00:35: > 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 The perils of quick and dirty test programs... I actually have that generic loop already, just failed to think it through completely before posting ;) Sorry for the trouble and thanks for the help. Regards Thomas
Thomas Kellerer wrote:
> when using the RETURNING clause in a DELETE statement the
> driver throws an error:
>
> org.postgresql.util.PSQLException: A result was returned when
> none was expected.
>
> The code that I'm using is as follows:
>
> Statement stmt = connection.createStatement();
> stmt.executeUpdate("delete from person where firstname like 'A%' returning id");
>
> Am I missing something or is the "RETURNING" feature not
> supported by the driver?
>
> I'm using postgresql-8.4-701.jdbc3.jar with Java5
That's easy; when you issue an SQL statement that returns a result,
you cannot use executeUpdate(java.lang.String).
Use executeQuery(java.lang.String) or execute(java.lang.String) and then getResultSet().
It does not matter whether the statement starts with SELECT or DELETE, the important
point is whether it returns a result set or not.
See for reference:
http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Statement.html
Yours,
Laurenz Albe