db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kmars...@apache.org
Subject svn commit: r601110 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/execute/DMLWriteResultSet.java testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
Date Tue, 04 Dec 2007 22:16:09 GMT
Author: kmarsden
Date: Tue Dec  4 14:16:08 2007
New Revision: 601110

URL: http://svn.apache.org/viewvc?rev=601110&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'

When a trigger is present, unchanged columns with be part of the UpdateResultSet. These columns
are present twice in the result set as a before and after value. There was code to "objectify"
the before value but the after value was not getting updated. Changed objectifyStream to make
sure after values point to data value and not the stream after objectifyStream occurs.


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

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DMLWriteResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DMLWriteResultSet.java?rev=601110&r1=601109&r2=601110&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DMLWriteResultSet.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DMLWriteResultSet.java
Tue Dec  4 14:16:08 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/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java?rev=601110&r1=601109&r2=601110&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
Tue Dec  4 14:16:08 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 {
+    	Connection conn = getConnection();
+    	// --- 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);
+        conn.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();
+        conn.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 {
+    	Connection conn = getConnection();
+
+        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);
+        conn.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();
+        
+        conn.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