Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 55801 invoked from network); 26 Apr 2006 22:05:53 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 26 Apr 2006 22:05:53 -0000 Received: (qmail 14546 invoked by uid 500); 26 Apr 2006 22:05:52 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 14521 invoked by uid 500); 26 Apr 2006 22:05:52 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 14510 invoked by uid 99); 26 Apr 2006 22:05:52 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 26 Apr 2006 15:05:52 -0700 X-ASF-Spam-Status: No, hits=-9.4 required=10.0 tests=ALL_TRUSTED,NO_REAL_NAME X-Spam-Check-By: apache.org Received: from [209.237.227.194] (HELO minotaur.apache.org) (209.237.227.194) by apache.org (qpsmtpd/0.29) with SMTP; Wed, 26 Apr 2006 15:05:50 -0700 Received: (qmail 55607 invoked by uid 65534); 26 Apr 2006 22:05:30 -0000 Message-ID: <20060426220530.55604.qmail@minotaur.apache.org> Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit 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 -0000 To: derby-commits@db.apache.org From: djd@apache.org X-Mailer: svnmailer-1.0.8 X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N 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;