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

@Test
public void givenDbConnectionK_whenUpdate_thenCorrect() throws SQLException {
    Employee employee = null;
    dbConnection.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
    try (Statement pstmt = dbConnection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE, ResultSet.HOLD_CURSORS_OVER_COMMIT)) {
        dbConnection.setAutoCommit(false);
        ResultSet rs = pstmt.executeQuery("select * from employees");
        while (rs.next()) {
            if (rs.getString("name")
                .equalsIgnoreCase("john")) {
                rs.updateString("name", "John Doe");
                rs.updateRow();
                dbConnection.commit();
                employee = populateResultSet(rs);
            }
        }
        rs.last();
    }

    assertEquals("Update using open cursor", "John Doe", employee.getName());
}
 

private void adjustTable(final Connection conn, final String table) throws Exception {

        System.out.println("Adjusting table " + table);

        Statement sta = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = sta.executeQuery("SELECT * FROM " + table + " WHERE DISPLAYVAL is not null");

        while (rs.next()) {

            final Object pk = rs.getObject("ATTRVALUE_ID");
            final String i18n = rs.getString("DISPLAYVAL");
            final String model = adjustValue(i18n);
            if (model != null && !model.equals(i18n)) {
                rs.updateObject("DISPLAYVAL", model);
                rs.updateRow();
                System.out.println("Adjusting object(" + pk + ") val: " + model);
            }
        }

        sta.close();
        conn.commit();

    }
 

protected boolean updateURSRowTx(ResultSet updatableRs, int tid) {
  try {
    int cid = updatableRs.getInt("CID");
    BigDecimal sec = updatableRs.getBigDecimal("SECURITIES").add(
        new BigDecimal(tid));
    updatableRs.updateBigDecimal("SECURITIES", sec);
    updatableRs.updateRow();
    Log.getLogWriter().info("update trade.networth set securities to be " + sec 
        + " for cid: " + cid);
  } catch (SQLException se) {
    if (se.getSQLState().equals("X0Z02")) {
      SQLHelper.printSQLException(se);
      return false; // expected updatable result set
    } else
      SQLHelper.handleSQLException(se);
  }
  return true;
}
 

/**
 * Tests calling updateObject with a null value on all columns.
 * @exception SQLException database access error. Causes test to 
 *                         fail with an error.
 */
public void testUpdateObjectWithNull() 
    throws SQLException
{
    Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY, 
            ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = s.executeQuery(SELECT_STMT);
    rs.next();

    Object value = null;
    
    for (int i = 1; i <= COLUMNS; i++) {
        rs.updateObject(i, value);
        assertNull("Expected rs.getObject(" + i + ") to be null", 
                   rs.getObject(i));
        assertTrue("Expected rs.wasNull() to return true",
                   rs.wasNull());
    }
    rs.updateRow();
    rs.close();
    checkColumnsAreNull();
    
    s.close();
}
 

/**
 * Tests the updateClob that accepts a input stream and the length of the IS
 * and the parameter name String.
 *
 * @throws an Exception
 */
public void testUpdateClobStringParameterNameWithLengthofIS()
        throws Exception {
    Reader r1 = new java.io.StringReader(str1);
    // InputStream for insertion.
    Reader r2 = new java.io.StringReader(str2);

    // Prepared Statement used to insert the data
    PreparedStatement ps_sb = prep("dClob");
    ps_sb.setInt(1, key);
    ps_sb.setCharacterStream(2, r1);
    ps_sb.executeUpdate();
    ps_sb.close();

    // Update operation
    ResultSet rs1 = fetchUpd("dClob", key);
    rs1.next();
    rs1.updateClob("dClob", r2, str2.length());
    rs1.updateRow();
    rs1.close();

    // Query to see whether the data that has been updated.
    rs1 = fetch("dClob", key);
    rs1.next();
    assertEquals(new StringReader(str2),
                 rs1.getCharacterStream(1));
    rs1.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 <code>updateBinaryStream</code> on a BINARY column, without
 * specifying length of inputstream.
 */
public void testUpdateBinaryStreamLengthless()
        throws IOException, SQLException {
    InputStream is1 = new java.io.ByteArrayInputStream(BYTES1);
    // InputStream used for update.
    InputStream is2 = new java.io.ByteArrayInputStream(BYTES2);

    //Prepared Statement used to insert the data
    PreparedStatement ps_sb = prep("dLongBit");
    ps_sb.setInt(1, key);
    ps_sb.setBinaryStream(2, is1);
    ps_sb.executeUpdate();
    ps_sb.close();

    //Update operation
    ResultSet rs1 = fetchUpd("dLongBit", key);
    rs1.next();
    rs1.updateBinaryStream(1, is2);
    rs1.updateRow();
    rs1.close();

    //Query to see whether the data that has been updated
    //using the updateBinaryStream method is the same
    //data that we expected

    rs1 = fetch("dLongBit", key);
    rs1.next();
    assertEquals(new ByteArrayInputStream(BYTES2), rs1.getBinaryStream(1));
    rs1.close();
}
 

public void testSelectForUpdate_PR_no_primary_key() 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)";
  
  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();
  conn.setAutoCommit(false);

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

  conn.setTransactionIsolation(getIsolationLevel());

  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");
}
 

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

