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

The following examples show how to use java.sql.ResultSet#updateRow() . These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may want to check out the right sidebar which shows the related API usage.
Example 1
Source Project: tutorials   File: ResultSetLiveTest.java    License: MIT License 6 votes vote down vote up
@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());
}
 
Example 2
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();

    }
 
Example 3
Source Project: gemfirexd-oss   File: SQLDistTxTest.java    License: Apache License 2.0 6 votes vote down vote up
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;
}
 
Example 4
/**
 * 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();
}
 
Example 5
Source Project: gemfirexd-oss   File: SURTest.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 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();
}
 
Example 6
/**
 * 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();
}
 
Example 7
Source Project: gemfirexd-oss   File: ResultSetTest.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 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();
}
 
Example 8
Source Project: gemfirexd-oss   File: SURTest.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * 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();
}
 
Example 9
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();
}
 
Example 10
Source Project: gemfirexd-oss   File: SQLDistTxTest.java    License: Apache License 2.0 4 votes vote down vote up
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);
  }
}
 
Example 11
Source Project: gemfirexd-oss   File: ResultSetTest.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * 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();
}
 
Example 12
/**
 * 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();
}
 
Example 13
Source Project: r-course   File: StatementsTest.java    License: MIT License 4 votes vote down vote up
/**
 * 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();
}
 
Example 14
Source Project: gemfirexd-oss   File: ResultSetTest.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * 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();
}
 
Example 15
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();        
}
 
Example 16
/**
 * 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");
}
 
Example 17
/**
 * 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();
}
 
Example 18
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");
}
 
Example 19
/** 
 * 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();
    
}
 
Example 20
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");
}