Derby
  1. Derby
  2. DERBY-211

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

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1.1.0
    • Fix Version/s: None
    • Component/s: Network Server
    • Urgency:
      Normal
    • Issue & fix info:
      Newcomer
    • Bug behavior facts:
      Deviation from standard, Embedded/Client difference

      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.

        Issue Links

          Activity

          Hide
          Dag H. Wanvik added a comment -

          I have talked to the JDBC spec lead, Lance Andersen about this issue again. In his (and my) opinion, the client driver behaves correctly in returning a update of -1. If we want to reconcile the drivers here, we should change the embedded driver's behavior accordingly.

          Show
          Dag H. Wanvik added a comment - I have talked to the JDBC spec lead, Lance Andersen about this issue again. In his (and my) opinion, the client driver behaves correctly in returning a update of -1. If we want to reconcile the drivers here, we should change the embedded driver's behavior accordingly.
          Hide
          Dag H. Wanvik added a comment - - edited

          We should update LangProcedureTest when this issue is fixed; with the the client driver have a different code path that embedded in a test case due to this issue.

          Show
          Dag H. Wanvik added a comment - - edited We should update LangProcedureTest when this issue is fixed; with the the client driver have a different code path that embedded in a test case due to this issue.
          Hide
          Dag H. Wanvik added a comment -

          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.

          Show
          Dag H. Wanvik added a comment - 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.
          Hide
          Daniel John Debrunner added a comment -

          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).

          Show
          Daniel John Debrunner added a comment - 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).
          Hide
          Daniel John Debrunner added a comment -

          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.

          Show
          Daniel John Debrunner added a comment - 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.
          Hide
          Anders Morken added a comment -

          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?

          Show
          Anders Morken added a comment - 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?
          Hide
          Daniel John Debrunner added a comment -

          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).

          Show
          Daniel John Debrunner added a comment - 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).
          Hide
          David Van Couvering added a comment -

          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.

          Show
          David Van Couvering added a comment - 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.
          Hide
          David Van Couvering added a comment -

          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...

          Show
          David Van Couvering added a comment - 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...

            People

            • Assignee:
              Unassigned
              Reporter:
              Kathey Marsden
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:

                Development