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/27 00:05:29 UTC
svn commit: r397318 - in /db/derby/code/trunk/java:
engine/org/apache/derby/iapi/types/SQLClob.java
testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql
Author: djd
Date: Wed Apr 26 15:05:27 2006
New Revision: 397318
URL: http://svn.apache.org/viewcvs?rev=397318&view=rev
Log:
DERBY-438 (partial) Handle CLOB columns in triggers by adding code to convert from a java.sql.Clob to a SQLClob.
Added tests in triggerGeneral.sql for small CLOB values.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLClob.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/SQLClob.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLClob.java?rev=397318&r1=397317&r2=397318&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLClob.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLClob.java Wed Apr 26 15:05:27 2006
@@ -29,7 +29,10 @@
import org.apache.derby.iapi.services.sanity.SanityManager;
+import java.sql.Blob;
+import java.sql.Clob;
import java.sql.Date;
+import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.Calendar;
@@ -298,4 +301,25 @@
{
throwLangSetMismatch("byte[]");
}
+
+ /**
+ * Set the value from an non-null Java.sql.Clob object.
+ */
+ final void setObject(Object theValue)
+ throws StandardException
+ {
+ Clob vc = (Clob) theValue;
+
+ try {
+ long vcl = vc.length();
+ if (vcl < 0L || vcl > Integer.MAX_VALUE)
+ throw this.outOfRange();
+
+ setValue(new ReaderToUTF8Stream(vc.getCharacterStream(),
+ (int) vcl, 0), (int) vcl);
+
+ } catch (SQLException e) {
+ throw dataTypeConversion("DAN-438-tmp");
+ }
+ }
}
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=397318&r1=397317&r2=397318&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 Wed Apr 26 15:05:27 2006
@@ -998,4 +998,51 @@
0 |NULL
1 |124594322143423214ab35f2e34c
2 |aa
+ij> drop table t438;
+0 rows inserted/updated/deleted
+ij> drop table t438_t;
+0 rows inserted/updated/deleted
+ij> -- now re-start with CLOB types
+create table t438 (id int, cost decimal(6,2), cl clob);
+0 rows inserted/updated/deleted
+ij> create table t438_t (id int, cl clob, 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, cl, l, nc, oc) values (n.id, n.cl, length(n.cl), n.cost, o.cost);
+0 rows inserted/updated/deleted
+ij> -- initially just some small CLOB values.
+insert into t438 values (1, 34.53, cast ('Italy''s centre-left leader Romano Prodi insists his poll victory is valid as contested ballots are checked.' as clob));
+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 ('free' as clob));
+1 row inserted/updated/deleted
+ij> select id, cost, length(cl) from t438 order by 1;
+ID |COST |3
+---------------------------------
+0 |95.32 |NULL
+1 |34.53 |107
+2 |22.21 |4
+ij> update t438 set cost = cost + 1.23;
+3 rows inserted/updated/deleted
+ij> select id, length(cl), l, nc, oc from t438_t order by 1,5,4;
+ID |2 |L |NC |OC
+-------------------------------------------------------
+0 |NULL |NULL |96.55 |95.32
+1 |107 |107 |35.76 |34.53
+2 |4 |4 |23.44 |22.21
+ij> select id, cast (cl as clob(60)) from t438 order by 1;
+ID |2
+------------------------------------------------------------------------
+0 |NULL
+1 |Italy's centre-left leader Romano Prodi insists his poll vic
+2 |free
+ij> select id, cast (cl as clob(60)) from t438_t order by 1;
+ID |2
+------------------------------------------------------------------------
+0 |NULL
+1 |Italy's centre-left leader Romano Prodi insists his poll vic
+2 |free
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=397318&r1=397317&r2=397318&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 Wed Apr 26 15:05:27 2006
@@ -557,3 +557,25 @@
select id, cast (bl as blob(20)) from t438 order by 1;
select id, cast (bl as blob(20)) from t438_t order by 1;
+drop table t438;
+drop table t438_t;
+
+-- now re-start with CLOB types
+create table t438 (id int, cost decimal(6,2), cl clob);
+create table t438_t (id int, cl clob, 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, cl, l, nc, oc) values (n.id, n.cl, length(n.cl), n.cost, o.cost);
+
+-- initially just some small CLOB values.
+insert into t438 values (1, 34.53, cast ('Italy''s centre-left leader Romano Prodi insists his poll victory is valid as contested ballots are checked.' as clob));
+insert into t438 values (0, 95.32, null);
+insert into t438 values (2, 22.21, cast ('free' as clob));
+select id, cost, length(cl) from t438 order by 1;
+
+update t438 set cost = cost + 1.23;
+select id, length(cl), l, nc, oc from t438_t order by 1,5,4;
+
+select id, cast (cl as clob(60)) from t438 order by 1;
+select id, cast (cl as clob(60)) from t438_t order by 1;