Java Code Examples for java.sql.ResultSet#getCursorName()

/**
 * Tests non covering index.
 * 
 * @throws SQLException
 */
public void testNonCoveringIndex() throws SQLException {
	PreparedStatement select;
	Statement update;
	ResultSet cursor;
	String cursorName;

	update = createStatement();
	select = prepareStatement("select c3, c2 from t1 where c3 > 125 and c1 > 0 for update");
	cursor = select.executeQuery(); // cursor is now open
	cursorName = cursor.getCursorName();

	for (int i = 0; i < (SIZE_OF_T1 / 2); i++) {
		assertEquals(cursor.next(), true);
		update.execute("update t1 set c3 = c3 + 25 where current of " + cursorName);
	}
	assertFalse(
			"Update using noncovering index failed! Still got rows.",
			cursor.next());

	cursor.close();
	select.close();

	/* see what we have in the table */
	select = prepareStatement("select c1, c3 from t1");
	cursor = select.executeQuery(); // cursor is now open
	for (int i = 0; i < SIZE_OF_T1; i++) {
		assertEquals(cursor.next(), true);
	}
	assertFalse(
			"Update using noncovering index failed! Got more rows.", cursor
					.next());

	select.close();
	cursor.close();

	rollback();
}
 

/**
 * Tests non covering index.
 * 
 * @throws SQLException
 */
public void testNonCoveringIndex() throws SQLException {
	PreparedStatement select;
	Statement update;
	ResultSet cursor;
	String cursorName;

	update = createStatement();
	select = prepareStatement("select c3, c2 from t1 where c3 > 125 and c1 > 0 for update");
	cursor = select.executeQuery(); // cursor is now open
	cursorName = cursor.getCursorName();

	for (int i = 0; i < (SIZE_OF_T1 / 2); i++) {
		assertEquals(cursor.next(), true);
		update.execute("update t1 set c3 = c3 + 25 where current of " + cursorName);
	}
	assertFalse(
			"Update using noncovering index failed! Still got rows.",
			cursor.next());

	cursor.close();
	select.close();

	/* see what we have in the table */
	select = prepareStatement("select c1, c3 from t1");
	cursor = select.executeQuery(); // cursor is now open
	for (int i = 0; i < SIZE_OF_T1; i++) {
		assertEquals(cursor.next(), true);
	}
	assertFalse(
			"Update using noncovering index failed! Got more rows.", cursor
					.next());

	select.close();
	cursor.close();

	rollback();
}
 

/**
 * Tests non covering index.
 * 
 * @throws SQLException
 */
public void testNonCoveringIndex() throws SQLException {
	PreparedStatement select;
	Statement update;
	ResultSet cursor;
	String cursorName;

	update = createStatement();
	select = prepareStatement("select c3, c2 from t1 where c3 > 125 and c1 > 0 for update");
	cursor = select.executeQuery(); // cursor is now open
	cursorName = cursor.getCursorName();

	for (int i = 0; i < (SIZE_OF_T1 / 2); i++) {
		assertEquals(cursor.next(), true);
		update.execute("update t1 set c3 = c3 + 25 where current of " + cursorName);
	}
	assertFalse(
			"Update using noncovering index failed! Still got rows.",
			cursor.next());

	cursor.close();
	select.close();

	/* see what we have in the table */
	select = prepareStatement("select c1, c3 from t1");
	cursor = select.executeQuery(); // cursor is now open
	for (int i = 0; i < SIZE_OF_T1; i++) {
		assertEquals(cursor.next(), true);
	}
	assertFalse(
			"Update using noncovering index failed! Got more rows.", cursor
					.next());

	select.close();
	cursor.close();

	rollback();
}
 

@Test(expected = SQLFeatureNotSupportedException.class)
public void assertGetCursorName() throws SQLException {
    for (ResultSet each : resultSets) {
        each.getCursorName();
    }
}
 

/**
 * Test the virtual memory heap.
 * 
 * @throws SQLException
 */
