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

/**
 *  ensures that a given table has columns with a given name
 */
private void ensureTableColumns(String tableName,
                                String[] columnNames) throws SQLException {

    ResultSet res = m_connection.getMetaData().getColumns(null, null,
        tableName, "%");

    while (res.next()) {
        assertEquals(
            "unexpected column name in table \"" + tableName
            + "\" at position "
            + (res.getRow() - 1), res.getString(
                "COLUMN_NAME"), columnNames[res.getRow() - 1]);
    }

    res.previous();
    assertEquals("not enough columns in table \"" + tableName + "\"",
                 columnNames.length, res.getRow());
}
 

/**
 *  Test that you can scroll forward and update indexed records
 *  in the scrollable ResultSet (not using FOR UPDATE).
 */
public void
    testIndexedScrollInsensitiveUpdateCursorWithoutForUpdate1()
    throws SQLException 
{
    Statement s = createStatement
        (ResultSet.TYPE_SCROLL_INSENSITIVE,
         ResultSet.CONCUR_UPDATABLE);
    s.setCursorName(getNextCursorName());
    ResultSet rs =
        s.executeQuery("select * from t1 where a=1 or a=2");
    
    rs.next();
    rs.next();
    rs.previous();
    verifyTuple(rs);
    updateTuple(rs);
    s.close();
}
 

/**
 * DERBY-1481 - ResultSet.beforeFirst() gives protocol error on scrollable,
 * updatable result sets that are downgraded to read-only
 * 
 * Check that no exception is thrown when calling positioning methods on a
 * result set that has been downgraded to read-only.
 *
 */
public void testDowngradeToScrollReadOnly() throws SQLException {
    Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                      ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = s.executeQuery("select * from t1 order by b");

    // check that the ResultSet was downgraded
    assertWarning(rs.getWarnings(), 
            QUERY_NOT_QUALIFIED_FOR_UPDATABLE_RESULTSET);
    
    // call positioning methods
    rs.next();
    rs.next();
    rs.previous();
    rs.relative(1);
    rs.absolute(3);
    rs.relative(-1);
    rs.first();
    rs.last();
    rs.beforeFirst();
    rs.afterLast();
    
    // close result set and statement
    rs.close();
    s.close();
}
 

/**
 *  Test that you can scroll forward and update indexed records
 *  in the scrollable ResultSet (using FOR UPDATE).
 */
public void
    testIndexedScrollInsensitiveUpdateCursorWithForUpdate1()
    throws SQLException 
{
    Statement s = createStatement
        (ResultSet.TYPE_SCROLL_INSENSITIVE,
         ResultSet.CONCUR_UPDATABLE);
    s.setCursorName(getNextCursorName());
    ResultSet rs = s.executeQuery
        ("select * from t1 where a=1 or a=2 for update");
    
    rs.next();
    rs.next();
    rs.previous();
    verifyTuple(rs);
    updateTuple(rs);
    rs.close();
    s.close();
}
 

/**
 *  Test that you can scroll forward and update indexed records
 *  in the scrollable ResultSet (using FOR UPDATE).
 */
public void
    testIndexedScrollInsensitiveUpdateCursorWithForUpdate1()
    throws SQLException 
{
    Statement s = createStatement
        (ResultSet.TYPE_SCROLL_INSENSITIVE,
         ResultSet.CONCUR_UPDATABLE);
    s.setCursorName(getNextCursorName());
    ResultSet rs = s.executeQuery
        ("select * from t1 where a=1 or a=2 for update");
    
    rs.next();
    rs.next();
    rs.previous();
    verifyTuple(rs);
    updateTuple(rs);
    rs.close();
    s.close();
}
 

/**
 * Update multiple keyed records using scrollable updatable resultset
 */
