You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ka...@apache.org on 2006/10/19 14:16:13 UTC
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/
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 ...]