db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kmars...@apache.org
Subject svn commit: r601414 - in /db/derby/code/branches/10.3/java: engine/org/apache/derby/impl/sql/execute/DMLWriteResultSet.java testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
Date Wed, 05 Dec 2007 17:09:19 GMT
Author: kmarsden
Date: Wed Dec  5 09:09:18 2007
New Revision: 601414

URL: http://svn.apache.org/viewvc?rev=601414&view=rev
Log:
DERBY-3238 When table contains large LOB values (> ~32K) trigger execution fails for that
row with ERROR XCL30: An IOException was thrown when reading a 'BLOB'

backport from trunk revision 601110

Modified:
    db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/execute/DMLWriteResultSet.java
    db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java

Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/execute/DMLWriteResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/execute/DMLWriteResultSet.java?rev=601414&r1=601413&r2=601414&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/execute/DMLWriteResultSet.java
(original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/execute/DMLWriteResultSet.java
Wed Dec  5 09:09:18 2007
@@ -21,6 +21,8 @@
 
 package org.apache.derby.impl.sql.execute;
 
+import java.io.InputStream;
+
 import org.apache.derby.iapi.types.DataValueDescriptor;
 import org.apache.derby.iapi.sql.execute.NoPutResultSet;
 import org.apache.derby.iapi.services.io.StreamStorable;
@@ -148,10 +150,29 @@
 					heapIx :
 					baseRowReadMap[heapIx];
 
+                
 				DataValueDescriptor col = row.getColumn(readIx+1);
+				InputStream stream = ((StreamStorable)col).returnStream();
 				((StreamStorable)col).loadStream();
+				// DERBY-3238 
+				// fix up any duplicate streams, for instance in the case of an update with a trigger,
+				// all the columns are read as update columns even if they are not updated, so 
+				// the update column will still have a reference to the original stream.
+				// If we knew from this context that this was an update and we knew the number
+				// of columns in the base table we would be able to calculate exactly the offset to 
+				// check, but we don't have that information from this context.
+				// If DERBY-1482 is fixed, perhaps this code can be removed.
+				
+				if (stream != null)
+					for (int i = 1; i <= row.nColumns(); i++)
+					{
+						DataValueDescriptor c = row.getColumn(i);
+						if (c instanceof StreamStorable)
+							if (((StreamStorable)c).returnStream() == stream)
+								row.setColumn(i, col.getClone());
+					}
+				}
 			}
-		}
 	}
 
 	/**

Modified: db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java?rev=601414&r1=601413&r2=601414&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
(original)
+++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
Wed Dec  5 09:09:18 2007
@@ -20,6 +20,8 @@
  */
 package org.apache.derbyTesting.functionTests.tests.lang;
 
+import java.io.ByteArrayInputStream;
+import java.io.CharArrayReader;
 import java.io.IOException;
 import java.io.InputStream;
 import java.io.Reader;
@@ -397,6 +399,210 @@
     {
         ((List) TRIGGER_INFO.get()).add(info);  
     }