public void testMultipleKeyUpdates()
    throws SQLException 
{
    Statement s = createStatement
        (ResultSet.TYPE_SCROLL_INSENSITIVE,
         ResultSet.CONCUR_UPDATABLE);
    s.setCursorName(getNextCursorName());
    ResultSet rs = s.executeQuery("select * from t1");
    
    rs.last();
    int primaryKey = rs.getInt(1);
    PreparedStatement ps = s.getConnection().prepareStatement
        ("update t1 set id = ? where id= ?");
    ps.setInt(1, -primaryKey);
    ps.setInt(2, primaryKey);
    assertEquals("Expected one row to be updated", 1,
                 ps.executeUpdate());
    ps.close();
    rs.updateInt(1, primaryKey*10);
    rs.updateInt(2, -555);
    rs.updateInt(3, -777);
    rs.updateRow();
    rs.first();
    rs.last();
    for (int i=0; i<10; i++) {
        rs.first();
        rs.last();
        rs.next();
        rs.previous();
        rs.updateInt(1, primaryKey*10 +i);
        rs.updateInt(2, (-555 -i));
        rs.updateInt(3, (-777 -i));
        rs.updateRow();
    }
    rs.close();
    s.close();
}
 

/**
 * Various tests for JIRA-614: handling of rows which span QRYDTA blocks. What happens
 * when the SplitQRYDTA has to span 3+ blocks.
 * 
 * @throws SQLException
 */
public void testSpanQRYDTABlocks() throws SQLException {
    int[] lens = { 32672, 32672, 32672, 32672, };
    boolean[] useClob = { false, false, false, false, };
    createTable(BIG_TABLE_NAME, lens, useClob);

    String[] sa = { "a", "b", "c", "d", };
    insertOneRow(BIG_TABLE_NAME, sa, lens);

    String[] row = getStringArray(sa, lens);
    String[][] expected = { row, };
    validTable(expected, BIG_TABLE_NAME);

    insertOneRow(BIG_TABLE_NAME, sa, lens);
    insertOneRow(BIG_TABLE_NAME, sa, lens);

    expected = new String[][] { row, row, row, };
    validTable(expected, BIG_TABLE_NAME);

    String sql1 = getSelectSql(BIG_TABLE_NAME);
    Statement st = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = st.executeQuery(sql1);
    assertEquals("Before operation, row No. is 0.", 0, rs.getRow());
    rs.first();
    assertEquals("After calling first(), row No. is 1.", 1, rs.getRow());
    validSingleRow(row, useClob, rs);
    rs.next();
    assertEquals("After calling next(), row No. is 2.", 2, rs.getRow());
    validSingleRow(row, useClob, rs);
    rs.previous();
    assertEquals("After calling previous(), row No. is 1.", 1, rs.getRow());
    validSingleRow(row, useClob, rs);
    rs.last();
    assertEquals("After calling last(), row No. is 3.", 3, rs.getRow());
    validSingleRow(row, useClob, rs);
    rs.close();
    st.close();
}
 

/**
 * Tests that the code path for LOB locator release works fine for result
 * sets without LOBs.
 *
 * @throws SQLException if the test fails for some reason
 */
public void testNoLOBs()
        throws SQLException {
    // Test a forward only result set, with autocommit.
    Statement stmt = createStatement();
    ResultSet rs = stmt.executeQuery("select * from sys.systables");
    while (rs.next()) {
        // Do nothing, just iterate through.
    }
    rs.close();

    // Basic test checking that the scrollable result code path works.
    stmt = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                           ResultSet.CONCUR_READ_ONLY);
    getConnection().setAutoCommit(false);
    rs = stmt.executeQuery("select * from sys.systables");
    rs.absolute(3);
    while (rs.next()) {
        // Do nothing, just iterate through.
    }
    // Just navigate randomly.
    rs.previous();
    rs.absolute(2);
    rs.relative(2);
    rs.afterLast();
    rs.first();
    rs.next();
    rs.last();
    rs.beforeFirst();
    // Close the statement instead of the result set first.
    stmt.close();
    rs.close();
    rollback();
}
 

/**
 * Test update indexed records using scrollable updatable resultset 
 */
