/**
   * Tests the implementation of getCharacterStream(long pos, long length).
   *
   * @throws Exception
   */
  public void testGetCharacterStreamLong() throws Exception {
    String str1 = "This is a test String. This is a test String";

    Reader r1 = new java.io.StringReader(str1);

    PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)");
    int id = BlobClobTestSetup.getID();
    ps.setInt(1, id);
    ps.setCharacterStream(2, r1);
    ps.execute();
    ps.close();

    Statement st = createStatement();

    ResultSet rs = st.executeQuery("select CLOBDATA from " + "BLOBCLOB where ID=" + id);
    rs.next();
    Clob clob = rs.getClob(1);

    Reader r_1 = clob.getCharacterStream(2L, 5L);
    String str2 = str1.substring(1, 6);
    Reader r_2 = new java.io.StringReader(str2);

    assertEquals(r_2, r_1);

    rs.close();
    st.close();
  }
  /**
   * Obtains streams from the Clob and makes sure we can always read the last char in the Clob.
   *
   * <p>See DERBY-4060.
   *
   * @param id id of the Clob to use
   * @param length the length of the Clob
   * @param alphabet the alphabet used to create the content
   * @throws IOException if reading from a stream fails
   * @throws SQLException if something goes wrong
   */
  private void getCharacterStreamLongLastChar(int id, int length, CharAlphabet alphabet)
      throws IOException, SQLException {
    // Get last char from the source stream.
    Reader cmpReader = new LoopingAlphabetReader(length, alphabet);
    cmpReader.skip(length - 1);
    char srcLastChar = (char) cmpReader.read();
    assertTrue(cmpReader.read() == -1);

    PreparedStatement ps = prepareStatement("select CLOBDATA from BLOBCLOB where ID=?");
    ps.setInt(1, id);
    // Read everything first.
    int charsToRead = length;
    ResultSet rs = ps.executeQuery();
    rs.next();
    Reader reader = rs.getClob(1).getCharacterStream(length - charsToRead + 1, charsToRead);
    // Drain the stream, and make sure we are able to read the last char.
    char lastCharRead = getLastCharInStream(reader, charsToRead);
    assertEquals(srcLastChar, lastCharRead);
    reader.close();
    rs.close();

    // Read a portion of the stream.
    charsToRead = length / 4;
    rs = ps.executeQuery();
    rs.next();
    reader = rs.getClob(1).getCharacterStream(length - charsToRead + 1, charsToRead);
    lastCharRead = getLastCharInStream(reader, charsToRead);
    assertEquals(srcLastChar, lastCharRead);
    reader.close();
    rs.close();

    // Read a very small portion of the stream.
    charsToRead = 1;
    rs = ps.executeQuery();
    rs.next();
    reader = rs.getClob(1).getCharacterStream(length - charsToRead + 1, charsToRead);
    lastCharRead = getLastCharInStream(reader, charsToRead);
    assertEquals(srcLastChar, lastCharRead);
    reader.close();
    rs.close();
  }
  /**
   * Test that <code>Clob.getCharacterStream(long,long)</code> works on CLOBs that are streamed from
   * store. (DERBY-2891)
   */
  public void testGetCharacterStreamLongOnLargeClob() throws Exception {
    getConnection().setAutoCommit(false);

    // create large (>32k) clob that can be read from store
    final int size = 33000;
    StringBuilder sb = new StringBuilder(size);
    for (int i = 0; i < size; i += 10) {
      sb.append("1234567890");
    }

    final int id = BlobClobTestSetup.getID();
    PreparedStatement ps =
        prepareStatement("insert into blobclob(id, clobdata) values (?,cast(? as clob))");
    ps.setInt(1, id);
    ps.setString(2, sb.toString());
    ps.executeUpdate();
    ps.close();

    Statement s = createStatement();
    ResultSet rs = s.executeQuery("select clobdata from blobclob where id = " + id);
    assertTrue(rs.next());
    Clob c = rs.getClob(1);

    // request a small region of the clob
    BufferedReader r = new BufferedReader(c.getCharacterStream(4L, 3L));
    assertEquals("456", r.readLine());

    r.close();
    c.free();
    rs.close();
    s.close();
    rollback();
  }
  //
  // Examine BLOBs and CLOBs.
  //
  private void vetLargeObjects(
      Connection conn, HashSet<String> unsupportedList, HashSet<String> notUnderstoodList)
      throws Exception {
    Statement stmt = conn.createStatement();

    stmt.execute("CREATE TABLE t (id INT PRIMARY KEY, " + "b BLOB(10), c CLOB(10))");
    stmt.execute(
        "INSERT INTO t (id, b, c) VALUES (1, "
            + "CAST ("
            + TestUtil.stringToHexLiteral("101010001101")
            + "AS BLOB(10)), CAST ('hello' AS CLOB(10)))");

    ResultSet rs = stmt.executeQuery("SELECT id, b, c FROM t");

    rs.next();

    Blob blob = rs.getBlob(2);
    Clob clob = rs.getClob(3);

    vetObject(blob, unsupportedList, notUnderstoodList);
    vetObject(clob, unsupportedList, notUnderstoodList);

    stmt.close();
    conn.rollback();
  }
  /**
   * Inserts a Clob with the specified length, using a stream source, then fetches it from the
   * database and checks the length.
   *
   * @param length number of characters in the Clob
   * @throws IOException if reading from the source fails
   * @throws SQLException if something goes wrong
   */
  private void insertAndFetchTest(long length) throws IOException, SQLException {
    PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)");
    int id = BlobClobTestSetup.getID();
    ps.setInt(1, id);
    ps.setCharacterStream(2, new LoopingAlphabetReader(length), length);
    long tsStart = System.currentTimeMillis();
    ps.execute();
    println(
        "Inserted "
            + length
            + " chars (length specified) in "
            + (System.currentTimeMillis() - tsStart)
            + " ms");
    Statement stmt = createStatement();
    tsStart = System.currentTimeMillis();
    ResultSet rs = stmt.executeQuery("select CLOBDATA from BLOBCLOB where id = " + id);
    assertTrue("Clob not inserted", rs.next());
    Clob aClob = rs.getClob(1);
    assertEquals("Invalid length", length, aClob.length());
    println("Fetched length (" + length + ") in " + (System.currentTimeMillis() - tsStart) + " ms");
    rs.close();

    // Insert same Clob again, using the lengthless override.
    id = BlobClobTestSetup.getID();
    ps.setInt(1, id);
    ps.setCharacterStream(2, new LoopingAlphabetReader(length));
    tsStart = System.currentTimeMillis();
    ps.executeUpdate();
    println(
        "Inserted "
            + length
            + " chars (length unspecified) in "
            + (System.currentTimeMillis() - tsStart)
            + " ms");
    rs = stmt.executeQuery("select CLOBDATA from BLOBCLOB where id = " + id);
    assertTrue("Clob not inserted", rs.next());
    aClob = rs.getClob(1);
    assertEquals("Invalid length", length, aClob.length());
    println("Fetched length (" + length + ") in " + (System.currentTimeMillis() - tsStart) + " ms");
    rs.close();

    rollback();
  }
