Derby
  1. Derby
  2. DERBY-5042

ResultSet.updateBoolean() on new BOOLEAN type throws exception

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.7.1.1
    • Fix Version/s: 10.8.1.2
    • Component/s: Network Client
    • Labels:
      None
    • Environment:
      OS X 10.6.5, Java 1.6
    • Bug behavior facts:
      Embedded/Client difference

      Description

      I am encountering an error trying to update a BOOLEAN type through an
      updatable ResultSet. My table looks like this:

      CREATE TABLE file_store (
      path VARCHAR(255) NOT NULL,
      network VARCHAR(32) NOT NULL,
      file_blob BLOB,
      file_md5 VARCHAR(32),
      is_directory BOOLEAN NOT NULL DEFAULT false,
      PRIMARY KEY (path, network)
      );

      My SQL looks like this:

      SELECT path, network, is_directory FROM file_store
      WHERE path=? AND network=? AND is_directory=true FOR UPDATE OF path, network, is_directory

      My Java code looks like this:

      ...
      resultSet.moveToInsertRow();
      resultSet.updateString("path", dirPath);
      resultSet.updateString("network", network);
      resultSet.updateBoolean("is_directory", true);
      resultSet.insertRow();

      An exception is thrown in the call to updateBoolean() as follows:

      java.sql.SQLException: An attempt was made to put a data value of type 'byte' into a data value of type 'BOOLEAN'.

      It's a shame, I was looking forward to using the new BOOLEAN type before I roll out the next version of our software. For now I will fallback to SMALLINT. Does/will ALTER TABLE support a change from SMALLINT to BOOLEAN?

      1. derby-5042-1a.diff
        3 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Transition Time In Source Status Execution Times Last Executer Last Execution Date
          Open Open In Progress In Progress
          8d 16h 33m 1 Knut Anders Hatlen 23/Feb/11 19:32
          In Progress In Progress Resolved Resolved
          17h 22m 1 Knut Anders Hatlen 24/Feb/11 12:54
          Resolved Resolved Closed Closed
          843d 20h 24m 1 Knut Anders Hatlen 17/Jun/13 10:19
          Gavin made changes -
          Workflow jira [ 12552962 ] Default workflow, editable Closed status [ 12802713 ]
          Knut Anders Hatlen made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Hide
          Knut Anders Hatlen added a comment -

          [bulk update] Close all resolved issues that haven't been updated for more than one year.

          Show
          Knut Anders Hatlen added a comment - [bulk update] Close all resolved issues that haven't been updated for more than one year.
          Rick Hillegas made changes -
          Fix Version/s 10.8.1.1 [ 12316356 ]
          Fix Version/s 10.8.1.2 [ 12316362 ]
          Rick Hillegas made changes -
          Fix Version/s 10.8.1.1 [ 12316356 ]
          Fix Version/s 10.8.1.0 [ 12315561 ]
          Knut Anders Hatlen made changes -
          Status In Progress [ 3 ] Resolved [ 5 ]
          Issue & fix info [Patch Available]
          Fix Version/s 10.8.0.0 [ 12315561 ]
          Resolution Fixed [ 1 ]
          Hide
          Knut Anders Hatlen added a comment -

          Committed revision 1074127.

          Show
          Knut Anders Hatlen added a comment - Committed revision 1074127.
          Knut Anders Hatlen made changes -
          Issue & fix info [Patch Available]
          Hide
          Knut Anders Hatlen added a comment -

          Derbyall, suites.All and the client/server compatibility tests passed with the patch.

          Show
          Knut Anders Hatlen added a comment - Derbyall, suites.All and the client/server compatibility tests passed with the patch.
          Knut Anders Hatlen made changes -
          Attachment derby-5042-1a.diff [ 12471763 ]
          Hide
          Knut Anders Hatlen added a comment -

          Attaching a patch that makes PreparedStatement and ResultSet agree on whether a boolean value should be represented by a java.lang.Boolean or a java.lang.Short, and also adds a test case that exposes the bug and verifies the fix.

          Running regression tests now.

          Show
          Knut Anders Hatlen added a comment - Attaching a patch that makes PreparedStatement and ResultSet agree on whether a boolean value should be represented by a java.lang.Boolean or a java.lang.Short, and also adds a test case that exposes the bug and verifies the fix. Running regression tests now.
          Knut Anders Hatlen made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          Knut Anders Hatlen made changes -
          Assignee Knut Anders Hatlen [ knutanders ]
          Nirmal Fernando made changes -
          Assignee C.S. Nirmal J. Fernando [ nirmal ]
          Knut Anders Hatlen made changes -
          Link This issue is related to DERBY-5058 [ DERBY-5058 ]
          Hide
          Brett Wooldridge added a comment -

          Perfect, that'll work.

          Show
          Brett Wooldridge added a comment - Perfect, that'll work.
          Hide
          Knut Anders Hatlen added a comment -

          Derby doesn't support cast from smallint to boolean, only from string to boolean. According to the comments in DERBY-4658, that's the only cast to boolean allowed by the SQL standard.

          But there are ways around it. For example, if you have a table T with a smallint column S, you could add a boolean column and populate it like this:

          ij> ALTER TABLE T ADD COLUMN B BOOLEAN;
          0 rows inserted/updated/deleted
          ij> UPDATE T SET B = (S<>0);
          4 rows inserted/updated/deleted
          ij> SELECT * FROM T;
          S |B
          ------------
          NULL |NULL
          0 |false
          1 |true
          2 |true

          4 rows selected

          Show
          Knut Anders Hatlen added a comment - Derby doesn't support cast from smallint to boolean, only from string to boolean. According to the comments in DERBY-4658 , that's the only cast to boolean allowed by the SQL standard. But there are ways around it. For example, if you have a table T with a smallint column S, you could add a boolean column and populate it like this: ij> ALTER TABLE T ADD COLUMN B BOOLEAN; 0 rows inserted/updated/deleted ij> UPDATE T SET B = (S<>0); 4 rows inserted/updated/deleted ij> SELECT * FROM T; S |B ------------ NULL |NULL 0 |false 1 |true 2 |true 4 rows selected
          Hide
          Brett Wooldridge added a comment -

          It shouldn't be a problem if boolean can be cast. In that case, in a future upgrade (of our software), we could create a new column of type boolean and populate it by casting the smallint (which I'm now using) to it. Then dropping the original smallint column. Is cast supported?

          Show
          Brett Wooldridge added a comment - It shouldn't be a problem if boolean can be cast. In that case, in a future upgrade (of our software), we could create a new column of type boolean and populate it by casting the smallint (which I'm now using) to it. Then dropping the original smallint column. Is cast supported?
          Knut Anders Hatlen made changes -
          Bug behavior facts [Embedded/Client difference]
          Component/s Network Client [ 11690 ]
          Hide
          Knut Anders Hatlen added a comment -

          updateBoolean() seems to work on the embedded driver.

          Show
          Knut Anders Hatlen added a comment - updateBoolean() seems to work on the embedded driver.
          Hide
          Knut Anders Hatlen added a comment -

          > Does/will ALTER TABLE support a change from SMALLINT to BOOLEAN?

          ALTER TABLE doesn't support changing the type of the column at all, currently. It only allows changing the length of those types that have a length attribute (VARCHAR, BLOB, CLOB, etc).

          ij> create table t(x smallint);
          0 rows inserted/updated/deleted
          ij> insert into t values 0,1,1,0,1,0,0,1;
          8 rows inserted/updated/deleted
          ij> alter table t alter column x set data type boolean;
          ERROR 42Z15: Invalid type specified for column 'X'. The type of a column may not be changed.

          Show
          Knut Anders Hatlen added a comment - > Does/will ALTER TABLE support a change from SMALLINT to BOOLEAN? ALTER TABLE doesn't support changing the type of the column at all, currently. It only allows changing the length of those types that have a length attribute (VARCHAR, BLOB, CLOB, etc). ij> create table t(x smallint); 0 rows inserted/updated/deleted ij> insert into t values 0,1,1,0,1,0,0,1; 8 rows inserted/updated/deleted ij> alter table t alter column x set data type boolean; ERROR 42Z15: Invalid type specified for column 'X'. The type of a column may not be changed.
          Nirmal Fernando made changes -
          Field Original Value New Value
          Assignee C.S. Nirmal J. Fernando [ nirmal ]
          Hide
          Brett Wooldridge added a comment -

          Forgot to mention, if it matters, I'm using the network server (non-Embedded).

          Show
          Brett Wooldridge added a comment - Forgot to mention, if it matters, I'm using the network server (non-Embedded).
          Brett Wooldridge created issue -

            People

            • Assignee:
              Knut Anders Hatlen
              Reporter:
              Brett Wooldridge
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development