Derby
  1. Derby
  2. DERBY-438

Update triggers on tables with BLOB and CLOB columns fail at execution time if the triggered-SQL-statement references the LOB column(s).

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.2, 10.1.1.0, 10.2.1.6
    • Fix Version/s: 10.2.1.6
    • Component/s: SQL
    • Labels:
      None

      Description

      Suppose I have 1) a table "t1" with blob data in it, and 2) an UPDATE trigger "tr1" defined on that table, where the triggered-SQL-action for "tr1" references the blob column from the updated ("new") row. Ex:

      create table t1 (id int, updated smallint, bl blob(32000));
      create trigger tr1 after update on t1 referencing new as n_row for each row mode db2sql values length(n_row.bl);

      Assuming that t1 has been populated with some data, then attempts to update t1 will fire the trigger, but the result will be one of the two following errors:

      1) If blob data is < 32K...

      If the actual data in the table is less than 32K in length, the result will be:

      ERROR XCL12: An attempt was made to put a data value of type 'org.apache.derby.impl.jdbc.EmbedBlob' into a data value of type 'BLOB'.

      2) If blob data is > 32K...

      If at least one row in the table has blob data that is longer than 32K (which means that Derby will stream it, so far as I can tell), then the error will be:

      ERROR XCL30: An IOException was thrown when reading a 'BLOB' from an InputStream.
      ERROR XJ001: Java exception: ': java.io.EOFException'.

      Note that for data larger than 32K, this error will occur regardless of whether or not the triggered-SQL-statement
      references the blob column.

      Surprisingly, it doesn't (appear to) matter what the trigger statement is actually doing--so long as it references the blob column at least once, one of these two errors will occur, depending on the length of the data. And if the data is greater than 32k, then the error will happen regardless of what the trigger does or whether or not it references the blob column.

      I looked at the documentation for UPDATE statements and TRIGGER statements, but nowhere did I see anything saying that either of these will not work with blobs. So as far as I can tell, both of the above scenarios should succeed...

      1. go.java
        2 kB
        A B
      2. derby438_draft_statement_LOB_patch.txt
        27 kB
        Daniel John Debrunner
      There are no Sub-Tasks for this issue.

        Activity

        A B created issue -
        A B made changes -
        Field Original Value New Value
        Attachment go.java [ 12311066 ]
        Manish Khettry made changes -
        Assignee Manish Khettry [ mkhettry ]
        Daniel John Debrunner made changes -
        Assignee Manish Khettry [ mkhettry ] Daniel John Debrunner [ djd ]
        Daniel John Debrunner made changes -
        Summary Update triggers on tables with blob columns fail at execution time if the triggered-SQL-statement references the blob column(s). Update triggers on tables with BLOB and CLOB columns fail at execution time if the triggered-SQL-statement references the LOB column(s).
        Daniel John Debrunner made changes -
        Attachment derby438_draft_statement_LOB_patch.txt [ 12326186 ]
        Daniel John Debrunner made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]
        A B made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Kathey Marsden made changes -
        Status Closed [ 6 ] Reopened [ 4 ]
        Resolution Fixed [ 1 ]
        Daniel John Debrunner made changes -
        Assignee Daniel John Debrunner [ djd ] Kathey Marsden [ kmarsden ]
        Kathey Marsden made changes -
        Status Reopened [ 4 ] Closed [ 6 ]
        Resolution Fixed [ 1 ]
        Assignee Kathey Marsden [ kmarsden ] Daniel John Debrunner [ djd ]
        Gavin made changes -
        Workflow jira [ 12321896 ] Default workflow, editable Closed status [ 12800528 ]

          People

          • Assignee:
            Daniel John Debrunner
            Reporter:
            A B
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development