/**
 *
 * 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);
    }
}