/**
* Derby - Class org.apache.derbyTesting.functionTests.tests.lang.ScrollCursors2Test
*
* 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.lang;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import junit.framework.Test;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.TestConfiguration;
public class ScrollCursors2Test extends BaseJDBCTestCase {
public ScrollCursors2Test(String name) {
super(name);
}
private static boolean isDerbyNetClient = usingDerbyNetClient();
/**
* Set up the test.
*
* This method creates the table used by the rest of the test.
*
*
* @exception SQLException
* Thrown if some unexpected error happens
*/
public void setUp() throws SQLException {
Connection conn = getConnection();
Statement s_i_r;
s_i_r = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
/* Create a table */
s_i_r.execute("create table t (i int, c50 char(50))");
/* Populate the table */
s_i_r.execute("insert into t (i) values (2), (3), (4), (5), (6)");
s_i_r.execute("update t set c50 = RTRIM(CAST (i AS CHAR(50)))");
s_i_r.close();
isDerbyNetClient = usingDerbyNetClient();
}
public void tearDown() throws SQLException, Exception {
Connection conn = getConnection();
Statement s = conn.createStatement();
s.execute("drop table t");
conn.commit();
super.tearDown();
}
public void testForwardOnlyNegative() throws SQLException {
Connection conn = getConnection();
PreparedStatement ps_f_r = null;
ResultSet rs;
SQLWarning warning;
Statement s_f_r = null;
s_f_r = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
// We should have gotten no warnings and a read only forward only cursor
warning = conn.getWarnings();
assertNull(warning);
conn.clearWarnings();
// Verify that setMaxRows(-1) fails
try {
s_f_r.setMaxRows(-1);
// Should never get here
fail("setMaxRows(-1) expected to fail");
} catch (SQLException sqle) {
/* Check to be sure the exception is the one we expect */
assertEquals("XJ063", sqle.getSQLState());
}
// Verify maxRows still 0
assertEquals("getMaxRows() expected to return 0", 0, s_f_r.getMaxRows());
// Verify that result set from statement is
// scroll insensitive and read only
rs = s_f_r.executeQuery("select * from t");
assertEquals(ResultSet.TYPE_FORWARD_ONLY, rs.getType());
assertEquals(ResultSet.CONCUR_READ_ONLY, rs.getConcurrency());
// Verify that first(), etc. don't work
try {
rs.first();
// Should never get here
fail("first() expected to fail");
} catch (SQLException sqle) {
assertOnlyOnScrollableException(sqle);
}
try {
rs.beforeFirst();
// Should never get here
fail("beforeFirst() expected to fail");
} catch (SQLException sqle) {
assertOnlyOnScrollableException(sqle);
}
try {
rs.isBeforeFirst();
// Should never get here
fail("isBeforeFirst() expected to fail");
} catch (SQLException sqle) {
// Check to be sure the exception is the one we expect
assertOnlyOnScrollableException(sqle);
}
try {
rs.isAfterLast();
// Should never get here
fail("isAfterLast() expected to fail");
} catch (SQLException sqle) {
// Check to be sure the exception is the one we expect
assertOnlyOnScrollableException(sqle);
}
try {
rs.isFirst();
// Should never get here
fail("isFirst() expected to fail");
} catch (SQLException sqle) {
// Check to be sure the exception is the one we expect
assertOnlyOnScrollableException(sqle);
}
try {
rs.isLast();
// Should never get here
fail("isLast() expected to fail");
} catch (SQLException sqle) {
// Check to be sure the exception is the one we expect
assertOnlyOnScrollableException(sqle);
}
try {
rs.absolute(1);
// Should never get here
fail("absolute() expected to fail");
} catch (SQLException sqle) {
assertOnlyOnScrollableException(sqle);
}
try {
rs.relative(1);
// Should never get here
fail("relative() expected to fail");
} catch (SQLException sqle) {
assertOnlyOnScrollableException(sqle);
}
// setFetchDirection should fail
try {
rs.setFetchDirection(ResultSet.FETCH_FORWARD);
// Should never get here
fail("setFetchDirection() expected to fail");
} catch (SQLException sqle) {
assertOnlyOnScrollableException(sqle);
}
// Book says that getFetchDirection(), getFetchSize() and
// setFetchSize() are all okay.
if (isDerbyNetClient)
assertEquals(0, rs.getFetchSize());
else
assertEquals(1, rs.getFetchSize());
rs.setFetchSize(5);
assertEquals(5,rs.getFetchSize());
assertEquals(ResultSet.FETCH_FORWARD,rs.getFetchDirection());
rs.close();
s_f_r.close();
ps_f_r = conn.prepareStatement("select * from t",
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
// We should have gotten no warnings and a read only forward only cursor
warning = conn.getWarnings();
assertNull(warning);
conn.clearWarnings();
// Verify that result set from statement is
// scroll insensitive and read only
rs = ps_f_r.executeQuery();
assertEquals(ResultSet.TYPE_FORWARD_ONLY, rs.getType());
assertEquals(ResultSet.CONCUR_READ_ONLY,rs.getConcurrency());
// Verify that first() doesn't work
try {
rs.first();
// Should never get here
fail("first() expected to fail");
} catch (SQLException sqle) {
assertOnlyOnScrollableException(sqle);
}
rs.close();
ps_f_r.close();
}
/**
* Positive tests for forward only cursors.
*
* This method tests forward only cursors.
*
*
* @exception SQLException
* Thrown if some unexpected error happens
*/
public void testForwardOnlyPositive() throws SQLException {
Connection conn = getConnection();
boolean passed = true;
ResultSet rs;
SQLWarning warning;
Statement s_f_r = null;
s_f_r = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
// We should have gotten no warnings and a read only forward only cursor
warning = conn.getWarnings();
assertNull(warning);
conn.clearWarnings();
// Verify that setMaxRows(4) succeeds
s_f_r.setMaxRows(5);
assertEquals(5, s_f_r.getMaxRows());
rs = s_f_r.executeQuery("values 1, 2, 3, 4, 5, 6");
// Iterate straight thru RS, expect only 5 rows.
assertNotNull(rs);
JDBC.assertDrainResults(rs, 5);
s_f_r.close();
}
/**
* Scroll sensitive cursor tests
*
* This method tests scroll sensitive cursors. (Not implemented, so we
* should get back scroll insensitive curors with read only concurrency.)
*
* @exception SQLException
* Thrown if some unexpected error happens
*/
public void testScrollSensitive() throws SQLException {
Connection conn = getConnection();
ResultSet rs;
SQLWarning warning;
Statement s_s_r = null; // sensitive, read only
Statement s_s_u = null; // sensitive, updatable
s_s_r = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
// We should have gotten a warning and a scroll insensitive cursor
warning = conn.getWarnings();
assertNotNull(warning);
conn.clearWarnings();
// Verify that result set from statement is
// scroll insensitive and read only
rs = s_s_r.executeQuery("select * from t");
assertEquals(ResultSet.TYPE_SCROLL_INSENSITIVE, rs.getType());
assertEquals(ResultSet.CONCUR_READ_ONLY, rs.getConcurrency());
rs.close();
// Close the statement
s_s_r.close();
s_s_u = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
// We should have gotten 1 warning and a updatable scroll
// insensitive cursor.
warning = conn.getWarnings();
assertNotNull(warning);
conn.clearWarnings();
// Verify that result set from statement is
// scroll insensitive and read only
rs = s_s_u.executeQuery("select * from t");
assertEquals(ResultSet.TYPE_SCROLL_INSENSITIVE, rs.getType());
assertEquals(ResultSet.CONCUR_UPDATABLE, rs.getConcurrency());
rs.close();
}
/**
* Positive tests for scroll insensitive cursor.
*
*
* @exception SQLException
* Thrown if some unexpected error happens
*/
public void testScrollInsensitivePositive() throws SQLException {
Connection conn = getConnection();
boolean passed = true;
PreparedStatement ps_i_r = null;
ResultSet rs;
SQLWarning warning;
Statement s_i_r = null; // insensitive, read only
s_i_r = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
// We should not have gotten any warnings
// and should have gotten a scroll insensitive cursor
warning = conn.getWarnings();
assertNull(warning);
conn.clearWarnings();
// run a query
rs = s_i_r.executeQuery("select * from t");
// verify scroll insensitive and read only
assertEquals(ResultSet.TYPE_SCROLL_INSENSITIVE, rs.getType());
assertEquals(ResultSet.CONCUR_READ_ONLY, rs.getConcurrency());
// We should be positioned before the 1st row
assertTrue(rs.isBeforeFirst());
assertFalse(rs.absolute(0));
// still expected to be before first
assertTrue(rs.isBeforeFirst());
// go to first row
assertTrue(rs.first());
assertEquals(rs.getInt(1), 2);
assertTrue(rs.isFirst());
// move to before first
rs.beforeFirst();
assertTrue(rs.isBeforeFirst());
// move to last row
assertTrue(rs.last());
assertTrue(rs.isLast());
assertFalse(rs.isAfterLast());
assertEquals(6, rs.getInt(1));
assertFalse("not expecting to find another row", rs.next());
assertTrue(rs.isAfterLast());
// We're after the last row, verify that only isAfterLast()
// returns true
assertFalse(rs.isLast());
assertFalse(rs.isFirst());
assertFalse(rs.isBeforeFirst());
assertEquals(ResultSet.FETCH_FORWARD, rs.getFetchDirection());
rs.setFetchDirection(ResultSet.FETCH_UNKNOWN);
assertEquals(ResultSet.FETCH_UNKNOWN, rs.getFetchDirection());
if (isDerbyNetClient)
assertEquals(64, rs.getFetchSize());
else
assertEquals(1, rs.getFetchSize());
rs.setFetchSize(5);
assertEquals(5, rs.getFetchSize());
// setFetchSize() to 0 should have no effect.
// for client server, fetchSize should have to 64
rs.setFetchSize(0);
if (isDerbyNetClient)
assertEquals(64, rs.getFetchSize());
else
assertEquals(5, rs.getFetchSize());
// done
rs.close();
// Empty result set tests (DERBY-992)
rs = s_i_r.executeQuery("select * from t where 1=0");
rs.afterLast();
assertFalse("afterLast() on empty RS should be no-op", rs.isAfterLast());
rs.beforeFirst();
assertFalse("beforeFirst() on empty RS should be no-op", rs
.isBeforeFirst());
rs.close();
ps_i_r = conn.prepareStatement("select * from t",
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
// We should not have gotten any warnings
// and should have gotten a prepared scroll insensitive cursor
warning = conn.getWarnings();
assertNull(warning);
rs = ps_i_r.executeQuery();
// make sure it's scrollable
rs.last();
rs.close();
ps_i_r.close();
// Check setMaxRows()/getMaxRows()
assertEquals(0, s_i_r.getMaxRows());
s_i_r.setMaxRows(5);
assertEquals(5, s_i_r.getMaxRows());
rs = s_i_r.executeQuery("values 1, 2, 3, 4, 5, 6");
assertNotNull(rs);
// Iterate straight thru RS, expect only 5 rows.
for (int index = 1; index < 6; index++) {
assertTrue(rs.next());
}
// We should not see another row (only 5, not 6)
assertFalse(rs.next());
rs.close();
// Jump around and verify setMaxRows() works.
rs = s_i_r.executeQuery("values 1, 2, 3, 4, 5, 6");
assertNotNull(rs);
assertTrue(rs.last());
// Iterate backwards thru RS, expect only 4 more (5 total) rows.
for (int index = 1; index < 5; index++) {
assertTrue(rs.previous());
}
// We should not see another row (only 5, not 6)
assertFalse(rs.previous());
rs.close();
rs = s_i_r.executeQuery("values 1, 2, 3, 4, 5, 6");
assertNotNull(rs);
rs.afterLast();
// Iterate backwards thru RS, expect only 5 rows.
for (int index = 1; index < 6; index++) {
assertTrue(rs.previous());
}
// We should not see another row (only 5, not 6)
assertFalse(rs.previous());
rs.close();
// Verify setting maxRows back to 0 works.
s_i_r.setMaxRows(0);
rs = s_i_r.executeQuery("values 1, 2, 3, 4, 5, 6");
assertNotNull(rs);
// Iterate straight thru RS, expect 6 rows.
for (int index = 1; index < 7; index++) {
assertTrue(rs.next());
}
// We should not see another row
assertFalse(rs.next());
rs.close();
}
/**
* Negative tests for scroll insensitive cursor.
*
* @exception SQLException
* Thrown if some unexpected error happens
*/
public void testScrollInsensitiveNegative() throws SQLException {
Connection conn = getConnection();
boolean passed = true;
ResultSet rs;
SQLWarning warning;
Statement s_i_r = null; // insensitive, read only
s_i_r = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
// We should not have gotten any warnings
// and should have gotten a scroll insensitive cursor
warning = conn.getWarnings();
assertNull(warning);
conn.clearWarnings();
// Verify that setMaxRows(-1) fails
try {
s_i_r.setMaxRows(-1);
// Should never get here
fail("setMaxRows(-1) expected to fail");
} catch (SQLException sqle) {
/* Check to be sure the exception is the one we expect */
assertEquals("XJ063", sqle.getSQLState());
}
// Verify maxRows still 0
assertEquals(0, s_i_r.getMaxRows());
// Empty result set
rs = s_i_r.executeQuery("select * from t where 1=0");
// isBeforeFirst() and isAfterLast() should always return false
// when result set is empty
assertFalse(rs.isBeforeFirst());
assertFalse(rs.next());
assertFalse(rs.previous());
assertFalse(rs.isAfterLast());
assertFalse(rs.isFirst());
assertFalse(rs.isLast());
assertFalse(rs.relative(0));
assertFalse(rs.relative(1));
assertFalse(rs.relative(-1));
assertFalse(rs.absolute(0));
assertFalse(rs.absolute(1));
assertFalse(rs.absolute(-1));
rs.close();
// End of empty result set tests
// Non-empty result set
rs = s_i_r.executeQuery("select * from t");
// Negative fetch size
try {
rs.setFetchSize(-5);
fail("setFetchSize(-5) expected to fail");
} catch (SQLException sqle) {
/* Check to be sure the exception is the one we expect */
assertEquals("XJ062", sqle.getSQLState());
}
rs.close();
s_i_r.close();
}
/**
* CallableStatement tests.
*
* @exception SQLException
* Thrown if some unexpected error happens
*/
public void testCallableStatements() throws SQLException {
Connection conn = getConnection();
SQLWarning warning;
CallableStatement cs_s_r = null; // sensitive, read only
CallableStatement cs_s_u = null; // sensitive, updatable
CallableStatement cs_i_r = null; // insensitive, read only
CallableStatement cs_f_r = null; // forward only, read only
cs_s_r = conn.prepareCall("values cast (? as Integer)",
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
// We should have gotten 1 warnings
warning = conn.getWarnings();
assertNotNull(warning);
if (!isDerbyNetClient)
assertEquals("01J02", warning.getSQLState());
else
assertEquals("01J10", warning.getSQLState());
assertNull(warning.getNextWarning());
conn.clearWarnings();
cs_s_r.close();
cs_s_u = conn.prepareCall("values cast (? as Integer)",
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
// We should have gotten 1 warning
warning = conn.getWarnings();
assertNotNull(warning);
if (!isDerbyNetClient)
assertEquals("01J02", warning.getSQLState());
else
assertEquals("01J10", warning.getSQLState());
assertNull(warning.getNextWarning());
conn.clearWarnings();
cs_s_u.close();
cs_i_r = conn.prepareCall("values cast (? as Integer)",
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
// We should have gotten 0 warnings
warning = conn.getWarnings();
assertNull(warning);
conn.clearWarnings();
cs_i_r.close();
cs_f_r = conn.prepareCall("values cast (? as Integer)",
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
// We should have gotten 0 warnings
warning = conn.getWarnings();
assertNull(warning);
conn.clearWarnings();
cs_f_r.close();
}
/**
* Tests for PreparedStatement.getMetaData().
*
* @exception SQLException
* Thrown if some unexpected error happens
*/
public void testGetMetaData() throws SQLException {
Connection conn = getConnection();
PreparedStatement ps_f_r = null; // forward only, read only
ResultSet rs;
ResultSetMetaData rsmd_ps;
ResultSetMetaData rsmd_rs;
SQLWarning warning;
ps_f_r = conn.prepareStatement("select c50, i, 43 from t",
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
rsmd_ps = ps_f_r.getMetaData();
assertNotNull(rsmd_ps);
// Now get meta data from result set
rs = ps_f_r.executeQuery();
rsmd_rs = rs.getMetaData();
assertNotNull(rsmd_rs);
// check column count
assertEquals(rsmd_ps.getColumnCount(), rsmd_rs.getColumnCount());
// get column name for 2nd column
assertEquals(rsmd_ps.getColumnName(2), rsmd_rs.getColumnName(2));
assertEquals(rsmd_ps.isReadOnly(2), rsmd_rs.isReadOnly(2));
rs.close();
ps_f_r.close();
}
public void testScrollVerifyMaxRowWithFetchSize() throws SQLException {
scrollVerifyMaxRowWithFetchSize(10, 10);
scrollVerifyMaxRowWithFetchSize(10, 5);
scrollVerifyMaxRowWithFetchSize(10, 0);
scrollVerifyMaxRowWithFetchSize(0, 0);
scrollVerifyMaxRowWithFetchSize(0, 5);
scrollVerifyMaxRowWithFetchSize(0, 10);
scrollVerifyMaxRowWithFetchSize(0, 15);
}
/**
* Tests for maxRow and fetchSize with scrollable cursors
*
*
* @param maxRows
* The maxRows value to use
* @param fetchSize
* The fetchSize value to use
*
* @exception SQLException
* Thrown if some unexpected error happens
*/
private void scrollVerifyMaxRowWithFetchSize(int maxRows, int fetchSize)
throws SQLException {
Connection conn = getConnection();
ResultSet rs;
boolean passed = true;
Statement s_i_r = null;
s_i_r = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
s_i_r.setMaxRows(maxRows);
// Execute query
rs = s_i_r
.executeQuery("values 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15");
rs.setFetchSize(fetchSize);
// this should not affect the ResultSet because
s_i_r.setMaxRows(2);
if (maxRows == 0)
maxRows = 15;
assertNotNull(rs);
// Start from before first
// Iterate straight thru RS, expect only maxRows rows.
for (int index = 1; index < maxRows + 1; index++) {
assertTrue("rs.next() failed, index = " + index, rs.next());
assertEquals(index, rs.getInt(1));
}
// We should not see another row (only maxRows, not total)
assertFalse(rs.next());
// Start from first and verify maxRows
assertTrue(rs.first());
// Iterate forward thru RS, expect only (maxRows - 1) more rows.
for (int index = 1; index < maxRows; index++) {
assertTrue(rs.next());
assertEquals(index + 1, rs.getInt(1));
}
// We should not see another row (only maxRows, not total)
assertFalse(rs.next());
// Start from afterLast and verify maxRows
rs.afterLast();
// Iterate backwards thru RS, expect only (maxRows - 1) rows.
for (int index = 1; index < maxRows + 1; index++) {
assertTrue(rs.previous());
assertEquals(maxRows - index + 1, rs.getInt(1));
}
// We should not see another row (only maxRows, not total)
assertFalse(rs.previous());
// Start from last and verify maxRows
assertTrue(rs.last());
// Iterate backwards thru RS, expect only (maxRows - 1) more rows.
for (int index = 1; index < maxRows; index++) {
assertTrue(rs.previous());
assertEquals((maxRows - index), rs.getInt(1));
}
// We should not see another row (only 5, not 6)
assertFalse(rs.previous());
rs.last();
int rows = rs.getRow();
rs.absolute(rows / 2);
assertFalse(rs.relative(-1 * (rows)));
assertTrue(rs.isBeforeFirst());
rs.absolute(rows / 2);
assertFalse(rs.relative(rows));
assertTrue(rs.isAfterLast());
rs.absolute(rows / 2);
assertFalse("absolute(" + (rows + 1)
+ ") should return false, position outside of the resultSet",
rs.absolute(rows + 1));
rs.absolute(rows / 2);
assertFalse(rs.absolute((-1) * (rows + 1)));
assertTrue(rs.isBeforeFirst());
rs.close();
}
private void assertOnlyOnScrollableException(SQLException sqle) {
if (!isDerbyNetClient) {
assertEquals("XJ061", sqle.getSQLState());
} else {
assertEquals("XJ125", sqle.getSQLState());
}
}
public static Test suite() {
return TestConfiguration.defaultSuite(ScrollCursors2Test.class);
}
}