Derby
  1. Derby
  2. DERBY-1938

Add support for setObject(<arg>, null)

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.7.1.1
    • Component/s: JDBC
    • Labels:
      None

      Description

      Derby presently does not implement support for the method
      PreparedStatement.setObject (and similarly for CallableStatement.setObject)
      when the supplied value is null, unless a type argument (3rd arg) is also present.
      That is, in:
      void setObject(int parameterIndex,
      Object x)
      throws SQLException
      x can not be null.

      Derby will presently throw an SQLException (client: XJ021, embedded: 22005)
      if x is null when calling this method on a preparedStatement.

      Porting some applications may be made easier if this restriction is lifted.
      See also discussion in DERBY-1904.

      1. DERBY-1938.patch
        117 kB
        Tomohito Nakayama
      2. derby-1938-1a-reworked_patch.diff
        4 kB
        Kristian Waagan
      3. derby-1938-1b-reworked_patch.diff
        6 kB
        Kristian Waagan

        Issue Links

          Activity

          Hide
          Patrick Holthuizen added a comment -

          Another thank you from me too.

          Show
          Patrick Holthuizen added a comment - Another thank you from me too.
          Hide
          Kristian Waagan added a comment -

          Committed patch 1b to trunk with revision 995089.

          Show
          Kristian Waagan added a comment - Committed patch 1b to trunk with revision 995089.
          Hide
          Dag H. Wanvik added a comment -

          Thanks for handling this one, guys. +1 from me too.

          Show
          Dag H. Wanvik added a comment - Thanks for handling this one, guys. +1 from me too.
          Hide
          Knut Anders Hatlen added a comment -

          Thanks for the updated patch, Kristian. +1 from me.

          Show
          Knut Anders Hatlen added a comment - Thanks for the updated patch, Kristian. +1 from me.
          Hide
          Kristian Waagan added a comment -

          Thanks, Knut Anders.

          Attaching a revised patch 1b. More of the existing test could have been modified as well, but I won't do that.
          derbyall passed with the changes introduced by the patch.

          Show
          Kristian Waagan added a comment - Thanks, Knut Anders. Attaching a revised patch 1b. More of the existing test could have been modified as well, but I won't do that. derbyall passed with the changes introduced by the patch.
          Hide
          Knut Anders Hatlen added a comment -

          Hi Kristian,

          The patch looks good to me. One small nit: In the modified test case in ParameterMappingTest we could now remove the try/catch and just pass on exceptions up to the JUnit framework.

          Show
          Knut Anders Hatlen added a comment - Hi Kristian, The patch looks good to me. One small nit: In the modified test case in ParameterMappingTest we could now remove the try/catch and just pass on exceptions up to the JUnit framework.
          Hide
          Kristian Waagan added a comment -

          Attaching a reworked patch, needs review.
          Awaiting test results (suites.All passed, derbyall still running).

          Show
          Kristian Waagan added a comment - Attaching a reworked patch, needs review. Awaiting test results (suites.All passed, derbyall still running).
          Hide
          Patrick Holthuizen added a comment -

          I think, although the issue is marked as minor, it makes Derby easier to use and therefor may have a positive impact on the adoption of Derby with starters. And as said before it is completely in line with the JDBC 3.0 and 4.0 specification as Dag pointed out.

          Show
          Patrick Holthuizen added a comment - I think, although the issue is marked as minor, it makes Derby easier to use and therefor may have a positive impact on the adoption of Derby with starters. And as said before it is completely in line with the JDBC 3.0 and 4.0 specification as Dag pointed out.
          Hide
          Knut Anders Hatlen added a comment -

          If someone is willing to do the work to update the patch so that it applies cleanly on head of trunk, and it passes the tests, I think we could check it in. There was some controversy initially when the patch was contributed, but I agree with Dag's comment that the JDBC 4.0 API implies that it's OK for an implementation to accept setObject(<arg>, null), although it's not required.

          Show
          Knut Anders Hatlen added a comment - If someone is willing to do the work to update the patch so that it applies cleanly on head of trunk, and it passes the tests, I think we could check it in. There was some controversy initially when the patch was contributed, but I agree with Dag's comment that the JDBC 4.0 API implies that it's OK for an implementation to accept setObject(<arg>, null), although it's not required.
          Hide
          Patrick Holthuizen added a comment -

          This is issue has a patch. Is it going to be applied?

          Show
          Patrick Holthuizen added a comment - This is issue has a patch. Is it going to be applied?
          Hide
          Kathey Marsden added a comment -

          Unassigning due to inactivity. Please reassign yourself if you would like to work on this issue.

          Show
          Kathey Marsden added a comment - Unassigning due to inactivity. Please reassign yourself if you would like to work on this issue.
          Hide
          Kristian Waagan added a comment -

          Clearing patch available flag. No activity on this for a long time.
          If anyone feels the itch, please help drive this issue to completion.
          It is not quite clear to me what is actually holding it back, but there seems to be uncertainty on how it resolve the issue.

          Show
          Kristian Waagan added a comment - Clearing patch available flag. No activity on this for a long time. If anyone feels the itch, please help drive this issue to completion. It is not quite clear to me what is actually holding it back, but there seems to be uncertainty on how it resolve the issue.
          Hide
          Dag H. Wanvik added a comment -

          I googled and found a discussion on this on the mailinglist
          for the Jakarta taglibs project:

          http://www.mail-archive.com/taglibs-user@jakarta.apache.org/msg07198.html

          This thread (dated; 2004) seems to indicate that at least the jTDS driver for SQL server
          accepts this usage.

          Digging more, the next link says that the behavior is sanctioned an in deed checked
          by the J2EE CTS (the person claims to have consulted former spec lead Jonathan Bruce):

          http://www.mail-archive.com/taglibs-user@jakarta.apache.org/msg06987.html

          This is a link to mail by Lance, indicating the Oracle can also accept this in a
          "CTS compatibility mode":

          http://www.mail-archive.com/taglibs-user@jakarta.apache.org/msg06997.html

          The JDBC Tutorial books says "no", the 4.0 API says "OK, but not guaranteed portable".
          The API is more authoritative than the book, according to Lance.

          Given the above, and that the API is more authoritative than the book, I think it would
          perhaps be OK for Derby to support this in the interest of ease of portability of apps to
          Derby.

          Not my itch, though

          Show
          Dag H. Wanvik added a comment - I googled and found a discussion on this on the mailinglist for the Jakarta taglibs project: http://www.mail-archive.com/taglibs-user@jakarta.apache.org/msg07198.html This thread (dated; 2004) seems to indicate that at least the jTDS driver for SQL server accepts this usage. Digging more, the next link says that the behavior is sanctioned an in deed checked by the J2EE CTS (the person claims to have consulted former spec lead Jonathan Bruce): http://www.mail-archive.com/taglibs-user@jakarta.apache.org/msg06987.html This is a link to mail by Lance, indicating the Oracle can also accept this in a "CTS compatibility mode": http://www.mail-archive.com/taglibs-user@jakarta.apache.org/msg06997.html The JDBC Tutorial books says "no", the 4.0 API says "OK, but not guaranteed portable". The API is more authoritative than the book, according to Lance. Given the above, and that the API is more authoritative than the book, I think it would perhaps be OK for Derby to support this in the interest of ease of portability of apps to Derby. Not my itch, though
          Hide
          Tomohito Nakayama added a comment -

          Next information was posted to mailing list.

          The following wording was added to the JDBC 4.0 javadocs to address this issue:

          Note: Not all databases allow for a non-typed Null to be sent to the backend. For maximum portability, the setNull or the setObject(int parameterIndex, Object x, int sqlType) method should be used instead of setObject(int parameterIndex, Object x).

          I think the decision should be deferred and wait for requests ...

          Show
          Tomohito Nakayama added a comment - Next information was posted to mailing list. The following wording was added to the JDBC 4.0 javadocs to address this issue: Note: Not all databases allow for a non-typed Null to be sent to the backend. For maximum portability, the setNull or the setObject(int parameterIndex, Object x, int sqlType) method should be used instead of setObject(int parameterIndex, Object x). I think the decision should be deferred and wait for requests ...
          Hide
          Tomohito Nakayama added a comment -

          I think DERBY-1904 suggests to support for setObject(<arg>, null) In the view of other JDBC driver and/or applications ...

          I think it is subtle in spec because 13.2.2.3 observe silent for type of java null ...

          Show
          Tomohito Nakayama added a comment - I think DERBY-1904 suggests to support for setObject(<arg>, null) In the view of other JDBC driver and/or applications ... I think it is subtle in spec because 13.2.2.3 observe silent for type of java null ...
          Hide
          Daniel John Debrunner added a comment -

          Section 13.2.2.2 does not apply here. Since Java null has no type it cannot be mapped using this rule:
          "the Java Object mapped using the default mapping for that object type. "

          I think the real justification for changing setObject(col, null) seems be to match other JDBC drivers (which ones?) and/or applications that seem to expect this to work. But there's liittle evidence of that justification in this thread.

          I think it's clear that the JDBC spec (from the tutorial) indicates that applications should not depend on this behaviour.

          Show
          Daniel John Debrunner added a comment - Section 13.2.2.2 does not apply here. Since Java null has no type it cannot be mapped using this rule: "the Java Object mapped using the default mapping for that object type. " I think the real justification for changing setObject(col, null) seems be to match other JDBC drivers (which ones?) and/or applications that seem to expect this to work. But there's liittle evidence of that justification in this thread. I think it's clear that the JDBC spec (from the tutorial) indicates that applications should not depend on this behaviour.
          Hide
          Tomohito Nakayama added a comment -
          • Description of the patch
          • Make it possible to setObject(<arg>,null) in embedded and NetClient.
          • Result of the test
          • Execute derbyall and found no error.
          Show
          Tomohito Nakayama added a comment - Description of the patch Make it possible to setObject(<arg>,null) in embedded and NetClient. Result of the test Execute derbyall and found no error.
          Hide
          Tomohito Nakayama added a comment -

          In the spec of jdbc-3_0, I found next content.

          13.2.2.2 Type Conversions Using the Method setObject
          <snip>
          If setObject is called without a type parameter, the Java Object
          mapped using the default mapping for that object type.
          <snip>

          13.2.2.3 Setting NULL Parameters
          <snip>
          If a Java null is passed to any of the setter methods
          parameter will be set to JDBC NULL.

          According to this content, I think we can remove code to throw exception of dataTypeConversion in
          http://issues.apache.org/jira/browse/DERBY-1938#action_12440800 .

          Show
          Tomohito Nakayama added a comment - In the spec of jdbc-3_0, I found next content. 13.2.2.2 Type Conversions Using the Method setObject <snip> If setObject is called without a type parameter, the Java Object mapped using the default mapping for that object type. <snip> 13.2.2.3 Setting NULL Parameters <snip> If a Java null is passed to any of the setter methods parameter will be set to JDBC NULL. According to this content, I think we can remove code to throw exception of dataTypeConversion in http://issues.apache.org/jira/browse/DERBY-1938#action_12440800 .
          Hide
          Tomohito Nakayama added a comment -

          I found next comment in the code.

          org.apache.derby.impl.jdbc.EmbedPreparedStatement :

          public final void setObject(int parameterIndex, Object x) throws SQLException {
          checkStatus();
          int colType = getParameterJDBCType(parameterIndex);

          // JDBC Tutorial and Reference books states in the PreparedStatement
          // overview, that passing a untyped null into setObject() is not allowed.
          // JCC disallows this, basically SQL can not handle a untyped NULL.
          // Section 25.1.6 (Third edition), 24.1.5 (Second Edition)

          if (x == null)

          { //setNull(parameterIndex, colType); //return; throw dataTypeConversion(parameterIndex, "null"); }
          Show
          Tomohito Nakayama added a comment - I found next comment in the code. org.apache.derby.impl.jdbc.EmbedPreparedStatement : public final void setObject(int parameterIndex, Object x) throws SQLException { checkStatus(); int colType = getParameterJDBCType(parameterIndex); // JDBC Tutorial and Reference books states in the PreparedStatement // overview, that passing a untyped null into setObject() is not allowed. // JCC disallows this, basically SQL can not handle a untyped NULL. // Section 25.1.6 (Third edition), 24.1.5 (Second Edition) if (x == null) { //setNull(parameterIndex, colType); //return; throw dataTypeConversion(parameterIndex, "null"); }
          Hide
          Bernt M. Johnsen added a comment -

          NOTE: Tutorial, 3rd ed says (25.1.6 p. 653): "The method setObject, however, can take a null value only if the JDBC type is specified".

          Although the tutorial is not the spec, it's clearly the intention that the SQL NULL in JDBC should behave like SQL NULL in the SQL spec, and that JDBC compliant applications should not call setObject(n, null) and that drivers are not required to implement it to be JDBC compliant.

          Show
          Bernt M. Johnsen added a comment - NOTE: Tutorial, 3rd ed says (25.1.6 p. 653): "The method setObject, however, can take a null value only if the JDBC type is specified". Although the tutorial is not the spec, it's clearly the intention that the SQL NULL in JDBC should behave like SQL NULL in the SQL spec, and that JDBC compliant applications should not call setObject(n, null) and that drivers are not required to implement it to be JDBC compliant.

            People

            • Assignee:
              Kristian Waagan
              Reporter:
              Dag H. Wanvik
            • Votes:
              2 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development