Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Message-ID: <20060411215523.75991.qmail@minotaur.apache.org>
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r393318 - in /db/derby/code/trunk/java:
 engine/org/apache/derby/iapi/types/SQLBlob.java
 testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
 testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql
Date: Tue, 11 Apr 2006 21:55:15 -0000
To: derby-commits@db.apache.org
From: djd@apache.org

Author: djd
Date: Tue Apr 11 14:55:13 2006
New Revision: 393318

URL: http://svn.apache.org/viewcvs?rev=393318&view=rev
Log:
DERBY-438 (partial) Support BLOBs as column in trigger actions. Initial testing with small BLOB values,
will add other test cases for larger BLOB values, and then subsequently CLOBs. 

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLBlob.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLBlob.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLBlob.java?rev=393318&r1=393317&r2=393318&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLBlob.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLBlob.java Tue Apr 11 14:55:13 2006
@@ -44,6 +44,7 @@
 import java.io.ObjectOutput;
 import java.io.ObjectInput;
 import java.io.IOException;
+import java.sql.Blob;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.PreparedStatement;
@@ -195,6 +196,22 @@
 
 		// This may cause problems for streaming blobs, by materializing the whole blob.
 		ps.setBytes(position, getBytes());
+    }
+    
+    /**
+     * Set the value from an non-null object.
+     */
+    final void setObject(Object theValue)
+        throws StandardException
+    {
+        Blob vb = (Blob) theValue;
+        try {
+            stream = vb.getBinaryStream();
+        } catch (SQLException e) {
+            throw dataTypeConversion("DAN-438-tmp");
+       }
+        streamLength = -1; // unknown
+        dataValue = null;
     }
 }
 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out?rev=393318&r1=393317&r2=393318&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out Tue Apr 11 14:55:13 2006
@@ -897,4 +897,38 @@
 ij(CONNECTION1)> create trigger mytrigger after update on myschema.mytable for each row mode db2sql select * from sys.systables;
 0 rows inserted/updated/deleted
 ij(CONNECTION1)> rollback;
-ij(CONNECTION1)> 
+ij(CONNECTION1)> disconnect;
+ij> -- DERBY-438 - Working triggers with BLOB columns
+set connection CONNECTION0;
+ij> autocommit on;
+ij> create table t438 (id int,  cost decimal(6,2), bl blob);
+0 rows inserted/updated/deleted
+ij> create table t438_t (id int, bl blob, l int, nc decimal(6,2), oc decimal(6,2));
+0 rows inserted/updated/deleted
+ij> create trigger tr_438 after update on t438
+referencing new as n old as o
+for each row mode db2sql
+insert into t438_t(id, bl, l, nc, oc) values (n.id, n.bl, length(n.bl), n.cost, o.cost);
+0 rows inserted/updated/deleted
+ij> -- initially just some small BLOB values.
+insert into t438 values (1, 34.53, cast (X'124594322143423214ab35f2e34c' as blob));
+1 row inserted/updated/deleted
+ij> insert into t438 values (0, 95.32, null);
+1 row inserted/updated/deleted
+ij> insert into t438 values (2, 22.21, cast (X'aa' as blob));
+1 row inserted/updated/deleted
+ij> select id, cost, length(bl) from t438 order by 1;
+ID         |COST     |3          
+---------------------------------
+0          |95.32    |NULL       
+1          |34.53    |14         
+2          |22.21    |1          
+ij> update t438 set cost = cost + 1.23;
+3 rows inserted/updated/deleted
+ij> select id, length(bl), l, nc, oc from t438_t order by 1,5,4;
+ID         |2          |L          |NC       |OC       
+-------------------------------------------------------
+0          |NULL       |NULL       |96.55    |95.32    
+1          |14         |14         |35.76    |34.53    
+2          |1          |1          |23.44    |22.21    
+ij> 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql?rev=393318&r1=393317&r2=393318&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql Tue Apr 11 14:55:13 2006
@@ -501,4 +501,24 @@
 create table myschema.mytable (i int);
 create trigger mytrigger after update on myschema.mytable for each row mode db2sql select * from sys.systables;
 rollback;
+disconnect;
+
+-- DERBY-438 - Working triggers with BLOB columns
+set connection CONNECTION0;
+autocommit on;
+create table t438 (id int,  cost decimal(6,2), bl blob);
+create table t438_t (id int, bl blob, l int, nc decimal(6,2), oc decimal(6,2));
+create trigger tr_438 after update on t438
+referencing new as n old as o
+for each row mode db2sql
+insert into t438_t(id, bl, l, nc, oc) values (n.id, n.bl, length(n.bl), n.cost, o.cost);
+
+-- initially just some small BLOB values.
+insert into t438 values (1, 34.53, cast (X'124594322143423214ab35f2e34c' as blob));
+insert into t438 values (0, 95.32, null);
+insert into t438 values (2, 22.21, cast (X'aa' as blob));
+select id, cost, length(bl) from t438 order by 1;
+
+update t438 set cost = cost + 1.23;
+select id, length(bl), l, nc, oc from t438_t order by 1,5,4;