public void testSecondaryIndexKeyUpdate1()
    throws SQLException 
{
    
    Statement s = createStatement
        (ResultSet.TYPE_SCROLL_INSENSITIVE,
         ResultSet.CONCUR_UPDATABLE);
    s.setCursorName(getNextCursorName());
    ResultSet rs = s.executeQuery("select * from t1");
    
    rs.last();
    rs.next();
    int newKey = 0;
    while(rs.previous()) {
        // Update the secondary key of all rows
        rs.updateInt(2, newKey--);
        rs.updateRow();
    }
    PreparedStatement ps = prepareStatement
        ("select * from t1 where a=?");
    for (int i=0; i<recordCount; i++) {
        int key = -i;
        ps.setInt(1, key);
        ResultSet rs2 = ps.executeQuery();
        assertTrue("Expected query to have 1 row", rs2.next());
        println("T1: Read Tuple:(" + rs2.getInt(1) + "," +
                rs2.getInt(2) + "," +
                rs2.getInt(3) + ")");
        assertEquals("Unexpected value of id", key, rs2.getInt(2));
        assertTrue("Did not expect more than 1 row, " +
                   "however rs2.next returned another row",
                   !rs2.next());
    }
    
    s.close();
    ps.close();
}
 

/**
 * Tests that the code path for LOB locator release works fine for result
 * sets without LOBs.
 *
 * @throws SQLException if the test fails for some reason
 */
public void testNoLOBs()
        throws SQLException {
    // Test a forward only result set, with autocommit.
    Statement stmt = createStatement();
    ResultSet rs = stmt.executeQuery("select * from sys.systables");
    while (rs.next()) {
        // Do nothing, just iterate through.
    }
    rs.close();

    // Basic test checking that the scrollable result code path works.
    stmt = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                           ResultSet.CONCUR_READ_ONLY);
    getConnection().setAutoCommit(false);
    rs = stmt.executeQuery("select * from sys.systables");
    rs.absolute(3);
    while (rs.next()) {
        // Do nothing, just iterate through.
    }
    // Just navigate randomly.
    rs.previous();
    rs.absolute(2);
    rs.relative(2);
    rs.afterLast();
    rs.first();
    rs.next();
    rs.last();
    rs.beforeFirst();
    // Close the statement instead of the result set first.
    stmt.close();
    rs.close();
    rollback();
}
 

/**
 * Tests a sequence of operations on a scrollable, updatable resultset.
 *
 * @throws SQLException if the test fails
 */
public void testScrollableUpdateWithLocators()
        throws SQLException {
    getConnection().setAutoCommit(false);
    Statement stmt = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                     ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = stmt.executeQuery(
            "select dBlob, dClob from LOBLOC_NO_NULLS");
    rs.absolute(3);
    Clob c1 = rs.getClob(2);
    final int origLength = (int)c1.length();
    final String origContent = c1.getSubString(1, origLength);
    // Do a change
    c1.setString(origLength, "FIRSTPASS");
    rs.absolute(7);
    rs.next();
    // Move back to row 3
    rs.absolute(3);
    Clob c2 = rs.getClob(2);
    assertEquals(origContent, c2.getSubString(1, (int)c2.length()));
    rs.updateRow(); // Should be a no-op
    rs.absolute(3);
    // Expect this to fail if the restriction that LOB columns cannot be
    // accessed more than once is enforced.
    Clob c3 = rs.getClob(2);
    assertEquals(origContent, c3.getSubString(1, (int)c3.length()));
    rs.previous();
    rs.next();
    Clob c4 = rs.getClob(2);
    final String newContent = "THIS IS THE NEW VALUE!";
    c4.setString(1, newContent);
    rs.updateClob(2, c4);
    rs.updateRow();
    c4.setString(1, "THIS IS NOT NOT NOT THE NEW VALUE!");
    rs.updateRow();
    rs.next();
    rs.absolute(3);
    Clob c5 = rs.getClob(2);
    assertEquals(newContent, c5.getSubString(1, (int)c5.length()));
    rollback();
    assertInvalid(c1);
    assertInvalid(c2);
    assertInvalid(c3);
    assertInvalid(c4);
    assertInvalid(c5);
}
 

