db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kahat...@apache.org
Subject svn commit: r465601 [2/3] - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests: master/ master/DerbyNet/ master/DerbyNetClient/ suites/ tests/jdbcapi/ util/streams/
Date Thu, 19 Oct 2006 12:16:13 GMT
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/BlobClob4BlobTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/BlobClob4BlobTest.java?view=auto&rev=465601
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/BlobClob4BlobTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/BlobClob4BlobTest.java Thu Oct 19 05:16:12 2006
@@ -0,0 +1,3147 @@
+/*
+ *
+ * Derby - Class BlobClob4BlobTest
+ *
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements.  See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License.  You may obtain a copy of the License at
+ *
+ *    http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
+ * either express or implied. See the License for the specific
+ * language governing permissions and limitations under the License.
+ */
+package org.apache.derbyTesting.functionTests.tests.jdbcapi;
+
+import java.io.ByteArrayInputStream;
+import java.io.InputStream;
+import java.io.Reader;
+import java.io.StringReader;
+import java.util.Arrays;
+import java.util.Random;
+import java.util.zip.CRC32;
+import org.apache.derbyTesting.functionTests.util.Formatters;
+
+import org.apache.derbyTesting.functionTests.util.streams.CharAlphabet;
+import org.apache.derbyTesting.functionTests.util.streams.LoopingAlphabetReader;
+import org.apache.derbyTesting.functionTests.util.streams.LoopingAlphabetStream;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+
+import junit.framework.*;
+import java.sql.*;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+/**
+ * Test of JDBC blob and clob
+ */
+public class BlobClob4BlobTest extends BaseJDBCTestCase {
+
+    /** Creates a new instance of BlobClob4BlobTest */
+    public BlobClob4BlobTest(String name) {
+        super(name);
+    }
+
+    /**
+     * Set up the conection to the database.
+     */
+    public void setUp() throws  Exception {
+        getConnection().setAutoCommit(false);
+
+        // creating small tables then add large column - that way forcing table
+        // to have default small page size, but have large rows.
+
+        Statement stmt = createStatement();
+        stmt.executeUpdate("CREATE TABLE testClob (b INT, c INT)");
+        stmt.executeUpdate("ALTER TABLE testClob ADD COLUMN a CLOB(300K)");
+
+        stmt.executeUpdate("CREATE TABLE testBlob (b INT)");
+        stmt.executeUpdate("ALTER TABLE testBlob ADD COLUMN a blob(300k)");
+        stmt.executeUpdate("ALTER TABLE testBlob ADD COLUMN crc32 BIGINT");
+
+        stmt.close();
+
+    }
+
+    protected void tearDown() throws Exception {
+        Statement stmt = createStatement();
+        stmt.executeUpdate("DROP TABLE testClob");
+        stmt.executeUpdate("DROP TABLE testBlob");
+        commit();
+        super.tearDown();
+    }
+
+    /***                TESTS               ***/
+
+    /**
+     * Tests PreparedStatement.setCharacterStream
+     */
+    public void testSetCharacterStream() throws Exception {
+        int clobLength = 5009;
+
+        // insert a streaming column
+        PreparedStatement ps = prepareStatement(
+                "insert into testClob (a) values(?)");
+        Reader streamReader = new LoopingAlphabetReader(
+                clobLength, CharAlphabet.tamil());
+        ps.setCharacterStream(1, streamReader, clobLength);
+        ps.executeUpdate();
+        streamReader.close();
+        commit();
+
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("SELECT a FROM testClob");
+        while (rs.next()) {
+            Clob clob = rs.getClob(1);
+            assertEquals("FAIL - wrong clob length", clobLength, clob.length());
+            Reader clobValue = clob.getCharacterStream();
+            Reader origValue = new LoopingAlphabetReader(
+                    clobLength, CharAlphabet.tamil());
+
+            assertTrue("New clob value did not match",
+                    compareReaders(origValue, clobValue));
+            origValue.close();
+            clobValue.close();
+        }
+        rs.close();
+        stmt.close();
+
+        commit();
+
+    }
+
+    /**
+     *  basic test of getAsciiStream also tests length
+     */
+    public void testGetAsciiStream() throws Exception {
+        byte[] buff = new byte[1024];
+
+        insertDefaultData();
+
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("SELECT a, b FROM testClob");
+
+        // fetch row back, get the column as a clob.
+        Clob clob;
+        int clobLength;
+        while (rs.next()) {
+            // get the first column in select as a clob
+            clob = rs.getClob(1);
+            clobLength = rs.getInt(2);
+
+            if (clob != null) {
+                assertEquals("FAIL - wrong clob.length()",
+                        clobLength, clob.length());
+
+                InputStream fin = clob.getAsciiStream();
+                int columnSize = 0;
+                int size = -1;
+                do {
+                    size = fin.read(buff);
+                    columnSize += (size > 0) ? size : 0;
+                } while (size >= 0);
+
+                assertEquals("FAIL - wrong column size",
+                        clobLength, columnSize);
+            } else {
+                assertTrue("Clob was null but length was not 0",
+                        (clobLength == 0));
+            }
+        }
+        rs.close();
+        stmt.close();
+
+        commit();
+    }
+
+    /**
+     * basic test of getCharacterStream also tests length
+     */
+    public void testGetCharacterStream() throws Exception {
+
+        char[] buff = new char[128];
+
+        insertDefaultData();
+
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("select a , b from testClob");
+        ResultSetMetaData met = rs.getMetaData();
+
+        // fetch row back, get the column as a clob.
+        int clobLength = 0;
+        while (rs.next()) {
+            // get the first column as a clob
+            Clob clob = rs.getClob(1);
+            clobLength = rs.getInt(2);
+            if (clob != null) {
+                assertEquals("FAIL - wrong clob.length()",
+                        clobLength, clob.length());
+
+                Reader reader = clob.getCharacterStream();
+                int columnSize = 0;
+                int size = -1;
+                do {
+                    size = reader.read(buff);
+                    columnSize += (size >= 0) ? size : 0;
+                } while (size >= 0);
+
+                assertEquals("FAIL - wrong column size",
+                        clobLength, columnSize);
+            } else {
+                assertTrue("Clob was null but length was not 0",
+                        (clobLength == 0));
+            }
+        }
+        rs.close();
+        stmt.close();
+
+        commit();
+    }
+
+    /**
+     * test of getCharacterStream on a table containing unicode characters
+     */
+    public void testGetCharacterStreamWithUnicode() throws Exception {
+        String[] unicodeStrings = {
+            "\u0061\u0062\u0063",
+            "\u0370\u0371\u0372",
+            "\u05d0\u05d1\u05d2"};
+        insertUnicodeData(unicodeStrings);
+
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM testClob");
+
+        int clobLength = 0, arrayIndex = 0;
+        while (rs.next()) {
+            clobLength = rs.getInt(2);
+            arrayIndex = rs.getInt(3);
+            Clob clob = rs.getClob(1);
+            if (clob != null) {
+                assertEquals("FAIL - wrong clob.length()",
+                        clobLength, clob.length());
+
+                Reader clobValue = clob.getCharacterStream();
+                if (arrayIndex > 0) {
+                    char[] buff = new char[3];
+                    clobValue.read(buff);
+                    assertEquals("Clob value does not match unicodeString",
+                            unicodeStrings[arrayIndex],
+                            new String(buff));
+                    assertEquals("Expected end of stream",
+                            -1, clobValue.read());
+                } else {
+                    Reader origValue = new LoopingAlphabetReader(
+                            clobLength, CharAlphabet.tamil());
+                    compareReaders(origValue, clobValue);
+                }
+            } else {
+                assertTrue("Clob was null but length was not 0",
+                        (clobLength == 0));
+            }
+        }
+        rs.close();
+        stmt.close();
+
+        commit();
+    }
+
+    /**
+     * Test triggers on CLOB columns.
+     */
+    public void testTriggersWithClobColumn() throws Exception {
+        insertDefaultData();
+
+        Statement stmt = createStatement();
+        stmt.executeUpdate(
+                "CREATE TABLE testClobTriggerA (a CLOB(400k), b int)");
+        stmt.executeUpdate(
+                "CREATE TABLE testClobTriggerB (a CLOB(400k), b int)");
+        stmt.executeUpdate(
+                "create trigger T13A after update on testClob " +
+                "referencing new as n old as o " +
+                "for each row mode db2sql "+
+                "insert into testClobTriggerA(a, b) values (n.a, n.b)");
+        stmt.executeUpdate(
+                "create trigger T13B after INSERT on testClobTriggerA " +
+                "referencing new_table as n " +
+                "for each statement mode db2sql "+
+                "insert into testClobTriggerB(a, b) select n.a, n.b from n");
+
+        commit();
+
+        // Fire the triggers
+        stmt.executeUpdate("UPDATE testClob SET b = b + 0");
+        commit();
+
+        // Verify the results
+        Statement origSt = createStatement();
+        Statement trigASt = createStatement();
+        Statement trigBSt = createStatement();
+
+        ResultSet origRS = origSt.executeQuery(
+                "select a, length(a), b  from testClob order by b");
+        ResultSet trigARS = trigASt.executeQuery(
+                "select a, length(a), b from testClobTriggerA order by b");
+        ResultSet trigBRS = trigBSt.executeQuery(
+                "select a, length(a), b from testClobTriggerA order by b");
+
+        int count = 0;
+        while (origRS.next()) {
+            count ++;
+            assertTrue("row trigger produced less rows " +
+                    count, trigARS.next());
+            assertTrue("statement trigger produced less rows " +
+                    count, trigBRS.next());
+
+            if (origRS.getClob(1) != null) {
+                assertEquals("FAIL - Invalid checksum for row trigger",
+                        getStreamCheckSum(origRS.getClob(1).getAsciiStream()),
+                        getStreamCheckSum(trigARS.getClob(1).getAsciiStream()));
+                assertEquals("FAIL - Invalid checksum for statement trigger",
+                        getStreamCheckSum(origRS.getClob(1).getAsciiStream()),
+                        getStreamCheckSum(trigBRS.getClob(1).getAsciiStream()));
+            }
+
+            assertEquals("FAIL - Invalid length in row trigger",
+                    origRS.getInt(2), trigARS.getInt(2));
+            assertEquals("FAIL - Invalid length in statement trigger",
+                    origRS.getInt(2), trigBRS.getInt(2));
+
+            assertEquals("FAIL - Length not updated on row trigger",
+                    origRS.getInt(3), trigARS.getInt(3));
+            assertEquals("FAIL - Length not updated on statement trigger",
+                    origRS.getInt(3), trigBRS.getInt(3));
+        }
+
+        origRS.close();
+        trigARS.close();
+        trigBRS.close();
+        origSt.close();
+        trigASt.close();
+        trigBSt.close();
+
+        stmt.executeUpdate("DROP TRIGGER T13A");
+        stmt.executeUpdate("DROP TRIGGER T13B");
+        stmt.executeUpdate("DROP TABLE testClobTriggerB");
+        stmt.executeUpdate("DROP TABLE testClobTriggerA");
+
+        stmt.close();
+        commit();
+    }
+
+    /**
+     * test Clob.getSubString() method
+     */
+    public void testGetSubString() throws Exception {
+        insertDefaultData();
+
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("select a, b from testClob");
+        int clobLength = 0;
+        Clob clob;
+        while (rs.next()) {
+            clob = rs.getClob(1);
+            if (clob == null)
+                continue;
+            clobLength = rs.getInt(2);
+            verifyInterval(clob, 9905, 50, 0, clobLength);
+            verifyInterval(clob, 5910, 150, 1, clobLength);
+            verifyInterval(clob, 5910, 50, 2, clobLength);
+            verifyInterval(clob, 204, 50, 3, clobLength);
+            verifyInterval(clob, 68, 50, 4, clobLength);
+            verifyInterval(clob, 1, 50, 5, clobLength);
+            verifyInterval(clob, 1, 1, 6, clobLength);
+            verifyInterval(clob, 1, 0, 7, clobLength); // length 0 at start
+            verifyInterval(clob, clobLength + 1, 0, 8, clobLength); // and end
+            if (clobLength > 100) {
+                String res = clob.getSubString(clobLength-99,200);
+                assertEquals("FAIL - wrong length of substring",
+                        100, res.length());
+            }
+        }
+        rs.close();
+    }
+
+    /**
+     * test getSubString with unicode
+     */
+    public void testGetSubStringWithUnicode() throws Exception {
+        String[] unicodeStrings = {
+            "\u0061\u0062\u0063",
+            "\u0370\u0371\u0372",
+            "\u05d0\u05d1\u05d2"};
+        insertUnicodeData(unicodeStrings);
+
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("select a, b, c from testClob");
+        int clobLength = 0, arrayIndex = 0;
+        Clob clob;
+        while (rs.next()) {
+            clob = rs.getClob(1);
+            clobLength = rs.getInt(2);
+            arrayIndex = rs.getInt(3);
+            if (clob != null) {
+                if (arrayIndex >= 0) {
+                    assertEquals("FAIL - wrong substring returned",
+                            unicodeStrings[arrayIndex],
+                            clob.getSubString(1, 3));
+                } else {
+                    if (clob.length() > 0) {
+                        long charsToRead = Math.min((clob.length() / 3), 2048);
+                        char[] charValue = new char[(int)charsToRead];
+                        Reader clobReader = clob.getCharacterStream();
+                        clobReader.read(charValue);
+                        clobReader.read(charValue);
+                        String subString = clob.getSubString(charsToRead + 1,
+                                (int)charsToRead);
+                        assertEquals("FAIL - wrong substring length",
+                                charValue.length, subString.length());
+                        for (int i=0; i< charValue.length; i++) {
+                            assertEquals("FAIL - wrong substring returned at " +
+                                    i, charValue[i], subString.charAt(i));
+                        }
+                    }
+                }
+            }
+        }
+        rs.close();
+        stmt.close();
+    }
+
+    /**
+     * test position with a String argument
+     */
+    public void testPositionString() throws Exception {
+        insertDefaultData();
+
+        runPositionStringTest();
+    }
+
+    /**
+     * test position with a String argument and unicode data.
+     */
+    public void testPositionStringWithUnicode() throws Exception {
+        String[] unicodeStrings = {
+            "\u0061\u0062\u0063",
+            "\u0370\u0371\u0372",
+            "\u05d0\u05d1\u05d2"};
+        insertUnicodeData(unicodeStrings);
+
+        runPositionStringTest();
+    }
+
+    private void runPositionStringTest() throws Exception {
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("select a, b from testClob");
+        int clobLength = 0;
+        Clob clob;
+        Random random = new Random();
+        String searchString;
+        int start, length, startSearchPos;
+        int distance, maxStartPointDistance;
+        long foundAt;
+        // clobs are generated with looping alphabet streams
+        maxStartPointDistance = CharAlphabet.MODERNLATINLOWER.length;
+        while (rs.next()) {
+            clob = rs.getClob(1);
+            clobLength = rs.getInt(2);
+            if (clob != null && clobLength > 0) {
+                println("\n\nclobLength: " + clobLength);
+                for (int i=0; i<10; i++) {
+                    // find a random string to search for
+                    start = Math.max(random.nextInt(clobLength - 1), 1);
+                    // length = random.nextInt(clobLength - start) + 1;
+                    // use 256 until DERBY-1917 is fixed
+                    length = random.nextInt(256) + 1;
+                    println("start:" + start + " length:" + length);
+                    searchString = clob.getSubString(start, length);
+                    // get random position to start the search from
+                    distance = random.nextInt(maxStartPointDistance);
+                    startSearchPos = Math.max((start - distance), 1);
+                    // make sure that the searched string does not happen
+                    // before the expected position
+                    String tmp = clob.getSubString(startSearchPos, start);
+                    if (tmp.indexOf(searchString) != -1) {
+                        startSearchPos = start;
+                    }
+                    println("startSearchPos: " + startSearchPos +
+                            "searchString: " + searchString);
+                    foundAt = clob.position(searchString, startSearchPos);
+                    assertEquals("FAIL - wrong match found for " +
+                            searchString + " start at " + startSearchPos +
+                            " with length " + searchString.length(),
+                            start, foundAt);
+                }
+            }
+        }
+        rs.close();
+        stmt.close();
+    }
+
+    /**
+     * test position with a Clob argument
+     */
+    public void testPositionClob() throws Exception {
+        insertDefaultData();
+
+        runPositionClobTest();
+    }
+
+    /**
+     * test position with a Clob argument containing unicode characters
+     */
+    public void testPositionClobWithUnicode() throws Exception {
+        String[] unicodeStrings = {
+            "\u0061\u0062\u0063",
+            "\u0370\u0371\u0372",
+            "\u05d0\u05d1\u05d2"};
+        insertUnicodeData(unicodeStrings);
+
+        runPositionClobTest();
+    }
+
+    private void runPositionClobTest() throws Exception {
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("select a, b from testClob");
+        int clobLength = 0;
+        Clob clob;
+        Statement stmt2 = createStatement();
+        Random random = new Random();
+        String searchString;
+        int start, length, startSearchPos;
+        int distance, maxStartPointDistance;
+        long foundAt;
+        // clobs are generated with looping alphabet streams
+        maxStartPointDistance = CharAlphabet.MODERNLATINLOWER.length;
+        while (rs.next()) {
+            clob = rs.getClob(1);
+            clobLength = rs.getInt(2);
+            if (clob != null && clobLength > 0) {
+                println("\n\nclobLength: " + clobLength);
+                // Create a table with clobs to search
+                stmt2.executeUpdate("CREATE TABLE searchClob " +
+                        "(a clob(300K), start int, position int)");
+                // insert clobs into the table
+                PreparedStatement ps = prepareStatement(
+                        "INSERT INTO searchClob values (?, ?, ?) ");
+                for (int i=0; i<10; i++) {
+                    // find a random string to search for
+                    start = Math.max(random.nextInt(clobLength - 1), 1);
+                    length = random.nextInt(clobLength - start) + 1;
+                    println("start:" + start + " length:" + length);
+                    searchString = clob.getSubString(start, length);
+                    // get random position to start the search from
+                    distance = random.nextInt(maxStartPointDistance);
+                    startSearchPos = Math.max((start - distance), 1);
+                    // make sure that the searched string does not happen
+                    // before the expected position
+                    String tmp = clob.getSubString(startSearchPos, start);
+                    if (tmp.indexOf(searchString) != -1) {
+                        startSearchPos = start;
+                    }
+
+                    ps.setString(1, searchString);
+                    ps.setInt(2, startSearchPos);
+                    ps.setInt(3, start);
+                    ps.executeUpdate();
+                }
+
+                ps.close();
+
+                ResultSet rs2 = stmt2.executeQuery(
+                        "SELECT a, start, position FROM searchClob");
+                while (rs2.next()) {
+                    Clob searchClob = rs2.getClob(1);
+                    startSearchPos = rs2.getInt(2);
+                    start = rs2.getInt(3);
+
+                    searchString = searchClob.getSubString(1L,
+                            (int)searchClob.length());
+                    println("startSearchPos: " + startSearchPos +
+                            "searchString: " + searchString);
+                    foundAt = clob.position(searchClob, startSearchPos);
+                    assertEquals("FAIL - wrong match found for " +
+                            searchString + " starting at " + startSearchPos +
+                            " with length " + searchString.length(),
+                            start, foundAt);
+                }
+                rs2.close();
+                stmt2.executeUpdate("DROP TABLE searchClob");
+            }
+        }
+        rs.close();
+        stmt.close();
+        stmt2.close();
+    }
+
+    /**
+     * make sure clobs work for small CLOB fields also test length method
+     */
+    public void testSmallClobFields() throws Exception {
+        Statement stmt = createStatement();
+        stmt.executeUpdate(
+                "ALTER TABLE testClob ADD COLUMN smallClob CLOB(10)");
+
+        PreparedStatement ps = prepareStatement(
+                "insert into testClob (smallClob) values(?)");
+        String val = "";
+        for (int i = 0; i < 10; i++) {
+            // insert a string
+            ps.setString(1, val);
+            ps.executeUpdate();
+            val += "x";
+        }
+
+        ResultSet rs = stmt.executeQuery("select a from testClob");
+        byte[] buff = new byte[128];
+        int j = 0;
+        // fetch all rows back, get the columns as clobs.
+        while (rs.next()) {
+            // get the first column as a clob
+            Clob clob = rs.getClob(1);
+            if (clob != null) {
+                InputStream fin = clob.getAsciiStream();
+                int columnSize = 0, size = 0;
+                do
+                {
+                    size = fin.read(buff);
+                    columnSize += (size > 0) ? size : 0;
+                } while (size != -1);
+                assertEquals("FAIL - wrong clob size", j, columnSize);
+                assertEquals("FAIL - wrong clob length", j, clob.length());
+                j++;
+            }
+        }
+        rs.close();
+        stmt.close();
+    }
+
+    /**
+     * make sure cannot get a clob from an int column
+     */
+    public void testGetClobFromIntColumn() throws Exception{
+
+        insertDefaultData();
+
+        Statement stmt = createStatement();
+
+        ResultSet rs = stmt.executeQuery("select b from testClob");
+        while (rs.next()) {
+            try {
+                Clob clob = rs.getClob(1);
+                fail("FAIL - getClob on column type int should throw " +
+                        "an exception");
+            } catch (SQLException se) {
+                checkException(LANG_DATA_TYPE_GET_MISMATCH, se);
+            }
+        }
+        rs.close();
+        stmt.close();
+    }
+
+    /**
+     * make sure setClob doesn't work on an int column
+     */
+    public void testSetClobToIntColumn() throws Exception {
+        insertDefaultData();
+
+        PreparedStatement ps = prepareStatement(
+                "insert into testClob (b, c) values (?, ?)");
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("select a, b from testClob");
+        Clob clob;
+        int clobLength;
+        while (rs.next()) {
+            // get the first ncolumn as a clob
+            clob = rs.getClob(1);
+            clobLength = rs.getInt(2);
+            if (clob != null) {
+                try {
+                    ps.setClob(1,clob);
+                    ps.setInt(2, clobLength);
+                    ps.executeUpdate();
+                    fail("FAIL - can not use setClob on int column");
+                } catch (SQLException se) {
+                    checkException(LANG_DATA_TYPE_GET_MISMATCH, se);
+                }
+            }
+        }
+        rs.close();
+        stmt.close();
+    }
+
+    /**
+     * test raising of exceptions
+     */
+    public void testRaisingOfExceptionsClob() throws Exception {
+        insertDefaultData();
+
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery(
+                "select a, b from testClob WHERE a is not NULL");
+        int i = 0, clobLength = 0;
+        Clob clob;
+        rs.next();
+        clob = rs.getClob(1);
+        clobLength = rs.getInt(2);
+        rs.close();
+        assertFalse("FAIL - clob can not be null", clob == null);
+
+        // 0 or negative position value
+        try {
+            clob.getSubString(0,5);
+            fail("FAIL - getSubString with 0 as position should have " +
+                    "caused an exception");
+        } catch (SQLException e) {
+            checkException(BLOB_BAD_POSITION, e);
+        }
+
+        // negative length value
+        try {
+            clob.getSubString(1,-76);
+            fail("FAIL - getSubString with negative length should have " +
+                    "caused an exception");
+        } catch (SQLException e) {
+            checkException(BLOB_NONPOSITIVE_LENGTH, e);
+        }
+        // boundary negative 1 length
+        try {
+            clob.getSubString(1,-1);
+            fail("FAIL - getSubString with negative length should have " +
+                    "caused an exception");
+        } catch (SQLException e) {
+            checkException(BLOB_NONPOSITIVE_LENGTH, e);
+        }
+        // before start with length zero
+        try {
+            clob.getSubString(0,0);
+            fail("FAIL - getSubString with 0 as position should have " +
+                    "caused an exception");
+        } catch (SQLException e) {
+            checkException(BLOB_BAD_POSITION, e);
+        }
+        // 2 past end with length 0
+        try {
+            clob.getSubString(clobLength + 2,0);
+            fail("FAIL - getSubString with position bigger than clob " +
+                    "should have caused an exception");
+        }  catch (SQLException e) {
+            checkException(BLOB_POSITION_TOO_LARGE, e);
+        } catch (StringIndexOutOfBoundsException se) {
+            assertTrue("FAIL - This exception should only happen with DerbyNet",
+                    usingDerbyNet());
+        }
+        // 0 or negative position value
+        try {
+            clob.position("xx",-4000);
+            fail("FAIL - position with negative as position should " +
+                    "have caused an exception");
+        } catch (SQLException e) {
+            checkException(BLOB_BAD_POSITION, e);
+        }
+        // null pattern
+        try {
+            clob.position((String) null,5);
+            fail("FAIL = position((String) null,5)");
+        } catch (SQLException e) {
+            checkException(BLOB_NULL_PATTERN_OR_SEARCH_STR, e);
+        }
+        // 0 or negative position value
+        try {
+            clob.position(clob,-42);
+            fail("FAIL = position(clob,-42)");
+        } catch (SQLException e) {
+            checkException(BLOB_BAD_POSITION, e);
+        }
+        // null pattern
+        try {
+            clob.position((Clob) null,5);
+            fail("FAIL = pposition((Clob) null,5)");
+        } catch (SQLException e) {
+            checkException(BLOB_NULL_PATTERN_OR_SEARCH_STR, e);
+        }
+    }
+
+    /**
+     * test setClob
+     */
+    public void testSetClob() throws Exception {
+        insertDefaultData();
+        ResultSet rs, rs2;
+        Statement stmt = createStatement();
+        stmt.execute("create table testSetClob (a CLOB(300k), b integer)");
+        PreparedStatement ps = prepareStatement(
+                "insert into testSetClob values(?,?)");
+        rs = stmt.executeQuery("select a, b from testClob");
+        Clob clob;
+        int clobLength;
+        while (rs.next()) {
+            // get the first column as a clob
+            clob = rs.getClob(1);
+            clobLength = rs.getInt(2);
+            if (clob != null && clobLength != 0) {
+                ps.setClob(1,clob);
+                ps.setInt(2,clobLength);
+                ps.executeUpdate();
+            }
+        }
+        rs.close();
+
+        rs = stmt.executeQuery("select a, b from testSetClob");
+        Clob clob2;
+        int clobLength2, nullClobs = 0;
+        while (rs.next()) {
+            clob2 = rs.getClob(1);
+            clobLength2 = rs.getInt(2);
+            assertFalse("FAIL - Clob is NULL", clob2 == null);
+            assertEquals("FAIL - clob.length() != clobLength",
+                    clobLength2, clob2.length());
+        }
+        rs.close();
+        stmt.executeUpdate("DROP TABLE testSetClob");
+        stmt.close();
+    }
+
+    /**
+     * Test Clob.position()
+     */
+    public void testPositionAgressive() throws Exception {
+        Statement s = createStatement();
+
+        s.execute("CREATE TABLE C8.T8POS" +
+                "(id INT NOT NULL PRIMARY KEY, DD CLOB(1m), pos INT, L INT)");
+        s.execute("CREATE TABLE C8.T8PATT(PATT CLOB(300k))");
+
+        // characters used to fill the String
+        char[] fill = new char[4];
+        fill[0] = 'd';          // 1 byte UTF8 character (ASCII)
+        fill[1] = '\u03a9';     // 2 byte UTF8 character (Greek)
+        fill[2] = '\u0e14';     // 3 byte UTF8 character (Thai)
+        fill[3] = 'j';          // 1 byte UTF8 character (ASCII)
+
+        char[] base = new char[256 * 1024];
+
+        for (int i = 0; i < base.length; i += 4) {
+
+            base[i] = fill[0];
+            base[i+1] = fill[1];
+            base[i+2] = fill[2];
+            base[i+3] = fill[3];
+
+        }
+
+        char[]  patternBase = new char[2 * 1024];
+        for (int i = 0; i < patternBase.length; i += 8) {
+
+            patternBase[i] = 'p';
+            patternBase[i+1] = 'a';
+            patternBase[i+2] = 't';
+            patternBase[i+3] = '\u03aa';
+            patternBase[i+4] = (char) i;// changed value to keep pattern varying
+            patternBase[i+5] = 'b';
+            patternBase[i+6] = 'm';
+            patternBase[i+7] = '\u0e15';
+
+        }
+
+        PreparedStatement ps = prepareStatement(
+                "INSERT INTO C8.T8POS VALUES (?, ?, ?, ?)");
+        PreparedStatement psp = prepareStatement(
+                "INSERT INTO C8.T8PATT VALUES (?)");
+
+        T8insert(ps, 1, base, 256, patternBase, 8, 100, true);
+        T8insert(ps, 2, base, 3988, patternBase, 8, 2045, true);
+        T8insert(ps, 3, base, 16321, patternBase, 8, 4566, true);
+        T8insert(ps, 4, base, 45662, patternBase, 8, 34555, true);
+        T8insert(ps, 5, base, 134752, patternBase, 8, 67889, true);
+        T8insert(ps, 6, base, 303, patternBase, 8, 80, false);
+        T8insert(ps, 7, base, 4566, patternBase, 8, 2086, false);
+        T8insert(ps, 8, base, 17882, patternBase, 8, 4426, false);
+        T8insert(ps, 9, base, 41567, patternBase, 8, 31455, false);
+        String pstr =
+                T8insert(ps, 10, base, 114732, patternBase, 8, 87809, false);
+
+        commit();
+
+        psp.setString(1, pstr);
+        psp.executeUpdate();
+
+        checkClob8(s, pstr);
+        commit();
+
+        ResultSet rsc = s.executeQuery("SELECT PATT FROM C8.T8PATT");
+        rsc.next();
+        checkClob8(s, rsc.getClob(1));
+
+        rsc.close();
+
+
+        commit();
+
+        s.execute("DELETE FROM C8.T8POS");
+        s.execute("DELETE FROM C8.T8PATT");
+
+
+        T8insert(ps, 1, base, 256, patternBase, 134, 100, true);
+        T8insert(ps, 2, base, 3988, patternBase, 134, 2045, true);
+        T8insert(ps, 3, base, 16321, patternBase, 134, 4566, true);
+        T8insert(ps, 4, base, 45662, patternBase, 134, 34555, true);
+        T8insert(ps, 5, base, 134752, patternBase, 134, 67889, true);
+        T8insert(ps, 6, base, 303, patternBase, 134, 80, false);
+        T8insert(ps, 7, base, 4566, patternBase, 134, 2086, false);
+        T8insert(ps, 8, base, 17882, patternBase, 134, 4426, false);
+        T8insert(ps, 9, base, 41567, patternBase, 134, 31455, false);
+        pstr = T8insert(ps, 10, base, 114732, patternBase, 134, 87809, false);
+
+        commit();
+        psp.setString(1, pstr);
+        psp.executeUpdate();
+        commit();
+
+
+        checkClob8(s, pstr);
+        commit();
+
+        rsc = s.executeQuery("SELECT PATT FROM C8.T8PATT");
+        rsc.next();
+        checkClob8(s, rsc.getClob(1));
+
+        s.execute("DELETE FROM C8.T8POS");
+        s.execute("DELETE FROM C8.T8PATT");
+
+        T8insert(ps, 1, base, 256, patternBase, 679, 100, true);
+        T8insert(ps, 2, base, 3988, patternBase, 679, 2045, true);
+        T8insert(ps, 3, base, 16321, patternBase, 679, 4566, true);
+        T8insert(ps, 4, base, 45662, patternBase, 679, 34555, true);
+        T8insert(ps, 5, base, 134752, patternBase, 679, 67889, true);
+        T8insert(ps, 6, base, 303, patternBase, 679, 80, false);
+        T8insert(ps, 7, base, 4566, patternBase, 679, 2086, false);
+        T8insert(ps, 8, base, 17882, patternBase, 679, 4426, false);
+        T8insert(ps, 9, base, 41567, patternBase, 679, 31455, false);
+        pstr = T8insert(ps, 10, base, 114732, patternBase, 679, 87809, false);
+
+        commit();
+        psp.setString(1, pstr);
+        psp.executeUpdate();
+        commit();
+
+
+        checkClob8(s, pstr);
+        commit();
+
+        rsc = s.executeQuery("SELECT PATT FROM C8.T8PATT");
+        rsc.next();
+        checkClob8(s, rsc.getClob(1));
+
+        s.execute("DELETE FROM C8.T8POS");
+        s.execute("DELETE FROM C8.T8PATT");
+        ps.close();
+        psp.close();
+
+        s.execute("DROP TABLE C8.T8POS");
+        s.execute("DROP TABLE C8.T8PATT");
+
+        s.close();
+
+        commit();
+    }
+
+    private static String T8insert(PreparedStatement ps, int id, char[] base,
+            int bl, char[] pattern, int pl, int pos, boolean addPattern)
+            throws SQLException
+    {
+
+        StringBuffer sb = new StringBuffer();
+        sb.append(base, 0, bl);
+
+        // Assume the pattern looks like Abcdefgh
+        // put together a block of misleading matches such as
+        // AAbAbcAbcdAbcde
+
+        int last = addPatternPrefix(sb, pattern, pl, 5, 10);
+
+        if (last >= (pos / 2))
+            pos = (last + 10) * 2;
+
+        // now a set of misleading matches up to half the pattern width
+        last = addPatternPrefix(sb, pattern, pl, pl/2, pos/2);
+
+        if (last >= pos)
+            pos = last + 13;
+
+        // now a complete set of misleading matches
+        pos = addPatternPrefix(sb, pattern, pl, pl - 1, pos);
+
+        if (addPattern) {
+            // and then the pattern
+            sb.insert(pos, pattern, 0, pl);
+        } else {
+            pos = -1;
+        }
+
+
+        String dd = sb.toString();
+        String pstr = new String(pattern, 0, pl);
+
+        assertEquals("FAIL - test confused pattern not at expected location",
+                pos, dd.indexOf(pstr));
+
+        // JDBC uses 1 offset for first character
+        if (pos != -1)
+            pos = pos + 1;
+
+        ps.setInt(1, id);
+        ps.setString(2, dd);
+        ps.setInt(3, pos);
+        ps.setInt(4, dd.length());
+        ps.executeUpdate();
+
+        return pstr;
+
+    }
+
+    private static int addPatternPrefix(
+            StringBuffer sb, char[] pattern, int pl, int fakeCount, int pos) {
+
+        for (int i = 0; i < fakeCount && i < (pl - 1); i++) {
+
+            sb.insert(pos, pattern, 0, i + 1);
+            pos += i + 1;
+        }
+
+        return pos;
+    }
+
+    private static void checkClob8(Statement s, String pstr) throws SQLException
+    {
+
+        ResultSet rs = s.executeQuery(
+                "SELECT ID, DD, POS, L FROM C8.T8POS ORDER BY 1");
+
+        while (rs.next()) {
+
+            int id = rs.getInt(1);
+
+            java.sql.Clob cl = rs.getClob(2);
+
+            int pos = rs.getInt(3);
+            int len = rs.getInt(4);
+
+            long clobPosition = cl.position(pstr, 1);
+            assertEquals("FAIL - position did not match",
+                    (long) pos, clobPosition);
+        }
+        rs.close();
+    }
+
+    private static void checkClob8(Statement s, Clob pstr) throws SQLException {
+        ResultSet rs = s.executeQuery(
+                "SELECT ID, DD, POS, L FROM C8.T8POS ORDER BY 1");
+
+        while (rs.next()) {
+
+            int id = rs.getInt(1);
+
+            java.sql.Clob cl = rs.getClob(2);
+
+            int pos = rs.getInt(3);
+            int len = rs.getInt(4);
+
+            long clobPosition = cl.position(pstr, 1);
+            assertEquals("FAIL - position did not match",
+                    (long) pos, clobPosition);
+
+        }
+        rs.close();
+    }
+
+    /**
+     * make sure clob is still around after we go to the next row,
+     * after we close the result set, and after we close the statement
+     */
+    public void testClobAfterClose() throws Exception {
+        insertDefaultData();
+
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("select a, b from testClob");
+        byte[] buff = new byte[128];
+        Clob[] clobArray = new Clob[10];
+        int[] clobLengthArray = new int[10];
+        int j = 0;
+        while (rs.next()) {
+            clobArray[j] = rs.getClob(1);
+            clobLengthArray[j++] = rs.getInt(2);
+        }
+        rs.close();
+        stmt.close();
+
+        for (int i = 0; i < 10; i++) {
+            if (clobArray[i] != null) {
+                InputStream fin = clobArray[i].getAsciiStream();
+                int columnSize = 0;
+                for (;;) {
+                    int size = fin.read(buff);
+                    if (size == -1)
+                        break;
+                    columnSize += size;
+                }
+                assertEquals("FAIL - wrong column size",
+                        columnSize, clobLengthArray[i]);
+                assertEquals("FAIL - wrong column size",
+                        columnSize, clobArray[i].length());
+            }
+        }
+    }
+
+    /**
+     * test locking
+     */
+    public void testLockingClob() throws Exception {
+        insertDefaultData();
+
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("select a, b from testClob");
+        // fetch row back, get the column as a clob.
+        Clob clob = null, shortClob = null;
+        int clobLength;
+        while (rs.next()) {
+            clobLength = rs.getInt(2);
+            if (clobLength == 10000)
+                clob = rs.getClob(1);
+            if (clobLength == 26)
+                shortClob = rs.getClob(1);
+        }
+        rs.close();
+        stmt.close();
+
+        assertTrue("shortClob is null", shortClob != null);
+        assertTrue("clob is null", clob != null);
+
+        Connection conn2 = openDefaultConnection();
+        // turn off autocommit, otherwise blobs/clobs cannot hang around
+        // until end of transaction
+        conn2.setAutoCommit(false);
+        // update should go through since we don't get any locks on clobs
+        // that are not long columns
+        Statement stmt2 = conn2.createStatement();
+        stmt2.executeUpdate("update testClob set a = 'foo' where b = 26");
+        assertEquals("FAIL: clob length changed", 26, shortClob.length());
+        // should timeout waiting for the lock to do this
+        try {
+            stmt2.executeUpdate(
+                    "update testClob set b = b + 1 where b = 10000");
+            fail("FAIL: row should be locked");
+        } catch (SQLException se) {
+            checkException(LOCK_TIMEOUT, se);
+        }
+        assertEquals("FAIL: clob length changed", 10000, clob.length());
+        stmt2.close();
+        conn2.rollback();
+        conn2.close();
+    }
+
+    /**
+     * test locking with a long row + long column
+     */
+    public void testLockingWithLongRowClob() throws Exception
+    {
+        ResultSet rs;
+        Statement stmt, stmt2;
+        stmt = createStatement();
+        stmt.execute("alter table testClob add column al varchar(2000)");
+        stmt.execute("alter table testClob add column bl varchar(3000)");
+        stmt.execute("alter table testClob add column cl varchar(2000)");
+        stmt.execute("alter table testClob add column dl varchar(3000)");
+        stmt.execute("alter table testClob add column el CLOB(400k)");
+        PreparedStatement ps = prepareStatement(
+            "insert into testClob (al, bl, cl, dl, el, b) values(?,?,?,?,?,?)");
+        ps.setString(1,Formatters.padString("blaaa",2000));
+        ps.setString(2,Formatters.padString("tralaaaa",3000));
+        ps.setString(3,Formatters.padString("foodar",2000));
+        ps.setString(4,Formatters.padString("moped",3000));
+        InputStream streamIn = new LoopingAlphabetStream(10000);
+        ps.setAsciiStream(5, streamIn, 10000);
+        ps.setInt(6, 1);
+        ps.executeUpdate();
+        streamIn.close();
+        commit();
+
+        stmt = createStatement();
+        rs = stmt.executeQuery("select el from testClob");
+        // fetch row back, get the column as a clob.
+        Clob clob = null;
+        assertTrue("FAIL - row not found", rs.next());
+        clob = rs.getClob(1);
+        assertTrue("FAIL - clob is null", clob != null);
+        rs.close();
+        stmt.close();
+
+        Connection conn2 = openDefaultConnection();
+        // turn off autocommit, otherwise blobs/clobs cannot hang around
+        // until end of transaction
+        conn2.setAutoCommit(false);
+        // the following should timeout
+        stmt2 = conn2.createStatement();
+        try {
+            stmt2.executeUpdate(
+                    "update testClob set el = 'smurfball' where b = 1");
+            fail("FAIL - statement should timeout");
+        } catch (SQLException se) {
+            checkException(LOCK_TIMEOUT, se);
+        }
+        stmt2.close();
+        conn2.commit();
+        stmt.close();
+    }
+
+    /**
+     * test accessing clob after commit
+     */
+    public void testClobAfterCommit() throws Exception {
+        insertDefaultData();
+
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("select a,b from testClob");
+        // fetch row back, get the column as a clob.
+        Clob clob = null, shortClob = null;
+        int clobLength;
+        int i = 0;
+        while (rs.next()) {
+            clobLength = rs.getInt(2);
+            if (clobLength == 10000)
+                clob = rs.getClob(1);
+            if (clobLength == 26)
+                shortClob = rs.getClob(1);
+        }
+        rs.close();
+        stmt.close();
+        commit();
+
+        // no problem accessing this after commit since it is in memory
+        assertEquals("FAIL - can not access short clob after commit",
+                26, shortClob.length());
+
+        // these should all give blob/clob data unavailable exceptions
+        try {
+            clob.length();
+            if (usingEmbedded()) {
+                fail("FAIL - should not be able to access large log " +
+                        "after commit");
+            }
+        } catch (SQLException e) {
+            checkException(BLOB_ACCESSED_AFTER_COMMIT, e);
+            assertTrue("FAIL - Derby Client and JCC should not get an " +
+                    "exception", usingEmbedded());
+        }
+        try {
+            clob.getSubString(2,3);
+            if (usingEmbedded()) {
+                fail("FAIL - should not be able to access large log " +
+                        "after commit");
+            }
+        } catch (SQLException e) {
+            checkException(BLOB_ACCESSED_AFTER_COMMIT, e);
+            assertTrue("FAIL - Derby Client and JCC should not get an " +
+                    "exception", usingEmbedded());
+        }
+        try {
+            clob.getAsciiStream();
+            if (usingEmbedded()) {
+                fail("FAIL - should not be able to access large log " +
+                        "after commit");
+            }
+        } catch (SQLException e) {
+            checkException(BLOB_ACCESSED_AFTER_COMMIT, e);
+            assertTrue("FAIL - Derby Client and JCC should not get an " +
+                    "exception", usingEmbedded());
+        }
+        try {
+            clob.position("foo",2);
+            if (usingEmbedded()) {
+                fail("FAIL - should not be able to access large log " +
+                        "after commit");
+            }
+        } catch (SQLException e) {
+            checkException(BLOB_ACCESSED_AFTER_COMMIT, e);
+            assertTrue("FAIL - Derby Client and JCC should not get an " +
+                    "exception", usingEmbedded());
+        }
+        try {
+            clob.position(clob,2);
+            if (usingEmbedded()) {
+                fail("FAIL - should not be able to access large log " +
+                        "after commit");
+            }
+        } catch (SQLException e) {
+            checkException(BLOB_ACCESSED_AFTER_COMMIT, e);
+            assertTrue("FAIL - Derby Client and JCC should not get an " +
+                    "exception", usingEmbedded());
+        }
+    }
+
+    /**
+     * test accessing clob after closing the connection
+     */
+    public void testClobAfterClosingConnection() throws Exception {
+        insertDefaultData();
+
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("select a,b from testClob");
+        // fetch row back, get the column as a clob.
+        Clob clob = null, shortClob = null;
+        int clobLength;
+        while (rs.next()) {
+            clobLength = rs.getInt(2);
+            if (clobLength == 10000)
+                clob = rs.getClob(1);
+            if (clobLength == 26)
+                shortClob = rs.getClob(1);
+        }
+        rs.close();
+        commit();
+        getConnection().close();
+
+        // no problem accessing this after commit since it is in memory
+        assertEquals("FAIL - erorr accessing short lob after closing the " +
+                "connection", 26,  shortClob.length());
+
+        // these should all give blob/clob data unavailable exceptions
+        try {
+            clob.length();
+            if (usingEmbedded()) {
+                fail("FAIL - should not be able to access large lob " +
+                        "after the connection is closed");
+            }
+        } catch (SQLException e) {
+            checkException(NO_CURRENT_CONNECTION, e);
+            assertTrue("FAIL - Derby Net and JCC should not get an exception",
+                    usingEmbedded());
+        }
+        try {
+            clob.getSubString(2,3);
+            fail("FAIL - should not be able to access large lob " +
+                    "after the connection is closed");
+        } catch (SQLException e) {
+            checkException(NO_CURRENT_CONNECTION, e);
+        }
+        try {
+            clob.getAsciiStream();
+            fail("FAIL - should not be able to access large lob " +
+                    "after the connection is closed");
+        } catch (SQLException e) {
+            checkException(NO_CURRENT_CONNECTION, e);
+        }
+        try {
+            clob.position("foo",2);
+            if (usingEmbedded()) {
+                fail("FAIL - should not be able to access large lob " +
+                        "after the connection is closed");
+            }
+        } catch (SQLException e) {
+            if (usingEmbedded()) {
+                checkException(BLOB_ACCESSED_AFTER_COMMIT, e);
+            } else {
+                checkException(NO_CURRENT_CONNECTION, e);
+            }
+        }
+        try {
+            clob.position(clob,2);
+            fail("FAIL - should not be able to access large lob " +
+                    "after the connection is closed");
+        } catch (SQLException e) {
+            checkException(NO_CURRENT_CONNECTION, e);
+        }
+    }
+
+    /**
+     * Test fix for derby-1382.
+     *
+     * Test that the getClob() returns the correct value for the clob before and
+     * after updating the clob when using result sets of type
+     * TYPE_SCROLL_INSENSITIVE.
+     *
+     * The method updateString(int, String) is used to set the value on the
+     * clob because the method updateBlob(int, Blob) has not yet been
+     * implemented for DerbyNetClient.
+     *
+     * @param conn Connection
+     * @throws SQLException
+     */
+    public void xTestGetClobBeforeAndAfterUpdate() throws SQLException {
+        String clobData = "initial clob ";
+        PreparedStatement ps = prepareStatement("insert into " +
+                "testClob (b, a) values (?, ?)");
+        for (int i=0; i<10; i++) {
+            ps.setInt(1, i);
+            ps.setString(2, clobData + i);
+            ps.execute();
+        }
+        ps.close();
+
+        Statement scrollStmt = createStatement(
+                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
+        ResultSet rs = scrollStmt.executeQuery("SELECT b, a FROM testClob");
+
+        String value;
+        Clob c;
+
+        rs.first();
+        checkContentsBeforeAndAfterUpdatingClob(rs);
+        rs.next();
+        checkContentsBeforeAndAfterUpdatingClob(rs);
+        rs.relative(3);
+        checkContentsBeforeAndAfterUpdatingClob(rs);
+        rs.absolute(7);
+        checkContentsBeforeAndAfterUpdatingClob(rs);
+        rs.previous();
+        checkContentsBeforeAndAfterUpdatingClob(rs);
+        rs.last();
+        checkContentsBeforeAndAfterUpdatingClob(rs);
+        rs.previous();
+        checkContentsBeforeAndAfterUpdatingClob(rs);
+
+        rs.close();
+        scrollStmt.close();
+    }
+
+    private void checkContentsBeforeAndAfterUpdatingClob(ResultSet rs)
+    throws SQLException {
+        Clob c;
+        String value, expectedValue;
+        String clobData = "initial clob ";
+        String updatedClobData = "updated clob ";
+
+        c = rs.getClob(2);
+        // check contents
+        value = c.getSubString(1, (int)c.length());
+        expectedValue = clobData + rs.getInt(1);
+        assertEquals("FAIL - wrong clob value", expectedValue, value);
+        // update contents
+        value = updatedClobData + rs.getInt(1);
+        rs.updateString(2, value);
+        rs.updateRow();
+        // check update values
+        rs.next(); // leave the row
+        rs.previous(); // go back to updated row
+        c = rs.getClob(2);
+        // check contents
+        value = c.getSubString(1, (int)c.length());
+        expectedValue = updatedClobData + rs.getInt(1);
+        assertEquals("FAIL - wrong clob value", expectedValue, value);
+    }
+
+    /**
+     * Test fix for derby-1421.
+     *
+     * Test that the getClob() returns the correct value for the blob before and
+     * after updating the Clob using the method updateCharacterStream().
+     *
+     * @param conn Connection
+     * @throws SQLException
+     */
+    public void xTestGetClobBeforeAndAfterUpdateStream() throws SQLException {
+        String clobData = "initial clob ";
+        PreparedStatement ps = prepareStatement("insert into " +
+                "testClob (b, a) values (?, ?)");
+        for (int i=0; i<10; i++) {
+            ps.setInt(1, i);
+            ps.setString(2, clobData + i);
+            ps.execute();
+        }
+        ps.close();
+
+        Statement scrollStmt = createStatement(
+                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
+        ResultSet rs = scrollStmt.executeQuery("SELECT b, a FROM testClob");
+
+        rs.first();
+        updateClobWithUpdateCharacterStream(rs);
+        rs.next();
+        updateClobWithUpdateCharacterStream(rs);
+        rs.relative(3);
+        updateClobWithUpdateCharacterStream(rs);
+        rs.absolute(7);
+        updateClobWithUpdateCharacterStream(rs);
+        rs.previous();
+        updateClobWithUpdateCharacterStream(rs);
+        rs.last();
+        updateClobWithUpdateCharacterStream(rs);
+        rs.previous();
+        updateClobWithUpdateCharacterStream(rs);
+
+        rs.close();
+        scrollStmt.close();
+    }
+
+    private void updateClobWithUpdateCharacterStream(ResultSet rs)
+    throws SQLException {
+        Clob c;
+        String value, expectedValue;
+        String clobData = "initial clob ";
+        String updatedClobData = "updated clob ";
+
+        c = rs.getClob(2);
+        // check contents
+        value = c.getSubString(1, (int)c.length());
+        expectedValue = clobData + rs.getInt(1);
+        assertEquals("FAIL - wrong clob value", expectedValue, value);
+
+        // update contents
+        value = (updatedClobData + rs.getInt(1));
+        Reader updateValue = new StringReader(value);
+        rs.updateCharacterStream(2, updateValue, value.length());
+        rs.updateRow();
+        // check update values
+        rs.next(); // leave the row
+        rs.previous(); // go back to updated row
+        c = rs.getClob(2);
+        // check contents
+        value = c.getSubString(1, (int)c.length());
+        expectedValue = updatedClobData + rs.getInt(1);
+        assertEquals("FAIL - wrong clob value", expectedValue, value);
+    }
+
+    /**
+     * test clob finalizer closes the container (should only release table and
+     * row locks that are read_committed)
+     * NOTE: this test does not produce output since it needs to call the
+     * garbage collector whose behaviour is unreliable. It is in the test run to
+     * exercise the code (most of the time).
+     */
+    public void testClobFinalizer() throws Exception {
+        insertDefaultData();
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("select a, b from testClob");
+        byte[] buff = new byte[128];
+        Clob[] clobArray = new Clob[10];
+        int[] clobLengthArray = new int[10];
+        int j = 0;
+        while (rs.next()) {
+            clobArray[j] = rs.getClob(1);
+            clobLengthArray[j++] = rs.getInt(2);
+        }
+        rs.close();
+        stmt.close();
+
+        for (int i = 0; i < 10; i++) {
+            clobArray[i] = null;
+        }
+
+        System.gc();
+        System.gc();
+    }
+
+
+    /**
+     * basic test of getBinaryStream also tests length
+     */
+    public void testGetBinaryStream() throws Exception {
+        insertDefaultData();
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("select a, b, crc32 from testBlob");
+        testBlobContents(rs);
+        stmt.close();
+        commit();
+    }
+
+    /**
+     * test getBytes
+     */
+    public void testGetBytes() throws Exception {
+        insertDefaultData();
+
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("select a,b from testBlob");
+        int blobLength = 0;
+        Blob blob;
+        while (rs.next()) {
+            blob = rs.getBlob(1);
+            blobLength = rs.getInt(2);
+            if (blob != null) {
+                verifyInterval(blob, 9905, 50, 0, blobLength);
+                verifyInterval(blob, 5910, 150, 1, blobLength);
+                verifyInterval(blob, 5910, 50, 2, blobLength);
+                verifyInterval(blob, 204, 50, 3, blobLength);
+                verifyInterval(blob, 68, 50, 4, blobLength);
+                verifyInterval(blob, 1, 50, 5, blobLength);
+                verifyInterval(blob, 1, 1, 6, blobLength);
+                verifyInterval(blob, 1, 0, 7, blobLength);
+                verifyInterval(blob, blobLength + 1, 0, 8, blobLength);
+                if (blobLength > 100) {
+                    byte[] res = blob.getBytes(blobLength-99,200);
+                    assertEquals("FAIL - wrong length in bytes",
+                            100, res.length);
+                    // Get expected value
+                    InputStream inStream = blob.getBinaryStream();
+                    long left = blobLength - 100;
+                    long read = 0;
+                    while (left > 0 && read != -1) {
+                        read = inStream.skip(Math.min(1024, left));
+                        left -= read > 0? read : 0;
+                    }
+                    byte[] expected = new byte[100];
+                    read = inStream.read(expected);
+                    inStream.close();
+                    assertEquals("FAIL - wrong value",
+                            new String(expected),new String(res));
+                }
+            } else {
+                assertTrue("FAIL - blob was NULL but length != 0",
+                        blobLength == 0);
+            }
+        }
+        stmt.close();
+        commit();
+    }
+
+    /**
+     * test position with a byte[] argument
+     */
+    public void testPositionBytes() throws Exception {
+        insertDefaultData();
+
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("select a, b from testBlob");
+        int blobLength = 0;
+        Blob blob;
+        Random random = new Random();
+        byte[] searchBytes;
+        int start, length, startSearchPos;
+        int distance, maxStartPointDistance;
+        long foundAt;
+        // clobs are generated with looping alphabet streams
+        maxStartPointDistance = CharAlphabet.MODERNLATINLOWER.length;
+        while (rs.next()) {
+            blob = rs.getBlob(1);
+            blobLength = rs.getInt(2);
+            if (blob != null && blobLength > 0) {
+                println("\n\nblobLength: " + blobLength);
+                for (int i=0; i<10; i++) {
+                    // find a random string to search for
+                    start = Math.max(random.nextInt(blobLength - 1), 1);
+                    length = random.nextInt(blobLength - start) + 1;
+                    println("start:" + start + " length:" + length);
+                    searchBytes = blob.getBytes(start, length);
+                    String searchString = new String(searchBytes);
+                    // get random position to start the search from
+                    distance = random.nextInt(maxStartPointDistance);
+                    startSearchPos = Math.max((start - distance), 1);
+                    // make sure that the searched string does not happen
+                    // before the expected position
+                    byte[] tmp = blob.getBytes(startSearchPos, start);
+                    if (new String(tmp).indexOf(searchString) != -1)
+                    {
+                        startSearchPos = start;
+                    }
+                    println("startSearchPos: " + startSearchPos +
+                            "searchString: " + new String(searchBytes));
+                    foundAt = blob.position(searchBytes, startSearchPos);
+                    assertEquals("FAIL - wrong match found for " +
+                            searchString + " starting at " + startSearchPos +
+                            " and length of " + searchBytes.length,
+                            start, foundAt);
+
+                }
+            }
+        }
+        rs.close();
+        stmt.close();
+    }
+
+    /**
+     * Test Blob.position() with blob argument
+     */
+    public void testPositionBlob() throws Exception {
+        insertDefaultData();
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("select a, b from testBlob");
+        int blobLength = 0;
+        Blob blob;
+        Statement stmt2 = createStatement();
+        Random random = new Random();
+        String searchString;
+        int start, length, startSearchPos;
+        int distance, maxStartPointDistance;
+        long foundAt;
+        // clobs are generated with looping alphabet streams
+        maxStartPointDistance = CharAlphabet.MODERNLATINLOWER.length;
+        while (rs.next()) {
+            blob = rs.getBlob(1);
+            blobLength = rs.getInt(2);
+            if (blob != null && blobLength > 0) {
+                println("\n\nblobLength: " + blobLength);
+                // Create a table with clobs to search
+                stmt2.executeUpdate("CREATE TABLE searchBlob " +
+                        "(a Blob(300K), start int, position int)");
+                // insert clobs into the table
+                PreparedStatement ps = prepareStatement(
+                        "INSERT INTO searchBlob values (?, ?, ?) ");
+                for (int i=0; i<10; i++) {
+                    // find a random string to search for
+                    start = Math.max(random.nextInt(blobLength - 1), 1);
+                    length = random.nextInt(blobLength - start) + 1;
+                    println("start:" + start + " length:" + length);
+                    searchString = new String(blob.getBytes(start, length));
+                    // get random position to start the search from
+                    distance = random.nextInt(maxStartPointDistance);
+                    startSearchPos = Math.max((start - distance), 1);
+                    // make sure that the searched string does not happen
+                    // before the expected position
+                    String tmp = new String(
+                            blob.getBytes(startSearchPos, start));
+                    if (tmp.indexOf(searchString) != -1) {
+                        startSearchPos = start;
+                    }
+
+                    ps.setBytes(1, searchString.getBytes());
+                    ps.setInt(2, startSearchPos);
+                    ps.setInt(3, start);
+                    ps.executeUpdate();
+                }
+
+                ps.close();
+
+                ResultSet rs2 = stmt2.executeQuery(
+                        "SELECT a, start, position FROM searchBlob");
+                while (rs2.next()) {
+                    Blob searchBlob = rs2.getBlob(1);
+                    startSearchPos = rs2.getInt(2);
+                    start = rs2.getInt(3);
+
+                    searchString = new String(
+                            searchBlob.getBytes(1L, (int)searchBlob.length()));
+                    println("startSearchPos: " + startSearchPos +
+                            "searchString: " + searchString);
+                    foundAt = blob.position(searchBlob, startSearchPos);
+                    assertEquals("FAIL - wrong match found for " +
+                            searchString + " starting at " + startSearchPos +
+                            " and length of " + searchString.length(),
+                            start, foundAt);
+                }
+                rs2.close();
+                stmt2.executeUpdate("DROP TABLE searchBlob");
+            }
+        }
+        rs.close();
+        stmt.close();
+        stmt2.close();
+    }
+
+    /**
+     * Test triggers on BLOB columns.
+     */
+    public void testTriggerWithBlobColumn() throws Exception {
+        insertDefaultData();
+
+        Statement stmt = createStatement();
+        stmt.executeUpdate("CREATE TABLE blobTest8TriggerA " +
+                "(a BLOB(400k), b int, crc32 BIGINT)");
+        stmt.executeUpdate("CREATE TABLE blobTest8TriggerB " +
+                "(a BLOB(400k), b int, crc32 BIGINT)");
+        stmt.executeUpdate(
+                "create trigger T8A after update on testBlob " +
+                "referencing new as n old as o " +
+                "for each row mode db2sql "+
+                "insert into blobTest8TriggerA(a, b, crc32) " +
+                "values (n.a, n.b, n.crc32)");
+        stmt.executeUpdate(
+                "create trigger T8B after INSERT on blobTest8TriggerA " +
+                "referencing new_table as n " +
+                "for each statement mode db2sql "+
+                "insert into blobTest8TriggerB(a, b, crc32) " +
+                "select n.a, n.b, n.crc32 from n");
+
+        commit();
+        ResultSet rs = stmt.executeQuery(
+                "select a,b,crc32 from blobTest8TriggerA");
+        assertFalse("FAIL - Table blobTest8TriggerA should contain no rows",
+                rs.next());
+        rs.close();
+        commit();
+        stmt.executeUpdate("UPDATE testBlob set b = b + 0");
+        commit();
+        rs = stmt.executeQuery(
+                "select a,b,crc32 from blobTest8TriggerA");
+        testBlobContents(rs);
+        rs.close();
+        commit();
+
+        rs = stmt.executeQuery(
+                "select a,b,crc32 from blobTest8TriggerB");
+        testBlobContents(rs);
+        rs.close();
+        commit();
+        stmt.executeUpdate("DROP TRIGGER T8A");
+        stmt.executeUpdate("DROP TRIGGER T8B");
+        stmt.executeUpdate("DROP TABLE blobTest8TriggerB");
+        stmt.executeUpdate("DROP TABLE blobTest8TriggerA");
+
+        stmt.close();
+        commit();
+
+    }
+
+    /**
+     * tests small blob abd length method
+     */
+    public void testVarbinary() throws Exception{
+
+        Statement stmt = createStatement();
+        stmt.execute("ALTER TABLE testBlob ADD COLUMN smallBlob blob(13)");
+
+        PreparedStatement ps = prepareStatement(
+                "insert into testBlob (smallBlob) values (?)");
+        String val = "";
+
+        for (int i = 0; i < 10; i++) {
+            // insert a string
+            ps.setBytes(1, val.getBytes("US-ASCII"));
+            ps.executeUpdate();
+            val = val.trim() + "x";
+        }
+
+        ResultSet rs = stmt.executeQuery("select smallBlob from testBlob");
+        byte[] buff = new byte[128];
+        int j = 0;
+        // fetch all rows back, get the columns as clobs.
+        while (rs.next()) {
+            // get the first column as a clob
+            Blob blob = rs.getBlob(1);
+            assertTrue("FAIL - blob is null", blob != null);
+            InputStream fin = blob.getBinaryStream();
+            int columnSize = 0;
+            for (;;) {
+                int size = fin.read(buff);
+                if (size == -1)
+                    break;
+                columnSize += size;
+            }
+            assertEquals("FAIL - unexpected blob size", j, columnSize);
+            assertEquals("FAIL - unexpected blob length", j, blob.length());
+            j++;
+        }
+        ps.close();
+        stmt.close();
+        commit();
+    }
+
+    /**
+     * make sure cannot get a blob from an int column
+     */
+    public void testGetBlobFromIntColumn() throws Exception {
+
+        insertDefaultData();
+        Statement stmt = createStatement();
+
+        ResultSet rs = stmt.executeQuery("select b from testClob");
+        while (rs.next()) {
+            // get the first column as a clob
+            try {
+                Blob blob = rs.getBlob(1);
+                fail("FAIL - getBlob on int column should throw and " +
+                        "exception");
+            } catch (SQLException se) {
+                checkException(LANG_DATA_TYPE_GET_MISMATCH, se);
+            }
+        }
+        stmt.close();
+        commit();
+
+    }
+
+    /**
+     * make sure setBlob doesn't work for an int column
+     */
+    public void testSetBlobOnIntColumn() throws Exception {
+        insertDefaultData();
+
+        Statement stmt = createStatement();
+        PreparedStatement ps = prepareStatement(
+                "insert into testBlob (b) values(?)");
+        ResultSet rs = stmt.executeQuery("select a,b from testBlob");
+        Blob blob;
+        int blobLength;
+        while (rs.next()) {
+            // get the first column as a blob
+            blob = rs.getBlob(1);
+            if (blob != null) {
+                try {
+                    ps.setBlob(1,blob);
+                    ps.executeUpdate();
+                    fail("FAIL - setBlob worked on INT column");
+                } catch (SQLException se) {
+                    checkException(LANG_DATA_TYPE_GET_MISMATCH, se);
+                }
+            }
+        }
+        rs.close();
+        stmt.close();
+        ps.close();
+        commit();
+    }
+
+    /**
+     * test raising of exceptions
+     */
+    public void testRaisingOfExceptionsBlob() throws Exception {
+        insertDefaultData();
+
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("select a,b from testBlob");
+
+        int blobLength = 0;
+        Blob blob;
+        while (rs.next()) {
+            blob = rs.getBlob(1);
+            blobLength = rs.getInt(2);
+            if (blob != null) {
+
+                // 0 or negative position value
+                try {
+                    blob.getBytes(0, 5);
+                    fail("FAIL - getBytes with 0 as position should " +
+                            "have caused an exception");
+                } catch (SQLException e) {
+                    checkException(BLOB_BAD_POSITION, e);
+                }
+                // negative length value
+                try {
+                    blob.getBytes(1, -76);
+                    fail("FAIL - getBytes with negative length should " +
+                            "have caused an exception");
+                } catch (SQLException e) {
+                    checkException(BLOB_NONPOSITIVE_LENGTH, e);
+                }
+                // zero length value
+                try {
+                    blob.getBytes(1, -1);
+                    fail("FAIL - getBytes with negative length should " +
+                            "have caused an exception");
+                } catch (SQLException e) {
+                    checkException(BLOB_NONPOSITIVE_LENGTH, e);
+                }
+                // before begin length 0
+                try {
+                    blob.getBytes(0, 0);
+                    fail("FAIL - getBytes with 0 position and length " +
+                            "should have caused an exception");
+                } catch (SQLException e) {
+                    checkException(BLOB_BAD_POSITION, e);
+                }
+                // after end length 0
+                try {
+                    blob.getBytes(blobLength + 2, 0);
+                    fail("FAIL - getBytes with position larger than " +
+                            "the length of the blob should have caused an " +
+                            "exception");
+                } catch (SQLException e) {
+                    checkException(BLOB_POSITION_TOO_LARGE, e);
+                } catch (NegativeArraySizeException nase) {
+                    assertTrue("FAIL - this exception should only happen " +
+                            "with DerbyNet", usingDerbyNet());
+                }
+                // 0 or negative position value
+                try {
+                    blob.position(new byte[0], -4000);
+                    if (!usingDerbyNet()) {
+                        fail("FAIL - position with negative start " +
+                                "position should have caused an exception");
+                    }
+                } catch (SQLException e) {
+                    checkException(BLOB_BAD_POSITION, e);
+                    assertTrue("FAIL - JCC should not get an exception",
+                            !usingDerbyNet());
+                }
+                // null pattern
+                try {
+                    blob.position((byte[]) null, 5);
+                    fail("FAIL - position with null pattern should " +
+                            "have caused an exception");
+                } catch (SQLException e) {
+                    checkException(BLOB_NULL_PATTERN_OR_SEARCH_STR, e);
+                }
+                // 0 or negative position value
+                try {
+                    blob.position(blob, -42);
+                    if (!usingDerbyNet()) {
+                        fail("FAIL - position with negative start " +
+                                "position should have caused an exception");
+                    }
+                } catch (SQLException e) {
+                    checkException(BLOB_BAD_POSITION, e);
+                } catch (ArrayIndexOutOfBoundsException aob) {
+                    assertTrue("FAIL - this excpetion should only happen " +
+                            "with DerbyNet", usingDerbyNet());
+                }
+                // null pattern
+                try {
+                    blob.position((Blob) null, 5);
+                    fail("FAIL - position with null pattern should " +
+                            "have caused an exception");
+                } catch (SQLException e) {
+                    checkException(BLOB_NULL_PATTERN_OR_SEARCH_STR, e);
+                }
+            }
+        }
+        rs.close();
+        stmt.close();
+        commit();
+    }
+
+    /**
+     * test setBlob
+     */
+    public void testSetBlob() throws Exception {
+        insertDefaultData();
+
+        Statement stmt = createStatement();
+        stmt.execute("create table testBlobX (a blob(300K), b integer)");
+        PreparedStatement ps = prepareStatement(
+                "insert into testBlobX values(?,?)");
+        ResultSet rs = stmt.executeQuery("select a, b from testBlob");
+        Blob blob;
+        int blobLength;
+        while (rs.next()) {
+            // get the first column as a blob
+            blob = rs.getBlob(1);
+            blobLength = rs.getInt(2);
+            if (blob != null) {
+                ps.setBlob(1,blob);
+                ps.setInt(2,blobLength);
+                ps.executeUpdate();
+            }
+        }
+        rs.close();
+        commit();
+
+        rs = stmt.executeQuery("select a,b from testBlobX");
+        Blob blob2;
+        int blobLength2;
+        while (rs.next()) {
+            // get the first column as a blob
+            blob2 = rs.getBlob(1);
+            blobLength2 = rs.getInt(2);
+            assertTrue("FAIL - blob is NULL", blob2 != null);
+            assertEquals("FAIL - wrong blob length",
+                    blob2.length(), blobLength2);
+        }
+        rs.close();
+
+        stmt.executeUpdate("DROP TABLE testBlobX");
+        stmt.close();
+        commit();
+    }
+
+    /**
+     * make sure blob is still around after we go to the next row,
+     * after we close the result set, and after we close the statement
+     */
+    public void testBlobAfterClose() throws Exception {
+        insertDefaultData();
+
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("select a,b from testBlob");
+        byte[] buff = new byte[128];
+        Blob[] blobArray = new Blob[10];
+        int[] blobLengthArray = new int[10];
+        int j = 0;
+        while (rs.next()) {
+            blobArray[j] = rs.getBlob(1);
+            blobLengthArray[j++] = rs.getInt(2);
+        }
+        rs.close();
+        stmt.close();
+
+        for (int i = 0; i < 10; i++) {
+            if (blobArray[i] != null) {
+                InputStream fin = blobArray[i].getBinaryStream();
+                int columnSize = 0;
+                for (;;) {
+                    int size = fin.read(buff);
+                    if (size == -1)
+                        break;
+                    columnSize += size;
+                }
+                assertEquals("FAIL - invalid length",
+                        blobLengthArray[i], columnSize);
+                assertEquals("FAIL - invalid length",
+                        columnSize, blobArray[i].length());
+            }
+        }
+    }
+
+    /**
+     * test locking
+     */
+    public void testLockingBlob() throws Exception {
+        insertDefaultData();
+
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("select a,b from testBlob");
+        // fetch row back, get the column as a blob.
+        Blob blob = null, shortBlob = null;
+        int blobLength;
+        while (rs.next()) {
+            blobLength = rs.getInt(2);
+            if (blobLength == 10000)
+                blob = rs.getBlob(1);
+            if (blobLength == 26)
+                shortBlob = rs.getBlob(1);
+        }
+        rs.close();
+
+        Connection conn2 = openDefaultConnection();
+        // turn off autocommit, otherwise blobs/clobs cannot hang around
+        // until end of transaction
+        conn2.setAutoCommit(false);
+        // Update should go through since we don't get any locks on blobs
+        // that are not long columns
+        Statement stmt2 = conn2.createStatement();
+        stmt2.executeUpdate("update testBlob set a = null where b = 26");
+        assertEquals("FAIL - blob was updated", 26, shortBlob.length());
+        stmt2.close();
+
+        // should timeout waiting for the lock to do this
+        try {
+            stmt2 = conn2.createStatement();
+            stmt2.executeUpdate(
+                    "update testBlob set b = b + 1 where b = 10000");
+            fail("FAIL - should have gotten lock timeout");
+        } catch (SQLException se) {
+            checkException(LOCK_TIMEOUT, se);
+        }
+        commit();
+        conn2.commit();
+    }
+
+    /**
+     * test locking with a long row + long column
+     */
+    public void testLockingWithLongRowBlob() throws Exception
+    {
+        ResultSet rs;
+        Statement stmt, stmt2;
+        stmt = createStatement();
+        stmt.execute("alter table testBlob add column al varchar(2000)");
+        stmt.execute("alter table testBlob add column bl varchar(3000)");
+        stmt.execute("alter table testBlob add column cl varchar(2000)");
+        stmt.execute("alter table testBlob add column dl varchar(3000)");
+        stmt.execute("alter table testBlob add column el BLOB(400k)");
+        PreparedStatement ps = prepareStatement(
+            "insert into testBlob (al, bl, cl, dl, el, b) values(?,?,?,?,?,?)");
+        ps.setString(1,Formatters.padString("blaaa",2000));
+        ps.setString(2,Formatters.padString("tralaaaa",3000));
+        ps.setString(3,Formatters.padString("foodar",2000));
+        ps.setString(4,Formatters.padString("moped",3000));
+        InputStream streamIn = new LoopingAlphabetStream(10000);
+        ps.setBinaryStream(5, streamIn, 10000);
+        ps.setInt(6, 1);
+        ps.executeUpdate();
+        streamIn.close();
+        commit();
+
+        stmt = createStatement();
+        rs = stmt.executeQuery("select el from testBlob");
+        // fetch row back, get the column as a clob.
+        Blob blob = null;
+        assertTrue("FAIL - row not found", rs.next());
+        blob = rs.getBlob(1);
+        assertTrue("FAIL - blob is null", blob != null);
+        rs.close();
+        stmt.close();
+
+        Connection conn2 = openDefaultConnection();
+        // turn off autocommit, otherwise blobs/clobs cannot hang around
+        // until end of transaction
+        conn2.setAutoCommit(false);
+        // the following should timeout
+        stmt2 = conn2.createStatement();
+        try {
+            stmt2.executeUpdate("update testBlob set el = null where b = 1");
+            fail("FAIL - statement should timeout");
+        } catch (SQLException se) {
+            checkException(LOCK_TIMEOUT, se);
+        }
+        stmt2.close();
+        conn2.commit();
+        stmt.close();
+    }
+
+    /**
+     * test accessing blob after commit
+     */
+    public void testBlobAfterCommit() throws Exception {
+        insertDefaultData();
+
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("select a, b from testBlob");
+        // fetch row back, get the column as a blob.
+        Blob blob = null, shortBlob = null;
+        int blobLength;
+        while (rs.next()) {
+            blobLength = rs.getInt(2);
+            if (blobLength == 10000)
+                blob = rs.getBlob(1);
+            if (blobLength == 26)
+                shortBlob = rs.getBlob(1);
+        }
+        rs.close();
+        commit();
+
+
+        assertTrue("FAIL - shotBlob is NULL", shortBlob != null);
+        assertEquals("FAIL - wrong length after commit",
+                26, shortBlob.length());
+
+        // these should all give blob/clob data unavailable exceptions
+        try {
+            blob.length();
+            if (usingEmbedded()) {
+                fail("FAIL - should not be able to access large log " +
+                        "after commit");
+            }
+        } catch (SQLException e) {
+            checkException(BLOB_ACCESSED_AFTER_COMMIT, e);
+            assertTrue("FAIL - method should not fail when using Derby Client" +
+                    " and JCC", usingEmbedded());
+        }
+        try {
+            blob.getBytes(2,3);
+            if (usingEmbedded()) {
+                fail("FAIL - should not be able to access large log " +
+                        "after commit");
+            }
+        } catch (SQLException e) {
+            checkException(BLOB_ACCESSED_AFTER_COMMIT, e);
+            assertTrue("FAIL - method should not fail when using Derby Client" +
+                    " and JCC", usingEmbedded());
+        }
+        try {
+            blob.getBinaryStream();
+            if (usingEmbedded()) {
+                fail("FAIL - should not be able to access large log " +
+                        "after commit");
+            }
+        } catch (SQLException e) {
+            checkException(BLOB_ACCESSED_AFTER_COMMIT, e);
+            assertTrue("FAIL - method should not fail when using Derby Client" +
+                    " and JCC", usingEmbedded());
+        }
+        try {
+            blob.position("foo".getBytes("US-ASCII"),2);
+            if (usingEmbedded()) {
+                fail("FAIL - should not be able to access large log " +
+                        "after commit");
+            }
+        } catch (SQLException e) {
+            checkException(BLOB_ACCESSED_AFTER_COMMIT, e);
+            assertTrue("FAIL - method should not fail when using Derby Client" +
+                    " and JCC", usingEmbedded());
+        }
+        try {
+            blob.position(blob,2);
+            if (usingEmbedded()) {
+                fail("FAIL - should not be able to access large log " +
+                        "after commit");
+            }
+        } catch (SQLException e) {
+            checkException(BLOB_ACCESSED_AFTER_COMMIT, e);
+            assertTrue("FAIL - method should not fail when using Derby Client" +
+                    " and JCC", usingEmbedded());
+        }
+    }
+
+    /**
+     * test accessing blob after closing the connection
+     */
+    public void testBlobAfterClosingConnection() throws Exception {
+        insertDefaultData();
+
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("select a, b from testBlob");
+        // fetch row back, get the column as a blob.
+        Blob blob = null, shortBlob = null;
+        int blobLength;
+        while (rs.next()) {
+            blobLength = rs.getInt(2);
+            if (blobLength == 10000)
+                blob = rs.getBlob(1);
+            if (blobLength == 26)
+                shortBlob = rs.getBlob(1);
+        }
+        rs.close();
+        rollback();
+        getConnection().close();
+
+        try {
+            long length = shortBlob.length();
+            // no problem accessing this after commit since it is in memory
+            if (usingEmbedded()) {
+                assertEquals("FAIL - wrong blob length", 26, length);
+            } else {
+                fail("FAIL - should get an exception, connection is closed");
+            }
+        } catch (SQLException e) {
+            checkException(NO_CURRENT_CONNECTION, e);
+            assertTrue("FAIL - Embedded should not get this exception",
+                    !usingEmbedded());
+        }
+
+        // these should all give blob/clob data unavailable exceptions
+        try {
+            blob.length();
+            fail("FAIL - should not be able to access large lob " +
+                    "after the connection is closed");
+        } catch (SQLException e) {
+            if (usingEmbedded()) {
+                checkException(BLOB_ACCESSED_AFTER_COMMIT, e);
+            } else {
+                checkException(NO_CURRENT_CONNECTION, e);
+            }
+        }
+        try {
+            blob.getBytes(2,3);
+            fail("FAIL - should not be able to access large lob " +
+                    "after the connection is closed");
+        } catch (SQLException e) {
+            if (usingEmbedded()) {
+                checkException(BLOB_ACCESSED_AFTER_COMMIT, e);
+            } else {
+                checkException(NO_CURRENT_CONNECTION, e);
+            }
+        }
+        try {
+            blob.getBinaryStream();
+            fail("FAIL - should not be able to access large lob " +
+                    "after the connection is closed");
+        } catch (SQLException e) {
+            if (usingEmbedded()) {
+                checkException(BLOB_ACCESSED_AFTER_COMMIT, e);
+            } else {
+                checkException(NO_CURRENT_CONNECTION, e);
+            }
+        }
+        try {
+            blob.position("foo".getBytes("US-ASCII"),2);
+            fail("FAIL - should not be able to access large lob " +
+                    "after the connection is closed");
+        } catch (SQLException e) {
+            if (usingEmbedded()) {
+                checkException(BLOB_ACCESSED_AFTER_COMMIT, e);
+            } else {
+                checkException(NO_CURRENT_CONNECTION, e);
+            }
+        }
+        try {
+            blob.position(blob,2);
+            fail("FAIL - should not be able to access large lob " +
+                    "after the connection is closed");
+        } catch (SQLException e) {
+            if (usingEmbedded()) {
+                checkException(BLOB_ACCESSED_AFTER_COMMIT, e);
+            } else {
+                checkException(NO_CURRENT_CONNECTION, e);
+            }
+        }
+
+        // restart the connection
+        getConnection().setAutoCommit(false);
+    }
+
+    /**
+     * test blob finalizer closes the container
+     * (should only release table and row locks that are read_committed)
+     * NOTE: this test does not produce output since it needs to call the
+     * garbage collector whose behaviour is unreliable. It is in the test run to
+     * exercise the code (most of the time).
+     */
+    public void testBlobFinalizer() throws Exception {
+        insertDefaultData();
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("select a,b from testBlob");
+        byte[] buff = new byte[128];
+        Blob[] blobArray = new Blob[10];
+        int[] blobLengthArray = new int[10];
+        int j = 0;
+        while (rs.next()) {
+            blobArray[j] = rs.getBlob(1);
+            blobLengthArray[j++] = rs.getInt(2);
+        }
+        rs.close();
+        stmt.close();
+
+        for (int i = 0; i < 10; i++) {
+            blobArray[i] = null;
+        }
+
+        System.gc();
+        System.gc();
+
+    }
+
+    /**
+     * Test fix for derby-1382.
+     *
+     * Test that the getBlob() returns the correct value for the blob before and
+     * after updating the blob when using result sets of type
+     * TYPE_SCROLL_INSENSITIVE.
+     *
+     * The method updateBytes(int, byte[]) is used to set the value on the
+     * clob because the method updateClob(int, Clob) has not yet been
+     * implemented for DerbyNetClient.
+     *
+     * @throws Exception
+     */

[... 749 lines stripped ...]


Mime
View raw message