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

/**
 * Tests calling updateInt on all columns of the row.
 * @exception SQLException database access error. Causes test to 
 *                         fail with an error.
 */
public void testUpdateInt() 
    throws SQLException
{
    Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY, 
            ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = s.executeQuery(SELECT_STMT);
    rs.next();

    for (int i = 1; i <= COLUMNS; i++) {
        rs.updateInt(i, 2);
        assertEquals("Expected rs.getInt(" + i + 
                     ") to match updated value", 2, rs.getInt(i));
    }
    rs.updateRow();
    rs.close();
    checkColumnsAreUpdated();
    
    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();
}
 

/**
 * 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();
}
 

/**
 * Updates the current row in the ResultSet using updateRow()
 * @param rs ResultSet to be updated
 * @param meta meta for the ResultSet
 **/
private void updateRow(final ResultSet rs, final ResultSetMetaData meta) 
    throws SQLException
{
    for (int column = 1; column<=meta.getColumnCount(); column++) {
        if (meta.getColumnType(column)==Types.INTEGER) {
            // Set to negative value
            rs.updateInt(column, -rs.getInt(column));
        } else {
            rs.updateString(column, "UPDATED_" + rs.getString(column));
        }
    }
    rs.updateRow();
}
 

/**
 * 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 that the system does not purge any records
 * as long as we do either a defragment, or truncate
 **/