public void testSelectForUpdate_PR_composite_key_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");
}
 

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

/**
 * This methods tests the ResultSet interface method
 * updateAsciiStream
 *
 * @throws SQLException if some error occurs while calling the method
 */

public void testUpdateAsciiStream()
throws Exception {
    //create the table
    stmt.execute("create table UpdateTestTable_ResultSet (sno int, " +
            "datacol LONG VARCHAR)");

    //Byte array in which the returned bytes from
    //the Database after the update are stored. This
    //array is then checked to determine if it
    //has the same elements of the Byte array used for
    //the update operation

    byte[] bytes_ret = new byte[10];

    //Input Stream inserted initially
    InputStream is = new java.io.ByteArrayInputStream(BYTES1);

    //InputStream that is used for update
    InputStream is_for_update = new
            java.io.ByteArrayInputStream(BYTES2);

    //Prepared Statement used to insert the data
    PreparedStatement ps_sb = prepareStatement
            ("insert into UpdateTestTable_ResultSet values(?,?)");
    ps_sb.setInt(1,1);
    ps_sb.setAsciiStream(2,is,BYTES1.length);
    ps_sb.executeUpdate();
    ps_sb.close();

    //Update operation
    //use a different ResultSet variable so that the
    //other tests can go on unimpacted

    ResultSet rs1 = stmt.executeQuery
            ("select * from UpdateTestTable_ResultSet for update");
    rs1.next();
    rs1.updateAsciiStream(2,is_for_update,(int)BYTES2.length);
    rs1.updateRow();
    rs1.close();

    //Query to see whether the data that has been updated
    //using the updateAsciiStream method is the same
    //data that we expected

    rs1 = stmt.executeQuery
            ("select * from UpdateTestTable_ResultSet");
    rs1.next();
    InputStream is_ret = rs1.getAsciiStream(2);

    is_ret.read(bytes_ret);
    is_ret.close();

    for(int i=0;i<BYTES2.length;i++) {
        assertEquals("Error in updateAsciiStream",BYTES2[i],bytes_ret[i]);
    }
    rs1.close();
    //delete the table
    stmt .execute("drop table UpdateTestTable_ResultSet");
}
 

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

/**
 * This methods tests the ResultSet interface method
 * updateBlob
 *
 * @throws SQLException if some error occurs while calling the method
 */