Exemple #6
0
  /**
   * Creates a list of items from ResultSet.
   *
   * @param rs ResultSet object created from query
   * @return list of created items
   */
  public static List<Item> createItems(ResultSet rs) throws SQLException {

    List<Item> item = new LinkedList<Item>();
    while (rs.next()) {
      Item newItem =
          new Item(
              rs.getInt("ITEM_ID"),
              rs.getString("NAME"),
              rs.getInt("PRICE"),
              rs.getInt("QUANT"),
              rs.getClob("DESCR"));
      item.add(newItem);
    }
    return item;
  }
  /**
   * Insert a row with a large clob into the test table. Read the row from the database and assign
   * the clob value to <code>clob</code>.
   *
   * @return The id of the row that was inserted
   * @throws java.sql.SQLException
   */
  private int initializeLongClob() throws SQLException {
    // Clob needs to be larger than one page for locking to occur
    final int lobLength = 40000;

    // Insert a long Clob
    PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)");
    int id = BlobClobTestSetup.getID();
    ps.setInt(1, id);
    ps.setCharacterStream(2, new LoopingAlphabetReader(lobLength), lobLength);
    ps.execute();
    ps.close();
    commit();

    // Fetch the Clob object from the database
    Statement st = createStatement();
    ResultSet rs = st.executeQuery("select CLOBDATA from BLOBCLOB where ID=" + id);
    rs.next();
    clob = rs.getClob(1);
    rs.close();
    st.close();

    return id;
  }
  /**
   * Tests the exceptions thrown by the getCharacterStream (long pos, long length) for the following
   * conditions a) pos <= 0 b) pos > (length of LOB) c) length < 0 d) pos + length > (length of
   * LOB).
   *
   * @throws SQLException
   */
  public void testGetCharacterStreamLongExceptionConditions() throws SQLException {
    String str1 = "This is a test String. This is a test String";

    Reader r1 = new java.io.StringReader(str1);

    PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)");
    int id = BlobClobTestSetup.getID();
    ps.setInt(1, id);
    ps.setCharacterStream(2, r1);
    ps.execute();
    ps.close();

    Statement st = createStatement();

    ResultSet rs = st.executeQuery("select CLOBDATA from " + "BLOBCLOB where ID=" + id);
    rs.next();
    Clob clob = rs.getClob(1);
    // check the case where pos <= 0
    try {
      // set pos as negative
      clob.getCharacterStream(-2L, 5L);
      // Should not come here. The exception has to be thrown.
      fail("FAIL: Expected SQLException for pos being negative " + "not thrown");
    } catch (SQLException sqle) {
      // The SQLState for the exception thrown when pos <= 0 is XJ070
      assertSQLState("XJ070", sqle);
    }

    // check for the case pos > length of clob
    try {
      // set the pos to any value greater than the Clob length
      clob.getCharacterStream(clob.length() + 1, 5L);
      // Should not come here. The exception has to be thrown.
      fail(
          "FAIL: Expected SQLException for position being greater than "
              + "length of LOB not thrown");
    } catch (SQLException sqle) {
      // The SQLState for the exception thrown when pos > length of Clob
      // is XJ076
      assertSQLState("XJ087", sqle);
    }

    // check for the case when length < 0
    try {
      // set length as negative
      clob.getCharacterStream(2L, -5L);
      // Should not come here. The exception has to be thrown.
      fail("Fail: expected exception for the length being negative " + "not thrown");
    } catch (SQLException sqle) {
      // The SQLState for the exception thrown when length < 0 of Clob
      // is XJ071
      assertSQLState("XJ071", sqle);
    }

    // check for the case when pos + length > length of Clob
    try {
      // set pos + length > length of Clob
      clob.getCharacterStream((clob.length() - 4), 10L);
      // Should not come here. The exception has to be thrown.
      fail(
          "Fail: expected exception for the sum of position and length"
              + " being greater than the LOB size not thrown");
    } catch (SQLException sqle) {
      // The SQLState for the exception thrown when length < 0 of Clob
      // is XJ087
      assertSQLState("XJ087", sqle);
    }
  }