| /** |
| * |
| * Derby - Class BLOBTest |
| * |
| * 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 org.apache.derbyTesting.functionTests.util.TestInputStream; |
| import org.apache.derbyTesting.functionTests.util.streams.LoopingAlphabetStream; |
| import org.apache.derbyTesting.functionTests.util.streams.ReadOnceByteArrayInputStream; |
| import org.apache.derbyTesting.functionTests.util.streams.StringReaderWithLength; |
| import org.apache.derbyTesting.junit.JDBC; |
| import org.apache.derbyTesting.junit.BaseJDBCTestCase; |
| |
| import junit.framework.Test; |
| import org.apache.derbyTesting.junit.BaseTestSuite; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.Statement; |
| import java.sql.Blob; |
| import java.sql.Connection; |
| import java.sql.SQLException; |
| import java.sql.Types; |
| import java.io.EOFException; |
| import java.io.IOException; |
| import java.io.InputStream; |
| import java.util.Random; |
| |
| /** |
| * Tests reading and updating binary large objects (BLOBs). |
| */ |
| final public class BLOBTest extends BaseJDBCTestCase |
| { |
| /** |
| * Constructor |
| * @param name name of test case (method). |
| */ |
| public BLOBTest(String name) |
| { |
| super(name); |
| } |
| |
| |
| /** |
| * Tests updating a Blob from a scollable resultset, using |
| * result set update methods. |
| * @exception SQLException causes test to fail with error |
| * @exception IOException causes test to fail with error |
| */ |
| public void testUpdateBlobFromScrollableResultSetUsingResultSetMethods() |
| throws SQLException, IOException |
| { |
| final Statement stmt = |
| createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, |
| ResultSet.CONCUR_UPDATABLE); |
| final ResultSet rs = |
| stmt.executeQuery("SELECT * from " + |
| BLOBDataModelSetup.getBlobTableName()); |
| println("Last"); |
| rs.last(); |
| |
| final int newVal = rs.getInt(1) + 11; |
| final int newSize = rs.getInt(2) / 2; |
| testUpdateBlobWithResultSetMethods(rs, newVal, newSize); |
| |
| println("Verify updated blob using result set"); |
| verifyBlob(newVal, newSize, rs.getBlob(3)); |
| |
| rs.close(); |
| stmt.close(); |
| } |
| |
| /** |
| * Tests updating a Blob from a forward only resultset, using |
| * result set update methods. |
| * @exception SQLException causes test to fail with error |
| * @exception IOException causes test to fail with error |
| */ |
| public void testUpdateBlobFromForwardOnlyResultSetUsingResultSetMethods() |
| throws SQLException, IOException |
| { |
| final Statement stmt = |
| createStatement(ResultSet.TYPE_FORWARD_ONLY, |
| ResultSet.CONCUR_UPDATABLE); |
| final ResultSet rs = |
| stmt.executeQuery("SELECT * from " + |
| BLOBDataModelSetup.getBlobTableName()); |
| |
| while (rs.next()) { |
| println("Next"); |
| final int val = rs.getInt(1); |
| if (val == BLOBDataModelSetup.bigVal) break; |
| } |
| |
| final int newVal = rs.getInt(1) + 11; |
| final int newSize = rs.getInt(2) / 2; |
| testUpdateBlobWithResultSetMethods(rs, newVal, newSize); |
| |
| rs.close(); |
| stmt.close(); |
| } |
| |
| /** |
| * Tests updating a Blob from a scollable resultset, using |
| * positioned updates. |
| * @exception SQLException causes test to fail with error |
| * @exception IOException causes test to fail with error |
| */ |
| public void testUpdateBlobFromScrollableResultSetUsingPositionedUpdates() |
| throws SQLException, IOException |
| { |
| final Statement stmt = |
| createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, |
| ResultSet.CONCUR_UPDATABLE); |
| final ResultSet rs = |
| stmt.executeQuery("SELECT * from " + |
| BLOBDataModelSetup.getBlobTableName()); |
| println("Last"); |
| rs.last(); |
| |
| final int newVal = rs.getInt(1) + 11; |
| final int newSize = rs.getInt(2) / 2; |
| testUpdateBlobWithPositionedUpdate(rs, newVal, newSize); |
| |
| rs.relative(0); // Necessary after a positioned update |
| |
| println("Verify updated blob using result set"); |
| verifyBlob(newVal, newSize, rs.getBlob(3)); |
| |
| rs.close(); |
| stmt.close(); |
| } |
| |
| /** |
| * Tests updating a Blob from a forward only resultset, using |
| * methods. |
| * @exception SQLException causes test to fail with error |
| * @exception IOException causes test to fail with error |
| */ |
| public void testUpdateBlobFromForwardOnlyResultSetUsingPositionedUpdates() |
| throws SQLException, IOException |
| { |
| final Statement stmt = |
| createStatement(ResultSet.TYPE_FORWARD_ONLY, |
| ResultSet.CONCUR_UPDATABLE); |
| final ResultSet rs = |
| stmt.executeQuery("SELECT * from " + |
| BLOBDataModelSetup.getBlobTableName()); |
| while (rs.next()) { |
| println("Next"); |
| final int val = rs.getInt(1); |
| if (val == BLOBDataModelSetup.bigVal) break; |
| } |
| |
| final int newVal = rs.getInt(1) + 11; |
| final int newSize = rs.getInt(2) / 2; |
| testUpdateBlobWithPositionedUpdate(rs, newVal, newSize); |
| |
| rs.close(); |
| stmt.close(); |
| } |
| |
| /** |
| * Tests updating a Blob from a scollable resultset produced by a |
| * select query with projection. Updates are made using |
| * result set update methods. |
| * @exception SQLException causes test to fail with error |
| * @exception IOException causes test to fail with error |
| */ |
| public void testUpdateBlobFromScrollableResultSetWithProjectUsingResultSetMethods() |
| throws SQLException, IOException |
| { |
| final Statement stmt = |
| createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, |
| ResultSet.CONCUR_UPDATABLE); |
| final ResultSet rs = |
| stmt.executeQuery("SELECT data,val,length from " + |
| BLOBDataModelSetup.getBlobTableName()); |
| println("Last"); |
| rs.last(); |
| |
| final int newVal = rs.getInt(2) + 11; |
| final int newSize = rs.getInt(3) / 2; |
| testUpdateBlobWithResultSetMethods(rs, newVal, newSize); |
| |
| println("Verify updated blob using result set"); |
| verifyBlob(newVal, newSize, rs.getBlob(1)); |
| |
| rs.close(); |
| stmt.close(); |
| } |
| |
| /** |
| * Tests updating a Blob from a forward only resultset, produced by |
| * a select query with projection. Updates are made using |
| * result set update methods. |
| * @exception SQLException causes test to fail with error |
| * @exception IOException causes test to fail with error |
| */ |
| public void testUpdateBlobFromForwardOnlyResultSetWithProjectUsingResultSetMethods() |
| throws SQLException, IOException |
| { |
| final Statement stmt = |
| createStatement(ResultSet.TYPE_FORWARD_ONLY, |
| ResultSet.CONCUR_UPDATABLE); |
| final ResultSet rs = |
| stmt.executeQuery("SELECT data,val,length from " + |
| BLOBDataModelSetup.getBlobTableName()); |
| |
| while (rs.next()) { |
| println("Next"); |
| final int val = rs.getInt("VAL"); |
| if (val == BLOBDataModelSetup.bigVal) break; |
| } |
| |
| final int newVal = rs.getInt("VAL") + 11; |
| final int newSize = BLOBDataModelSetup.bigSize / 2; |
| testUpdateBlobWithResultSetMethods(rs, newVal, newSize); |
| |
| rs.close(); |
| stmt.close(); |
| } |
| |
| /** |
| * Tests updating a Blob from a scollable resultset, produced by |
| * a select query with projection. Updates are made using |
| * positioned updates |
| * @exception SQLException causes test to fail with error |
| * @exception IOException causes test to fail with error |
| */ |
| public void testUpdateBlobFromScrollableResultSetWithProjectUsingPositionedUpdates() |
| throws SQLException, IOException |
| { |
| final Statement stmt = |
| createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, |
| ResultSet.CONCUR_UPDATABLE); |
| final ResultSet rs = |
| stmt.executeQuery("SELECT data from " + |
| BLOBDataModelSetup.getBlobTableName() + |
| " WHERE val= " + BLOBDataModelSetup.bigVal); |
| println("Last"); |
| rs.last(); |
| |
| final int newVal = BLOBDataModelSetup.bigVal * 2; |
| final int newSize = BLOBDataModelSetup.bigSize / 2; |
| testUpdateBlobWithPositionedUpdate(rs, newVal, newSize); |
| |
| rs.relative(0); // Necessary after a positioned update |
| |
| println("Verify updated blob using result set"); |
| verifyBlob(newVal, newSize, rs.getBlob("DATA")); |
| |
| rs.close(); |
| stmt.close(); |
| } |
| |
| /** |
| * Tests updating a Blob from a forward only resultset, produced by |
| * a select query with projection. Updates are made using |
| * positioned updates. |
| * @exception SQLException causes test to fail with error |
| * @exception IOException causes test to fail with error |
| */ |
| public void testUpdateBlobFromForwardOnlyResultSetWithProjectUsingPositionedUpdates() |
| throws SQLException, IOException |
| { |
| final Statement stmt = |
| createStatement(ResultSet.TYPE_FORWARD_ONLY, |
| ResultSet.CONCUR_UPDATABLE); |
| final ResultSet rs = |
| stmt.executeQuery("SELECT data from " + |
| BLOBDataModelSetup.getBlobTableName() + |
| " WHERE val = " + BLOBDataModelSetup.bigVal); |
| rs.next(); |
| |
| final int newVal = BLOBDataModelSetup.bigVal * 2; |
| final int newSize = BLOBDataModelSetup.bigSize / 2; |
| testUpdateBlobWithPositionedUpdate(rs, newVal, newSize); |
| |
| rs.close(); |
| stmt.close(); |
| } |
| |
| |
| /** |
| * Tests updating the Blob using result set update methods. |
| * @param rs result set, currently positioned on row to be updated |
| * @param newVal new value in val column and blob data |
| * @param newSize new size of Blob |
| * @exception SQLException causes test to fail with error |
| * @exception IOException causes test to fail with error |
| */ |
| private void testUpdateBlobWithResultSetMethods(final ResultSet rs, |
| final int newVal, |
| final int newSize) |
| throws SQLException, IOException |
| { |
| int val = rs.getInt("VAL"); |
| int size = rs.getInt("LENGTH"); |
| println("VerifyBlob"); |
| verifyBlob(val, size, rs.getBlob("DATA")); |
| |
| println("UpdateBlob"); |
| final TestInputStream newStream = new TestInputStream(newSize, newVal); |
| |
| rs.updateInt("VAL", newVal); |
| rs.updateInt("LENGTH", newSize); |
| rs.updateBinaryStream("DATA", newStream, newSize); |
| rs.updateRow(); |
| |
| println("Verify updated blob with another query"); |
| verifyNewValueInTable(newVal, newSize); |
| } |
| |
| /** |
| * Tests updating the Blob using positioned updates |
| * @param rs result set, currently positioned on row to be updated |
| * @param newVal new value in val column and blob data |
| * @param newSize new size of Blob |
| * @exception SQLException causes test to fail with error |
| * @exception IOException causes test to fail with error |
| */ |
| private void testUpdateBlobWithPositionedUpdate(final ResultSet rs, |
| final int newVal, |
| final int newSize) |
| throws SQLException, IOException |
| { |
| final PreparedStatement preparedStatement = prepareStatement |
| ("UPDATE " + BLOBDataModelSetup.getBlobTableName() + |
| " SET val=?, length = ?, data = ? WHERE CURRENT OF " + |
| rs.getCursorName()); |
| |
| println("UpdateBlob"); |
| |
| final TestInputStream newStream = new TestInputStream(newSize, newVal); |
| |
| preparedStatement.setInt(1, newVal); |
| preparedStatement.setInt(2, newSize); |
| preparedStatement.setBinaryStream(3, newStream, newSize); |
| preparedStatement.executeUpdate(); |
| |
| println("Verify updated blob with another query"); |
| verifyNewValueInTable(newVal, newSize); |
| } |
| |
| /** |
| * Tests that a stream value in a values clause can be cast to a BLOB. |
| * <p> |
| * See DERBY-4102 (test case resulted in a ClassCastException earlier). |
| * |
| * @throws IOException if something goes wrong |
| * @throws SQLException if something goes wrong |
| */ |
| public void testBlobCastInValuesClause() |
| throws IOException, SQLException { |
| // The length must be at least 32 KB. |
| final int length = 38*1024; |
| PreparedStatement ps = prepareStatement("values cast(? as blob)"); |
| ps.setBinaryStream(1, new LoopingAlphabetStream(length), length); |
| ResultSet rs = ps.executeQuery(); |
| assertTrue(rs.next()); |
| Blob b = rs.getBlob(1); |
| assertEquals(length, b.length()); |
| // Select some parts of the Blob, moving backwards. |
| assertEquals(100, b.getBytes(32*1024-27, 100).length); |
| assertEquals(1029, b.getBytes(19*1024, 1029).length); |
| // Compare a fresh stream with the one from the Blob. |
| assertEquals(new LoopingAlphabetStream(length), b.getBinaryStream()); |
| assertEquals(-1, b.position(new byte[] {(byte)'a', (byte)'A'}, 1)); |
| assertEquals(length, b.length()); |
| assertFalse(rs.next()); |
| rs.close(); |
| } |
| |
| |
| /** |
| * Tests that a lob can be safely occur multiple times in a SQL select. |
| * <p/> |
| * See DERBY-4477. |
| * <p/> |
| * @see org.apache.derbyTesting.functionTests.tests.memory.BlobMemTest#testDerby4477_3645_3646_Repro_lowmem |
| * @see org.apache.derbyTesting.functionTests.tests.memory.ClobMemTest#testDerby4477_3645_3646_Repro_lowmem_clob |
| */ |
| public void testDerby4477_3645_3646_Repro() throws SQLException, IOException { |
| setAutoCommit(false); |
| Statement s = createStatement(); |
| |
| s.executeUpdate( |
| "CREATE TABLE T_MAIN(" + |
| "ID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, " + |
| "V BLOB(590473235) )"); |
| |
| PreparedStatement ps = prepareStatement( |
| "INSERT INTO T_MAIN(V) VALUES (?)"); |
| |
| byte[] bytes = new byte[35000]; |
| |
| for (int i = 0; i < 35000; i++) { |
| bytes[i] = (byte)i; |
| } |
| |
| ps.setBytes(1, bytes); |
| ps.executeUpdate(); |
| ps.close(); |
| |
| s.executeUpdate("CREATE TABLE T_COPY ( V1 BLOB(2M), V2 BLOB(2M))"); |
| |
| // This failed in the repro for DERBY-3645 solved as part of |
| // DERBY-4477: |
| s.executeUpdate("INSERT INTO T_COPY SELECT V, V FROM T_MAIN"); |
| |
| // Check that the two results are identical: |
| ResultSet rs = s.executeQuery("SELECT * FROM T_COPY"); |
| rs.next(); |
| String v1 = rs.getString(1); |
| String v2 = rs.getString(2); |
| assertEquals(v1.length(), v2.length()); |
| |
| for (int i=0; i < v1.length(); i++) { |
| assertEquals(v1.charAt(i), v2.charAt(i)); |
| } |
| |
| // Verify against a single select too (both above could be wrong..) |
| rs = s.executeQuery("SELECT V from T_MAIN"); |
| rs.next(); |
| String v3 = rs.getString(1); |
| assertEquals(v1.length(), v3.length()); |
| |
| for (int i=0; i < v1.length(); i++) { |
| assertEquals(v1.charAt(i), v3.charAt(i)); |
| } |
| |
| // This failed in the repro for DERBY-3646 solved as part of |
| // DERBY-4477 (repro slightly rewoked here): |
| rs = s.executeQuery("SELECT 'I', V, ID, V from T_MAIN"); |
| rs.next(); |
| |
| InputStream s1 = rs.getBinaryStream(2); |
| |
| // JDBC says that the next getBinaryStream will close the s1 stream so |
| // verify it now. Cf. DERBY-4521. |
| |
| for (int i = 0; i < 35000; i++) { |
| assertEquals((byte)i, (byte)s1.read()); |
| } |
| |
| assertEquals(-1, s1.read()); |
| s1.close(); |
| |
| InputStream s2 = rs.getBinaryStream(4); |
| |
| for (int i = 0; i < 35000; i++) { |
| assertEquals((byte)i, (byte)s2.read()); |
| } |
| |
| assertEquals(-1, s2.read()); |
| s2.close(); |
| |
| rs.close(); |
| |
| rollback(); |
| } |
| |
| |
| /** |
| * Tests that a lob can be safely occur multiple times in a SQL select in |
| * a trigger context. |
| * <p/> |
| * See DERBY-4477. |
| */ |
| public void testDerby4477_2349_Repro() throws SQLException, IOException { |
| |
| setAutoCommit(false); |
| |
| Statement s = createStatement(); |
| |
| s.executeUpdate("CREATE TABLE T_MAIN(" + |
| "ID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, " + |
| "V BLOB(590473235) )"); |
| s.executeUpdate("CREATE TABLE T_ACTION_ROW(ID INT, A CHAR(1), " + |
| "V1 BLOB(590473235), V2 BLOB(590473235) )"); |
| s.executeUpdate("CREATE TABLE T_ACTION_STATEMENT(ID INT, A CHAR(1), " + |
| "V1 BLOB(590473235), V2 BLOB(590473235) )"); |
| |
| // ON INSERT copy the typed value V into the action table. |
| // Use V twice to ensure there are no issues with values |
| // that can be streamed. |
| // Two identical actions, per row and per statement. |
| s.executeUpdate( |
| "CREATE TRIGGER AIR AFTER INSERT ON T_MAIN " + |
| " REFERENCING NEW AS N FOR EACH ROW " + |
| " INSERT INTO T_ACTION_ROW(A, V1, ID, V2) " + |
| " VALUES ('I', N.V, N.ID, N.V)"); |
| |
| s.executeUpdate( |
| "CREATE TRIGGER AIS AFTER INSERT ON T_MAIN " + |
| " REFERENCING NEW_TABLE AS N FOR EACH STATEMENT " + |
| " INSERT INTO T_ACTION_STATEMENT(A, V1, ID, V2) " + |
| " SELECT 'I', V, ID, V FROM N"); |
| |
| s.executeUpdate("INSERT INTO T_MAIN(V) VALUES NULL"); |
| |
| s.close(); |
| actionTypesCompareMainToAction(1); |
| |
| int jdbcType = Types.BLOB; |
| int precision = 590473235; |
| |
| Random r = new Random(); |
| |
| String ins1 = "INSERT INTO T_MAIN(V) VALUES (?)"; |
| String ins3 = "INSERT INTO T_MAIN(V) VALUES (?), (?), (?)"; |
| |
| PreparedStatement ps; |
| ps = prepareStatement(ins1); |
| setRandomValue(r, ps, 1, jdbcType, precision); |
| ps.executeUpdate(); |
| ps.close(); |
| |
| actionTypesCompareMainToAction(2); |
| |
| ps = prepareStatement(ins3); |
| setRandomValue(r, ps, 1, jdbcType, precision); |
| setRandomValue(r, ps, 2, jdbcType, precision); |
| setRandomValue(r, ps, 3, jdbcType, precision); |
| ps.executeUpdate(); |
| ps.close(); |
| |
| actionTypesCompareMainToAction(5); |
| |
| rollback(); |
| } |
| |
| /** |
| * Checks that Derby fails with an exception when a transaction using |
| * READ_UNCOMMITTED obtains a stream from a BLOB (reads one byte) and at |
| * the same time another connection deletes the BLOB. |
| * <p> |
| * Earlier only parts of the BLOB was returned, without errors. It was |
| * impossible to tell for the user that only parts of the value was |
| * retrieved. |
| * <p> |
| * See DERBY-2992. |
| */ |
| public void testDerby2992_Repro() |
| throws IOException, SQLException { |
| // Autocommit doesn't seem to be enabled here in all cases. |
| setAutoCommit(true); |
| |
| final String TBL = "D2992BLOB"; |
| // Switch to READ UNCOMMITTED. |
| getConnection().setTransactionIsolation( |
| Connection.TRANSACTION_READ_UNCOMMITTED); |
| Statement stmt = createStatement(); |
| dropTable(TBL); |
| stmt.executeUpdate("create table " + TBL + " (b blob)"); |
| stmt.close(); |
| |
| PreparedStatement ps = prepareStatement("insert into " + TBL + |
| " values (?)"); |
| int length = 65*1024*1024; // 65K |
| ps.setBinaryStream(1, new LoopingAlphabetStream(length), length); |
| ps.executeUpdate(); |
| ps.close(); |
| |
| stmt = createStatement(); |
| ResultSet rs = stmt.executeQuery("select B from " + TBL); |
| assertTrue(rs.next()); |
| |
| // Read one byte, keep the stream / rs open. |
| InputStream is = rs.getBinaryStream(1); |
| int i = is.read(); |
| assertTrue(i != -1); |
| |
| // Open a second connection and delete the BLOB. |
| Connection secondCon = openUserConnection("APP"); |
| Statement secondStmt = secondCon.createStatement(); |
| assertEquals(1, secondStmt.executeUpdate("delete from " + TBL)); |
| secondCon.close(); |
| |
| // Continue reading the BLOB through the stream. |
| // The stream has now probably read one page of data, and as we progress |
| // it will have to fetch the next page. However, the next page has been |
| // deleted. |
| byte[] buf = new byte[4096]; |
| try { |
| // Drain the stream. |
| while (is.read(buf) != -1) { } |
| // Expect the read call above to fail at some point. |
| fail("The read should have failed, value has been deleted"); |
| } catch (EOFException eofe) { |
| // As we expected, everything's fine. |
| } |
| } |
| |
| public static void setRandomValue( |
| Random r, |
| PreparedStatement ps, |
| int column, |
| int jdbcType, |
| int precision) throws SQLException, IOException { |
| |
| Object val = getRandomValue(r, jdbcType, precision); |
| |
| if (val instanceof StringReaderWithLength) { |
| StringReaderWithLength rd = (StringReaderWithLength) val; |
| ps.setCharacterStream(column, rd, rd.getLength()); |
| } else if (val instanceof InputStream) { |
| InputStream in = (InputStream) val; |
| ps.setBinaryStream(column, in, in.available()); |
| } else { |
| ps.setObject(column, val, jdbcType); |
| } |
| } |
| |
| public static Object getRandomValue( |
| Random r, |
| int jdbcType, |
| int precision) throws IOException { |
| |
| switch (jdbcType) { |
| case Types.BLOB: |
| if (precision > 256*1024) |
| precision = 256*1024; |
| return new ReadOnceByteArrayInputStream( |
| randomBinary(r, r.nextInt(precision))); |
| } |
| |
| fail("unexpected JDBC Type " + jdbcType); |
| return null; |
| } |
| |
| private static byte[] randomBinary(Random r, int len) { |
| byte[] bb = new byte[len]; |
| for (int i = 0; i < bb.length; i++) |
| bb[i] = (byte) r.nextInt(); |
| return bb; |
| } |
| |
| private void actionTypesCompareMainToAction( |
| int actionCount) throws SQLException, IOException { |
| |
| Statement s1 = createStatement(); |
| Statement s2 = createStatement(); |
| |
| String sqlMain = |
| "SELECT ID, V, V FROM T_MAIN ORDER BY 1"; |
| String sqlActionRow = |
| "SELECT ID, V1, V2 FROM T_ACTION_ROW ORDER BY 1"; |
| String sqlActionStatement = |
| "SELECT ID, V1, V2 FROM T_ACTION_STATEMENT ORDER BY 1"; |
| |
| ResultSet rsMain = s1.executeQuery(sqlMain); |
| ResultSet rsAction = s2.executeQuery(sqlActionRow); |
| JDBC.assertSameContents(rsMain, rsAction); |
| |
| rsMain = s1.executeQuery(sqlMain); |
| rsAction = s2.executeQuery(sqlActionStatement); |
| JDBC.assertSameContents(rsMain, rsAction); |
| |
| |
| assertTableRowCount("T_ACTION_ROW", actionCount); |
| assertTableRowCount("T_ACTION_STATEMENT", actionCount); |
| |
| s1.close(); |
| s2.close(); |
| } |
| |
| /** |
| * Regression test case for DERBY-1511. Scans of tables with large objects |
| * used to fail if commits were issued during the scans. |
| */ |
| public void testDerby1511() throws Exception { |
| setAutoCommit(false); |
| |
| Statement s = createStatement(); |
| s.executeUpdate("create table derby1511(b blob)"); |
| |
| PreparedStatement insert = prepareStatement( |
| "insert into derby1511(b) values (?)"); |
| |
| int rows = 20; |
| int length = 40000; // LOB size should be > 32 KB to expose the bug |
| // (otherwise, materialization happens and a |
| // different code path is taken) |
| |
| for (int i = 0; i < rows; i++) { |
| insert.setBinaryStream( |
| 1, new LoopingAlphabetStream(length), length); |
| insert.executeUpdate(); |
| } |
| |
| commit(); |
| |
| ResultSet rs = s.executeQuery("select b from derby1511"); |
| for (int i = 0; i < rows; i++) { |
| assertTrue("Too few rows", rs.next()); |
| // Second time this was called we used to get an error saying |
| // container has been closed. |
| assertEquals(new LoopingAlphabetStream(length), |
| rs.getBinaryStream(1)); |
| commit(); |
| } |
| |
| assertFalse("Too many rows", rs.next()); |
| rs.close(); |
| } |
| |
| /** |
| * Verifies that the table has row with column val=newVal |
| * and that it its data and size columns are consistent. |
| * @param newVal value expected to be found in the val column of a row |
| * @param newSize expected size of size column and size of blob |
| * @exception SQLException causes test to fail with error |
| * @exception IOException causes test to fail with error |
| */ |
| private void verifyNewValueInTable(final int newVal, |
| final int newSize) |
| throws IOException, SQLException |
| { |
| println("Verify new value in table: " + newVal); |
| |
| final Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, |
| ResultSet.CONCUR_READ_ONLY); |
| |
| final ResultSet rs = |
| stmt.executeQuery("SELECT * FROM " + |
| BLOBDataModelSetup.getBlobTableName() + |
| " WHERE val = " + newVal); |
| |
| println("Query executed, calling next"); |
| |
| boolean foundVal = false; |
| |
| while (rs.next()) { |
| println("Next called, verifying row"); |
| |
| assertEquals("Unexpected value in val column", |
| newVal, rs.getInt(1)); |
| |
| verifyBlob(newVal, newSize, rs.getBlob(3)); |
| foundVal = true; |
| } |
| assertTrue("No column with value= " + newVal + " found ", foundVal); |
| |
| rs.close(); |
| stmt.close(); |
| } |
| |
| |
| /** |
| * Verifies that the blob is consistent |
| * @param expectedVal the InputStream for the Blob should return this value |
| * for every byte |
| * @param expectedSize the BLOB should have this size |
| * @param blob the BLOB to check |
| * @exception SQLException causes test to fail with error |
| * @exception IOException causes test to fail with error |
| */ |
| private void verifyBlob(final int expectedVal, |
| final int expectedSize, |
| final Blob blob) |
| throws IOException, SQLException |
| { |
| final InputStream stream = blob.getBinaryStream(); |
| int blobSize = 0; |
| for (int val = stream.read(); val!=-1; val = stream.read()) { |
| blobSize++; |
| |
| // avoid doing a string-concat for every byte in blob |
| if (expectedVal!=val) { |
| assertEquals("Unexpected value in stream at position " + |
| blobSize, |
| expectedVal, val); |
| } |
| } |
| stream.close(); |
| assertEquals("Unexpected size of stream ", expectedSize, blobSize); |
| } |
| |
| /** |
| * The suite decorates the tests of this class with |
| * a setup which creates and populates the data model. |
| */ |
| public static Test suite() |
| { |
| BaseTestSuite mainSuite = |
| new BaseTestSuite(BLOBTest.class, "BLOBTest"); |
| return new BLOBDataModelSetup(mainSuite); |
| } |
| |
| /** |
| * The setup creates a Connection to the database. |
| * @exception Exception any exception will cause test to fail with error. |
| */ |
| public final void setUp() |
| throws Exception |
| { |
| getConnection().setAutoCommit(false); |
| } |
| } |
