/**
* 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();
}
/**
* 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();
}
public void tearDown() {
try {
Connection con = AllTests.getConnection();
Statement st = con.createStatement();
st.execute("drop table " + table);
st.close();
} catch (Throwable e) {
// e.printStackTrace();
}
}
/**
* Try to update the row with the given error. Flag a failure if a timeout occurs when not
* expected, and vice versa.
*
* @param id The id of the row to be updated
* @param timeoutExpected true if it is expected that the update times out
* @throws java.sql.SQLException
*/
private void executeParallelUpdate(int id, boolean timeoutExpected) throws SQLException {
Connection conn2 = openDefaultConnection();
Statement stmt2 = conn2.createStatement();
try {
stmt2.executeUpdate(
"update BLOBCLOB set BLOBDATA = " + "cast(X'FFFFFF' as blob) where ID=" + id);
stmt2.close();
conn2.commit();
conn2.close();
if (timeoutExpected) {
fail("FAIL - should have gotten lock timeout");
}
} catch (SQLException se) {
stmt2.close();
conn2.rollback();
conn2.close();
if (timeoutExpected) {
assertSQLState(LOCK_TIMEOUT, se);
} else {
throw se;
}
}
}
public void setUp() {
tearDown();
try {
Connection con = AllTests.getConnection();
Statement st = con.createStatement();
st.execute(
"create table " + table + "(a " + testValue.dataType + ", b " + testValue.dataType + ")");
st.close();
PreparedStatement pr = con.prepareStatement("INSERT into " + table + "(a,b) Values(?,?)");
pr.setObject(1, testValue.small);
pr.setObject(2, testValue.large);
pr.execute();
pr.setObject(1, testValue.small);
pr.setObject(2, testValue.small);
pr.execute();
pr.setObject(1, testValue.large);
pr.setObject(2, testValue.large);
pr.execute();
pr.setObject(1, testValue.large);
pr.setObject(2, testValue.small);
pr.execute();
pr.setObject(1, null);
pr.setObject(2, testValue.small);
pr.execute();
pr.setObject(1, testValue.small);
pr.setObject(2, null);
pr.execute();
pr.setObject(1, null);
pr.setObject(2, null);
pr.execute();
pr.close();
} catch (Throwable e) {
e.printStackTrace();
}
}
/**
* 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;
}
public void runTest() throws Exception {
Connection con = AllTests.getConnection();
Statement st = con.createStatement();
ResultSet rs;
rs = st.executeQuery("Select * from " + table + " where 1 = 0");
assertFalse("To many rows", rs.next());
rs = st.executeQuery("Select * from " + table + " where a = b");
assertTrue("To few rows", rs.next());
assertEqualsObject("Values not equals", rs.getObject(1), rs.getObject(2), false);
assertTrue("To few rows", rs.next());
assertEqualsObject("Values not equals", rs.getObject(1), rs.getObject(2), false);
assertFalse("To many rows", rs.next());
rs = st.executeQuery("Select * from " + table + " where a <= b and b <= a");
assertTrue("To few rows", rs.next());
assertEqualsObject("Values not equals", rs.getObject(1), rs.getObject(2), false);
assertTrue("To few rows", rs.next());
assertEqualsObject("Values not equals", rs.getObject(1), rs.getObject(2), false);
assertFalse("To many rows", rs.next());
rs = st.executeQuery("Select * from " + table + " where (a > (b))");
assertTrue("To few rows", rs.next());
assertFalse("To many rows", rs.next());
rs = st.executeQuery("Select * from " + table + " where a >= b");
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertFalse("To many rows", rs.next());
rs = st.executeQuery("Select * from " + table + " where not (a >= b)");
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertFalse("To many rows", rs.next());
rs = st.executeQuery("Select * from " + table + " where a < b");
assertTrue("To few rows", rs.next());
assertFalse("To many rows", rs.next());
rs = st.executeQuery("Select * from " + table + " where a < b or a>b");
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertFalse("To many rows", rs.next());
rs = st.executeQuery("Select * from " + table + " where a <= b");
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertFalse("To many rows", rs.next());
rs = st.executeQuery("Select * from " + table + " where a <> b");
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertFalse("To many rows", rs.next());
PreparedStatement pr =
con.prepareStatement("Select * from " + table + " where a between ? and ?");
pr.setObject(1, testValue.small);
pr.setObject(2, testValue.large);
rs = pr.executeQuery();
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertFalse("To many rows", rs.next());
pr.close();
pr = con.prepareStatement("Select * from " + table + " where a not between ? and ?");
pr.setObject(1, testValue.small);
pr.setObject(2, testValue.large);
rs = pr.executeQuery();
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertFalse("To many rows", rs.next());
pr.close();
pr = con.prepareStatement("Select * from " + table + " where a in(?,?)");
pr.setObject(1, testValue.small);
pr.setObject(2, testValue.large);
rs = pr.executeQuery();
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertFalse("To many rows", rs.next());
pr.close();
pr = con.prepareStatement("Select * from " + table + " where a not in(?,?)");
pr.setObject(1, testValue.small);
pr.setObject(2, testValue.large);
rs = pr.executeQuery();
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertFalse("To many rows", rs.next());
pr.close();
st.close();
}