public void testUpdateBlobStringParameterName()
throws Exception {
    // Life span of Blob objects are limited by the transaction.  Need
    // autocommit off so Blob objects survive execution of next statement.
    getConnection().setAutoCommit(false);

    //Byte array in which the returned bytes from
    //the Database after the update are stored. This
    //array is then checked to determine if it
    //has the same elements of the Byte array used for
    //the update operation

    byte[] bytes_ret = new byte[10];

    //1 Input Stream for insertion
    InputStream is1 = new java.io.ByteArrayInputStream(BYTES1);

    //2 Input Stream for insertion
    InputStream is2 = new java.io.ByteArrayInputStream(BYTES2);

    //Prepared Statement used to insert the data
    PreparedStatement ps_sb = prep("dBlob");

    //first insert
    ps_sb.setInt(1, key);
    ps_sb.setBinaryStream(2,is1,BYTES1.length);
    ps_sb.executeUpdate();

    //second insert
    int key2 = requestKey();
    ps_sb.setInt(1, key2);
    ps_sb.setBinaryStream(2,is2,BYTES2.length);
    ps_sb.executeUpdate();

    ps_sb.close();

    //Update operation
    //use a different ResultSet variable so that the
    //other tests can go on unimpacted
    //we do not have set methods on Clob and Blob implemented
    //So query the first Clob from the database
    //update the second result set with this
    //Clob value

    ResultSet rs1 = fetch("dBlob", key);
    rs1.next();
    Blob blob = rs1.getBlob(1);
    rs1.close();

    rs1 = fetchUpd("dBlob", key2);
    rs1.next();
    rs1.updateBlob("dBlob",blob);
    rs1.updateRow();
    rs1.close();

    //Query to see whether the data that has been updated
    //using the updateBlob method is the same
    //data that we expected

    rs1 = fetch("dBlob", key2);
    rs1.next();
    assertEquals(blob, rs1.getBlob(1)); 
    rs1.close();
}
 

/**
 * Tests for ResultSet.updateNString()
 * 
 * @throws Exception
 */
public void testUpdateNString() throws Exception {
    createTable("testUpdateNString", "(c1 CHAR(10) PRIMARY KEY, c2 NATIONAL CHARACTER(10)) default character set sjis");
    Properties props1 = new Properties();
    props1.put("useServerPrepStmts", "true"); // use server-side prepared statement
    props1.put("characterEncoding", "UTF-8"); // ensure charset is utf8 here
    Connection conn1 = getConnectionWithProps(props1);
    PreparedStatement pstmt1 = conn1.prepareStatement("INSERT INTO testUpdateNString (c1, c2) VALUES (?, ?)");
    pstmt1.setString(1, "1");
    pstmt1.setNString(2, "aaa");
    pstmt1.execute();
    Statement stmt1 = conn1.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    ResultSet rs1 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNString");
    rs1.next();
    rs1.updateNString("c2", "bbb");
    rs1.updateRow();
    rs1.moveToInsertRow();
    rs1.updateString("c1", "2");
    rs1.updateNString("c2", "ccc");
    rs1.insertRow();
    ResultSet rs2 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNString");
    rs2.next();
    assertEquals("1", rs2.getString("c1"));
    assertEquals("bbb", rs2.getNString("c2"));
    rs2.next();
    assertEquals("2", rs2.getString("c1"));
    assertEquals("ccc", rs2.getNString("c2"));
    pstmt1.close();
    stmt1.close();
    conn1.close();

    createTable("testUpdateNString", "(c1 CHAR(10) PRIMARY KEY, c2 CHAR(10)) default character set sjis"); // sjis field
    Properties props2 = new Properties();
    props2.put("useServerPrepStmts", "true"); // use server-side prepared statement
    props2.put("characterEncoding", "SJIS"); // ensure charset isn't utf8 here
    Connection conn2 = getConnectionWithProps(props2);
    PreparedStatement pstmt2 = conn2.prepareStatement("INSERT INTO testUpdateNString (c1, c2) VALUES (?, ?)");
    pstmt2.setString(1, "1");
    pstmt2.setString(2, "aaa");
    pstmt2.execute();
    Statement stmt2 = conn2.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    ResultSet rs3 = stmt2.executeQuery("SELECT c1, c2 FROM testUpdateNString");
    rs3.next();
    try {
        rs3.updateNString("c2", "bbb"); // field's charset isn't utf8
        fail();
    } catch (SQLException ex) {
        assertEquals("Can not call updateNString() when field's character set isn't UTF-8", ex.getMessage());
    }
    rs3.close();
    pstmt2.close();
    stmt2.close();
    conn2.close();
}
 

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

/**
 * This methods tests the ResultSet interface method
 * updateBlob
 *
 * @throws SQLException if some error occurs while calling the method
 */