private void testCompressDuringScan(boolean testDefragment, 
                                    boolean testTruncate)
    throws SQLException 
{
    getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
    Statement delStatement = createStatement();
    // First delete all records except the last and first
    int deleted = delStatement.executeUpdate
        ("delete from T1 where id>0 and id<" + (recordCount-1));
    int expectedDeleted = recordCount-2;    
    println("T1: delete records");
    assertEquals("Invalid number of records deleted", expectedDeleted, 
                 deleted);
    delStatement.close();
    commit();
    println("T1: commit");
    
    Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                      ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = s.executeQuery("select * from t1");
    rs.next();
    int firstKey = rs.getInt(1);
    println("T2: Read next Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    int lastKey = firstKey;
    while (rs.next()) {
        lastKey = rs.getInt(1);
        println("T2: Read next Tuple:(" + rs.getInt(1) + "," +
                rs.getInt(2) + "," +
                rs.getInt(3) + ")");
    }
    
    final Connection con2 = openDefaultConnection();
    con2.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
    final PreparedStatement ps2 = con2.prepareStatement
        ("call SYSCS_UTIL.INPLACE_COMPRESS_TABLE(?,?,?,?,?)");
    ps2.setString(1, "APP"); // schema
    ps2.setString(2, "T1");  // table name
    ps2.setInt(3, 0); // purge
    int defragment = testDefragment ? 1 : 0;
    int truncate = testTruncate ? 1 : 0;
    ps2.setInt(4, defragment); // defragment rows
    ps2.setInt(5, truncate); // truncate end
    
    println("T3: call SYSCS_UTIL.INPLACE_COMPRESS_TABLE");
    println("T3: defragmenting rows");
    try { 
        ps2.executeUpdate();
        con2.commit();
        println("T3: commit");
        fail("Expected T3 to hang waiting for Table lock");
    } catch (SQLException e) {            
        println("T3: got expected exception");
        con2.rollback();            
    }
    ps2.close();
    rs.first(); // Go to first tuple
    println("T1: Read first Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    rs.updateInt(2, 3);
    println("T1: updateInt(2, 3);");
    rs.updateRow();        
    println("T1: updateRow()");
    rs.last(); // Go to last tuple
    println("T1: Read last Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    rs.updateInt(2, 3);
    println("T1: updateInt(2, 3);");
    rs.updateRow();
    println("T1: updateRow()");
    commit();
    println("T1: commit");
    rs = s.executeQuery("select * from t1");
    println("T4: select * from table");
    while (rs.next()) {
        println("T4: Read next Tuple:(" + rs.getInt(1) + "," +
                rs.getInt(2) + "," +
                rs.getInt(3) + ")");
    }
    con2.close();
    s.close();
}
 

@Test(expected = SQLFeatureNotSupportedException.class)
public void assertUpdateIntForColumnIndex() throws SQLException {
    for (ResultSet each : resultSets) {
        each.updateInt(1, 1);
    }
}
 

public void testQuotedCursorsUpdate() throws SQLException {
  Connection conn = getConnection();
  Statement stmt = conn.createStatement();
  
  stmt.executeUpdate("create table \"my table\" (x int)");
  stmt.executeUpdate("insert into \"my table\" values (1), (2), (3) ");
  
  stmt.close();
  
  stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
      ResultSet.CONCUR_UPDATABLE);
  stmt.setCursorName("\"\"my quoted cursor\"\" \"\"with quotes "
      + "in middle\"\"and last \"\"");
  ResultSet rs = stmt.executeQuery("select * from \"my table\"");
  rs.next();

  // remember which int was updated
  int updatedInt = rs.getInt(1);

  rs.updateInt(1, 4);
  rs.updateRow();
  rs.close();
  
  rs = stmt.executeQuery("select * from \"my table\" order by x");
  

  // in GemFireXD, queries are not guaranteed to return results
  // in the same order they were inserted, so changing this test
  // to not assume which x was updated
  List<Integer> expected = new ArrayList<Integer>(Arrays.asList(1, 2, 3));
  expected.remove((Integer)updatedInt);
  expected.add(4);
  
  for (int i=2; i<=4; i++) {
      assertTrue("there is a row", rs.next());
      assertTrue("row contains correct value",
               expected.remove((Integer)rs.getInt(1)));
  }
  assertTrue("table correct size", expected.isEmpty());
  
  rs.close();
  stmt.close();        
}
 

/**
 * Test that the system does not purge any records
 * as long as we do either a defragment, or truncate
 **/
private void testCompressDuringScan(boolean testDefragment, 
                                    boolean testTruncate)
    throws SQLException 
{
    getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
    Statement delStatement = createStatement();
    // First delete all records except the last and first
    int deleted = delStatement.executeUpdate
        ("delete from T1 where id>0 and id<" + (recordCount-1));
    int expectedDeleted = recordCount-2;    
    println("T1: delete records");
    assertEquals("Invalid number of records deleted", expectedDeleted, 
                 deleted);
    delStatement.close();
    commit();
    println("T1: commit");
    
    Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                      ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = s.executeQuery("select * from t1");
    rs.next();
    int firstKey = rs.getInt(1);
    println("T2: Read next Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    int lastKey = firstKey;
    while (rs.next()) {
        lastKey = rs.getInt(1);
        println("T2: Read next Tuple:(" + rs.getInt(1) + "," +
                rs.getInt(2) + "," +
                rs.getInt(3) + ")");
    }
    
    final Connection con2 = openDefaultConnection();
    con2.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
    final PreparedStatement ps2 = con2.prepareStatement
        ("call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(?,?,?,?,?)");
    ps2.setString(1, "SPLICE"); // schema
    ps2.setString(2, "T1");  // table name
    ps2.setInt(3, 0); // purge
    int defragment = testDefragment ? 1 : 0;
    int truncate = testTruncate ? 1 : 0;
    ps2.setInt(4, defragment); // defragment rows
    ps2.setInt(5, truncate); // truncate end
    
    println("T3: call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE");
    println("T3: defragmenting rows");
    try { 
        ps2.executeUpdate();
        con2.commit();
        println("T3: commit");
        fail("Expected T3 to hang waiting for Table lock");
    } catch (SQLException e) {            
        println("T3: got expected exception");
        con2.rollback();            
    }
    ps2.close();
    rs.first(); // Go to first tuple
    println("T1: Read first Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    rs.updateInt(2, 3);
    println("T1: updateInt(2, 3);");
    rs.updateRow();        
    println("T1: updateRow()");
    rs.last(); // Go to last tuple
    println("T1: Read last Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    rs.updateInt(2, 3);
    println("T1: updateInt(2, 3);");
    rs.updateRow();
    println("T1: updateRow()");
    commit();
    println("T1: commit");
    rs = s.executeQuery("select * from t1");
    println("T4: select * from table");
    while (rs.next()) {
        println("T4: Read next Tuple:(" + rs.getInt(1) + "," +
                rs.getInt(2) + "," +
                rs.getInt(3) + ")");
    }
    con2.close();
    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.
 * 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(expected = SQLFeatureNotSupportedException.class)
public void assertUpdateIntForColumnIndex() throws SQLException {
    for (ResultSet each : resultSets) {
        each.updateInt(1, 1);
    }
}
 

public void testSelectForUpdate_RR_key_not_in_projection() throws Exception {
  startVMs(2, 2);

  String jdbcSQL = "create table Employee "
      + "(firstname varchar(50) not null, lastname varchar(50) not null, "
      + "workdept varchar(50), bonus int not null, primary key (firstname)) replicate";
  
  clientSQLExecute(1, jdbcSQL);
  
  jdbcSQL = "insert into employee values('neeraj', 'kumar', 'rnd', 0), "
      + "('asif', 'shahid', 'rnd', 0), "
      + "('dada', 'ji', 'rnd', 0), ('sum', 'wale', 'rnd', 0)";

  clientSQLExecute(1, jdbcSQL);

  String sql = "SELECT workdept, bonus "
      + "FROM EMPLOYEE FOR UPDATE of BONUS";

  final java.sql.Connection conn = TestUtil.getConnection();

  Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);

  conn.setTransactionIsolation(getIsolationLevel());
  conn.setAutoCommit(false);

  stmt.execute(sql);

  String conflictSql = "update employee set workdept = 'xxx' "
      + "where lastname = 'kumar'";

  this.serverVMs.get(0).invoke(getClass(), "verifyUpdateConflict",
      new Object[] { conflictSql, Boolean.TRUE, getIsolationLevel() });

  this.serverVMs.get(1).invoke(getClass(), "verifyUpdateConflict",
      new Object[] { conflictSql, Boolean.TRUE, getIsolationLevel() });

  String noConflictSql = "update employee set workdept = 'xxx' "
      + "where lastname = 'wale'";

  this.serverVMs.get(0).invoke(getClass(), "verifyUpdateConflict",
      new Object[] { noConflictSql, Boolean.TRUE, getIsolationLevel() });

  this.serverVMs.get(1).invoke(getClass(), "verifyUpdateConflict",
      new Object[] { noConflictSql, Boolean.TRUE, getIsolationLevel() });

  this.serverVMs.get(0).invoke(getClass(), "installObserver");

  this.serverVMs.get(0).invoke(getClass(), "executeSelectForUpdateQuery",
      new Object[] { sql, Boolean.TRUE, getIsolationLevel() });

  ResultSet uprs = stmt.getResultSet();

  String theDept = "rnd";
  while (uprs.next()) {
    String workDept = uprs.getString("WORKDEPT");
    if (workDept.equals(theDept)) {
      uprs.updateInt("bonus", 10);
      uprs.updateRow();
    }
  }
  conn.commit();
  sql = "select * from employee";

  stmt.execute(sql);
  ResultSet rs = stmt.getResultSet();
  int cnt = 0;
  while(rs.next()) {
    cnt++;
    int bonus = rs.getInt(4);
    assertEquals(10, bonus);
  }
  assertEquals(4, cnt);
  
  conn.commit();
  sqlExecuteVerify(new int[] { 1 }, new int[] {1}, sql, goldenTextFile, "equal_bonus");
}
 

@ExpectWarning("SQL_BAD_RESULTSET_ACCESS")
void bug4(ResultSet any, int anyInt) throws SQLException {
    any.updateInt(0, anyInt);
}
 

/**
 * 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();
}
 

public void testQuotedCursorsUpdate() throws SQLException {
  Connection conn = getConnection();
  Statement stmt = conn.createStatement();
  
  stmt.executeUpdate("create table \"my table\" (x int)");
  stmt.executeUpdate("insert into \"my table\" values (1), (2), (3) ");
  
  stmt.close();
  
  stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
      ResultSet.CONCUR_UPDATABLE);
  stmt.setCursorName("\"\"my quoted cursor\"\" \"\"with quotes "
      + "in middle\"\"and last \"\"");
  ResultSet rs = stmt.executeQuery("select * from \"my table\"");
  rs.next();

  // remember which int was updated
  int updatedInt = rs.getInt(1);

  rs.updateInt(1, 4);
  rs.updateRow();
  rs.close();
  
  rs = stmt.executeQuery("select * from \"my table\" order by x");
  

  // in GemFireXD, queries are not guaranteed to return results
  // in the same order they were inserted, so changing this test
  // to not assume which x was updated
  List<Integer> expected = new ArrayList<Integer>(Arrays.asList(1, 2, 3));
  expected.remove((Integer)updatedInt);
  expected.add(4);
  
  for (int i=2; i<=4; i++) {
      assertTrue("there is a row", rs.next());
      assertTrue("row contains correct value",
               expected.remove((Integer)rs.getInt(1)));
  }
  assertTrue("table correct size", expected.isEmpty());
  
  rs.close();
  stmt.close();        
}
 

public void testSelectForUpdate_PR_key_not_in_projection() throws Exception {
  startVMs(2, 2);

  String jdbcSQL = "create table Employee "
      + "(firstname varchar(50) not null, lastname varchar(50) not null, "
      + "workdept varchar(50), bonus int not null, primary key (firstname))";

  clientSQLExecute(1, jdbcSQL);

  jdbcSQL = "insert into employee values('neeraj', 'kumar', 'rnd', 0), "
      + "('asif', 'shahid', 'rnd', 0), "
      + "('dada', 'ji', 'rnd', 0), ('sum', 'wale', 'rnd', 0)";

  clientSQLExecute(1, jdbcSQL);

  String sql = "SELECT workdept, bonus "
      + "FROM EMPLOYEE FOR UPDATE of BONUS";

  final java.sql.Connection conn = TestUtil.getConnection();

  Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);

  conn.setTransactionIsolation(getIsolationLevel());
  conn.setAutoCommit(false);
  stmt.execute(sql);

  this.serverVMs.get(0).invoke(getClass(), "installObserver");

  this.serverVMs.get(0).invoke(getClass(), "executeSelectForUpdateQuery",
      new Object[] { sql, Boolean.TRUE, getIsolationLevel() });

  ResultSet uprs = stmt.getResultSet();

  String theDept = "rnd";
  while (uprs.next()) {
    String workDept = uprs.getString("WORKDEPT");
    if (workDept.equals(theDept)) {
      uprs.updateInt("bonus", 10);
      uprs.updateRow();
    }
  }
  conn.commit();
  sql = "select * from employee";

  stmt.execute(sql);
  ResultSet rs = stmt.getResultSet();
  int cnt = 0;
  while (rs.next()) {
    cnt++;
    int bonus = rs.getInt(4);
    assertEquals(10, bonus);
  }
  assertEquals(4, cnt);

  conn.commit();
  sqlExecuteVerify(new int[] { 1 }, new int[] { 1 }, sql, goldenTextFile,
      "equal_bonus");
}
 

protected void runSelectForUpdate_PR_key_in_projection(final Connection conn)
    throws Exception {
  String jdbcSQL = "create table Employee "
      + "(firstname varchar(50) not null, lastname varchar(50) not null, "
      //+ "workdept varchar(50), bonus int not null, primary key (firstname, lastname))";
  + "workdept varchar(50), bonus int not null, primary key (firstname))";
  
  clientSQLExecute(1, jdbcSQL);

  jdbcSQL = "insert into employee values('neeraj', 'kumar', 'rnd', 0), "
      + "('asif', 'shahid', 'rnd', 0), "
      + "('dada', 'ji', 'rnd', 0), ('sum', 'wale', 'rnd', 0)";

  clientSQLExecute(1, jdbcSQL);

  String sql = "SELECT firstname, workdept, bonus "
      + "FROM EMPLOYEE FOR UPDATE of BONUS";

  Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);

  conn.setTransactionIsolation(getIsolationLevel());
  conn.setAutoCommit(false);

  stmt.execute(sql);

  this.serverVMs.get(0).invoke(getClass(), "executeSelectForUpdateQuery",
      new Object[] { sql, Boolean.TRUE, getIsolationLevel() });

  ResultSet uprs = stmt.getResultSet();

  String theDept = "rnd";
  while (uprs.next()) {
    String workDept = uprs.getString("WORKDEPT");
    if (workDept.equals(theDept)) {
      uprs.updateInt("bonus", 10);
      uprs.updateRow();
    }
  }
  conn.commit();
  sql = "select * from employee";

  stmt.execute(sql);
  ResultSet rs = stmt.getResultSet();
  int cnt = 0;
  while(rs.next()) {
    cnt++;
    int bonus = rs.getInt(4);
    assertEquals(10, bonus);
  }
  assertEquals(4, cnt);
  
  conn.commit();
  sqlExecuteVerify(new int[] { 1 }, new int[] {1}, sql, goldenTextFile, "equal_bonus");
}
 

public void testSelectForUpdate_RR_composite_key_partially_in_projection() throws Exception {
  startVMs(2, 2);

  String jdbcSQL = "create table Employee "
      + "(firstname varchar(50) not null, lastname varchar(50) not null, "
      + "workdept varchar(50), bonus int not null, primary key (firstname, lastname)) replicate";
  
  clientSQLExecute(1, jdbcSQL);
  
  jdbcSQL = "insert into employee values('neeraj', 'kumar', 'rnd', 0), "
      + "('asif', 'shahid', 'rnd', 0), "
      + "('dada', 'ji', 'rnd', 0), ('sum', 'wale', 'rnd', 0)";

  clientSQLExecute(1, jdbcSQL);

  String sql = "SELECT firstname, workdept, bonus "
      + "FROM EMPLOYEE FOR UPDATE of BONUS";

  final java.sql.Connection conn = TestUtil.getConnection();

  Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);

  conn.setTransactionIsolation(getIsolationLevel());
  conn.setAutoCommit(false);

  stmt.execute(sql);

  String conflictSql = "update employee set workdept = 'xxx' "
      + "where lastname = 'kumar'";

  this.serverVMs.get(0).invoke(getClass(), "verifyUpdateConflict",
      new Object[] { conflictSql, Boolean.TRUE, getIsolationLevel() });

  this.serverVMs.get(1).invoke(getClass(), "verifyUpdateConflict",
      new Object[] { conflictSql, Boolean.TRUE, getIsolationLevel() });

  String noConflictSql = "update employee set workdept = 'xxx' "
      + "where lastname = 'wale'";

  this.serverVMs.get(0).invoke(getClass(), "verifyUpdateConflict",
      new Object[] { noConflictSql, Boolean.TRUE, getIsolationLevel() });

  this.serverVMs.get(1).invoke(getClass(), "verifyUpdateConflict",
      new Object[] { noConflictSql, Boolean.TRUE, getIsolationLevel() });

  this.serverVMs.get(0).invoke(getClass(), "executeSelectForUpdateQuery",
      new Object[] { sql, Boolean.TRUE, getIsolationLevel() });

  ResultSet uprs = stmt.getResultSet();

  String theDept = "rnd";
  while (uprs.next()) {
    String workDept = uprs.getString("WORKDEPT");
    if (workDept.equals(theDept)) {
      uprs.updateInt("bonus", 10);
      uprs.updateRow();
    }
  }
  conn.commit();
  sql = "select * from employee";

  stmt.execute(sql);
  ResultSet rs = stmt.getResultSet();
  int cnt = 0;
  while(rs.next()) {
    cnt++;
    int bonus = rs.getInt(4);
    assertEquals(10, bonus);
  }
  assertEquals(4, cnt);
  
  conn.commit();
  sqlExecuteVerify(new int[] { 1 }, new int[] {1}, sql, goldenTextFile, "equal_bonus");
}
 

public void testSelectForUpdate_PR_composite_key_not_in_projection() throws Exception {
  startVMs(2, 2);

  String jdbcSQL = "create table Employee "
      + "(firstname varchar(50) not null, lastname varchar(50) not null, "
      + "workdept varchar(50), bonus int not null, primary key (firstname, lastname))";
  
  clientSQLExecute(1, jdbcSQL);
  
  jdbcSQL = "insert into employee values('neeraj', 'kumar', 'rnd', 0), "
      + "('asif', 'shahid', 'rnd', 0), "
      + "('dada', 'ji', 'rnd', 0), ('sum', 'wale', 'rnd', 0)";

  clientSQLExecute(1, jdbcSQL);

  String sql = "SELECT firstname, workdept, bonus "
      + "FROM EMPLOYEE FOR UPDATE of BONUS";

  final java.sql.Connection conn = TestUtil.getConnection();

  Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);

  conn.setTransactionIsolation(getIsolationLevel());
  conn.setAutoCommit(false);

  stmt.execute(sql);

  this.serverVMs.get(0).invoke(getClass(), "executeSelectForUpdateQuery",
      new Object[] { sql, Boolean.TRUE, getIsolationLevel() });

  ResultSet uprs = stmt.getResultSet();

  String theDept = "rnd";
  while (uprs.next()) {
    String workDept = uprs.getString("WORKDEPT");
    if (workDept.equals(theDept)) {
      uprs.updateInt("bonus", 10);
      uprs.updateRow();
    }
  }
  conn.commit();
  sql = "select * from employee";

  stmt.execute(sql);
  ResultSet rs = stmt.getResultSet();
  int cnt = 0;
  while(rs.next()) {
    cnt++;
    int bonus = rs.getInt(4);
    assertEquals(10, bonus);
  }
  assertEquals(4, cnt);
  
  conn.commit();
  sqlExecuteVerify(new int[] { 1 }, new int[] {1}, sql, goldenTextFile, "equal_bonus");
}
 

/**
	Set this value into a ResultSet for a subsequent ResultSet.insertRow
	or ResultSet.updateRow. This method will only be called for non-null values.

	@exception SQLException thrown by the ResultSet object
*/
public final void setInto(ResultSet rs, int position) throws SQLException {
	rs.updateInt(position, value);
}