Thread: getUdateCount() vs. RETURNING clause
Hi,
I'm having a problem using an UPDATE statement with a RETURNING clause.
It seems I cannot retrieve the number of rows affected using getUpdateCount()
This is my statement
UPDATE my_table
SET the_column = the_column + 1
WHERE id = =
RETURNING the_column;
My Java code is:
PreparedStatement pstmt = con.prepareStatement(UPDATE_SQL); // the statement from above
pstmt.setInt(1, 42);
boolean hasResult = pstmt.execute();
if (hasResult) {
ResultSet rs = pstmt.getResultSet();
if (rs != null && rs.next()) {
int newId = rs.getInt(1);
System.out.println("newid: " + newId);
}
}
int affected = pstmt.getUpdateCount();
System.out.println("affected: " + affected);
I do see the returned ID from the ResultSet but getUpdateCount() always returns -1 even though I know that a row was
updated.
What am I missing here?
My Postgres version is: 8.4.1
My driver version is: PostgreSQL 8.4 JDBC4 (build 701)
I'm using Java6 (1.6.0_16)
Regards
Thomas
Thomas Kellerer wrote:
> boolean hasResult = pstmt.execute();
>
> if (hasResult) {
> ResultSet rs = pstmt.getResultSet();
> if (rs != null && rs.next()) {
> int newId = rs.getInt(1);
> System.out.println("newid: " + newId);
> }
> }
>
> int affected = pstmt.getUpdateCount();
> System.out.println("affected: " + affected);
>
> I do see the returned ID from the ResultSet but getUpdateCount() always
> returns -1 even though I know that a row was updated.
> What am I missing here?
Update counts and result sets (for a particular result) are mutually
exclusive. If getResultSet() returns non-null then getUpdateCount() must
return -1; see the javadoc for those two methods.
You probably want to call getMoreResults() in there somewhere to step to
the next result.
-O
Oliver Jowett, 25.11.2009 01:40:
> Thomas Kellerer wrote:
>
>> boolean hasResult = pstmt.execute();
>>
>> if (hasResult) {
>> ResultSet rs = pstmt.getResultSet();
>> if (rs != null && rs.next()) {
>> int newId = rs.getInt(1);
>> System.out.println("newid: " + newId);
>> }
>> }
>>
>> int affected = pstmt.getUpdateCount();
>> System.out.println("affected: " + affected);
>>
>> I do see the returned ID from the ResultSet but getUpdateCount() always
>> returns -1 even though I know that a row was updated.
>> What am I missing here?
>
> Update counts and result sets (for a particular result) are mutually
> exclusive. If getResultSet() returns non-null then getUpdateCount() must
> return -1; see the javadoc for those two methods.
Hmm, my understand was a bit different.
The Javadocs simply say
There are no more results when the following is true:
((stmt.getMoreResults() == false) && (stmt.getUpdateCount() == -1))
It doesn't state that they are mutually exclusive. And for me it seemed to imply that I can call getMoreResults() and
getUpdateCount()in a loop in order to process everything that is returned.
Additionally the Javadocs for getUpdateCount() says:
"Gets the *current* result as an update count" and ".. if there are no more results it returns -1"
The word "current" here also let me to believe I can call those methods multiple times.
Regards
Thomas
Thomas Kellerer wrote: > Hmm, my understand was a bit different. > The Javadocs simply say > > There are no more results when the following is true: > ((stmt.getMoreResults() == false) && (stmt.getUpdateCount() == -1)) > > It doesn't state that they are mutually exclusive. And for me it seemed > to imply that I can call getMoreResults() and getUpdateCount() in a loop > in order to process everything that is returned. > > Additionally the Javadocs for getUpdateCount() says: > > "Gets the *current* result as an update count" and ".. if there are no > more results it returns -1" You've done some selective editing there. The javadoc I referred to is this (from the Java 6 javadoc): getResultSet(): Retrieves the current result as a ResultSet object. This method should be called only once per result. Returns: the current result as a ResultSet object or null if the result is an update count or there are no more results --- getUpdateCount(): Retrieves the current result as an update count; if the result is a ResultSet object or there are no more results, -1 is returned. This method should be called only once per result. Returns: the current result as an update count; -1 if the current result is a ResultSet object or there are no more results --- I think that's fairly clear: the "current result" is either a ResultSet, or an update count, but it can't be both. getUpdateCount() explicitly says that it returns "-1 [...] if the current result is a ResultSet object", and getResultSet() explicitly says that it returns "null if the [current] result is an update count." > The word "current" here also let me to believe I can call those methods > multiple times. From memory the postgresql driver doesn't care if you call them multiple times, but FWIW the javadoc says that you should only call them once per result. -O
Oliver Jowett, 25.11.2009 12:13: > > You've done some selective editing there. The javadoc I referred to is > this (from the Java 6 javadoc): > > getResultSet(): > > Retrieves the current result as a ResultSet object. This method should > be called only once per result. Correct, once per *result* not per statement. If the statement returns more than one result, I should be allowed to callit multiple time. I think the base of my (mis)understanding is that the term "current" lead me to believe that the "stack" of results a statementcan hold, could look like this: resultSet update count = 3 update count = 2 resultSet reslutSet So if I create a loop using the condition stated in the Javadocs the program flow would be as follows: 1) stmt.execute() returns true, so I call getResultSet() 2) getMoreResults() returns false, but getUpdateCount() returns 3 ==> go on 3) getMoreResults() returns false, but getUpdateCount() returns 2 ==> go on 4) getMoreResults() returns true, so getResultSet() returns a result set ==> go on 5) getMoreResults() returns true, so getResultSet() returns a result set ==> go on 6) getMoreResults() returns false, getUpdateCount() returns -1 ==> everything was processed. Apparently this interpretation of "current", "next" and "once per _result_" was wrong... Regards Thomas
Thomas Kellerer wrote:
> Oliver Jowett, 25.11.2009 12:13:
>>
>> You've done some selective editing there. The javadoc I referred to is
>> this (from the Java 6 javadoc):
>>
>> getResultSet():
>>
>> Retrieves the current result as a ResultSet object. This method should
>> be called only once per result.
>
> Correct, once per *result* not per statement. If the statement returns
> more than one result, I should be allowed to call it multiple time.
That's right, but you need a call to getMoreResults() to step through
the results between calls, as I suggested in my original response.
> I think the base of my (mis)understanding is that the term "current"
> lead me to believe that the "stack" of results a statement can hold,
> could look like this:
>
> resultSet
> update count = 3
> update count = 2
> resultSet
> reslutSet
Yes, you can have that. You step through the results by calling
getMoreResults(). At any particular point, the current result is either
a resultset or an update count, but never both.
> So if I create a loop using the condition stated in the Javadocs the
> program flow would be as follows:
>
> 1) stmt.execute() returns true, so I call getResultSet()
> 2) getMoreResults() returns false, but getUpdateCount() returns 3 ==> go on
> 3) getMoreResults() returns false, but getUpdateCount() returns 2 ==> go on
> 4) getMoreResults() returns true, so getResultSet() returns a result set
> ==> go on
> 5) getMoreResults() returns true, so getResultSet() returns a result set
> ==> go on
> 6) getMoreResults() returns false, getUpdateCount() returns -1 ==>
> everything was processed.
Yes, this is correct. It will look something like this:
boolean hasResultSet = stmt.execute();
int updateCount = stmt.getUpdateCount();
while (hasResultSet || updateCount != -1) {
if (hasResultSet) {
ResultSet rs = stmt.getResultSet();
// This result is a resultset, process rs.
} else {
// This result is an update count, process updateCount.
}
hasResultSet = stmt.getMoreResults();
updateCount = stmt.getUpdateCount();
}
-O
Oliver Jowett, 25.11.2009 13:16: >> So if I create a loop using the condition stated in the Javadocs the >> program flow would be as follows: >> >> 1) stmt.execute() returns true, so I call getResultSet() >> 2) getMoreResults() returns false, but getUpdateCount() returns 3 ==> go on >> 3) getMoreResults() returns false, but getUpdateCount() returns 2 ==> go on >> 4) getMoreResults() returns true, so getResultSet() returns a result set >> ==> go on >> 5) getMoreResults() returns true, so getResultSet() returns a result set >> ==> go on >> 6) getMoreResults() returns false, getUpdateCount() returns -1 ==> >> everything was processed. > > Yes, this is correct. It will look something like this: So my understanding was correct ;) Back to my original question then: why doesn't the Postgres driver return 1 as the updateCount in this situation? I only get a single result set (which is correct) but never a 1 as the update count. Regards Thomas
Thomas Kellerer wrote:
> Back to my original question then: why doesn't the Postgres driver
> return 1 as the updateCount in this situation?
> I only get a single result set (which is correct) but never a 1 as the
> update count.
Back to my original answer then ;-)
Quoting your original code:
> PreparedStatement pstmt = con.prepareStatement(UPDATE_SQL); // the statement from above
> pstmt.setInt(1, 42);
> boolean hasResult = pstmt.execute();
>
> if (hasResult) {
> ResultSet rs = pstmt.getResultSet();
> if (rs != null && rs.next()) {
> int newId = rs.getInt(1);
> System.out.println("newid: " + newId);
> }
> }
>
> int affected = pstmt.getUpdateCount();
> System.out.println("affected: " + affected);
You never call getMoreResults(), so you are only looking at a single
result, which is either a resultset or an update count, never both.
-O
Oliver Jowett wrote:
> You never call getMoreResults(), so you are only looking at a single
> result, which is either a resultset or an update count, never both.
Also, looking at the code a bit more, RETURNING is a bit of a special
case. Normally, if you have a command that returns a resultset, the
command status is ignored (you generally don't care about the command
status of, for example, a SELECT). Presumably that's happening here too.
(But the advice regarding getMoreResults() is generally applicable, e.g.
if you have a multiple-statement query).
You may have more success using something like this:
> PreparedStatement pstmt = con.prepareStatement("UPDATE something with no RETURNING clause", new String[] {
"some_column"});
> int updateCount = pstmt.executeUpdate();
> ResultSet results = pstmt.getGeneratedKeys();
The driver glues on an appropriate RETURNING clause and arranges for the
resulting resultset to appear via getGeneratedKeys(); the update count
should still appear as expected.
(I haven't actually tried this. caveat emptor)
-O
Oliver Jowett, 25.11.2009 14:42:
> You never call getMoreResults(), so you are only looking at a single
> result, which is either a resultset or an update count, never both.
[...]
Hmm, sorry I missed that in my initial email then. I did call getMoreResults()
The following still returns false for getMoreResults()
PreparedStatement pstmt = con.prepareStatement(update);
pstmt.setInt(1, 1);
boolean hasResult = pstmt.execute();
if (hasResult ) {
ResultSet rs = pstmt.getResultSet();
if (rs != null && rs.next()) {
int newId = rs.getInt(1);
System.out.println("newid: " + newId);
}
}
boolean more = pstmt.getMoreResults(); // returns false
> You may have more success using something like this:
>
>> PreparedStatement pstmt = con.prepareStatement("UPDATE something with no RETURNING clause", new String[] {
"some_column"});
>> int updateCount = pstmt.executeUpdate();
>> ResultSet results = pstmt.getGeneratedKeys();
That indeed works!
But I still think the behaviour with getMoreResults() is - at least - confusing ;)
Thanks a lot for all your patience!
Thomas