+    
+    /** 
+     * Test for DERBY-3238 trigger fails with IOException if triggering table has large lob.
+     * 
+     * @throws SQLException
+     * @throws IOException
+     */
+    public void testClobInTriggerTable() throws SQLException, IOException
+    {
+    	testClobInTriggerTable(1024);
+        testClobInTriggerTable(16384);
+         
+        testClobInTriggerTable(1024 *32 -1);
+        testClobInTriggerTable(1024 *32);
+        testClobInTriggerTable(1024 *32+1);
+        testClobInTriggerTable(1024 *64 -1);
+        testClobInTriggerTable(1024 *64);
+        testClobInTriggerTable(1024 *64+1);
+        
+    }
+   
+    /**
+     * Create a table with after update trigger on non-lob column.
+     * Insert clob of size clobSize into table and perform update
+     * on str1 column to fire trigger. Helper method called from 
+     * testClobInTriggerTable
+     * @param clobSize size of clob to test
+     * @throws SQLException
+     * @throws IOException
+     */
+    private void testClobInTriggerTable(int clobSize) throws SQLException, IOException {
+    	
+    	// --- add a clob
+    	String trig = " create trigger t_lob1 after update of str1 on lob1 ";
+        trig = trig + " REFERENCING OLD AS old NEW AS new FOR EACH ROW MODE DB2SQL ";
+        trig = trig + " insert into t_lob1_log(oldvalue, newvalue) values (old.str1, new.str1)";
+
+        Statement s = createStatement();
+        
+        s.executeUpdate("create table LOB1 (str1 Varchar(80), c_lob CLOB(50M))");
+        s.executeUpdate("create table t_lob1_log(oldvalue varchar(80), newvalue varchar(80),
chng_time timestamp default current_timestamp)");
+        s.executeUpdate(trig);
+        commit();      
+
+        PreparedStatement ps = prepareStatement("INSERT INTO LOB1 VALUES (?, ?)");
+        
+        ps.setString(1, clobSize +"");
+
+
+        char[] arr = new char[clobSize];
+        for (int i = 0; i < arr.length; i++)
+            arr[i] = 'a';
+
+        // - set the value of the input parameter to the input stream
+        ps.setCharacterStream(2, new CharArrayReader(arr) , clobSize);
+        ps.execute();
+        commit();
+
+        // Now executing update to fire trigger
+        s.executeUpdate("update LOB1 set str1 = str1 || ' '");
+        s.executeUpdate("drop table lob1");
+        s.executeUpdate("drop table t_lob1_log");
+    }
+    
+    
+    
+    /** 
+     * Test for DERBY-3238 trigger fails with IOException if triggering table has large lob.
+     * 
+     * @throws SQLException
+     * @throws IOException
+     */
+    public void testBlobInTriggerTable() throws SQLException, IOException
+    {        
+    	testBlobInTriggerTable(1024);
+        testBlobInTriggerTable(16384);
+         
+        testBlobInTriggerTable(1024 *32 -1);
+        testBlobInTriggerTable(1024 *32);
+        testBlobInTriggerTable(1024 *32+1);
+        testBlobInTriggerTable(1024 *64 -1);
+        testBlobInTriggerTable(1024 *64);
+        testBlobInTriggerTable(1024 *64+1);
+        testBlobInTriggerTable(1024 *1024* 7);
+    }
+    
+    
+    /**
+     * Create a table with after update trigger on non-lob column.
+     * Insert two blobs of size blobSize into table and perform update
+     * on str1 column to fire trigger. Helper method called from 
+     * testBlobInTriggerTable
+     * 
+     * @param blobSize  size of blob to test.
+     * @throws SQLException
+     * @throws IOException
+     */
+    private  void testBlobInTriggerTable(int blobSize) throws SQLException, IOException {
+    	
+
+        String trig = " create trigger t_lob1 after update of str1 on lob1 ";
+        trig = trig + " REFERENCING OLD AS old NEW AS new FOR EACH ROW MODE DB2SQL ";
+        trig = trig + " insert into t_lob1_log(oldvalue, newvalue) values (old.str1, new.str1)";
+
+        Statement s = createStatement();
+        
+        s.executeUpdate("create table LOB1 (str1 Varchar(80), b_lob BLOB(50M), b_lob2 BLOB(50M))");
+        s.executeUpdate("create table t_lob1_log(oldvalue varchar(80), newvalue varchar(80),
chng_time timestamp default current_timestamp)");
+        s.executeUpdate(trig);
+        commit();      
+
+    	// --- add a blob
+        PreparedStatement ps = prepareStatement("INSERT INTO LOB1 VALUES (?, ?, ?)");
+        
+        ps.setString(1, blobSize +"");
+
+
+        byte[] arr = new byte[blobSize];
+        for (int i = 0; i < arr.length; i++)
+            arr[i] = (byte)8;
+
+        // - set the value of the input parameter to the input stream
+        // use a couple blobs so we are sure it works with multiple lobs
+        ps.setBinaryStream(2, new ByteArrayInputStream(arr) , blobSize);
+        ps.setBinaryStream(3, new ByteArrayInputStream(arr) , blobSize);
+        ps.execute();
+        
+        commit();
+        // Now executing update to fire trigger
+        s.executeUpdate("update LOB1 set str1 = str1 || ' '");
+        s.executeUpdate("drop table lob1");
+        s.executeUpdate("drop table t_lob1_log");
+   
+    }
+    
+    /* 
+     * Test an update trigger on a Clob column
+     * 
+     */
+    public void testUpdateTriggerOnClobColumn() throws SQLException, IOException
+    {
+    	Connection conn = getConnection();
+    	Statement s = createStatement();
+    	String trig = " create trigger t_lob1 after update of str1 on lob1 ";
+    	trig = trig + " REFERENCING OLD AS old NEW AS new FOR EACH ROW MODE DB2SQL ";
+    	trig = trig + " insert into t_lob1_log(oldvalue, newvalue) values (old.str1, new.str1)";
+    	s.executeUpdate("create table LOB1 (str1 Varchar(80), C_lob CLOB(50M))");
+        s.executeUpdate("create table t_lob1_log(oldvalue varchar(80), newvalue varchar(80),
chng_time timestamp default current_timestamp)");
+        s.executeUpdate(trig);
+        conn.commit();
+        PreparedStatement ps = prepareStatement("INSERT INTO LOB1 VALUES (?, ?)");
+        int clobSize = 1024*64+1;
+        ps.setString(1, clobSize +"");
+
+
+        // - set the value of the input parameter to the input stream
+        ps.setCharacterStream(2, makeCharArrayReader('a', clobSize), clobSize);
+        ps.execute();
+        conn.commit();
+
+
+        PreparedStatement ps2 = prepareStatement("update LOB1 set c_lob = ? where str1 =
'" + clobSize + "'");
+        ps2.setCharacterStream(1,makeCharArrayReader('b',clobSize), clobSize);
+        ps2.executeUpdate();
+        conn.commit();
+        // 	--- reading the clob make sure it was updated
+        ResultSet rs = s.executeQuery("SELECT * FROM LOB1 where str1 = '" + clobSize + "'");
+        rs.next();
+     
+        Reader r = rs.getCharacterStream(2);
+        int count = 0;
+        int c;
+        do {
+        	c = r.read();        	 
+        	if (c!= -1)
+        	{
+        		count++;
+        		assertEquals('b',c);
+        	}	
+        } while (c != -1);
+          
+        assertEquals(clobSize,count);
+        rs.close();
+        s.executeUpdate("drop table lob1");
+        s.executeUpdate("drop table t_lob1_log");
+        
+	  
+    }
+    
+    /**
+     * Make a CharArrayReader
+     * @param c character to repeat	 
+     * @param size size of array
+     * @return CharArrayReader of specified character  repeating the specified character
  
+     */
+    private  CharArrayReader  makeCharArrayReader(char c, int size)
+    {
+   char[] arr = new char[size];
+   for (int i = 0; i < arr.length; i++)
+        	arr[i] = c;
+    return new CharArrayReader(arr);
+    }
+    
+    
     
     /**
      * Test that the action statement of a trigger



Mime
View raw message