public void testVirtualMemoryHeap() throws SQLException {
	PreparedStatement select = prepareStatement("select c1, c3 from t1 where c3 > 1 and c1 > 0 for update");
	Statement update = createStatement();
	String cursorName;
	ResultSet cursor;
	int expectedValue = 1;

	/* drop index and recreate it to be sure that it is ascending
                * (other subtests may have changed it)
                */
	assertUpdateCount(update, 0, "drop index I11");
	assertUpdateCount(update, 0, "create index I11 on T1 (c3, c1, c5)");

	cursor = select.executeQuery(); // cursor is now open
	cursorName = cursor.getCursorName();

	/* scan the entire table except the last row. */
	for (int i = 0; i < SIZE_OF_T1 - 1; i++) {	
		
		/*	Notice the order in the rows we get: from 2 to 102 asc order on second column (c3)
		 *	then from 202 down to 103 on that column; then from 203 up to 250.  The reason is
		 *	we are using asc index on c3, all the rows updated are in the future direction of the
		 *	index scan, so they all get filled into a hash table.  The MAX_MEMORY_PER_TABLE
		 *	property determines max cap of hash table 100.  So from row 103 it goes into virtual
		 *	memory heap, whose in memory part is also 100 entries.  So row 103 to 202 goes into
		 *	the in-memory part and gets dumped out in reverse order.  Finally Row 203 to 250"
		 *	goes into file system.  Here we mean row ids.
		 */
		if (i < MAX_CAP_OF_HASH_TABLE + 1) {
			expectedValue++;
		} else if (i > MAX_CAP_OF_HASH_TABLE && i <= MAX_CAP_OF_HASH_TABLE * 2) {
			if (i == MAX_CAP_OF_HASH_TABLE + 1) {
				expectedValue = 202;
			} else {
				expectedValue--;
			}
		} else if (i > MAX_CAP_OF_HASH_TABLE * 2) {
			if (i == MAX_CAP_OF_HASH_TABLE * 2 + 1) {
				expectedValue = 203;
			} else {
				expectedValue++;
			}
		}
		
		assertEquals(cursor.next(), true);
		//System.out.println("Row " + i + ": "+cursor.getInt(1)+","+cursor.getInt(2)+": "+expectedValue);
		assertEquals("Virtual memory heap test failed! Got unexpected value.", expectedValue, cursor.getInt(2));
		update.execute("update t1 set c3 = c3 + 250 where current of " + cursorName);
	}
	assertFalse(
			"Update with virtual memory heap failed! Still got rows.",
			cursor.next());

	cursor.close();
	update.close();

	/* see what we have in the table */
	select = prepareStatement("select c1, c3 from t1");
	cursor = select.executeQuery(); // cursor is now open
	for (int i = 0; i < SIZE_OF_T1; i++) {
		assertEquals(cursor.next(), true);
	}
	assertFalse(
			"Update with virtual memory heeap failed! Got more rows.",
			cursor.next());

	select.close();
	cursor.close();

	rollback();
}
 

/**
    * Test what happens to a positioned update when the cursor
    * it is compiled against changes to the SQL provided, changeToSQL. 
    * This test first prepares a cursor initialCursor 
    * using with the given name (or system name if null is passed in)
    * A cursor is opened and a positioned update is opened that updates.
    * 
    * If sqlState is null then no error is expected and thus the
    * positioned statement must update a single row.
    * Otherwise sqlState is the exected exception for the update.
    * 
    * If no error is expected then three rows will be either
    * updated or deleted depending on the positioned statement.
    * 
    * If an error is expected then two rows will be updated
    * or deleted.
    */
private void cursorChange(String sqlState,
           String cursorName,
           String initialCursor,
           String positionedStatement,
           String changeToCursor) throws SQLException {

	PreparedStatement select = prepareStatement(initialCursor);
	if (cursorName != null)
		select.setCursorName(cursorName);

	ResultSet cursor = select.executeQuery(); // cursor is now open

	// TEST: find the cursor during compilation
	cursorName = cursor.getCursorName();
	PreparedStatement update = prepareStatement(
               positionedStatement + cursorName);
	assertTrue(cursor.next());
	assertUpdateCount(update, 1);
	cursor.close();

	// now prepare the a cursor with the same name but different SQL.
	PreparedStatement selectdd = prepareStatement(changeToCursor);
	selectdd.setCursorName(cursorName);
	cursor = selectdd.executeQuery();
	assertTrue(cursor.next());
       if (sqlState != null)
	    assertStatementError(sqlState,update);
       else
           assertUpdateCount(update, 1);

	cursor.close();
	
	// now execute the original statement again and the positioned update
	// will work.
	cursor = select.executeQuery();
	cursor.next();
	assertUpdateCount(update, 1);

	cursor.close();
	update.close();
	selectdd.close();
	select.close();

}
 