public void testUpdateBlobStringParameterName()
throws Exception {
    // Life span of Blob objects are limited by the transaction.  Need
    // autocommit off so Blob objects survive execution of next statement.
    getConnection().setAutoCommit(false);

    //Byte array in which the returned bytes from
    //the Database after the update are stored. This
    //array is then checked to determine if it
    //has the same elements of the Byte array used for
    //the update operation

    byte[] bytes_ret = new byte[10];

    //1 Input Stream for insertion
    InputStream is1 = new java.io.ByteArrayInputStream(BYTES1);

    //2 Input Stream for insertion
    InputStream is2 = new java.io.ByteArrayInputStream(BYTES2);

    //Prepared Statement used to insert the data
    PreparedStatement ps_sb = prep("dBlob");

    //first insert
    ps_sb.setInt(1, key);
    ps_sb.setBinaryStream(2,is1,BYTES1.length);
    ps_sb.executeUpdate();

    //second insert
    int key2 = requestKey();
    ps_sb.setInt(1, key2);
    ps_sb.setBinaryStream(2,is2,BYTES2.length);
    ps_sb.executeUpdate();

    ps_sb.close();

    //Update operation
    //use a different ResultSet variable so that the
    //other tests can go on unimpacted
    //we do not have set methods on Clob and Blob implemented
    //So query the first Clob from the database
    //update the second result set with this
    //Clob value

    ResultSet rs1 = fetch("dBlob", key);
    rs1.next();
    Blob blob = rs1.getBlob(1);
    rs1.close();

    rs1 = fetchUpd("dBlob", key2);
    rs1.next();
    rs1.updateBlob("dBlob",blob);
    rs1.updateRow();
    rs1.close();

    //Query to see whether the data that has been updated
    //using the updateBlob method is the same
    //data that we expected

    rs1 = fetch("dBlob", key2);
    rs1.next();
    assertEquals(blob, rs1.getBlob(1)); 
    rs1.close();
}
 

protected void useUpdatableResultSet(Connection conn, boolean isInitTask) {
  if (isHATest)
    throw new TestException("need to handle tx node failure condition "
        + "in the test, as #43935 is fixed");

  int cid1 = (concUpdateTxMaxCid == 0) ? random.nextInt(100) : random
      .nextInt(concUpdateTxMaxCid);
  int cid2 = cid1 + 10;
  try {
    Statement s = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
        ResultSet.CONCUR_UPDATABLE);
    String[] updatableRs = { "select * from trade.customers where cid > "
        + cid1 + " and cid < " + cid2, };

    for (String sql : updatableRs) {
      ResultSet rs = s.executeQuery(sql);

      int prevCid = 0;
      boolean checkPrevRowLockNotHeld = false;
      while (rs.next()) {
        int cid = rs.getInt("CID");
        Log.getLogWriter().info("this row's cid is " + cid);
        if (random.nextBoolean()) {
          rs.updateString("CUST_NAME", "updated_custname");
          rs.updateRow();
          checkLockHeldForThisRow(cid);
          // according to comments in #43917 & #43937, normal updatable
          // resultset will
          // hold lock only after actually update the row.
          checkPrevRowLockNotHeld = false;
        } else {
          checkPrevRowLockNotHeld = true;
        }
        if (isInitTask & checkPrevRowLockNotHeld)
          checkLockNotHeldForPreviousRow(prevCid);

        rs.getString("ADDR");
        prevCid = cid;
      }
      rs.close();
    }
    commit(conn);
  } catch (SQLException se) {
    if (se.getSQLState().equals("X0Z02")) {
      if (isInitTask)
        throw new TestException(
            "only one ddl thread in this init task, we should not "
                + "see the conflict exception" + TestHelper.getStackTrace(se));
      else
        ; // expected updatable result set
    } else
      SQLHelper.handleSQLException(se);
  }
}
 

public void testSelectForUpdate_PR_key_not_in_projection_and_whereClause()
    throws Exception {
  startVMs(2, 1);

  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 where lastname = 'kumar' 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 where lastname = 'kumar'";
  stmt.execute(sql);
  ResultSet rs = stmt.getResultSet();
  int cnt = 0;
  while(rs.next()) {
    cnt++;
    int bonus = rs.getInt(4);
    assertEquals(10, bonus);
  }
  assertEquals(1, cnt);
  
  conn.commit();
}
 

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