db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject 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
Date Wed, 26 Apr 2006 22:05:29 GMT
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;



Mime
View raw message