@Test(expected = SQLFeatureNotSupportedException.class)
public void assertGetCursorName() throws SQLException {
    for (ResultSet each : resultSets) {
        each.getCursorName();
    }
}
 

/**
 * Test the virtual memory heap.
 * 
 * @throws SQLException
 */
public void testVirtualMemoryHeap() throws SQLException {
	PreparedStatement select = prepareStatement("select c1, c3 from t1 where c3 > 1 and c1 > 0 for update");
	Statement update = createStatement();
	String cursorName;
	ResultSet cursor;
	int expectedValue = 1;

	/* drop index and recreate it to be sure that it is ascending
                * (other subtests may have changed it)
                */
	assertUpdateCount(update, 0, "drop index I11");
	assertUpdateCount(update, 0, "create index I11 on T1 (c3, c1, c5)");

	cursor = select.executeQuery(); // cursor is now open
	cursorName = cursor.getCursorName();

	/* scan the entire table except the last row. */
	for (int i = 0; i < SIZE_OF_T1 - 1; i++) {	
		
		/*	Notice the order in the rows we get: from 2 to 102 asc order on second column (c3)
		 *	then from 202 down to 103 on that column; then from 203 up to 250.  The reason is
		 *	we are using asc index on c3, all the rows updated are in the future direction of the
		 *	index scan, so they all get filled into a hash table.  The MAX_MEMORY_PER_TABLE
		 *	property determines max cap of hash table 100.  So from row 103 it goes into virtual
		 *	memory heap, whose in memory part is also 100 entries.  So row 103 to 202 goes into
		 *	the in-memory part and gets dumped out in reverse order.  Finally Row 203 to 250"
		 *	goes into file system.  Here we mean row ids.
		 */
		if (i < MAX_CAP_OF_HASH_TABLE + 1) {
			expectedValue++;
		} else if (i > MAX_CAP_OF_HASH_TABLE && i <= MAX_CAP_OF_HASH_TABLE * 2) {
			if (i == MAX_CAP_OF_HASH_TABLE + 1) {
				expectedValue = 202;
			} else {
				expectedValue--;
			}
		} else if (i > MAX_CAP_OF_HASH_TABLE * 2) {
			if (i == MAX_CAP_OF_HASH_TABLE * 2 + 1) {
				expectedValue = 203;
			} else {
				expectedValue++;
			}
		}
		
		assertEquals(cursor.next(), true);
		//System.out.println("Row " + i + ": "+cursor.getInt(1)+","+cursor.getInt(2)+": "+expectedValue);
		assertEquals("Virtual memory heap test failed! Got unexpected value.", expectedValue, cursor.getInt(2));
		update.execute("update t1 set c3 = c3 + 250 where current of " + cursorName);
	}
	assertFalse(
			"Update with virtual memory heap failed! Still got rows.",
			cursor.next());

	cursor.close();
	update.close();

	/* see what we have in the table */
	select = prepareStatement("select c1, c3 from t1");
	cursor = select.executeQuery(); // cursor is now open
	for (int i = 0; i < SIZE_OF_T1; i++) {
		assertEquals(cursor.next(), true);
	}
	assertFalse(
			"Update with virtual memory heeap failed! Got more rows.",
			cursor.next());

	select.close();
	cursor.close();

	rollback();
}
 

/**
    * Test what happens to a positioned update when the cursor
    * it is compiled against changes to the SQL provided, changeToSQL. 
    * This test first prepares a cursor initialCursor 
    * using with the given name (or system name if null is passed in)
    * A cursor is opened and a positioned update is opened that updates.
    * 
    * If sqlState is null then no error is expected and thus the
    * positioned statement must update a single row.
    * Otherwise sqlState is the exected exception for the update.
    * 
    * If no error is expected then three rows will be either
    * updated or deleted depending on the positioned statement.
    * 
    * If an error is expected then two rows will be updated
    * or deleted.
    */
