| /* |
| * |
| * 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.CharArrayReader; |
| import java.io.IOException; |
| import java.io.InputStream; |
| import java.io.Reader; |
| import java.io.StringReader; |
| import java.io.UnsupportedEncodingException; |
| import java.sql.Blob; |
| import java.sql.Clob; |
| import java.sql.Connection; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.ResultSetMetaData; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import java.sql.Types; |
| import java.util.Arrays; |
| import java.util.Random; |
| import java.util.zip.CRC32; |
| import junit.framework.Test; |
| import org.apache.derbyTesting.functionTests.util.Formatters; |
| import org.apache.derbyTesting.functionTests.util.streams.ByteAlphabet; |
| 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 org.apache.derbyTesting.junit.BaseTestSuite; |
| import org.apache.derbyTesting.junit.CleanDatabaseTestSetup; |
| import org.apache.derbyTesting.junit.DatabasePropertyTestSetup; |
| import org.apache.derbyTesting.junit.Decorator; |
| import org.apache.derbyTesting.junit.JDBC; |
| import org.apache.derbyTesting.junit.TestConfiguration; |
| import org.apache.derbyTesting.junit.Utilities; |
| |
| /** |
| * 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)"); |
| |
| // multiple tests depend on small page size, make sure size is 4k |
| checkSmallPageSize(stmt, "TESTCLOB"); |
| |
| 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"); |
| |
| // multiple tests depend on small page size, make sure size is 4k |
| checkSmallPageSize(stmt, "TESTBLOB"); |
| |
| stmt.close(); |
| commit(); |
| } |
| |
| protected void tearDown() throws Exception { |
| rollback(); |
| Statement stmt = createStatement(); |
| stmt.executeUpdate("DROP TABLE testClob"); |
| stmt.executeUpdate("DROP TABLE testBlob"); |
| stmt.close(); |
| commit(); |
| super.tearDown(); |
| } |
| |
| /*** TESTS ***/ |
| |
| /** |
| * DERBY-3085. Test update where streamed parameter is not |
| * consumed by the server. Network Server needs to clean-up |
| * after execution. |
| * |
| */ |
| public void testUnconsumedParameter() throws SQLException |
| { |
| Connection conn = getConnection(); |
| conn.setAutoCommit(false); |
| Statement s = conn.createStatement(); |
| // Test table with no rows. |
| s.executeUpdate("create table testing(num int, addr varchar(40), contents blob(16M))"); |
| // no rows inserted so there is no match. |
| byte[] data = new byte[ 38000]; |
| for (int i = 0; i < data.length; i++) |
| data[i] = 'a'; |
| ByteArrayInputStream is = new ByteArrayInputStream( data); |
| String sql = "UPDATE testing SET Contents=? WHERE num=1"; |
| |
| PreparedStatement ps = prepareStatement( sql); |
| ps.setBinaryStream( 1, is,data.length); |
| //DERBY-4312 Make sure commit() doesn't interfere here |
| commit(); |
| ps.executeUpdate(); |
| // Make sure things still work ok when we have a parameter that does get consumed. |
| // insert a matching row. |
| s.executeUpdate("insert into testing values (1,null,null)"); |
| is = new ByteArrayInputStream(data); |
| ps.setBinaryStream( 1, is,data.length); |
| ps.executeUpdate(); |
| // Check update occurred |
| ResultSet rs = s.executeQuery("select length(contents) from testing where num = 1"); |
| JDBC.assertSingleValueResultSet(rs, "38000"); |
| ps.close(); |
| conn.commit(); |
| // Check the case where there are rows inserted but there is no match. |
| is = new ByteArrayInputStream( data); |
| sql = "UPDATE testing SET Contents=? WHERE num=2"; |
| ps = prepareStatement( sql); |
| ps.setBinaryStream( 1, is,data.length); |
| ps.executeUpdate(); |
| ps.close(); |
| s.executeUpdate("drop table testing"); |
| conn.commit(); |
| |
| // Test with multiple parameters |
| s.executeUpdate("create table testing(num int, addr varchar(40), contents blob(16M),contents2 blob(16M))"); |
| |
| is = new ByteArrayInputStream( data); |
| ByteArrayInputStream is2 = new ByteArrayInputStream(data); |
| sql = "UPDATE testing SET Contents=?, contents2=? WHERE num=1"; |
| |
| ps = prepareStatement( sql); |
| ps.setBinaryStream( 1, is,data.length); |
| ps.setBinaryStream(2, is2,data.length); |
| ps.executeUpdate(); |
| |
| |
| // multiple parameters and matching row |
| s.executeUpdate("insert into testing values (1,'addr',NULL,NULL)"); |
| is = new ByteArrayInputStream( data); |
| is2 = new ByteArrayInputStream(data); |
| ps.setBinaryStream( 1, is,data.length); |
| ps.setBinaryStream(2, is2,data.length); |
| ps.executeUpdate(); |
| rs = s.executeQuery("select length(contents), length(contents2) from testing where num = 1"); |
| JDBC.assertFullResultSet(rs, new String[][] {{"38000","38000"}}); |
| rs.close(); |
| s.executeUpdate("drop table testing"); |
| |
| // With Clob |
| s.executeUpdate("create table testing(num int, addr varchar(40), contents Clob(16M))"); |
| char[] charData = new char[ 38000]; |
| for (int i = 0; i < data.length; i++) |
| data[i] = 'a'; |
| CharArrayReader reader = new CharArrayReader( charData); |
| sql = "UPDATE testing SET Contents=? WHERE num=1"; |
| |
| ps = prepareStatement( sql); |
| ps.setCharacterStream( 1, reader,charData.length); |
| ps.executeUpdate(); |
| // with a matching row |
| s.executeUpdate("insert into testing values (1,null,null)"); |
| reader = new CharArrayReader(charData); |
| ps.setCharacterStream( 1, reader,data.length); |
| ps.executeUpdate(); |
| // Check update occurred |
| rs = s.executeQuery("select length(contents) from testing where num = 1"); |
| JDBC.assertSingleValueResultSet(rs, "38000"); |
| s.executeUpdate("drop table testing"); |
| ps.close(); |
| |
| conn.commit(); |
| |
| } |
| |
| /** |
| * Test that it is possible to change the isolation level after reading a |
| * BLOB (DERBY-3427). |
| */ |
| public void testIsolationLevelChangeAfterRead() throws SQLException { |
| ResultSet rs = |
| createStatement().executeQuery("VALUES CAST(X'FFFF' AS BLOB)"); |
| JDBC.assertDrainResults(rs); |
| getConnection().setTransactionIsolation( |
| Connection.TRANSACTION_SERIALIZABLE); |
| } |
| |
| /** |
| * 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); |
| //DERBY-4312 make sure commit() doesn't interfere |
| commit(); |
| ps.executeUpdate(); |
| streamReader.close(); |
| ps.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 baseTestTriggersWithClobColumn(boolean useOrderBy) |
| 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 "+ |
| "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 "+ |
| "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 = null; |
| ResultSet trigARS = null; |
| ResultSet trigBRS = null; |
| |
| if (useOrderBy) { |
| origRS = |
| origSt.executeQuery( |
| "select a, length(a), b from testClob order by b"); |
| trigARS = |
| trigASt.executeQuery( |
| "select a, length(a), b from testClobTriggerA order by b"); |
| trigBRS = |
| trigBSt.executeQuery( |
| "select a, length(a), b from testClobTriggerB order by b"); |
| |
| } else { |
| origRS = |
| origSt.executeQuery( |
| "select a, length(a), b from testClob"); |
| trigARS = |
| trigASt.executeQuery( |
| "select a, length(a), b from testClobTriggerA"); |
| trigBRS = |
| trigBSt.executeQuery( |
| "select a, length(a), b from testClobTriggerB"); |
| } |
| |
| 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()); |
| |
| Clob origClob = origRS.getClob(1); |
| if (origClob != null) { |
| assertEquals("FAIL - Invalid checksum for row trigger", |
| getStreamCheckSum(origClob.getAsciiStream()), |
| getStreamCheckSum(trigARS.getClob(1).getAsciiStream())); |
| assertEquals("FAIL - Invalid checksum for statement trigger", |
| getStreamCheckSum(origClob.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 triggers on CLOB columns. |
| * <p> |
| * Call with order by in the query, this causes a path through the |
| * sorter. |
| **/ |
| public void testTriggersWithClobColumnOrderBy() |
| throws Exception { |
| |
| baseTestTriggersWithClobColumn(true); |
| } |
| |
| /** |
| * Test triggers on CLOB columns. |
| * <p> |
| * Call with no order by in the query, thus a code path not through |
| * the sorter. |
| **/ |
| public void testTriggersWithClobColumn() |
| throws Exception { |
| |
| baseTestTriggersWithClobColumn(false); |
| } |
| |
| |
| /** |
| * 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(); |
| stmt.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; |
| 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); |
| |
| rs.close(); // Cleanup on fail |
| stmt.close(); |
| |
| 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(); |
| |
| rs.close(); // Cleanup on fail |
| stmt.close(); |
| |
| 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); |
| } |
| // 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 testPositionAgressiveUseOrderBy() |
| throws Exception { |
| baseTestPositionAgressive(true); |
| } |
| public void testPositionAgressiveDoNotUseOrderBy() |
| throws Exception { |
| baseTestPositionAgressive(false); |
| } |
| |
| public void baseTestPositionAgressive(boolean useOrderBy) |
| 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, useOrderBy); |
| commit(); |
| |
| ResultSet rsc = s.executeQuery("SELECT PATT FROM C8.T8PATT"); |
| rsc.next(); |
| checkClob8(s, rsc.getClob(1), useOrderBy); |
| |
| 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, useOrderBy); |
| commit(); |
| |
| rsc = s.executeQuery("SELECT PATT FROM C8.T8PATT"); |
| rsc.next(); |
| checkClob8(s, rsc.getClob(1), useOrderBy); |
| |
| 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, useOrderBy); |
| commit(); |
| |
| rsc = s.executeQuery("SELECT PATT FROM C8.T8PATT"); |
| rsc.next(); |
| checkClob8(s, rsc.getClob(1), useOrderBy); |
| |
| 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, |
| boolean useOrderBy) |
| throws SQLException |
| { |
| ResultSet rs = null; |
| |
| if (useOrderBy) |
| { |
| rs = s.executeQuery( |
| "SELECT ID, DD, POS, L FROM C8.T8POS ORDER BY 1"); |
| } |
| else |
| { |
| rs = s.executeQuery( |
| "SELECT ID, DD, POS, L FROM C8.T8POS"); |
| } |
| |
| 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, |
| boolean useOrderBy) |
| throws SQLException { |
| |
| |
| ResultSet rs = null; |
| |
| if (useOrderBy) |
| { |
| rs = s.executeQuery( |
| "SELECT ID, DD, POS, L FROM C8.T8POS ORDER BY 1"); |
| } |
| else |
| { |
| rs = s.executeQuery( |
| "SELECT ID, DD, POS, L FROM C8.T8POS"); |
| } |
| |
| 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"); |
| stmt2.close(); // Cleanup on fail |
| conn2.rollback(); |
| conn2.close(); |
| fail("FAIL: row should be locked"); |
| } catch (SQLException se) { |
| checkException(LOCK_TIMEOUT, se); |
| } |
| |
| // DERBY-3740, the reference below to clob must remain after the above |
| // expected lock timeout, otherwise GC might run on the clob and |
| // cause intermittent problems if the GC causes lock to be released |
| // early. |
| assertEquals("FAIL: clob length changed", 10000, clob.length()); |
| |
| // Test that update goes through after the transaction is committed |
| commit(); |
| stmt2.executeUpdate("update testClob set b = b + 1 where b = 10000"); |
| |
| 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); |
| // DERBY-4312 make sure commit() doesn't interfere here. |
| commit(); |
| ps.executeUpdate(); |
| streamIn.close(); |
| ps.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"); |
| stmt2.close(); // Cleanup on fail |
| conn2.rollback(); |
| conn2.close(); |
| fail("FAIL - statement should timeout"); |
| } catch (SQLException se) { |
| checkException(LOCK_TIMEOUT, se); |
| } |
| |
| // Test that update goes through after the transaction is committed |
| commit(); |
| stmt2.executeUpdate("update testClob set el = 'smurfball' where b = 1"); |
| |
| stmt2.close(); |
| conn2.commit(); |
| conn2.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); |
| } |
| |
| /* |
| * We call it before the commit(); to cache the result |
| * DERBY-3574 |
| */ |
| clob.length(); |
| shortClob.length(); |
| |
| rs.close(); |
| stmt.close(); |
| commit(); |
| assertTrue("FAIL - shortClob is NULL", shortClob != null); |
| // this should give blob/clob unavailable exceptions on client |
| try { |
| shortClob.length(); |
| //Should have thrown an SQLException in the |
| fail("FAIL - should not be able to access Clob after commit"); |
| } catch (SQLException e) { |
| //The same SQLState String INVALID_LOB |
| //is used for LOB's(Both Clob and Blob). Ensure that |
| //we get the expected exception by comparing the SQLState. |
| checkException(INVALID_LOB, e); |
| } |
| |
| // these should all give blob/clob data unavailable exceptions |
| try { |
| clob.length(); |
| //Large Clobs not accessible after commit. |
| //Should have thrown an SQLException here. |
| fail("FAIL - should not be able to access large Clob after commit"); |
| } catch (SQLException e) { |
| //The same SQLState String INVALID_LOB |
| //is used for LOB's(Both Clob and Blob). Ensure that |
| //we get the expected exception by comparing the SQLState. |
| checkException(INVALID_LOB, e); |
| } |
| try { |
| clob.getSubString(2,3); |
| //Large Clobs are not accessible after commit. |
| //Should have thrown an SQLException here. |
| fail("FAIL - should not be able to access large Clob after commit"); |
| } catch (SQLException e) { |
| //The same SQLState String INVALID_LOB |
| //is used for LOB's(Both Clob and Blob). Ensure that |
| //we get the expected exception by comparing the SQLState. |
| checkException(INVALID_LOB, e); |
| } |
| try { |
| clob.getAsciiStream(); |
| //Large Clobs are not accessible after commit. |
| //Should have thrown an SQLException here. |
| fail("FAIL - should not be able to access large Clob after commit"); |
| } catch (SQLException e) { |
| //The same SQLState String INVALID_LOB |
| //is used for LOB's(Both Clob and Blob). Ensure that |
| //we get the expected exception by comparing the SQLState. |
| checkException(INVALID_LOB, e); |
| } |
| try { |
| clob.position("foo",2); |
| //Large Clobs are not accessible after commit. |
| //Should have thrown an SQLException here. |
| fail("FAIL - should not be able to access large Clob after commit"); |
| } catch (SQLException e) { |
| //The same SQLState String INVALID_LOB |
| //is used for LOB's(Both Clob and Blob). Ensure that |
| //we get the expected exception by comparing the SQLState. |
| checkException(INVALID_LOB, e); |
| } |
| try { |
| clob.position(clob,2); |
| //Large Clobs are not accessible after commit. |
| //Should have thrown an SQLException here. |
| fail("FAIL - should not be able to access large Clob after commit"); |
| } catch (SQLException e) { |
| //The same SQLState String INVALID_LOB |
| //is used for LOB's(Both Clob and Blob). Ensure that |
| //we get the expected exception by comparing the SQLState. |
| checkException(INVALID_LOB, e); |
| } |
| } |
| |
| /** |
| * 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); |
| } |
| |
| /* |
| * We call it before the commit(); to cache the result |
| * DERBY-3574 |
| */ |
| clob.length(); |
| shortClob.length(); |
| |
| rs.close(); |
| stmt.close(); |
| commit(); |
| getConnection().close(); |
| |
| try { |
| long len = shortClob.length(); |
| //Clobs on the Embedded side and the NetworkClient |
| //side are not accessible after closing the |
| //connection. Should have thrown an SQLException here. |
| fail("FAIL - should not be able to access Clob after " + |
| "Connection Close"); |
| } |
| catch (SQLException e) { |
| //Ensure that we get the expected exception by comparing |
| //the SQLState. |
| checkException(NO_CURRENT_CONNECTION, e); |
| } |
| |
| // these should all give blob/clob data unavailable exceptions |
| try { |
| clob.length(); |
| //Large Clobs on the Embedded side and the NetworkClient |
| //side are not accessible after Connection Close. Should |
| //have thrown an SQLException here. |
| fail("FAIL - should not be able to access large " + |
| "Clob after Connection Close"); |
| } catch (SQLException e) { |
| //Ensure that we get the expected exception by comparing |
| //the SQLState. |
| checkException(NO_CURRENT_CONNECTION, e); |
| } |
| try { |
| clob.getSubString(2,3); |
| //Large Clobs on the Embedded side and the NetworkClient |
| //side are not accessible after Connection Close. Should |
| //have thrown an SQLException here. |
| fail("FAIL - should not be able to access large " + |
| "Clob after Connection Close"); |
| } catch (SQLException e) { |
| //Ensure that we get the expected exception by comparing |
| //the SQLState. |
| checkException(NO_CURRENT_CONNECTION, e); |
| } |
| try { |
| clob.getAsciiStream(); |
| //Large Clobs on the Embedded side and the NetworkClient |
| //side are not accessible after Connection Close. Should |
| //have thrown an SQLException here. |
| fail("FAIL - should not be able to access large " + |
| "Clob after Connection Close"); |
| } catch (SQLException e) { |
| //Ensure that we get the expected exception by comparing |
| //the SQLState. |
| checkException(NO_CURRENT_CONNECTION, e); |
| } |
| try { |
| clob.position("foo",2); |
| //Large Clobs on the Embedded side and the NetworkClient |
| //side are not accessible after Connection Close. Should |
| //have thrown an SQLException here. |
| fail("FAIL - should not be able to access large " + |
| "Clob after Connection Close"); |
| } catch (SQLException e) { |
| //Ensure that we get the expected exception by comparing |
| //the SQLState. |
| checkException(NO_CURRENT_CONNECTION, e); |
| } |
| try { |
| clob.position(clob,2); |
| //Large Clobs on the Embedded side and the NetworkClient |
| //side are not accessible after Connection Close. Should |
| //have thrown an SQLException here. |
| fail("FAIL - should not be able to access large " + |
| "Clob after Connection Close"); |
| } catch (SQLException e) { |
| //Ensure that we get the expected exception by comparing |
| //the SQLState. |
| checkException(NO_CURRENT_CONNECTION, e); |
| } |
| } |
| |
| /** |
| * Make sure we get an error attempting to access the |
| * lob after commit. |
| */ |
| public void testClobAfterCommitWithSecondClob() throws SQLException |
| { |
| getConnection().setAutoCommit(false); |
| Statement s1 = createStatement(); |
| ResultSet rs1 = s1.executeQuery("values cast('first' as clob)"); |
| rs1.next(); |
| Clob first = rs1.getClob(1); |
| rs1.close(); |
| commit(); |
| Statement s2 = createStatement(); |
| ResultSet rs2 = s2.executeQuery("values cast('second' as clob)"); |
| rs2.next(); |
| Clob second = rs2.getClob(1); |
| try { |
| first.getSubString(1,100); |
| fail("first.getSubString should have failed because after the commit"); |
| } catch (SQLException se){ |
| assertSQLState(INVALID_LOB,se); |
| } |
| assertEquals("second",second.getSubString(1, 100)); |
| rs2.close(); |
| } |
| /** |
| * 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. |
| * |
| * @throws SQLException |
| */ |
| public void testGetClobBeforeAndAfterUpdate() 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); |
| c.setString(1, value); |
| rs.updateClob(2, c); |
| 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(). |
| * |
| * @throws SQLException |
| */ |
| public void testGetClobBeforeAndAfterUpdateStream() 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"); |
| |
| 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, "US-ASCII"); |
| // 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,"US-ASCII").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(); |
| } |
| |
| /** |
| * Tests the {@code Blob.position} using a deterministic sequence of |
| * actions and arguments. |
| */ |
| public void testPositionBlobDeterministic() |
| throws IOException, SQLException { |
| getConnection().setAutoCommit(false); |
| final int size = 100000; |
| PreparedStatement ps = prepareStatement( |
| "INSERT INTO testBlob (a, b) VALUES (?, ?)"); |
| ps.setBinaryStream(1, new LoopingAlphabetStream(size), size); |
| ps.setInt(2, size); |
| ps.executeUpdate(); |
| ps.close(); |
| ps = prepareStatement("select a from testBlob where b = ?"); |
| ps.setInt(1, size); |
| ResultSet rs = ps.executeQuery(); |
| assertTrue("No data found", rs.next()); |
| Blob blob = rs.getBlob(1); |
| // Try with a one-byte pattern. |
| byte[] pattern = new byte[] {(byte)'k'}; // k number 11 in the alphabet |
| assertEquals(11, blob.position(pattern, 1)); |
| // Try with a non-existing pattern. |
| pattern = new byte[] {(byte)'p', (byte)'o'}; |
| assertEquals(-1, blob.position(pattern, size / 3)); |
| |
| // Loop through all matches |
| pattern = new byte[] {(byte)'d', (byte)'e'}; |
| long foundAtPos = 1; |
| int index = 0; |
| int stepSize = ByteAlphabet.modernLatinLowercase().byteCount(); |
| while ((foundAtPos = blob.position(pattern, foundAtPos +1)) != -1) { |
| assertEquals((stepSize * index++) + 4, foundAtPos); |
| byte[] fetchedPattern = blob.getBytes(foundAtPos, pattern.length); |
| assertTrue(Arrays.equals(pattern, fetchedPattern)); |
| } |
| |
| // Try a longer pattern. |
| int pSize = 65*1024; // 65 KB |
| pattern = new byte[pSize]; |
| assertEquals(pSize, new LoopingAlphabetStream(pSize).read(pattern)); |
| assertEquals(1, blob.position(pattern, 1)); |
| assertEquals(stepSize * 100 +1, |
| blob.position(pattern, stepSize * 99 + 7)); |
| // Try again after getting the length. |
| assertEquals(size, blob.length()); |
| assertEquals(stepSize * 100 +1, |
| blob.position(pattern, stepSize * 99 + 7)); |
| |
| // Try specifing a starting position that's too big. |
| try { |
| blob.position(pattern, size*2); |
| fail("Accepted position after end of Blob"); |
| } catch (SQLException sqle) { |
| assertSQLState("XJ076", sqle); |
| } |
| |
| // Fetch the last 5 bytes, try with a partial match at the end. |
| byte[] blobEnd = blob.getBytes(size - 4, 5); |
| pattern = new byte[6]; |
| System.arraycopy(blobEnd, 0, pattern, 0, blobEnd.length); |
| pattern[5] = 'X'; // Only lowercase in the looping alphabet stream. |
| assertEquals(-1, blob.position(pattern, size - 10)); |
| |
| // Get the very last byte, try with a partial match at the end. |
| blobEnd = blob.getBytes(size, 1); |
| pattern = new byte[] {blobEnd[0], 'X'}; |
| assertEquals(-1, blob.position(pattern, size - 5)); |
| } |
| |
| /** |
| * 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),"US-ASCII"); |
| // 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),"US-ASCII"); |
| if (tmp.indexOf(searchString) != -1) { |
| startSearchPos = start; |
| } |
| |
| ps.setBytes(1, searchString.getBytes("US-ASCII")); |
| 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()),"US-ASCII"); |
| 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 "+ |
| "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 "+ |
| "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); |
| rs.close(); |
| stmt.close(); |
| fail("FAIL - getBlob on int column should throw an " + |
| "exception"); |
| } catch (SQLException se) { |
| checkException(LANG_DATA_TYPE_GET_MISMATCH, se); |
| } |
| } |
| rs.close(); |
| 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(); |
| rs.close(); |
| stmt.close(); |
| ps.close(); |
| 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); |
| } |
| // 0 or negative position value |
| try { |
| blob.position(new byte[0], -4000); |
| fail("FAIL - position with negative start " + |
| "position should have caused an exception"); |
| } catch (SQLException e) { |
| checkException(BLOB_BAD_POSITION, e); |
| } |
| // 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); |
| fail("FAIL - position with negative start " + |
| "position should have caused an exception"); |
| } catch (SQLException e) { |
| checkException(BLOB_BAD_POSITION, e); |
| } |
| // 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 { |
| |
| Statement stmt = createStatement(); |
| |
| insertDefaultData(); |
| |
| // for blob lock to remain, autocommit must be false. |
| assertFalse(getConnection().getAutoCommit()); |
| |
| // test depends on small page size, make sure size is 4k |
| checkSmallPageSize(stmt, "TESTBLOB"); |
| |
| 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()); |
| |
| // should timeout waiting for the lock to do this |
| try { |
| |
| stmt2.executeUpdate( |
| "update testBlob set b = b + 1 where b = 10000"); |
| stmt.close(); |
| stmt2.close(); |
| conn2.rollback(); |
| conn2.close(); |
| fail("FAIL - should have gotten lock timeout"); |
| } catch (SQLException se) { |
| checkException(LOCK_TIMEOUT, se); |
| } |
| |
| // DERBY-3740, add a reference to the retrieved blobs that is used |
| // after the expected lock timeout in conn2. Before this change |
| // this test would intermittently fail. I believe that a smart |
| // JVM/JIT recognized that the blob reference was no longer used |
| // after the above while loop, and allowed gc on it before the routine |
| // could get to the expected conflicting lock. Upon GC the blob's |
| // finalize code closes the internal stream and releases the lock in |
| // read committed mode. |
| |
| // make sure we got the 10000 byte blob which should be a stream. |
| assertTrue(blob != null); |
| |
| // make sure we got the 26 byte blob which should be materialized. |
| assertTrue(shortBlob != null); |
| |
| // Test that update goes through after the transaction is committed |
| commit(); |
| stmt2.executeUpdate("update testBlob set b = b + 1 where b = 10000"); |
| |
| stmt.close(); |
| stmt2.close(); |
| conn2.commit(); |
| conn2.close(); |
| } |
| |
| /** |
| * test locking with a long row + long column |
| */ |
| public void testLockingWithLongRowBlob() throws Exception |
| { |
| ResultSet rs; |
| Statement stmt, stmt2; |
| |
| // for blob lock to remain, autocommit must be false. |
| assertFalse(getConnection().getAutoCommit()); |
| |
| stmt = createStatement(); |
| |
| // test depends on small page size, make sure size is 4k |
| checkSmallPageSize(stmt, "TESTBLOB"); |
| |
| 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(); |
| ps.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"); |
| stmt2.close(); |
| stmt.close(); |
| conn2.rollback(); |
| conn2.close(); |
| fail("FAIL - statement should timeout"); |
| } catch (SQLException se) { |
| checkException(LOCK_TIMEOUT, se); |
| } |
| // Test that update goes through after the transaction is committed |
| commit(); |
| |
| // DERBY-3740, add a reference to the retrieved blobs that is used |
| // after the expected lock timeout in conn2. Before this change |
| // this test would intermittently fail. I believe that a smart |
| // JVM/JIT recognized that the blob reference was no longer used |
| // after the above while loop, and allowed gc on it before the routine |
| // could get to the expected conflicting lock. Upon GC the blob's |
| // finalize code closes the internal stream and releases the lock in |
| // read committed mode. |
| assertTrue( |
| "FAIL - blob is null after expected lock timeout", blob != null); |
| |
| stmt2.executeUpdate("update testBlob set el = null where b = 1"); |
| |
| stmt2.close(); |
| conn2.commit(); |
| stmt.close(); |
| conn2.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); |
| } |
| |
| /* |
| * We call it before the commit(); to cache the result |
| * DERBY-3574 |
| */ |
| blob.length(); |
| shortBlob.length(); |
| |
| rs.close(); |
| stmt.close(); |
| commit(); |
| |
| |
| assertTrue("FAIL - shortBlob is NULL", shortBlob != null); |
| // This should give blob/clob unavailable exceptions with both |
| // client and embedded driver. |
| try { |
| shortBlob.length(); |
| fail("FAIL - should not be able to access Blob after commit"); |
| } catch (SQLException e) { |
| checkException(INVALID_LOB, e); |
| } |
| |
| assertTrue("FAIL - blob is NULL", blob != null); |
| // these should all give blob/clob data unavailable exceptions |
| try { |
| blob.length(); |
| fail("FAIL - should not be able to access large Blob after commit"); |
| } catch (SQLException e) { |
| checkException(INVALID_LOB, e); |
| } |
| try { |
| blob.getBytes(2,3); |
| fail("FAIL - should not be able to access large Blob after commit"); |
| } catch (SQLException e) { |
| checkException(INVALID_LOB, e); |
| } |
| try { |
| blob.getBinaryStream(); |
| fail("FAIL - should not be able to access large Blob after commit"); |
| } catch (SQLException e) { |
| checkException(INVALID_LOB, e); |
| } |
| try { |
| blob.position("foo".getBytes("US-ASCII"),2); |
| fail("FAIL - should not be able to access large Blob after commit"); |
| } catch (SQLException e) { |
| checkException(INVALID_LOB, e); |
| } |
| try { |
| blob.position(blob,2); |
| fail("FAIL - should not be able to access large Blob after commit"); |
| } catch (SQLException e) { |
| checkException(INVALID_LOB, e); |
| } |
| } |
| |
| /** |
| * 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); |
| } |
| |
| /* |
| * We call it before the commit(); to cache the result |
| * DERBY-3574 |
| */ |
| blob.length(); |
| shortBlob.length(); |
| |
| rs.close(); |
| rollback(); |
| getConnection().close(); |
| |
| try { |
| long length = shortBlob.length(); |
| fail("FAIL - should get an exception, connection is closed"); |
| } catch (SQLException e) { |
| checkException(NO_CURRENT_CONNECTION, e); |
| } |
| |
| // 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) { |
| 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) { |
| 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) { |
| 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) { |
| 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) { |
| 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"); |
| |
| 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. |
| * |
| * @throws Exception |
| */ |
| public void testGetBlobBeforeAndAfterUpdate() throws Exception { |
| String blobData = "initial blob "; |
| PreparedStatement ps = |
| prepareStatement( |
| "insert into testBlob (b, a) values (?, ?)"); |
| for (int i=0; i<10; i++) { |
| ps.setInt(1, i); |
| ps.setBytes(2, (blobData + i).getBytes("US-ASCII")); |
| ps.execute(); |
| } |
| ps.close(); |
| |
| Statement scrollStmt = createStatement( |
| ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); |
| ResultSet rs = scrollStmt.executeQuery("SELECT b, a FROM testBlob"); |
| |
| rs.first(); |
| checkContentsBeforeAndAfterUpdatingBlob(rs); |
| rs.next(); |
| checkContentsBeforeAndAfterUpdatingBlob(rs); |
| rs.relative(3); |
| checkContentsBeforeAndAfterUpdatingBlob(rs); |
| rs.absolute(7); |
| checkContentsBeforeAndAfterUpdatingBlob(rs); |
| rs.previous(); |
| checkContentsBeforeAndAfterUpdatingBlob(rs); |
| rs.last(); |
| checkContentsBeforeAndAfterUpdatingBlob(rs); |
| rs.previous(); |
| checkContentsBeforeAndAfterUpdatingBlob(rs); |
| |
| rs.close(); |
| scrollStmt.close(); |
| |
| } |
| |
| private void checkContentsBeforeAndAfterUpdatingBlob(ResultSet rs) |
| throws SQLException, UnsupportedEncodingException { |
| Blob b; |
| byte[] value, expectedValue; |
| String blobData = "initial blob "; |
| String updatedBlobData = "updated blob "; |
| |
| b = rs.getBlob(2); |
| // check contents |
| value = b.getBytes(1, blobData.length() + 1); |
| expectedValue = (blobData + rs.getInt(1)).getBytes("US-ASCII"); |
| assertTrue("FAIL - wrong blob value", |
| Arrays.equals(value, expectedValue)); |
| |
| // update contents |
| value = (updatedBlobData + rs.getInt(1)).getBytes("US-ASCII"); |
| b.setBytes(1, value); |
| rs.updateBlob(2, b); |
| rs.updateRow(); |
| // check update values |
| rs.next(); // leave the row |
| rs.previous(); // go back to updated row |
| b = rs.getBlob(2); |
| // check contents |
| value = b.getBytes(1, updatedBlobData.length() + 1); |
| expectedValue = (updatedBlobData + rs.getInt(1)).getBytes("US-ASCII"); |
| assertTrue("FAIL - wrong blob value", |
| Arrays.equals(value, expectedValue)); |
| } |
| |
| /** |
| * Test fix for derby-1421. |
| * |
| * Test that the getBlob() returns the correct value for the blob before and |
| * after updating the blob using the method updateBinaryStream(). |
| * |
| * @throws Exception |
| */ |
| public void testGetBlobBeforeAndAfterUpdateStream() throws Exception { |
| |
| String blobData = "initial blob "; |
| PreparedStatement ps = |
| prepareStatement( |
| "insert into testBlob (b, a) values (?, ?)"); |
| for (int i=0; i<10; i++) { |
| ps.setInt(1, i); |
| ps.setBytes(2, (blobData + i).getBytes("US-ASCII")); |
| ps.execute(); |
| } |
| ps.close(); |
| |
| Statement scrollStmt = createStatement( |
| ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); |
| ResultSet rs = scrollStmt.executeQuery("SELECT b, a FROM testBlob"); |
| |
| rs.first(); |
| updateBlobWithUpdateBinaryStream(rs); |
| rs.next(); |
| updateBlobWithUpdateBinaryStream(rs); |
| rs.relative(3); |
| updateBlobWithUpdateBinaryStream(rs); |
| rs.absolute(7); |
| updateBlobWithUpdateBinaryStream(rs); |
| rs.previous(); |
| updateBlobWithUpdateBinaryStream(rs); |
| rs.last(); |
| updateBlobWithUpdateBinaryStream(rs); |
| rs.previous(); |
| updateBlobWithUpdateBinaryStream(rs); |
| |
| rs.close(); |
| scrollStmt.close(); |
| } |
| |
| private void updateBlobWithUpdateBinaryStream(ResultSet rs) |
| throws SQLException, UnsupportedEncodingException { |
| Blob b; |
| byte[] value, expectedValue; |
| String blobData = "initial blob "; |
| String updatedBlobData = "updated blob "; |
| |
| b = rs.getBlob(2); |
| // check contents |
| value = b.getBytes(1, blobData.length() + 1); |
| expectedValue = (blobData + rs.getInt(1)).getBytes("US-ASCII"); |
| |
| assertTrue("FAIL - wrong blob value", |
| Arrays.equals(value, expectedValue)); |
| |
| // update contents |
| value = (updatedBlobData + rs.getInt(1)).getBytes("US-ASCII"); |
| InputStream updateValue = new ByteArrayInputStream(value); |
| rs.updateBinaryStream(2, updateValue, value.length); |
| rs.updateRow(); |
| // check update values |
| rs.next(); // leave the row |
| rs.previous(); // go back to updated row |
| b = rs.getBlob(2); |
| // check contents |
| value = b.getBytes(1, updatedBlobData.length() + 1); |
| expectedValue = (updatedBlobData + rs.getInt(1)).getBytes("US-ASCII"); |
| assertTrue("FAIL - wrong blob value", |
| Arrays.equals(value, expectedValue)); |
| } |
| |
| /** |
| * test behaviour of system with self destructive user |
| * update a long column underneath a clob |
| */ |
| public void testSelfDestructiveClob() throws Exception { |
| insertDefaultData(); |
| |
| Statement stmt = createStatement(); |
| ResultSet rs = stmt.executeQuery( |
| "select a, b from testClob where b = 10000"); |
| byte[] buff = new byte[128]; |
| // fetch row back, get the column as a clob. |
| Clob clob = null; |
| InputStream fin; |
| int clobLength = 0, i = 0; |
| assertTrue("FAIL - row not found", rs.next()); |
| i++; |
| clobLength = rs.getInt(2); |
| // get the first column as a clob |
| clob = rs.getClob(1); |
| assertEquals("FAIL - wrong clob length", 10000, clobLength); |
| fin = clob.getAsciiStream(); |
| int columnSize = 0; |
| |
| PreparedStatement ps = prepareStatement( |
| "update testClob set a = ? where b = 10000"); |
| StringBuffer foo = new StringBuffer(); |
| for (int k = 0; k < 1000; k++) |
| foo.append('j'); |
| ps.setString(1,foo.toString()); |
| ps.executeUpdate(); |
| |
| rs = stmt.executeQuery("select a from testClob where b = 10000"); |
| while (rs.next()) { |
| int j = 1; |
| String val = rs.getString(1); |
| assertEquals("FAIL - invalid blob value", foo.substring(0, 50), |
| val.substring(0,50)); |
| j++; |
| } |
| |
| while (columnSize < 11000) { |
| int size = fin.read(buff); |
| if (size == -1) |
| break; |
| columnSize += size; |
| } |
| assertEquals("FAIL - invalid column size", 10000, columnSize); |
| |
| assertEquals("FAIL - invalid column size", clobLength, columnSize); |
| assertEquals("FAIL - invalid column size", columnSize, clob.length()); |
| |
| rs.close(); |
| stmt.close(); |
| } |
| |
| /** |
| * test behaviour of system with self destructive user |
| * drop table and see what happens to the clob |
| * expect an IOException when moving to a new page of the long column |
| */ |
| public void testSelfDestructiveClob2() throws Exception { |
| insertDefaultData(); |
| |
| Statement stmt = createStatement(); |
| ResultSet rs = stmt.executeQuery( |
| "select a,b from testClob where b = 10000"); |
| byte[] buff = new byte[128]; |
| // fetch row back, get the column as a clob. |
| Clob clob = null; |
| InputStream fin; |
| int clobLength = 0, i = 0; |
| assertTrue("FAIL - row not found", rs.next()); |
| i++; |
| clobLength = rs.getInt(2); |
| // get the first column as a clob |
| clob = rs.getClob(1); |
| assertEquals("FAIL - wrong clob length", 10000, clobLength); |
| fin = clob.getAsciiStream(); |
| int columnSize = 0; |
| |
| stmt.executeUpdate("drop table testClob"); |
| |
| try { |
| while (columnSize < 11000) { |
| int size = fin.read(buff); |
| if (size == -1) |
| break; |
| columnSize += size; |
| } |
| fail("FAIL - should have got an IOException"); |
| } catch (java.io.IOException ioe) { |
| if(usingEmbedded()) { |
| assertEquals("FAIL - wrong exception", |
| "ERROR 40XD0: Container has been closed.", |
| ioe.getMessage()); |
| } |
| } |
| |
| rollback(); |
| } |
| |
| /** |
| * Test fix for derby-265. |
| * Test that if getBlob is called after the transaction |
| * in which it was created is committed, a proper user error |
| * is thrown instead of an NPE. |
| * Basically per the spec, getBlob is valid only for the duration of |
| * the transaction it was created in |
| * Updated for DERBY-1511: The test case wasn't supposed to fail in the |
| * first place (neither with NPE nor with "proper user error") since none |
| * of the BLOBs are accessed after the transaction that created them was |
| * completed. |
| * @throws Exception |
| * @throws FileNotFoundException |
| * @throws IOException |
| */ |
| public void testNegativeTestDerby265Blob() throws Exception { |
| getConnection().setAutoCommit(false); |
| |
| PreparedStatement ps = prepareStatement( |
| "insert into testBlob(b, a) values(?,?)"); |
| for (int i = 0; i < 3; i++) { |
| InputStream fis = new LoopingAlphabetStream(300000); |
| ps.setInt(1, i); |
| ps.setBinaryStream(2, fis, 300000); |
| ps.executeUpdate(); |
| fis.close(); |
| } |
| commit(); |
| |
| getConnection().setAutoCommit(true); |
| |
| Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY, |
| ResultSet.CONCUR_READ_ONLY); |
| s.execute("SELECT b, a FROM testBlob"); |
| ResultSet rs1 = s.getResultSet(); |
| Statement s2 = createStatement(ResultSet.TYPE_FORWARD_ONLY, |
| ResultSet.CONCUR_READ_ONLY); |
| s2.executeQuery("SELECT b, a FROM testBlob"); |
| ResultSet rs2 = s2.getResultSet(); |
| rs2.next(); |
| |
| Blob b2 = rs2.getBlob(2); |
| rs1.next(); |
| Blob b1 = rs1.getBlob(2); |
| rs1.close(); |
| |
| // DERBY-1511: Fetching the next BLOB here used to fail because it |
| // had been pre-fetched before the commit and was closed in the commit. |
| // Now, we don't pre-fetch BLOBs anymore, so expect to get a working |
| // object here. |
| assertTrue(rs2.next()); |
| assertNotNull(rs2.getBlob(2)); |
| rs2.close(); |
| } |
| |
| /** |
| * Test fix for derby-265. |
| * Test that if getClob is called after the transaction |
| * in which it was created is committed, a proper user error |
| * is thrown instead of an NPE. |
| * Basically per the spec, getClob is valid only for the duration of |
| * the transaction in it was created in |
| * Updated for DERBY-1511: The test case wasn't supposed to fail in the |
| * first place (neither with NPE nor with "proper user error") since none |
| * of the CLOBs are accessed after the transaction that created them was |
| * completed. |
| * @throws Exception |
| */ |
| public void testNegativeTestDerby265Clob() throws Exception { |
| |
| getConnection().setAutoCommit(false); |
| |
| PreparedStatement ps = prepareStatement( |
| "insert into testClob(b, a) values(?,?)"); |
| for (int i = 0; i < 3; i++) { |
| Reader fis = new LoopingAlphabetReader(300000); |
| ps.setInt(1, i); |
| ps.setCharacterStream(2, fis, 300000); |
| ps.executeUpdate(); |
| fis.close(); |
| } |
| commit(); |
| |
| getConnection().setAutoCommit(true); |
| |
| Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY, |
| ResultSet.CONCUR_READ_ONLY); |
| s.execute("SELECT b, a FROM testClob"); |
| ResultSet rs1 = s.getResultSet(); |
| Statement s2 = createStatement(ResultSet.TYPE_FORWARD_ONLY, |
| ResultSet.CONCUR_READ_ONLY); |
| s2.executeQuery("SELECT b, a FROM testClob"); |
| ResultSet rs2 = s2.getResultSet(); |
| rs2.next(); |
| |
| Clob b2 = rs2.getClob(2); |
| rs1.next(); |
| Clob b1 = rs1.getClob(2); |
| rs1.close(); |
| |
| // DERBY-1511: Fetching the next CLOB here used to fail because it |
| // had been pre-fetched before the commit and was closed in the commit. |
| // Now, we don't pre-fetch CLOBs anymore, so expect to get a working |
| // object here. |
| assertTrue(rs2.next()); |
| assertNotNull(rs2.getClob(2)); |
| rs2.close(); |
| } |
| |
| public static Test suite() { |
| BaseTestSuite suite = new BaseTestSuite("BlobClob4BlobTest"); |
| suite.addTest(baseSuite("embedded")); |
| suite.addTest( |
| TestConfiguration.clientServerDecorator(baseSuite("client"))); |
| |
| // JSR169 does not have encryption support |
| if (JDBC.vmSupportsJDBC3()) |
| { |
| Test encSuite = baseSuite("encrypted"); |
| suite.addTest(Decorator.encryptedDatabase (encSuite)); |
| } |
| |
| return suite; |
| } |
| |
| private static Test baseSuite(String name) { |
| BaseTestSuite suite = new BaseTestSuite( |
| BlobClob4BlobTest.class, "BlobClob4BlobTest:" + name); |
| return new CleanDatabaseTestSetup( |
| DatabasePropertyTestSetup.setLockTimeouts(suite, 2, 4)); |
| } |
| |
| |
| private void insertDefaultData() throws Exception { |
| PreparedStatement ps = prepareStatement( |
| "INSERT INTO testClob (a, b, c) VALUES (?, ?, ?)"); |
| |
| String clobValue = ""; |
| ps.setString(1, clobValue); |
| ps.setInt(2, clobValue.length()); |
| ps.setLong(3, 0); |
| ps.addBatch(); |
| clobValue = "you can lead a horse to water but you can't form it " + |
| "into beverage"; |
| ps.setString(1, clobValue); |
| ps.setInt(2, clobValue.length()); |
| ps.setLong(3, 0); |
| ps.addBatch(); |
| clobValue = "a stitch in time says ouch"; |
| ps.setString(1, clobValue); |
| ps.setInt(2, clobValue.length()); |
| ps.setLong(3, 0); |
| ps.addBatch(); |
| clobValue = "here is a string with a return \n character"; |
| ps.setString(1, clobValue); |
| ps.setInt(2, clobValue.length()); |
| ps.setLong(3, 0); |
| ps.addBatch(); |
| |
| ps.executeBatch(); |
| ps.clearBatch(); |
| |
| insertLoopingAlphabetStreamData( |
| ps, CharAlphabet.modernLatinLowercase(), 0); |
| insertLoopingAlphabetStreamData( |
| ps, CharAlphabet.modernLatinLowercase(), 56); |
| insertLoopingAlphabetStreamData( |
| ps, CharAlphabet.modernLatinLowercase(), 5000); |
| insertLoopingAlphabetStreamData( |
| ps, CharAlphabet.modernLatinLowercase(), 10000); |
| insertLoopingAlphabetStreamData( |
| ps, CharAlphabet.modernLatinLowercase(), 300000); |
| |
| ps.setNull(1, Types.CLOB); |
| ps.setInt(2, 0); |
| ps.setLong(3, 0); |
| ps.executeUpdate(); |
| |
| ps.close(); |
| |
| ps = prepareStatement( |
| "INSERT INTO testBlob (a, b, crc32) VALUES (?, ?, ?)"); |
| |
| byte[] blobValue = "".getBytes("US-ASCII"); |
| ps.setBytes(1, blobValue); |
| ps.setInt(2, blobValue.length); |
| ps.setLong(3, getStreamCheckSum(new ByteArrayInputStream(blobValue))); |
| ps.addBatch(); |
| blobValue = ("you can lead a horse to water but you can't form it " + |
| "into beverage").getBytes("US-ASCII"); |
| ps.setBytes(1, blobValue); |
| ps.setInt(2, blobValue.length); |
| ps.setLong(3, getStreamCheckSum(new ByteArrayInputStream(blobValue))); |
| ps.addBatch(); |
| blobValue = "a stitch in time says ouch".getBytes("US-ASCII"); |
| ps.setBytes(1, blobValue); |
| ps.setInt(2, blobValue.length); |
| ps.setLong(3, getStreamCheckSum(new ByteArrayInputStream(blobValue))); |
| ps.addBatch(); |
| blobValue = "here is a string with a return \n character". |
| getBytes("US-ASCII"); |
| ps.setBytes(1, blobValue); |
| ps.setInt(2, blobValue.length); |
| ps.setLong(3, getStreamCheckSum(new ByteArrayInputStream(blobValue))); |
| ps.addBatch(); |
| |
| ps.executeBatch(); |
| ps.clearBatch(); |
| |
| insertLoopingAlphabetStreamData( |
| ps, 0); |
| insertLoopingAlphabetStreamData( |
| ps, 56); |
| insertLoopingAlphabetStreamData( |
| ps, 5000); |
| insertLoopingAlphabetStreamData( |
| ps, 10000); |
| insertLoopingAlphabetStreamData( |
| ps, 300000); |
| |
| ps.setNull(1, Types.BLOB); |
| ps.setInt(2, 0); |
| ps.setNull(3, Types.BIGINT); |
| ps.executeUpdate(); |
| |
| ps.close(); |
| |
| commit(); |
| } |
| |
| |
| private void insertUnicodeData(String[] unicodeString) throws Exception { |
| PreparedStatement ps = prepareStatement( |
| "INSERT INTO testClob (a, b, c) VALUES (?, ?, ?)"); |
| |
| for (int i=0; i<unicodeString.length; i++) { |
| ps.setString(1, unicodeString[i]); |
| ps.setInt(2, unicodeString[i].length()); |
| ps.setInt(3, i); |
| ps.addBatch(); |
| } |
| ps.executeBatch(); |
| ps.clearBatch(); |
| |
| insertLoopingAlphabetStreamData( |
| ps, CharAlphabet.tamil(), 0); |
| insertLoopingAlphabetStreamData( |
| ps, CharAlphabet.tamil(), 56); |
| insertLoopingAlphabetStreamData( |
| ps, CharAlphabet.tamil(), 5000); |
| insertLoopingAlphabetStreamData( |
| ps, CharAlphabet.tamil(), 10000); |
| insertLoopingAlphabetStreamData( |
| ps, CharAlphabet.tamil(), 300000); |
| |
| ps.setNull(1, Types.CLOB); |
| ps.setInt(2, 0); |
| ps.setInt(3, -1); |
| ps.executeUpdate(); |
| |
| ps.close(); |
| commit(); |
| } |
| |
| private void insertLoopingAlphabetStreamData( |
| PreparedStatement ps, |
| int lobLength) |
| throws Exception |
| { |
| ps.setBinaryStream(1, new LoopingAlphabetStream(lobLength), lobLength); |
| ps.setInt(2, lobLength); |
| ps.setLong(3, getStreamCheckSum(new LoopingAlphabetStream(lobLength))); |
| ps.executeUpdate(); |
| } |
| |
| private void insertLoopingAlphabetStreamData( |
| PreparedStatement ps, |
| CharAlphabet alphabet, |
| int lobLength) |
| throws Exception |
| { |
| ps.setCharacterStream(1, new LoopingAlphabetReader(lobLength, alphabet), |
| lobLength); |
| ps.setInt(2, lobLength); |
| ps.setLong(3, -1); |
| ps.executeUpdate(); |
| } |
| |
| private boolean compareReaders(Reader origValue, Reader newValue) |
| throws Exception { |
| char[] origBuff = new char[1024]; |
| char[] newBuff = new char[1024]; |
| int countOrig = -1; |
| int countNew = -1; |
| do { |
| countOrig = origValue.read(origBuff); |
| countNew = newValue.read(newBuff); |
| if (countOrig != countNew) { |
| return false; |
| } |
| if (!java.util.Arrays.equals(origBuff, newBuff)) { |
| return false; |
| } |
| } while (countOrig != -1); |
| return true; |
| } |
| |
| /** |
| * Get the CRC32 checksum of a stream, reading |
| * its contents entirely and closing it. |
| */ |
| private long getStreamCheckSum(InputStream in) |
| throws Exception { |
| CRC32 sum = new CRC32(); |
| |
| byte[] buf = new byte[32*1024]; |
| |
| for (;;) { |
| int read = in.read(buf); |
| if (read == -1) |
| break; |
| sum.update(buf, 0, read); |
| } |
| in.close(); |
| return sum.getValue(); |
| } |
| |
| /** |
| * Verifies the value returned by the method Clob.getSubstring() |
| */ |
| private void verifyInterval(Clob clob, long pos, int length, |
| int testNum, int clobLength) throws Exception { |
| try { |
| String subStr = clob.getSubString(pos, length); |
| assertEquals("FAIL - getSubString returned wrong length", |
| Math.min((clob.length() - pos) + 1, length), |
| subStr.length()); |
| assertEquals("FAIL - clob has mismatched lengths", |
| clobLength, clob.length()); |
| assertFalse("FAIL - NO ERROR ON getSubString POS TOO LARGE", |
| (pos > clobLength + 1)); |
| |
| // Get expected value usign Clob.getAsciiStream() |
| char[] value = new char[length]; |
| String valueString; |
| Reader reader = clob.getCharacterStream(); |
| long left = pos - 1; |
| long skipped = 0; |
| if (clobLength > 0) { |
| println("clobLength: " + clobLength); |
| while (left > 0 && skipped >= 0) { |
| skipped = reader.skip(Math.min(1024, left)); |
| left -= skipped > 0 ? skipped : 0; |
| } |
| } |
| int numBytes = reader.read(value); |
| |
| // chech the the two values match |
| if (numBytes >= 0) { |
| char[] readBytes = new char[numBytes]; |
| System.arraycopy(value, 0, readBytes, 0, numBytes); |
| valueString = new String(readBytes); |
| assertEquals("FAIL - wrong substring value", |
| valueString, subStr); |
| } else { |
| assertTrue("FAIL - wrong length", subStr.length() == 0); |
| } |
| } catch (SQLException e) { |
| if (pos <= 0) { |
| checkException(BLOB_BAD_POSITION, e); |
| } else { |
| if (pos > clobLength + 1) { |
| checkException(BLOB_POSITION_TOO_LARGE, e); |
| } else { |
| throw e; |
| } |
| } |
| } |
| } |
| |
| /** |
| * Verifies the value returned by the method Blob.getBytes() |
| */ |
| private void verifyInterval(Blob blob, long pos, int length, |
| int testNum, int blobLength) throws Exception { |
| try { |
| String subStr = new String(blob.getBytes(pos,length), "US-ASCII"); |
| assertEquals("FAIL - getSubString returned wrong length ", |
| Math.min((blob.length() - pos) + 1, length), |
| subStr.length()); |
| assertEquals("FAIL - clob has mismatched lengths", |
| blobLength, blob.length()); |
| assertFalse("FAIL - NO ERROR ON getSubString POS TOO LARGE", |
| (pos > blobLength + 1)); |
| |
| // Get expected value usign Blob.getBinaryStream() |
| byte[] value = new byte[length]; |
| String valueString; |
| InputStream inStream = blob.getBinaryStream(); |
| inStream.skip(pos - 1); |
| int numBytes = inStream.read(value); |
| // check that the two values match |
| if (numBytes >= 0) { |
| byte[] readBytes = new byte[numBytes]; |
| System.arraycopy(value, 0, readBytes, 0, numBytes); |
| valueString = new String(readBytes, "US-ASCII"); |
| assertEquals("FAIL - wrong substring value", |
| valueString, subStr); |
| } else { |
| assertTrue("FAIL - wrong length", subStr.length() == 0); |
| } |
| } catch (SQLException e) { |
| if (pos <= 0) { |
| checkException(BLOB_BAD_POSITION, e); |
| } else { |
| if (pos > blobLength + 1) { |
| checkException(BLOB_POSITION_TOO_LARGE, e); |
| } else { |
| throw e; |
| } |
| } |
| } |
| } |
| |
| |
| /** |
| * Test the contents of the testBlob table or ResultSet |
| * with identical shape. |
| * @param rs |
| * @throws Exception |
| */ |
| public void testBlobContents(ResultSet rs) throws Exception { |
| int nullCount = 0; |
| int rowCount = 0; |
| byte[] buff = new byte[128]; |
| // fetch row back, get the long varbinary column as a blob. |
| Blob blob; |
| int blobLength = 0, i = 0; |
| while (rs.next()) { |
| i++; |
| // get the first column as a clob |
| blob = rs.getBlob(1); |
| long crc32 = rs.getLong(3); |
| boolean crc2Null = rs.wasNull(); |
| if (blob == null) { |
| assertTrue("FAIL - NULL BLOB but non-NULL checksum", crc2Null); |
| nullCount++; |
| } else { |
| rowCount++; |
| |
| long blobcrc32 = getStreamCheckSum(blob.getBinaryStream()); |
| assertEquals("FAIL - mismatched checksums for blob with " + |
| "length " + blob.length(), blobcrc32, crc32); |
| |
| InputStream fin = blob.getBinaryStream(); |
| int columnSize = 0; |
| for (;;) { |
| int size = fin.read(buff); |
| if (size == -1) |
| break; |
| columnSize += size; |
| } |
| blobLength = rs.getInt(2); |
| assertEquals("FAIL - wrong column size", |
| blobLength, columnSize); |
| assertEquals("FAIL - wrong column length", |
| blobLength, blob.length()); |
| } |
| } |
| assertEquals("FAIL - wrong not null row count null:" + nullCount, |
| 9, rowCount); |
| assertEquals("FAIL - wrong null blob count", 1, nullCount); |
| } |
| |
| /** |
| * From LobTest.java, test various inserts on a BLOB column |
| * |
| * @throws SQLException |
| */ |
| public void testBlobInsert() throws SQLException { |
| String[] typeNames = { "int", "char(10)", "varchar(80)", "long varchar", |
| "char(10) for bit data", "long varchar for bit data", "blob(80)" }; |
| |
| Connection conn = getConnection(); |
| Statement s = conn.createStatement(); |
| |
| // create table for testing |
| |
| s.execute("create table blobCheck (bl blob(80)) "); |
| |
| int columns = typeNames.length; |
| // test insertion of literals. |
| for (int i = 0; i < columns; i++) { |
| |
| if (typeNames[i].indexOf("blob") == -1) |
| continue; |
| |
| // Check char literals. |
| // (fail) |
| String insert = "insert into blobCheck (bl" |
| + " ) values ('string' )"; |
| assertStatementError("42821",s,insert); |
| // (succeed) |
| insert = "insert into blobCheck (bl" + " ) values (cast (" |
| + Utilities.stringToHexLiteral("string") + " as blob(80)) )"; |
| s.execute(insert); |
| // Check bit literals. |
| // (fail) |
| insert = "insert into blobCheck (bl" + " ) values (X'48' )"; |
| assertStatementError("42821",s,insert); |
| // old CS compatible value: ( b'01001' ) |
| // (succeed) |
| insert = "insert into blobCheck (bl" |
| + " ) values (cast (X'C8' as blob(80)) )"; |
| s.execute(insert); |
| // Check hex literals. |
| // (fail) |
| insert = "insert into blobCheck (bl" + " ) values ( X'a78a' )"; |
| assertStatementError("42821",s,insert); |
| // (succeed) |
| insert = "insert into blobCheck (bl" |
| + " ) values (cast (X'a78a' as blob(80)) )"; |
| s.execute(insert); |
| } |
| s.execute("drop table blobCheck"); |
| } |
| |
| |
| private void checkException(String SQLState, SQLException se) |
| throws Exception |
| { |
| assertSQLState(SQLState, se); |
| } |
| |
| |
| /** |
| * DERBY-3243 Fix ArrayIndexOutOfBounds Exception |
| * if we retrieve more than 32K lobs |
| * |
| */ |
| public void testRetrieveMoreThan32KLobs() throws SQLException |
| { |
| int numRows = 34000; |
| // Load the database |
| Connection conn = getConnection(); |
| conn.setAutoCommit(false); |
| Statement s = createStatement(); |
| |
| PreparedStatement ps = prepareStatement("INSERT INTO TESTCLOB VALUES(?,?,?)"); |
| for (int i = 0 ; i < numRows;i++) |
| { |
| ps.setInt(1,i); |
| ps.setInt(2,i); |
| ps.setString(3,"" + i); |
| ps.executeUpdate(); |
| if (i % 1000 == 0) { |
| commit(); |
| } |
| } |
| commit(); |
| |
| // retrieve the data |
| |
| ResultSet rs = s.executeQuery("SELECT * from TESTCLOB"); |
| while (rs.next()) { |
| rs.getInt(1); |
| Clob c = rs.getClob(3); |
| c.getSubString(1,100); |
| } |
| rs.close(); |
| |
| conn.commit(); |
| |
| |
| } |
| |
| /** |
| * Test that Blob.truncate() resets the position before copying the first |
| * N bytes into a new holder object. The bug is only triggered if the |
| * BLOB returned as a stream from store. That is, it must be larger than |
| * one page so that it's not materialized. Regression test for DERBY-5113. |
| */ |
| public void testDerby5113() throws Exception { |
| setAutoCommit(false); |
| |
| // Insert a BLOB larger than one page. Normally, this means larger |
| // than 32K, but the test tables use smaller pages, see comment in |
| // setUp(). |
| PreparedStatement insert = prepareStatement( |
| "insert into testblob(a) values ?"); |
| insert.setBinaryStream(1, new LoopingAlphabetStream(5000), 5000); |
| insert.executeUpdate(); |
| |
| // Retrieve the BLOB. |
| Statement s = createStatement(); |
| ResultSet rs = s.executeQuery("select a from testblob"); |
| rs.next(); |
| Blob blob = rs.getBlob(1); |
| |
| // Now call getBytes() so that the position in the underlying stream |
| // is changed. |
| byte[] bytes = blob.getBytes(1, 3000); |
| assertEquals(new LoopingAlphabetStream(3000), |
| new ByteArrayInputStream(bytes)); |
| |
| // Truncate the BLOB. This used to fail with "Reached EOF prematurely" |
| // because truncate() didn't move the position in the underlying stream |
| // back to the beginning. |
| blob.truncate(4000); |
| |
| // Verify that the BLOB was truncated correctly. |
| assertEquals(4000, blob.length()); |
| bytes = blob.getBytes(1, 4000); |
| assertEquals(new LoopingAlphabetStream(4000), |
| new ByteArrayInputStream(bytes)); |
| |
| rs.close(); |
| } |
| |
| private void checkSmallPageSize(Statement st, String tblName) |
| throws SQLException |
| { |
| ResultSet rs = st.executeQuery( |
| "select * from TABLE(SYSCS_DIAG.SPACE_TABLE('"+tblName+"')) T"); |
| |
| int found_rows = 0; |
| while (rs.next()) |
| { |
| found_rows++; |
| assertEquals(4096, rs.getInt("pagesize")); |
| } |
| assertTrue(found_rows == 1); |
| rs.close(); |
| } |
| |
| |
| private static final String BLOB_BAD_POSITION = "XJ070"; |
| private static final String BLOB_NONPOSITIVE_LENGTH = "XJ071"; |
| private static final String BLOB_POSITION_TOO_LARGE = "XJ076"; |
| private static final String LANG_DATA_TYPE_GET_MISMATCH = "22005"; |
| private static final String BLOB_NULL_PATTERN_OR_SEARCH_STR = "XJ072"; |
| private static final String LOCK_TIMEOUT = "40XL1"; |
| private static final String NO_CURRENT_CONNECTION = "08003"; |
| private static final String INVALID_LOB = "XJ215"; |
| |
| } |