[ http://issues.apache.org/jira/browse/DERBY-438?page=comments#action_12377483 ]
Daniel John Debrunner commented on DERBY-438: --------------------------------------------- Several fixes have been made (seen the Subversion commits) that support blob/clob in row triggers including the case when the LOB value is streamed from the store. Writing a test for statement triggers showed an issue where the implementation of the statement trigger depended on a VTI (ResultSet implementation) that was still at JDBC 1.2 level, thus not supporting BLOB/CLOB. Some description of how triggers are executed has been documented on the wiki at: http://wiki.apache.org/db-derby/TriggerImplementation The proposed change to fix this is to change the VTIs used in triggers from implementations of java.sql.ResultSet that wrap a embedded java.sql.ResultSet that wraps a Derby language ResultSet, to implementations of java.sql.PreparedStatement that simply return the embedded java.sql.ResultSet that wraps a Derby language ResultSet. PreparedStatement VTIs are more flexible that ResultSet VTIs in that they can be updatable (not required in this case) and (for this case) the implementation of the ResultSet can be hidden by the class, allowing different implementations in different situations. This fixes the situation for statement triggers as the ResultSet now returned to the re-written trigger action statement will support BLOB/CLOB as it will be JDBC ResultSet as returned from the embedded driver. Since the api of the VTI does not change this fix should work on soft upgrade for old triggers from 10.1/10.0 and triggers created in 10.2 soft upgrade that will then run in 10.1/10.0 > Update triggers on tables with BLOB and CLOB columns fail at execution time > if the triggered-SQL-statement references the LOB column(s). > ---------------------------------------------------------------------------------------------------------------------------------------- > > Key: DERBY-438 > URL: http://issues.apache.org/jira/browse/DERBY-438 > Project: Derby > Type: Bug > Components: SQL > Versions: 10.1.1.0, 10.2.0.0, 10.0.2.2 > Reporter: A B > Assignee: Daniel John Debrunner > Fix For: 10.2.0.0 > Attachments: go.java > > 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... -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