private void cursorChange(String sqlState,
           String cursorName,
           String initialCursor,
           String positionedStatement,
           String changeToCursor) throws SQLException {

	PreparedStatement select = prepareStatement(initialCursor);
	if (cursorName != null)
		select.setCursorName(cursorName);

	ResultSet cursor = select.executeQuery(); // cursor is now open

	// TEST: find the cursor during compilation
	cursorName = cursor.getCursorName();
	PreparedStatement update = prepareStatement(
               positionedStatement + cursorName);
	assertTrue(cursor.next());
	assertUpdateCount(update, 1);
	cursor.close();

	// now prepare the a cursor with the same name but different SQL.
	PreparedStatement selectdd = prepareStatement(changeToCursor);
	selectdd.setCursorName(cursorName);
	cursor = selectdd.executeQuery();
	assertTrue(cursor.next());
       if (sqlState != null)
	    assertStatementError(sqlState,update);
       else
           assertUpdateCount(update, 1);

	cursor.close();
	
	// now execute the original statement again and the positioned update
	// will work.
	cursor = select.executeQuery();
	cursor.next();
	assertUpdateCount(update, 1);

	cursor.close();
	update.close();
	selectdd.close();
	select.close();

}
 

/**
 * Test the virtual memory heap.
 * 
 * @throws SQLException
 */
public void testVirtualMemoryHeap() throws SQLException {
	PreparedStatement select = prepareStatement("select c1, c3 from t1 where c3 > 1 and c1 > 0 for update");
	Statement update = createStatement();
	String cursorName;
	ResultSet cursor;
	int expectedValue = 1;

	/* drop index and recreate it to be sure that it is ascending
                * (other subtests may have changed it)
                */
	assertUpdateCount(update, 0, "drop index I11");
	assertUpdateCount(update, 0, "create index I11 on T1 (c3, c1, c5)");

	cursor = select.executeQuery(); // cursor is now open
	cursorName = cursor.getCursorName();

	/* scan the entire table except the last row. */
	for (int i = 0; i < SIZE_OF_T1 - 1; i++) {	
           // Expect the values to be returned in index order.
           expectedValue++;
		
		assertEquals(cursor.next(), true);
		//System.out.println("Row " + i + ": "+cursor.getInt(1)+","+cursor.getInt(2)+": "+expectedValue);
		assertEquals("Virtual memory heap test failed! Got unexpected value.", expectedValue, cursor.getInt(2));
		update.execute("update t1 set c3 = c3 + 250 where current of " + cursorName);
	}
	assertFalse(
			"Update with virtual memory heap failed! Still got rows.",
			cursor.next());

	cursor.close();
	update.close();

	/* see what we have in the table */
	select = prepareStatement("select c1, c3 from t1");
	cursor = select.executeQuery(); // cursor is now open
	for (int i = 0; i < SIZE_OF_T1; i++) {
		assertEquals(cursor.next(), true);
	}
	assertFalse(
			"Update with virtual memory heeap failed! Got more rows.",
			cursor.next());

	select.close();
	cursor.close();

	rollback();
}
 

/**
    * Test what happens to a positioned update when the cursor
    * it is compiled against changes to the SQL provided, changeToSQL. 
    * This test first prepares a cursor initialCursor 
    * using with the given name (or system name if null is passed in)
    * A cursor is opened and a positioned update is opened that updates.
    * 
    * If sqlState is null then no error is expected and thus the
    * positioned statement must update a single row.
    * Otherwise sqlState is the exected exception for the update.
    * 
    * If no error is expected then three rows will be either
    * updated or deleted depending on the positioned statement.
    * 
    * If an error is expected then two rows will be updated
    * or deleted.
    */
private void cursorChange(String sqlState,
           String cursorName,
           String initialCursor,
           String positionedStatement,
           String changeToCursor) throws SQLException {

	PreparedStatement select = prepareStatement(initialCursor);
	if (cursorName != null)
		select.setCursorName(cursorName);

	ResultSet cursor = select.executeQuery(); // cursor is now open

	// TEST: find the cursor during compilation
	cursorName = cursor.getCursorName();
	PreparedStatement update = prepareStatement(
               positionedStatement + cursorName);
	assertTrue(cursor.next());
	assertUpdateCount(update, 1);
	cursor.close();

	// now prepare the a cursor with the same name but different SQL.
	PreparedStatement selectdd = prepareStatement(changeToCursor);
	selectdd.setCursorName(cursorName);
	cursor = selectdd.executeQuery();
	assertTrue(cursor.next());
       if (sqlState != null)
	    assertStatementError(sqlState,update);
       else
           assertUpdateCount(update, 1);

	cursor.close();
	
	// now execute the original statement again and the positioned update
	// will work.
	cursor = select.executeQuery();
	cursor.next();
	assertUpdateCount(update, 1);

	cursor.close();
	update.close();
	selectdd.close();
	select.close();

}