Derby
  1. Derby
  2. DERBY-2786

Behaviour of inout parameters in Embedded and Network client is different if parameters are set but the CallableStatment is not executed.

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.3.1.4
    • Fix Version/s: None
    • Component/s: JDBC, Network Client
    • Environment:
      Operating Systems: Fedora Core release 6
    • Urgency:
      Normal
    • Bug behavior facts:
      Embedded/Client difference

      Description

      The behavior of embedded and network client is different in for inout
      parameters if parameters are set but the CallableStatment is not executed.
      For detailed description of this issue on derby-dev, refer to
      http://www.nabble.com/Regarding-DERBY-2658%3A-Converting-jdbcapi-parameterMetaDataJdbc30.java-to-JUnit-tf3882490.html#a11003923

      1. Main.java
        5 kB
        Dag H. Wanvik

        Issue Links

          Activity

          Hide
          Rick Hillegas added a comment -

          Triaged for 10.5.2: Assigned normal urgency.

          Show
          Rick Hillegas added a comment - Triaged for 10.5.2: Assigned normal urgency.
          Hide
          Dag H. Wanvik added a comment -

          Downgrading to minor.

          Show
          Dag H. Wanvik added a comment - Downgrading to minor.
          Hide
          Dag H. Wanvik added a comment -

          One could handle this by changing getXXX to retrieve the value, if
          set, from the parameters_ array, ca so:

          int getIntX(int parameterIndex) throws SqlException {
          :
          setWasNull(parameterIndex);

          if (wasNull_ == WAS_NULL_UNSET &&
          parameterSet_[parameterIndex - 1])

          { return getInt(parameters_[parameterIndex - 1]); }

          else

          { return wasNullX() ? 0 : singletonRowData_.getInt(parameterIndex); }

          }

          but one needs to handle type conversion/checking appropriately as well
          (inside the new getInt(Object o) above). The current conversion methods are geared to pick
          up data from the query execution result, not the parameter array, so fixing this will
          likely require a whole new set of conversion methods.

          Show
          Dag H. Wanvik added a comment - One could handle this by changing getXXX to retrieve the value, if set, from the parameters_ array, ca so: int getIntX(int parameterIndex) throws SqlException { : setWasNull(parameterIndex); if (wasNull_ == WAS_NULL_UNSET && parameterSet_ [parameterIndex - 1] ) { return getInt(parameters_[parameterIndex - 1]); } else { return wasNullX() ? 0 : singletonRowData_.getInt(parameterIndex); } } but one needs to handle type conversion/checking appropriately as well (inside the new getInt(Object o) above). The current conversion methods are geared to pick up data from the query execution result, not the parameter array, so fixing this will likely require a whole new set of conversion methods.
          Hide
          Dag H. Wanvik added a comment -

          Attaching a small repro; Main.java

          Show
          Dag H. Wanvik added a comment - Attaching a small repro; Main.java
          Hide
          Dag H. Wanvik added a comment -

          I had a look at the client code; it does not handle CallableStatement#getXXX()
          prior to executing the procedure INOUT parameters:

          E.g.

          int getIntX(int parameterIndex) throws SqlException

          { super.checkForClosedStatement(); checkGetterPreconditions(parameterIndex); setWasNull(parameterIndex); ** return wasNullX() ? 0 : singletonRowData_.getInt(parameterIndex); }

          The setWasNull throws if the procedure has not been executed yet.
          Embedded handles this. It is not obvious from the Javadoc
          that this is a requirement:

          (quote from CallableStatement):
          "The type of all OUT parameters must be registered prior to executing the stored procedure;
          their values are retrieved after execution via the get methods provided here."

          I found no verbiage discussing whether it is legal to access an INOUT parameter prior to execution.

          BTW, in my small repro, I got an exception on the client (not wrong value as mention in thread; don't know why;
          looking at the code it seems there should be an exception, but maybe the some flag is not cleared on
          re-execution.

          Output from my repro:

          • Testing with org.apache.derby.jdbc.ClientDriver
            XJ088: java.sql.SQLException: Invalid operation: wasNull() called with no data retrieved.
            java.sql.SQLException: Invalid operation: wasNull() called with no data retrieved.
            at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95)
            at org.apache.derby.client.am.SqlException.getSQLException(SqlException.java:362)
            at org.apache.derby.client.am.CallableStatement.getInt(CallableStatement.java:322)
            at derby2786.Main.doStuff(Main.java:126)
            at derby2786.Main.doBothDrivers(Main.java:192)
            at derby2786.Main.main(Main.java:211)
            Caused by: org.apache.derby.client.am.SqlException: Invalid operation: wasNull() called with no data retrieved.
            at org.apache.derby.client.am.CallableStatement.wasNullX(CallableStatement.java:226)
            at org.apache.derby.client.am.CallableStatement.getIntX(CallableStatement.java:331)
            at org.apache.derby.client.am.CallableStatement.getInt(CallableStatement.java:313)
            ... 3 more
          • Testing with org.apache.derby.jdbc.EmbeddedDriver
            DUMMYINT alias before returned 6
            DUMMYINT alias after returned 22222
          Show
          Dag H. Wanvik added a comment - I had a look at the client code; it does not handle CallableStatement#getXXX() prior to executing the procedure INOUT parameters: E.g. int getIntX(int parameterIndex) throws SqlException { super.checkForClosedStatement(); checkGetterPreconditions(parameterIndex); setWasNull(parameterIndex); ** return wasNullX() ? 0 : singletonRowData_.getInt(parameterIndex); } The setWasNull throws if the procedure has not been executed yet. Embedded handles this. It is not obvious from the Javadoc that this is a requirement: (quote from CallableStatement): "The type of all OUT parameters must be registered prior to executing the stored procedure; their values are retrieved after execution via the get methods provided here." I found no verbiage discussing whether it is legal to access an INOUT parameter prior to execution. BTW, in my small repro, I got an exception on the client (not wrong value as mention in thread; don't know why; looking at the code it seems there should be an exception, but maybe the some flag is not cleared on re-execution. Output from my repro: Testing with org.apache.derby.jdbc.ClientDriver XJ088: java.sql.SQLException: Invalid operation: wasNull() called with no data retrieved. java.sql.SQLException: Invalid operation: wasNull() called with no data retrieved. at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95) at org.apache.derby.client.am.SqlException.getSQLException(SqlException.java:362) at org.apache.derby.client.am.CallableStatement.getInt(CallableStatement.java:322) at derby2786.Main.doStuff(Main.java:126) at derby2786.Main.doBothDrivers(Main.java:192) at derby2786.Main.main(Main.java:211) Caused by: org.apache.derby.client.am.SqlException: Invalid operation: wasNull() called with no data retrieved. at org.apache.derby.client.am.CallableStatement.wasNullX(CallableStatement.java:226) at org.apache.derby.client.am.CallableStatement.getIntX(CallableStatement.java:331) at org.apache.derby.client.am.CallableStatement.getInt(CallableStatement.java:313) ... 3 more Testing with org.apache.derby.jdbc.EmbeddedDriver DUMMYINT alias before returned 6 DUMMYINT alias after returned 22222

            People

            • Assignee:
              Unassigned
              Reporter:
              Ramin Moazeni
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:

                Development