Network Server returns no result sets for a procedure call that returns no result

• Key: DERBY-211
• Type: Bug
• Status: Open
• Priority: Major
• Assignee: Unassigned
• Reporter: Kathey Marsden
• Component/s: Network Server
• Affects Version/s: 10.1.1.0

Description

For a call of a procedure with no dynamic results embedded
Cloudscape returns a one result, an update count of zero. However, using the network server no results are returned. We should be consistent.

To reproduce
See call za() in lang/procedure.java test.

Comments

[David Van Couvering]

What is the right behavior? Should we return no results or a result with a zero update count? My intuition says no results makes more sense, but perhaps we should make the network server be consistent with the embedded behavior...

[David Van Couvering]

From the JDBC 3 spec, section 13.3.3.3 we have

"If the type or number of results returned by a CallableStatement object are not
known until run time, the CallableStatement object should be executed with the
method execute. The methods getMoreResults, getUpdateCount, and
getResultSet can be used to retrieve all the results.
The method execute returns true if the first result is a ResultSet object and
false if it is an update count.
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 number of rows that were affected by the statement. If it is -1, it
indicates that there are no more results."

Although it's not explicitly clear, it seems to me the intent is that it should return no results with an update count of zero. I propose to change the embedded driver to behave this way, making it consistent with the client driver and IMHO the intent of the spec. My concern, however, is that this is changing the behavior of an interface that has been around a bit longer within Derby, and I would like to understand if anyone feels this will have a significant impact on users.

[Daniel John Debrunner]

David, your comment is confusing. 'should return no results with an update count of zero' , no results means no results, not no ResultSets, an update count that is not -1 is a result.

Thus I would see no results being returned as (and this is what the network client returns):

cs.execute() returns false
cs.getUpdateCount() returns -1

Did you mean 'update count of negative one', as the behaviour you seem to describe is what that embedded driver does today, while you talk about changing it (to the same thing)?

The embedded driver returns (in this situation)

cs.execute() returns false
cs.getUpdateCount() returns 0

It seems Derby embedded always returns 0 (a single update count result) for statements like DDL, procedure calls, unless the statement returns ResultSet(s) or a valid update count (INSERT/DELETE/UPDATE).

[Anders Morken]

David and Daniel, did you agree on the proper solution for this? This is marked as a major priority newcomer bug, but this newcomer is unsure about the proper solution. Should the network client mirror the embedded driver, or was there a problem with the embedded driver's adherence to the spec in this case?

[Daniel John Debrunner]

Not sure there was clarity. Looking a little more, at the javadoc for Statement.executeUpdate, if the same call was made using executeUpdate() then the correct return for executeUpdate would be 0.

PreparedStatement.executeUpdate
"Returns:
    either (1) the row count for INSERT, UPDATE, or DELETE statements or (2) 0 for SQL statements that return nothing"

Thus I believe the correct sequence here would be:

cs.executeUpdate() returns 0
cs.getUpdateCount() returns 0 // returns the current update count
cs.getMoreResults() returns false // move to next result, false to indicate it is not a ResultSet
cs.getUpdateCount() returns -1 // indicates no more results

Thus to match, executing using cs.execute() would be

cs.execute() returns false
cs.getUpdateCount() returns 0
cs.getMoreResults() returns false // move to next result, false to indicate it is not a ResultSet
cs.getUpdateCount() returns -1 // indicates no more results

-------------------------------------------------------------------------------------------
Though maybe reading the javadoc different way one could say this is a valid sequence

cs.executeUpdate() returns 0
cs.getUpdateCount() returns -1 // no results (the 0 is not an update count)
cs.getMoreResults() returns false // move to next result, false to indicate it is not a ResultSet
cs.getUpdateCount() returns -1 // no results

cs.execute() returns false
cs.getUpdateCount() returns -1
cs.getMoreResults() returns false // move to next result, false to indicate it is not a ResultSet
cs.getUpdateCount() returns -1 // indicates no more results

--------------------------------------------------------------------------

JDBC 4.0 proposed final draft clarifies this behaviour. The changes are:

section 13.2.4.2 JDBC 3.0 (executeUpdate)
"If the statement being executed returns a ResultSet object, an SQLException is
thrown."

changed to

13.3.3.2 JDBC 4.0 (executeUpdate)

"If the stored procedure does not return a update count, the method executeUpdate
throws an SQLException."

This new text is in conflct with the javadoc for PreparedStatement.executeUpdate which has not changed from JDBC 3.0.

Then this text is new in JDBC 4.0 section 13.3.3.3

"The method execute returns true if the first result is a ResultSet object and false
if it is an update count.
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."

Note this text points to the second approach in this comment, that getUpdateCount would return -1.
However the first sentence does conflict the second, what if there is no update count?

I will send this to the JDBC 4.0 expert group.

[Daniel John Debrunner]

13.3.3.2 JDBC 4.0 (CallableStatement.executeUpdate) has not changed from JDBC 3.0's 13.3.3 section
for CallableStatement., I was looking at the PreparedStatement section in JDBC 3.0 by mistake.

Section 13.3.3.2 JDBC 4.0 (CallableStatement.executeUpdate) and JDBC 3.0's 13.3.3 sections
do seem to be in conflict with the javadoc for PreparedStatement..executeUpdate.
(No override of executeUpdate exists in CallableStatement).

[Dag H. Wanvik]

I have pinged Lance on the JDBC EG about this one again, would be nice toi get this settled

I had to add more cruft to CommentTest.java as part of DERBY-4338 for the client/embedded difference on this,
please search for "DERBY-211" once this gets fixed.