/**
 * Test what happens if you update a tuple which has been modified by 
 * another transaction.
 **/
public void testUpdateModifiedTuple1()
    throws SQLException 
{
    getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                      ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = s.executeQuery("select * from t1");
    rs.next(); // Point to first tuple
    println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    int firstKey = rs.getInt(1);
    rs.next(); // Go to next
    println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    Connection con2 = openDefaultConnection();
    con2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    try {
        PreparedStatement ps2 = con2.prepareStatement
            ("update t1 set b=? where id=?");
        ps2.setInt(1, 999);
        ps2.setInt(2, firstKey);
        assertEquals("Expected one record to be updated", 1, 
                     ps2.executeUpdate());
        println("T2: Updated b=999 where id=" + firstKey);
        con2.commit();
        println("T2: commit");
    } catch (SQLException e) {
        con2.rollback();
        throw e;
    }
    rs.previous(); // Go back to first tuple
    println("T1: Read previous Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    rs.updateInt(2, 3);
    rs.updateRow();
    println("T1: updated column 2, to value=3");
    commit();
    println("T1: commit");
    rs = s.executeQuery("select * from t1");
    while (rs.next()) {
        println("T3: Read next Tuple:(" + rs.getInt(1) + "," +
                rs.getInt(2) + "," +
                rs.getInt(3) + ")");
        
    }
    con2.close();
}
 

/**
 * Test what happens if you update a deleted tuple using positioned update
 * (same as testUpdatePurgedTuple1, except here we use positioned updates)
 **/
public void testUpdatePurgedTuple2()
    throws SQLException 
{
    getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                      ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = s.executeQuery("select * from t1");
    rs.next(); // Point to first tuple
    println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    int firstKey = rs.getInt(1);
    rs.next(); // Go to next
    println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    Connection con2 = openDefaultConnection();
    con2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    try {
        PreparedStatement ps2 = con2.prepareStatement
            ("delete from t1 where id=?");
        ps2.setInt(1, firstKey);
        assertEquals("Expected one record to be deleted", 1, 
                     ps2.executeUpdate());
        println("T2: Deleted record with id=" + firstKey);
        con2.commit();
        println("T2: commit");
    } catch (SQLException e) {
        con2.rollback();
        throw e;
    }
    rs.previous(); // Go back to first tuple
    println("T1: Read previous Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    
    PreparedStatement ps = prepareStatement
        ("update T1 set a=? where current of " + rs.getCursorName());
    ps.setInt(1, 3);
    int updateCount = ps.executeUpdate();
    println("T1: update table, set a=3 where current of " + 
            rs.getCursorName());
    println("T1: commit");
    commit();
    rs = s.executeQuery("select * from t1");
    while (rs.next()) {
        println("T3: Tuple:(" + rs.getInt(1) + "," +
                rs.getInt(2) + "," +
                rs.getInt(3) + ")");
        
    }
    
    con2.close();
}
 

/**
 * Test what happens if you update a deleted tuple using positioned update
 * (same as testUpdatePurgedTuple1, except here we use positioned updates)
 **/
public void testUpdatePurgedTuple2()
    throws SQLException 
{
    getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                      ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = s.executeQuery("select * from t1");
    rs.next(); // Point to first tuple
    println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    int firstKey = rs.getInt(1);
    rs.next(); // Go to next
    println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    Connection con2 = openDefaultConnection();
    con2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    try {
        PreparedStatement ps2 = con2.prepareStatement
            ("delete from t1 where id=?");
        ps2.setInt(1, firstKey);
        assertEquals("Expected one record to be deleted", 1, 
                     ps2.executeUpdate());
        println("T2: Deleted record with id=" + firstKey);
        con2.commit();
        println("T2: commit");
    } catch (SQLException e) {
        con2.rollback();
        throw e;
    }
    rs.previous(); // Go back to first tuple
    println("T1: Read previous Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    
    PreparedStatement ps = prepareStatement
        ("update T1 set a=? where current of " + rs.getCursorName());
    ps.setInt(1, 3);
    int updateCount = ps.executeUpdate();
    println("T1: update table, set a=3 where current of " + 
            rs.getCursorName());
    println("T1: commit");
    commit();
    rs = s.executeQuery("select * from t1");
    while (rs.next()) {
        println("T3: Tuple:(" + rs.getInt(1) + "," +
                rs.getInt(2) + "," +
                rs.getInt(3) + ")");
        
    }
    
    con2.close();
}
 

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

/**
 * Test what happens if you update a tuple which has been modified by 
 * another transaction (in this case the same column)
 **/
public void testUpdateModifiedTuple2()
    throws SQLException 
{
    getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                      ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = s.executeQuery("select * from t1");
    rs.next(); // Point to first tuple
    println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    int firstKey = rs.getInt(1);
    rs.next(); // Go to next
    println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    Connection con2 = openDefaultConnection();
    con2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    try {
        PreparedStatement ps2 = con2.prepareStatement
            ("update t1 set b=? where id=?");
        ps2.setInt(1, 999);
        ps2.setInt(2, firstKey);
        assertEquals("Expected one record to be updated", 1, 
                     ps2.executeUpdate());
        println("T2: Updated b=999 where id=" + firstKey);
        con2.commit();
        println("T2: commit");
    } catch (SQLException e) {
        con2.rollback();
        throw e;
    }
    rs.previous(); // Go back to first tuple
    println("T1: Read previous Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    rs.updateInt(3, 9999);
    rs.updateRow();
    println("T1: updated column 3, to value=9999");
    commit();
    println("T1: commit");
    rs = s.executeQuery("select * from t1");
    while (rs.next()) {
        println("T3: Read next Tuple:(" + rs.getInt(1) + "," +
                rs.getInt(2) + "," +
                rs.getInt(3) + ")");
        
    }
    con2.close();
}
 

/**
 * Positive tests with scrollable read-only.
 */
public void testOffsetFetchFirstReadOnlyScrollableRS()
        throws Exception
{
    Statement stm = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                    ResultSet.CONCUR_READ_ONLY);
    ResultSet   rs;
    String[]    variants;

    /*
     * offset 0 rows (a no-op), update a row and verify result
     */
    variants = makeVariants( "select * from t1 %", FIRST_ROWS_ONLY, "0", null );
    for ( int i = 0; i < variants.length; i++ )
    {
        rs = stm.executeQuery( variants[ i ] );
        rs.next();
        rs.next(); // at row 2
        assertTrue(rs.getInt(2) == 2);
        rs.close();
    }
    
    /*
     * offset 1 rows, fetch 3 row, check that we have the right ones
     */
    variants = makeVariants( "select * from t1 %", FIRST_ROWS_ONLY, "1", "3" );
    for ( int i = 0; i < variants.length; i++ )
    {
        rs = stm.executeQuery( variants[ i ] );
        rs.next();
        rs.next(); // at row 2, but row 3 of underlying rs

        assertTrue(rs.getInt(2) == 3);

        // Go backbards and update
        rs.previous();
        assertTrue(rs.getInt(2) == 2);

        // Try some navigation and border conditions
        rs.previous();
        assertTrue(rs.isBeforeFirst());
        rs.next();
        rs.next();
        rs.next();
        rs.next();
        assertTrue(rs.isAfterLast());
    }
    
    stm.close();
}
 

/**
 * Test what happens if you update a deleted tuple using positioned update
 * (same as testUpdatePurgedTuple1, except here we use positioned updates)
 **/
public void testUpdatePurgedTuple2()
    throws SQLException 
{
    getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                      ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = s.executeQuery("select * from t1");
    rs.next(); // Point to first tuple
    println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    int firstKey = rs.getInt(1);
    rs.next(); // Go to next
    println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    Connection con2 = openDefaultConnection();
    con2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    try {
        PreparedStatement ps2 = con2.prepareStatement
            ("delete from t1 where id=?");
        ps2.setInt(1, firstKey);
        assertEquals("Expected one record to be deleted", 1, 
                     ps2.executeUpdate());
        println("T2: Deleted record with id=" + firstKey);
        con2.commit();
        println("T2: commit");
    } catch (SQLException e) {
        con2.rollback();
        throw e;
    }
    rs.previous(); // Go back to first tuple
    println("T1: Read previous Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    
    PreparedStatement ps = prepareStatement
        ("update T1 set a=? where current of " + rs.getCursorName());
    ps.setInt(1, 3);
    int updateCount = ps.executeUpdate();
    println("T1: update table, set a=3 where current of " + 
            rs.getCursorName());
    println("T1: commit");
    commit();
    rs = s.executeQuery("select * from t1");
    while (rs.next()) {
        println("T3: Tuple:(" + rs.getInt(1) + "," +
                rs.getInt(2) + "," +
                rs.getInt(3) + ")");
        
    }
    
    con2.close();
}
 

/**
 * Test what happens if you update a tuple which has been modified by 
 * another transaction.
 **/
public void testUpdateModifiedTuple1()
    throws SQLException 
{
    getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                      ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = s.executeQuery("select * from t1");
    rs.next(); // Point to first tuple
    println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    int firstKey = rs.getInt(1);
    rs.next(); // Go to next
    println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    Connection con2 = openDefaultConnection();
    con2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    try {
        PreparedStatement ps2 = con2.prepareStatement
            ("update t1 set b=? where id=?");
        ps2.setInt(1, 999);
        ps2.setInt(2, firstKey);
        assertEquals("Expected one record to be updated", 1, 
                     ps2.executeUpdate());
        println("T2: Updated b=999 where id=" + firstKey);
        con2.commit();
        println("T2: commit");
    } catch (SQLException e) {
        con2.rollback();
        throw e;
    }
    rs.previous(); // Go back to first tuple
    println("T1: Read previous Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    rs.updateInt(2, 3);
    rs.updateRow();
    println("T1: updated column 2, to value=3");
    commit();
    println("T1: commit");
    rs = s.executeQuery("select * from t1");
    while (rs.next()) {
        println("T3: Read next Tuple:(" + rs.getInt(1) + "," +
                rs.getInt(2) + "," +
                rs.getInt(3) + ")");
        
    }
    con2.close();
}
 

/**
 * Test what happens if you update a tuple which has been modified by 
 * another transaction (in this case the same column)
 **/
public void testUpdateModifiedTuple2()
    throws SQLException 
{
    getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                      ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = s.executeQuery("select * from t1");
    rs.next(); // Point to first tuple
    println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    int firstKey = rs.getInt(1);
    rs.next(); // Go to next
    println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    Connection con2 = openDefaultConnection();
    con2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    try {
        PreparedStatement ps2 = con2.prepareStatement
            ("update t1 set b=? where id=?");
        ps2.setInt(1, 999);
        ps2.setInt(2, firstKey);
        assertEquals("Expected one record to be updated", 1, 
                     ps2.executeUpdate());
        println("T2: Updated b=999 where id=" + firstKey);
        con2.commit();
        println("T2: commit");
    } catch (SQLException e) {
        con2.rollback();
        throw e;
    }
    rs.previous(); // Go back to first tuple
    println("T1: Read previous Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    rs.updateInt(3, 9999);
    rs.updateRow();
    println("T1: updated column 3, to value=9999");
    commit();
    println("T1: commit");
    rs = s.executeQuery("select * from t1");
    while (rs.next()) {
        println("T3: Read next Tuple:(" + rs.getInt(1) + "," +
                rs.getInt(2) + "," +
                rs.getInt(3) + ")");
        
    }
    con2.close();
}