You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by dj...@apache.org on 2006/04/11 23:55:15 UTC
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
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;