/*
 *
 * Derby - Class SURTest
 *
 * 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.jdbcapi;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.util.Iterator;
import junit.extensions.TestSetup;
import junit.framework.Test;
import org.apache.derbyTesting.functionTests.util.SQLStateConstants;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.TestConfiguration;

/**
 * Tests for variants of scrollable updatable resultsets.
 *
 */
public class SURTest extends SURBaseTest {
    
    /** Creates a new instance of SURTest */
    public SURTest(String name) {
        super(name);
    }

    /**
     * Test that you get a warning when specifying a query which is not
     * updatable and concurrency mode CONCUR_UPDATABLE.
     * In this case, the query contains an "order by"
     */
    public void testConcurrencyModeWarning1()
        throws SQLException 
    {
        Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                          ResultSet.CONCUR_UPDATABLE);
        s.setCursorName(getNextCursorName());
        ResultSet rs = s.executeQuery("select * from t1 order by a");
        
        SQLWarning warn = rs.getWarnings();
        assertEquals("Expected resultset to be read only",
                     ResultSet.CONCUR_READ_ONLY,
                     rs.getConcurrency());
        assertWarning(warn, QUERY_NOT_QUALIFIED_FOR_UPDATABLE_RESULTSET);
        scrollForward(rs);
        rs.close();
        s.close();
    }
    
    /**
     * Test that you get a warning when specifying a query which is not
     * updatable and concurrency mode CONCUR_UPDATABLE.
     * In this case, the query contains a join.
     */
    public void testConcurrencyModeWarning2()
        throws SQLException 
    {
        Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                          ResultSet.CONCUR_UPDATABLE);
        s.setCursorName(getNextCursorName());
        ResultSet rs = s.executeQuery
            ("select * from t1 as table1,t1 as table2 where " +
             "table1.a=table2.a");
        
        SQLWarning warn = rs.getWarnings();
        assertEquals("Expected resultset to be read only",
                     ResultSet.CONCUR_READ_ONLY,
                     rs.getConcurrency());
        assertWarning(warn, QUERY_NOT_QUALIFIED_FOR_UPDATABLE_RESULTSET);
        scrollForward(rs);
        rs.close();
        s.close();
    }
    
    /**
     * Test that you get an exception when specifying update clause
     * "FOR UPDATE"
     * along with a query which is not updatable.
     * In this case, the query contains and order by.
     */
    public void testForUpdateException1()
        throws SQLException 
    {
        Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                          ResultSet.CONCUR_UPDATABLE);
        try {
            String queryString =
                "select * from t1 order by a for update";
            s.setCursorName(getNextCursorName());
            ResultSet rs = s.executeQuery(queryString);
            
            assertTrue("Expected query '" + queryString +
                       "' to fail", false);
        } catch (SQLException e) {
            assertEquals("Unexpected SQLState", 
                         FOR_UPDATE_NOT_PERMITTED_SQL_STATE,
                         e.getSQLState());
        }
        rollback();
        s.close();
    }
    
    /**
     * Test that you get an exception when specifying update clause
     * "FOR UPDATE" along with a query which is not updatable.
     * In this case, the query contains a join
     */
    public void testForUpdateException2()
        throws SQLException 
    {
        Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                          ResultSet.CONCUR_UPDATABLE);
        try {
            String queryString =
                "select * from t1 as table1,t1 as table2" +
                " where table1.a=table2.a for update";
            s.setCursorName(getNextCursorName());
            ResultSet rs = s.executeQuery(queryString);
            
            assertTrue("Expected query '" + queryString + "' to fail",
                       false);
        } catch (SQLException e) {
            assertEquals("Unexpected SQLState", 
                         FOR_UPDATE_NOT_PERMITTED_SQL_STATE,
                         e.getSQLState());
        }
        rollback();
        s.close();
    }
    
    /**
     * Test that you can scroll forward and read all records in the
     * ResultSet
     */
    public void testForwardOnlyReadOnly1()
        throws SQLException 
    {
        Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                          ResultSet.CONCUR_READ_ONLY);
        s.setCursorName(getNextCursorName());
        ResultSet rs = s.executeQuery("select * from t1");
        
        scrollForward(rs);
        rs.close();
        s.close();
    }
    
    
    /**
     * Test that you get an exception if you try to update a ResultSet
     * with concurrency mode CONCUR_READ_ONLY.
     */
    public void testFailOnUpdateOfReadOnlyResultSet1()
        throws SQLException 
    {
        Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                          ResultSet.CONCUR_READ_ONLY);
        s.setCursorName(getNextCursorName());
        ResultSet rs = s.executeQuery("select * from t1");
        
        rs.next();
        assertFailOnUpdate(rs);
        s.close();
    }
    
    /**
     * Test that you get an exception when attempting to update a
     * ResultSet which has been downgraded to a read only ResultSet.
     */
    public void testFailOnUpdateOfReadOnlyResultSet2()
        throws SQLException 
    {
        Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                          ResultSet.CONCUR_UPDATABLE);
        s.setCursorName(getNextCursorName());
        ResultSet rs = s.executeQuery("select * from t1 order by id");
        
        rs.next();
        assertFailOnUpdate(rs);
        s.close();
    }
    
    /**
     * Test that you get an exception when attempting to update a
     * ResultSet which has been downgraded to a read only ResultSet.
     */
    public void testFailOnUpdateOfReadOnlyResultSet3()
        throws SQLException 
    {
        Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                          ResultSet.CONCUR_UPDATABLE);
        s.setCursorName(getNextCursorName());
        ResultSet rs =
            s.executeQuery("select * from t1 for read only");
        
        rs.next();
        assertFailOnUpdate(rs);
        s.close();
    }
    
    /**
     * Test that you get an exception when attempting to update a
     * ResultSet which has been downgraded to a read only ResultSet.
     */
    public void testFailOnUpdateOfReadOnlyResultSet4()
        throws SQLException 
    {
        Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                          ResultSet.CONCUR_UPDATABLE);
        s.setCursorName(getNextCursorName());
        ResultSet rs = s.executeQuery
            ("select * from t1 where a=1 for read only");
        
        rs.next();
        verifyTuple(rs);
        assertFailOnUpdate(rs);
        s.close();
    }
    
    
    /**
     * Test that you get an exception if you try to update a ResultSet
     * with concurrency mode CONCUR_READ_ONLY.
     */
    public void testFailOnUpdateOfReadOnlyResultSet5()
        throws SQLException 
    {
        Statement s = createStatement(ResultSet.
                                          TYPE_SCROLL_INSENSITIVE,
                                          ResultSet.CONCUR_READ_ONLY);
        s.setCursorName(getNextCursorName());
        ResultSet rs = s.executeQuery
            ("select * from t1 where a=1 for read only");
        
        rs.next();
        verifyTuple(rs);
        assertFailOnUpdate(rs);
        s.close();
    }

    /** 
     * Test that when doing an update immediately after
     * a commit, the update fails, because the cursor has been 
     * postioned between the current row and the next row.
     * The test uses a FORWARD_ONLY resultset and ResultSet update methods
     * when doing the update.
     */
    public void testCursorStateAfterCommit1() 
        throws SQLException
    {
        testCursorStateAfterCommit(false, ResultSet.TYPE_FORWARD_ONLY);
    }

    /** 
     * Test that when doing an update immediately after
     * a commit, the update fails, because the cursor has been 
     * postioned between the current row and the next row.
     * The test uses a SCROLL_INSENSITIVE resultset and ResultSet update methods
     * when doing the update.
     */
    public void testCursorStateAfterCommit2() 
        throws SQLException
    {
        testCursorStateAfterCommit(false, ResultSet.TYPE_SCROLL_INSENSITIVE);
    }
    
     /** 
     * Test that when doing an update immediately after
     * a commit, the update fails, because the cursor has been 
     * postioned between the current row and the next row.
     * The test uses a FORWARD_ONLY resultset and positioned updates.
     */
    public void testCursorStateAfterCommit3() 
        throws SQLException
    {
        testCursorStateAfterCommit(true, ResultSet.TYPE_FORWARD_ONLY);
    }

    /** 
     * Test that when doing an update immediately after
     * a commit, the update fails, because the cursor has been 
     * postioned between the current row and the next row.
     * The test uses a SCROLL_INSENSITIVE resultset and positioned updates.
     */
    public void testCursorStateAfterCommit4() 
        throws SQLException
    {
        testCursorStateAfterCommit(true, ResultSet.TYPE_SCROLL_INSENSITIVE);
    }
    
    /** 
     * Test that when doing an update immediately after
     * a commit, the update fails, because the cursor has been 
     * postioned between the current row and the next row.
     * If the cursor gets repositioned, it allows an update.
     * @param positioned true to use positioned update, otherwise use 
     *                   ResultSet.updateRow()
     * @param resultSetType type of result set (as in ResultSet.getType())
     */
    private void testCursorStateAfterCommit(final boolean positioned, 
                                            final int resultSetType) 
        throws SQLException
    {
        final Statement s = createStatement(resultSetType, 
                                                ResultSet.CONCUR_UPDATABLE);
        final String cursorName = getNextCursorName();
        s.setCursorName(cursorName);
        
        final ResultSet rs = s.executeQuery("select a from t1");
        final int recordToUpdate = 5;
        
        if (resultSetType==ResultSet.TYPE_FORWARD_ONLY) {
            for (int i = 0; i < recordToUpdate; i++) {
                rs.next();
            }
        } else {
            rs.absolute(recordToUpdate);
        }
        
        commit();
        
        PreparedStatement ps = 
            prepareStatement("update t1 set a=? where current of " +
                                 cursorName);
        // First: check that we get an exception on update without repositioning:
        try {
            if (positioned) {
                ps.setInt(1, -1);
                ps.executeUpdate();                
                fail("Expected exception to be thrown on positioned update " + 
                     "since cursor is not positioned");
            } else {
                rs.updateInt(1, -1);
                rs.updateRow();
                fail("Expected exception to be thrown on updateRow() since " +
                     "cursor is not positioned");
            }
        } catch (SQLException e) {
            assertSQLState("Unexpected SQLState when updating row after commit",
                           SQLStateConstants.INVALID_CURSOR_STATE_NO_SUBCLASS,
                           e);
        }
        
        // Check that we after a repositioning can update:
        if (resultSetType==ResultSet.TYPE_FORWARD_ONLY) {
            rs.next();
        } else {
            rs.relative(0);
        }
        if (positioned) {
            ps.setInt(1, -1);
            ps.executeUpdate();                
        } else {
            rs.updateInt(1, -1);
            rs.updateRow();
        }
        
        s.close();
        ps.close();
        
    }

    /**
     * Test that you can correctly run multiple updateXXX() + updateRow() 
     * combined with cancelRowUpdates().
     */
    public void testMultiUpdateRow1() 
        throws SQLException 
    {
        Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                          ResultSet.CONCUR_UPDATABLE);
        s.setCursorName(getNextCursorName());
        ResultSet rs = s.executeQuery("select * from t1");
        rs.absolute(5);
        final int oldCol2 = rs.getInt(2);
        final int newCol2 = -2222;
        final int oldCol3 = rs.getInt(3);
        final int newCol3 = -3333;
                
        rs.updateInt(2, newCol2);
        assertEquals("Expected the resultset to be updated after updateInt",
                     newCol2, rs.getInt(2));
        rs.cancelRowUpdates();
        assertEquals("Expected updateXXX to have no effect after cancelRowUpdated",
                     oldCol2, rs.getInt(2));
        rs.updateInt(2, newCol2);
        assertEquals("Expected the resultset to be updated after updateInt", 
                     newCol2, rs.getInt(2));
        assertTrue("Expected rs.rowUpdated() to be false before updateRow", 
                   !rs.rowUpdated());
        rs.updateRow();
        
        assertTrue("Expected rs.rowUpdated() to be true after updateRow", 
                   rs.rowUpdated());
        assertEquals("Expected the resultset detect the updates of previous " + 
                     "updateRow", newCol2, rs.getInt(2));
        
        rs.updateInt(3, newCol3);
        
        assertEquals("Expected the resultset to be updated after updateInt", 
                     newCol3, rs.getInt(3));
        assertEquals("Expected the resultset detect the updates of previous " + 
                     "updateRow", newCol2, rs.getInt(2));
        
        rs.cancelRowUpdates();
        
        assertEquals("Expected updateXXX to have no effect after " +
                     "cancelRowUpdated", oldCol3, rs.getInt(3));
        assertEquals("Expected the resultset detect the updates of previous " +
                     "updateRow after cancelRowUpdated", newCol2, rs.getInt(2));
        rs.updateInt(3, newCol3);
        rs.updateRow();
        assertEquals("Expected the resultset to be updated after updateInt", 
                     newCol3, rs.getInt(3));
        rs.cancelRowUpdates();
        
        assertEquals("Expected the resultset detect the updates of previous" + 
                     "updateRow after cancelRowUpdates", newCol2, rs.getInt(2));
        assertEquals("Expected the resultset detect the updates of previous" + 
                     "updateRow after cancelRowUpdates", newCol3, rs.getInt(3));
        assertTrue("Expected rs.rowUpdated() to be true after " + 
                   "updateRow and cancelRowUpdates", rs.rowUpdated());
        
        rs.close();
        s.close();
    }

    /**
     * Test that you can correctly run multiple updateNull() + updateRow() 
     * combined with cancelRowUpdates().
     */
    public void testMultiUpdateRow2() 
        throws SQLException 
    {
        Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                          ResultSet.CONCUR_UPDATABLE);
        s.setCursorName(getNextCursorName());
        ResultSet rs = s.executeQuery("select * from t1");
        rs.absolute(5);
        final int oldCol2 = rs.getInt(2);
        final int oldCol3 = rs.getInt(3);
        
        rs.updateNull(2);
        assertEquals("Expected the resultset to be updated after updateNull",
                     0, rs.getInt(2));
        assertTrue("Expected wasNull to be true after updateNull", rs.wasNull());
        rs.cancelRowUpdates();
        assertEquals("Expected updateXXX to have no effect after cancelRowUpdated",
                     oldCol2, rs.getInt(2));
        rs.updateNull(2);
        assertEquals("Expected the resultset to be updated after updateNull", 
                     0, rs.getInt(2));
        assertTrue("Expected wasNull to be true after updateNull", rs.wasNull());
        assertTrue("Expected rs.rowUpdated() to be false before updateRow", 
                   !rs.rowUpdated());
        rs.updateRow();
        
        assertTrue("Expected rs.rowUpdated() to be true after updateRow", 
                   rs.rowUpdated());
        assertEquals("Expected the resultset detect the updates of previous " + 
                     "updateRow", 0, rs.getInt(2));
        
        rs.updateNull(3);
        
        assertEquals("Expected the resultset to be updated after updateNull", 
                     0, rs.getInt(3));
        assertTrue("Expected wasNull to be true after updateNull", rs.wasNull());
        assertEquals("Expected the resultset detect the updates of previous " + 
                     "updateRow", 0, rs.getInt(2));
        
        rs.cancelRowUpdates();
        
        assertEquals("Expected updateXXX to have no effect after " +
                     "cancelRowUpdated", oldCol3, rs.getInt(3));
        assertEquals("Expected the resultset detect the updates of previous " +
                     "updateRow after cancelRowUpdated", 0, rs.getInt(2));
        rs.updateNull(3);
        rs.updateRow();
        assertEquals("Expected the resultset to be updated after updateNull", 
                     0, rs.getInt(3));
        rs.cancelRowUpdates();
        
        assertEquals("Expected the resultset detect the updates of previous" + 
                     "updateRow after cancelRowUpdates", 0, rs.getInt(2));
        assertEquals("Expected the resultset detect the updates of previous" + 
                     "updateRow after cancelRowUpdates", 0, rs.getInt(3));
        assertTrue("Expected rs.rowUpdated() to be true after " + 
                   "updateRow and cancelRowUpdates", rs.rowUpdated());
        
        rs.close();
        s.close();
    }

    /**
     * Test that you get cursor operation conflict warning if updating 
     * a row which has been deleted from the table.
     */
    public void testCursorOperationConflictWarning1() 
        throws SQLException 
    {
        Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                          ResultSet.CONCUR_UPDATABLE);
        s.setCursorName(getNextCursorName());
        ResultSet rs = s.executeQuery("select * from t1");
        rs.next();
        createStatement().executeUpdate("delete from t1 where id=" +
                                            rs.getString("ID"));
        final int newValue = -3333;
        final int oldValue = rs.getInt(2);
        rs.updateInt(2, newValue);
        rs.updateRow();
        
        SQLWarning warn = rs.getWarnings();
        assertWarning(warn, CURSOR_OPERATION_CONFLICT);
        assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
        assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
        assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
        
        rs.clearWarnings();
        rs.deleteRow();
        warn = rs.getWarnings();
        assertWarning(warn, CURSOR_OPERATION_CONFLICT);
        rs.relative(0);
        assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
        assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
        assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
        
        rs.close();
        s.close();
    }

    /**
     * Test that you get cursor operation conflict warning if updating 
     * a row which has been deleted from the table, now using 
     * positioned updates / deletes.
     */
    public void testCursorOperationConflictWarning2() 
        throws SQLException 
    {
        Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                          ResultSet.CONCUR_UPDATABLE);
        s.setCursorName(getNextCursorName());
        ResultSet rs = s.executeQuery("select * from t1");
        rs.next();
        createStatement().executeUpdate ("delete from t1 where id=" +
                                             rs.getString("ID"));
        
        final int newValue = -3333;
        final int oldValue = rs.getInt(2);
        
        Statement s3 = createStatement();
        int updateCount = s3.executeUpdate
            ("update t1 set A=" + newValue + 
             " where current of " + rs.getCursorName());
        
        rs.relative(0);
        SQLWarning warn = s3.getWarnings();
        assertWarning(warn, CURSOR_OPERATION_CONFLICT);
        assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
        assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
        assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
        assertEquals("Expected update count to be 0", 0, updateCount);
        
        Statement s4 = createStatement();
        updateCount = s4.executeUpdate("delete from t1 where current of " +
                                       rs.getCursorName());
        
        rs.relative(0);
        warn = s4.getWarnings();
        assertWarning(warn, CURSOR_OPERATION_CONFLICT);
        assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
        assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
        assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
        assertEquals("Expected update count to be 0", 0, updateCount);
        
        rs.close();
        s.close();
        s3.close();
        s4.close();
    }
    
    /**
     * Test that you can scroll forward and update indexed records in
     * the ResultSet (not using FOR UPDATE)
     */
    public void testIndexedUpdateCursor1()
        throws SQLException 
    {
        Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                          ResultSet.CONCUR_UPDATABLE);
        s.setCursorName(getNextCursorName());
        ResultSet rs = s.executeQuery("select * from t1 where a=1");
        
        assertTrue("Expected to get a tuple on rs.next()", rs.next());
        verifyTuple(rs);
        updateTuple(rs);
        s.close();
        
    }
    
    /**
     *  Test that you can scroll forward and update indexed records
     *  in the ResultSet (using FOR UPDATE).
     */
    public void testIndexedUpdateCursor2()
        throws SQLException 
    {
        Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                          ResultSet.CONCUR_UPDATABLE);
        s.setCursorName(getNextCursorName());
        ResultSet rs =
            s.executeQuery("select * from t1 where a=1 for update");
        
        assertTrue("Expected to get a tuple on rs.next()", rs.next());
        verifyTuple(rs);
        updateTuple(rs);
        s.close();
    }
    
    /**
     * Tests that it is possible to move using positioning methods after
     * moveToInsertRow and that it is possible to delete a row after 
     * positioning back from insertRow. Also tests that it is possible to 
     * insert a row when positioned on insert row, that it is not possible
     * to update or delete a row from insertRow and that it also is not possible
     * to insert a row without being on insert row.
     */
    public void testInsertRowWithScrollCursor() throws SQLException {
        Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                          ResultSet.CONCUR_UPDATABLE);
        
        int currentPosition, lastRow;
        
        s.setCursorName(getNextCursorName());
        ResultSet rs =
            s.executeQuery("select * from t1");
        
        rs.last();
        lastRow = rs.getRow();
        
        rs.beforeFirst();
        
        rs.next();
        
        // Test that it is possible to move to next row from insertRow
        currentPosition = rs.getRow();
        rs.moveToInsertRow();
        rs.updateInt(1, currentPosition + 1000);
        rs.next();
        assertEquals("CurrentPosition should be " + (currentPosition + 1), 
                rs.getRow(), currentPosition + 1);
        // should be able to delete the row
        rs.deleteRow();

        // Test that it is possible to move using relative from insertRow
        currentPosition = rs.getRow();
        rs.moveToInsertRow();
        rs.updateInt(1, currentPosition + 1000);
        rs.relative(2);
        assertEquals("CurrentPosition should be " + (currentPosition + 2), 
                rs.getRow(), currentPosition + 2);
        // should be able to delete the row
        rs.deleteRow();

        // Test that it is possible to move using absolute from insertRow
        currentPosition = rs.getRow();
        rs.moveToInsertRow();
        rs.updateInt(1, currentPosition + 1000);
        rs.absolute(6);
        assertEquals("CurrentPosition should be 6", rs.getRow(), 6);
        // should be able to delete the row
        rs.deleteRow();

        // Test that it is possible to move to previous row from insertRow
        currentPosition = rs.getRow();
        rs.moveToInsertRow();
        rs.updateInt(1, currentPosition + 1000);
        rs.previous();
        assertEquals("CurrentPosition should be " + (currentPosition - 1), 
                rs.getRow(), currentPosition - 1);
        // should be able to delete the row
        rs.deleteRow();

        // Test that it is possible to move to first row from insertRow
        currentPosition = rs.getRow();
        rs.moveToInsertRow();
        rs.updateInt(1, currentPosition + 1000);
        rs.first();
        assertEquals("CurrentPosition should be 1", rs.getRow(), 1);
        assertTrue("isFirst() should return true", rs.isFirst());
        // should be able to delete the row
        rs.deleteRow();

        // Test that it is possible to move to last row from insertRow
        currentPosition = rs.getRow();
        rs.moveToInsertRow();
        rs.updateInt(1, currentPosition + 1000);
        rs.last();
        assertEquals("CurrentPosition should be " + lastRow, 
                rs.getRow(), lastRow);
        assertTrue("isLast() should return true", rs.isLast());
        // should be able to delete the row
        rs.deleteRow();

        // Test that it is possible to move beforeFirst from insertRow
        currentPosition = rs.getRow();
        rs.moveToInsertRow();
        rs.updateInt(1, currentPosition + 1000);
        rs.beforeFirst();
        assertTrue("isBeforeFirst() should return true", rs.isBeforeFirst());
        rs.next();
        assertEquals("CurrentPosition should be 1", rs.getRow(), 1);
        assertTrue("isFirst() should return true", rs.isFirst());

        // Test that it is possible to move afterLast from insertRow
        currentPosition = rs.getRow();
        rs.moveToInsertRow();
        rs.updateInt(1, currentPosition + 1000);
        rs.afterLast();
        assertTrue("isAfterLast() should return true", rs.isAfterLast());
        rs.previous();
        assertEquals("CurrentPosition should be " + lastRow, 
                rs.getRow(), lastRow);
        assertTrue("isLast() should return true", rs.isLast());

        // Test that it is possible to insert a row and move back to current row
        rs.previous();
        currentPosition = rs.getRow();
        rs.moveToInsertRow();
        rs.updateInt(1, currentPosition + 1000);
        rs.insertRow();
        rs.moveToCurrentRow();
        assertEquals("CurrentPosition should be " + currentPosition, 
                rs.getRow(), currentPosition);

        
        try {
            rs.moveToInsertRow();
            rs.updateInt(1, currentPosition + 2000);
            rs.updateRow();
        } catch (SQLException se) {
            assertEquals("Expected exception", 
                    se.getSQLState().substring(0, 5), 
                    INVALID_CURSOR_STATE_NO_CURRENT_ROW);
        }
        
        try {
            rs.moveToInsertRow();
            rs.updateInt(1, currentPosition + 2000);
            rs.deleteRow();
        } catch (SQLException se) {
            assertEquals("Expected exception", 
                    se.getSQLState().substring(0, 5), 
                    INVALID_CURSOR_STATE_NO_CURRENT_ROW);
        }
        
        try {
            rs.moveToCurrentRow();
            rs.updateInt(1, currentPosition + 2000);
            rs.insertRow();
        } catch (SQLException se) {
            assertEquals("Expected exception", 
                    se.getSQLState().substring(0, 5), 
                    CURSOR_NOT_POSITIONED_ON_INSERT_ROW);
        }
        
        rs.close();
        
        s.close();
    }
    
    /**
     *  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();
    }
    
    /**
     *  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 update of a keyed record using scrollable updatable
     * resultset.
     */
    public void testPrimaryKeyUpdate1()
        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();
        while(rs.previous()) {
            // Update the key of every second row.
            int key = rs.getInt(1);
            if (key%2==0) {
                int newKey = -key;
                rs.updateInt(1, newKey);
                rs.updateRow();
            }
        }
        PreparedStatement ps = prepareStatement
            ("select * from t1 where id=?");
        for (int i=0; i<recordCount; i++) {
            int key = (i%2==0) ? -i : 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(1));
            assertTrue("Did not expect more than 1 row, " +
                       "however rs2.next returned another row",
                       !rs2.next());
        }
        s.close();
        ps.close();
    }
        
    /**
     * Test update of a keyed record using other statement
     * object.
     */
    public void testOtherPrimaryKeyUpdate1()
        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 = 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());
        
        rs.updateInt(2, -555);
        rs.updateInt(3, -777);
        rs.updateRow();
        
        PreparedStatement ps2 = prepareStatement
            ("select * from t1 where id=?");
        ps2.setInt(1, -primaryKey);
        ResultSet rs2 = ps2.executeQuery();
        assertTrue("Expected query to have 1 row", rs2.next());
        println("T1: Read Tuple:(" + rs2.getInt(1) + "," +
                rs2.getInt(2) + "," +
                rs2.getInt(3) + ")");
        assertEquals("Expected a=-555", -555, rs2.getInt(2));
        assertEquals("Expected b=-777", -777, rs2.getInt(3));
        assertTrue("Did not expect more than 1 row, however " +
                   "rs2.next() returned another row", !rs2.next());
        
        
        s.close();
        ps.close();
        ps2.close();
    }
    
    /**
     * Test update of a keyed record using other both the
     * scrollable updatable resultset and using another statement
     * object.
     */
    public void testOtherAndOwnPrimaryKeyUpdate1()
        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 = 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());
        rs.updateInt(1, primaryKey*10);
        rs.updateInt(2, -555);
        rs.updateInt(3, -777);
        rs.updateRow();
        
        PreparedStatement ps2 =
            prepareStatement("select * from t1 where id=?");
        ps2.setInt(1, primaryKey*10);
        ResultSet rs2 = ps2.executeQuery();
        assertTrue("Expected query to have 1 row", rs2.next());
        println("T1: Read Tuple:(" + rs2.getInt(1) + "," +
                rs2.getInt(2) + "," +
                rs2.getInt(3) + ")");
        assertEquals("Expected a=-555", -555, rs2.getInt(2));
        assertEquals("Expected b=-777", -777, rs2.getInt(3));
        assertTrue("Did not expect more than 1 row, however " +
                   "rs2.next() returned another row", !rs2.next());
        
        s.close();
        ps.close();
        ps2.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();
    }
    
    /**
     * 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();
    }
    
    /**
     * Test update indexed records using other statement object
     * and using resultset.
     */
    public void testOtherSecondaryKeyUpdate1()
        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 indexedKey = rs.getInt(2);
        PreparedStatement ps =
            prepareStatement("update t1 set a = ? where a= ?");
        ps.setInt(1, -indexedKey);
        ps.setInt(2, indexedKey);
        assertEquals("Expected one row to be updated", 1,
                     ps.executeUpdate());
        
        rs.updateInt(1, -555);
        rs.updateInt(3, -777);
        rs.updateRow();
        
        PreparedStatement ps2 =
            prepareStatement("select * from t1 where a=?");
        ps2.setInt(1, -indexedKey);
        ResultSet rs2 = ps2.executeQuery();
        assertTrue("Expected query to have 1 row", rs2.next());
        println("T1: Read Tuple:(" + rs2.getInt(1) + "," +
                rs2.getInt(2) + "," +
                rs2.getInt(3) + ")");
        assertEquals("Expected id=-555", -555, rs2.getInt(1));
        assertEquals("Expected b=-777", -777, rs2.getInt(3));
        assertTrue("Did not expect more than 1 row, however " +
                   "rs2.next() returned another row", !rs2.next());
        
        s.close();
        ps.close();
        ps2.close();
    }
    
    /**
     * Test scrolling in a read only resultset
     */
    public void testScrollInsensitiveReadOnly1()
        throws SQLException 
    {
        Statement s = createStatement
            (ResultSet.TYPE_SCROLL_INSENSITIVE,
             ResultSet.CONCUR_READ_ONLY);
        s.setCursorName(getNextCursorName());
        ResultSet rs = s.executeQuery("select * from t1");
        
        scrollForward(rs);
        scrollBackward(rs);
        rs.close();
        s.close();
    }
    
    /**
     * Test updating a forward only resultset (with FOR UPDATE)
     */
    public void testForwardOnlyConcurUpdatableWithForUpdate1()
        throws SQLException 
    {
        Statement s = createStatement
            (ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
        s.setCursorName(getNextCursorName());
        ResultSet rs = s.executeQuery("select * from t1 for update");
        
        scrollForwardAndUpdate(rs);
        rs.close();
        s.close();
    }
    
    /**
     * Test updating a forward only resultset (without FOR UPDATE)
     */
    public void testForwardOnlyConcurUpdatableWithoutForUpdate1()
        throws SQLException 
    {
        Statement s = createStatement
            (ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
        s.setCursorName(getNextCursorName());
        ResultSet rs = s.executeQuery("select * from t1");
        
        scrollForwardAndUpdate(rs);
        rs.close();
        s.close();
    }
    
    /**
     * Test updating a forward only resultset (without FOR UPDATE)
     * and using positioned update
     */
    public void testPositionedUpdateWithoutForUpdate1()
        throws SQLException 
    {
        Statement s = createStatement
            (ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
        s.setCursorName("MYCURSOR");
        ResultSet rs = s.executeQuery("select * from t1");
        
        scrollForwardAndUpdatePositioned(rs);
        rs.close();
        s.close();
    }
    
    /**
     * Test updating a forward only resultset (with FOR UPDATE)
     * and using positioned update
     */
    public void testPositionedUpdateWithForUpdate1()
        throws SQLException 
    {
        Statement s = createStatement();
        s.setCursorName(getNextCursorName());
        ResultSet rs = s.executeQuery("select * from t1 for update");
        
        scrollForwardAndUpdatePositioned(rs);
        rs.close();
        s.close();
    }
    
    /**
     * Test positioned update of a scrollable resultset (with FOR UPDATE) 
     */
    public void testScrollablePositionedUpdateWithForUpdate1()
        throws SQLException 
    {
        Statement s = createStatement
            (ResultSet.TYPE_SCROLL_INSENSITIVE,
             ResultSet.CONCUR_READ_ONLY);
        s.setCursorName("MYCURSOR");
        ResultSet rs = s.executeQuery("select * from t1 for update");
        
        rs.next();
        int pKey = rs.getInt(1);
        rs.previous();
        rs.next();
        assertEquals("Expecting to be on the same row after previous() " + 
                     "+ next() ", pKey, rs.getInt(1));
        rs.next();
        rs.previous();
        assertEquals("Expecting to be on the same row after next() + " + 
                     "previous()", pKey, rs.getInt(1));
        final int previousA = rs.getInt(2);
        final int previousB = rs.getInt(3);
        println(rs.getCursorName());
        PreparedStatement ps = prepareStatement
            ("update T1 set a=?,b=? where current of " + rs.getCursorName());
        ps.setInt(1, 666);
        ps.setInt(2, 777);
        ps.executeUpdate();
        rs.next();
        rs.previous();
        assertEquals("Expected to be on the same row after next() + previous()",
                     pKey, rs.getInt(1));
        assertEquals("Expected row to be updated by own change, " + 
                     " however did not get updated value for column a", 
                     666, rs.getInt(2));
        assertEquals("Expected row to be updated by own change, however did " +
                     "not get updated value for column b", 777, rs.getInt(3));
        rs.close();
        s.setCursorName(getNextCursorName());
        rs = s.executeQuery("select * from t1 order by b");
        
        while (rs.next()) {
            if (rs.getInt(1)==pKey) {
                assertEquals("Expected row with primary key = " + pKey + 
                             " to be updated", 666, rs.getInt(2));
                assertEquals("Expected row with primary key = " + pKey + 
                             " to be updated", 777, rs.getInt(3));
            } else {
                println("Got tuple (" + rs.getInt(1) + "," + rs.getInt(2) + 
                        "," + rs.getInt(3) + "," + rs.getString(4)+ ")");
            }
        }

        s.close();
        ps.close();
    }
    
    /**
     * Test update of a scrollable resultset (with FOR UPDATE)
     * Only scrolling forward
     */
    public void testScrollInsensitiveConcurUpdatableWithForUpdate1()
        throws SQLException 
    {
        Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                          ResultSet.CONCUR_UPDATABLE);
        s.setCursorName(getNextCursorName());
        ResultSet rs = s.executeQuery("select * from t1 for update");
        scrollForwardAndUpdate(rs);
        rs.close();
        s.close();
    }
    
    /**
     * Test update of a scrollable resultset (with FOR UPDATE) 
     * Scrolling forward and backward.
     */
    public void testScrollInsensitiveConcurUpdatableWithForUpdate2()
        throws SQLException 
    {
        Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                          ResultSet.CONCUR_UPDATABLE);
        assertEquals("Invalid resultset concurrency on statement", 
                     ResultSet.CONCUR_UPDATABLE, s.getResultSetConcurrency());
        s.setCursorName(getNextCursorName());
        ResultSet rs = s.executeQuery("select * from t1 for update");
        
        assertEquals("Invalid resultset concurrency on resultset", 
                     ResultSet.CONCUR_UPDATABLE, rs.getConcurrency());
        scrollForward(rs);
        scrollBackwardAndUpdate(rs);
        rs.close();
        s.close();
    }
    
    /**
     * Test update of a scrollable resultset
     * Scrolling forward and backward. Then open another
     * resultset and verify the data.
     */
    private void testScrollInsensistiveConurUpdatable3(ResultSet rs) 
        throws SQLException 
    {
        while (rs.next()) {
        }
        while (rs.previous()) {
            int a = rs.getInt(1);
            int b = rs.getInt(2);
            int id = b - 17 - a;
            int newA = 1000;
            int newB = id + newA + 17;
            rs.updateInt(1, newA); // Set a to 1000
            rs.updateInt(2, newB); // Set b to checksum value
            rs.updateRow();
            
            assertEquals("Expected a to be 1000", 1000, rs.getInt(1));
        }
        int count = 0;
        while (rs.next()) {
            int a = rs.getInt(1);
            count++;
            assertEquals("Incorrect row updated for row " + count, 1000, a);
        }
        assertEquals("Expected count to be the same as number of records", 
                     recordCount, count);
        while (rs.previous()) {
            int a = rs.getInt(1);
            count--;
            assertEquals("Incorrect row updated for row " + count, 1000, a);
        }
        rs.close();
        Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY, 
                                          ResultSet.CONCUR_READ_ONLY);
        s.setCursorName(getNextCursorName());
        rs = s.executeQuery("select * from t1");
        
        while (rs.next()) {
            int id = rs.getInt(1);
            int a = rs.getInt(2);
            int b = rs.getInt(3);
            println("Updated tuple:" + id + "," + a + "," + b);
        }
        s.close();
    }
    
    /**
     * Test update of a scrollable resultset (with FOR UPDATE)
     * Scrolling forward and backward. Then open another
     * resultset and verify the data.
     */
    public void testScrollInsensitiveConcurUpdatableWithForUpdate3()
        throws SQLException 
    {
        Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                          ResultSet.CONCUR_UPDATABLE);
        s.setCursorName(getNextCursorName());
        ResultSet rs = s.executeQuery("select a,b from t1 for update");
        
        testScrollInsensistiveConurUpdatable3(rs);
        s.close();
    }
    
    /**
     * Test update of a scrollable resultset (without FOR UPDATE) 
     * Scrolling forward only
     */
    public void testScrollInsensitiveConcurUpdatableWithoutForUpdate1()
        throws SQLException 
    {
        Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                          ResultSet.CONCUR_UPDATABLE);
        s.setCursorName(getNextCursorName());
        ResultSet rs = s.executeQuery("select * from t1");
        
        scrollForwardAndUpdate(rs);
        rs.close();
        s.close();
    }
    
    /**
     * Test update of a scrollable resultset (without FOR UPDATE) 
     * Scrolling forward and backward.
     */
    public void testScrollInsensitiveConcurUpdatableWithoutForUpdate2()
        throws SQLException 
    {
        Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                          ResultSet.CONCUR_UPDATABLE);
        s.setCursorName(getNextCursorName());
        ResultSet rs = s.executeQuery("select * from t1");
        
        scrollForward(rs);
        scrollBackwardAndUpdate(rs);
        rs.close();
        s.close();
    }
    
    /**
     * Test update of a scrollable resultset (without FOR UPDATE)
     * Scrolling forward and backward. Then open another
     * resultset and verify the data.
     */
    public void testScrollInsensitiveConcurUpdatableWithoutForUpdate3()
        throws SQLException 
    {
        Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                          ResultSet.CONCUR_UPDATABLE);
        s.setCursorName(getNextCursorName());
        ResultSet rs = s.executeQuery("select a,b from t1");
        
        testScrollInsensistiveConurUpdatable3(rs);
        s.close();
    }
    
    /**
     * DERBY-4198 "When using the FOR UPDATE OF clause with SUR
     * (Scroll-insensive updatable result sets), the updateRow() method crashes"
     *
     * This bug revealed missing logic to handle the fact the the ExecRow
     * passed down to ScrollInsensitiveResultSet.updateRow does not always
     * contain all the rows of the basetable, cf. the logic of RowChangerImpl.
     * When an explicit list of columns is given as in FOR UPDATE OF
     * &lt;column-list&gt;, the ExecRow may contains a subset of the the base table
     * columns and ScrollInsensitiveResultSet was not ready to handle that.
     *
     * Test some of the cases which went wrong before the fix.
     *
     */
    public void testForUpdateWithColumnList() throws SQLException {
        Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                          ResultSet.CONCUR_UPDATABLE);

        // case a)
        ResultSet rs = s.executeQuery("select c from t1 for update of c");

        rs.next();
        rs.updateString(1,"foobar");
        rs.updateRow();
        rs.next();
        rs.previous();
        assertEquals("foobar", rs.getString(1));
        rs.close();

        // case b)
        rs = s.executeQuery("select id from t1 for update of id");
        rs.next();
        rs.updateInt(1,20);
        rs.updateRow();
        rs.next();
        rs.previous();
        assertEquals(20, rs.getInt(1));
        rs.close();

        // case c)
        rs = s.executeQuery("select * from t1 for update of id");
        rs.next();
        rs.updateInt(1,20);
        rs.updateRow();
        rs.next();
        rs.previous();
        assertEquals(20, rs.getInt(1));
        rs.close();

        // case d)
        rs = s.executeQuery("SELECT * from t1 for update of c");
        rs.next();
        int id = rs.getInt(1);
        int a =  rs.getInt(2);
        int b =  rs.getInt(3);
        rs.updateString(4,"foobar");
        rs.updateRow();
        rs.next();
        rs.previous();
        assertEquals(id, rs.getInt(1));
        assertEquals(a, rs.getInt(2));
        assertEquals(b, rs.getInt(3));
        assertEquals("foobar", rs.getString(4));
        rs.close();

        // case e)
        rs = s.executeQuery("SELECT * from t1 for update of id,a,b,c");
        rs.next();
        rs.updateInt(1, -20);
        rs.updateInt(2, 20);
        rs.updateInt(3, 21);
        rs.updateString(4,"foobar");
        rs.updateRow();
        rs.next();
        rs.previous();
        assertEquals(-20, rs.getInt(1));
        assertEquals(20, rs.getInt(2));
        assertEquals(21, rs.getInt(3));
        assertEquals("foobar", rs.getString(4));
        rs.close();

        // case f)
        rs = s.executeQuery("SELECT * from t1 for update of id, a,b,c");
        rs.next();
        rs.updateInt(1, 20);
        rs.updateRow();
        rs.next();
        rs.previous();
        assertEquals(20, rs.getInt(1));
        rs.close();

        // case h)
        rs = s.executeQuery("SELECT id from t1 for update of id, c");
           String cursorname = rs.getCursorName();
        rs.next();
           Statement s2 = createStatement();
        s2.executeUpdate("update t1 set c='foobar' where current of " +
                         cursorname);
        s2.close();
        rs.next();
        rs.previous();
        rs.getInt(1); // gives error state 22018 before fix
        rs.close();

        // case i)
        rs = s.executeQuery("SELECT id from t1 for update");
        cursorname = rs.getCursorName();
        rs.next();
        s2 = createStatement();
        s2.executeUpdate("update t1 set c='foobar' where current of " +
                         cursorname);
        s2.close();
        rs.next();
        rs.previous();
        rs.getInt(1); // ok before fix
        rs.close();

        // Odd cases: base row mentioned twice in rs, update 1st instance
        rs = s.executeQuery("SELECT id,a,id from t1");
        rs.next();
        rs.updateInt(1, 20);
        rs.updateRow();
        rs.next();
        rs.previous();
        assertEquals(20, rs.getInt(1));
        assertEquals(20, rs.getInt(3));
        rs.close();

        // Odd cases: base row mentioned twice in rs, update 2nd instance
        // with explicit column list; fails, see DERBY-4226.
        rs = s.executeQuery("SELECT id,a,id from t1 for update of id");
        rs.next();
        try {
            rs.updateInt(3, 20);
            fail("should fail");
        } catch (SQLException e) {
            String sqlState = usingEmbedded() ? "42X31" : "XJ124";
            assertSQLState(sqlState, e);
        }
        rs.close();

        // Odd cases: base row mentioned twice in rs, update 2nd instance
        // without explicit column list; works
        rs = s.executeQuery("SELECT id,a,id from t1 for update");
        rs.next();
        rs.updateInt(3, 20);
        rs.updateRow();
        assertEquals(20, rs.getInt(1));
        assertEquals(20, rs.getInt(3));
        rs.next();
        rs.previous();
        assertEquals(20, rs.getInt(1));
        assertEquals(20, rs.getInt(3));
        rs.close();

        s.close();
    }


    /**
     * Check that detectability methods throw the correct exception
     * when called in an illegal row state, that is, somehow not
     * positioned on a row. Minion of testDetectabilityExceptions.
     *
     * @param rs An open updatable result set.
     * @param state A string describing the illegal state.
     */
    private void checkDetectabilityCallsOutsideRow(ResultSet rs, 
                                                   String state)
    {
        boolean b;
        
        try {
            b = rs.rowUpdated();
            fail("rowUpdated while " + state + 
                 " did not throw exception: " + b);
        } catch (SQLException e) {
            assertEquals(e.getSQLState(),
                         INVALID_CURSOR_STATE_NO_CURRENT_ROW);
        }

        try {
            b = rs.rowDeleted();
            fail("rowdeleted while " + state + 
                 " did not throw exception: " + b);
        } catch (SQLException e) {
            assertEquals(e.getSQLState(),
                         INVALID_CURSOR_STATE_NO_CURRENT_ROW);
        }

        try {
            b = rs.rowInserted();
            fail("rowInserted while " + state + 
                 " did not throw exception: " + b);
        } catch (SQLException e) {
            assertEquals(e.getSQLState(),
                         INVALID_CURSOR_STATE_NO_CURRENT_ROW);
        }
    }


    /**
     * Test that rowUpdated() and rowDeleted() methods both return true when
     * the row has first been updated and then deleted using the updateRow()
     * and deleteRow() methods.
     */
    public void testRowUpdatedAndRowDeleted() throws SQLException {
        Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                           ResultSet.CONCUR_UPDATABLE);
        s.setCursorName(getNextCursorName());
        ResultSet rs = s.executeQuery("select a,b from t1");
        rs.next();
        rs.updateInt(1, rs.getInt(1) + 2 * recordCount);
        rs.updateRow();
        assertTrue("Expected rowUpdated() to return true", rs.rowUpdated());
        rs.deleteRow();
        rs.next();
        rs.previous();
        assertTrue("Expected rowUpdated() to return true", rs.rowUpdated());
        assertTrue("Expected rowDeleted() to return true", rs.rowDeleted());
        rs.next();
        assertFalse("Expected rowUpdated() to return false", rs.rowUpdated());
        assertFalse("Expected rowDeleted() to return false", rs.rowDeleted());
        rs.previous();
        assertTrue("Expected rowUpdated() to return true", rs.rowUpdated());
        assertTrue("Expected rowDeleted() to return true", rs.rowDeleted());
        rs.close();
        s.close();
    }


    /**
     * Test that the JDBC detectability calls throw correct exceptions when
     * called in in wrong row states. 
     * This is done for both supported updatable result set types.
     */
    public void testDetectabilityExceptions() throws SQLException 
    {
        Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                          ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = s.executeQuery("select * from t1");
        
        checkDetectabilityCallsOutsideRow(rs, "before positioning");

        rs.moveToInsertRow();
        checkDetectabilityCallsOutsideRow(rs, 
                                          "on insertRow before positioning");

        rs.next();
        rs.moveToInsertRow();
        checkDetectabilityCallsOutsideRow(rs, "on insertRow");
        rs.moveToCurrentRow(); // needed until to DERBY-1322 is fixed

        rs.beforeFirst();
        checkDetectabilityCallsOutsideRow(rs, "on beforeFirst row");

        rs.afterLast();
        checkDetectabilityCallsOutsideRow(rs, "on afterLast row");

        rs.first();
        rs.deleteRow();
        checkDetectabilityCallsOutsideRow(rs, "after deleteRow");

        rs.last();
        rs.deleteRow();
        checkDetectabilityCallsOutsideRow(rs, "after deleteRow of last row");

        rs.close();
        s.close();

        // Not strictly SUR, but fixed in same patch, so we test it here.
        s = createStatement(ResultSet.TYPE_FORWARD_ONLY, 
                                ResultSet.CONCUR_UPDATABLE);
        rs = s.executeQuery("select * from t1");

        checkDetectabilityCallsOutsideRow(rs, "before FO positioning");

        rs.moveToInsertRow();
        checkDetectabilityCallsOutsideRow(rs, 
                                          "on insertRow before FO positioning");

        rs.next();
        rs.moveToInsertRow();
        checkDetectabilityCallsOutsideRow(rs, "on FO insertRow");

        rs.next();
        rs.updateInt(2, 666);
        rs.updateRow();
        checkDetectabilityCallsOutsideRow(rs, "after FO updateRow");

        rs.next();
        rs.deleteRow();
        checkDetectabilityCallsOutsideRow(rs, "after FO deleteRow");

        while (rs.next()) {};
        checkDetectabilityCallsOutsideRow(rs, "after FO emptied out");

        rs.close();
        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();
    }


    /**
     * Get a cursor name. We use the same cursor name for all cursors.
     */
    private final String getNextCursorName() {
        return "MYCURSOR";
    }
    
    /**
     * Run the base suite in embedded and client mode.
     */
    public static Test suite() {
        BaseTestSuite mainSuite = new BaseTestSuite("SURTest");
        mainSuite.addTest(baseSuite("SURTest:embedded"));
        mainSuite.addTest(
                TestConfiguration.clientServerDecorator(baseSuite("SURTest:client")));
        return mainSuite;      
    }
    
    /**
     * The suite contains all testcases in this class running on different 
     * data models
     */
    private static Test baseSuite(String name) { 
        
        BaseTestSuite mainSuite = new BaseTestSuite(name);
        
        // Iterate over all data models and decorate the tests:
        for (Iterator i = SURDataModelSetup.SURDataModel.values().iterator();
             i.hasNext();) {
            
            SURDataModelSetup.SURDataModel model = 
                (SURDataModelSetup.SURDataModel) i.next();
            
            BaseTestSuite suite = new BaseTestSuite(SURTest.class);
            TestSetup decorator = new SURDataModelSetup
                (suite, model);
            
            mainSuite.addTest(decorator);    
        }
        
        return mainSuite;
    }
}