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