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

The following examples show how to use java.sql.ResultSet#updateInt() . 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 check out the related API usage on the sidebar.
Example 1
Source File: UpdateXXXTest.java    From spliceengine with GNU Affero General Public License v3.0 6 votes vote down vote up
/**
 * 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();
}
 
Example 2
Source File: SURTest.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
/**
 * 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();
}
 
Example 3
Source File: SURTest.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
/**
 * 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();
}
 
Example 4
Source File: SURQueryMixTest.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
/**
 * 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();
}
 
Example 5
Source File: SURTest.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
/**
 * 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();
}
 
Example 6
Source File: ConcurrencyTest.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
/**
 * 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();
}
 
Example 7
Source File: UnsupportedUpdateOperationResultSetTest.java    From sharding-jdbc-1.5.1 with Apache License 2.0 4 votes vote down vote up
@Test(expected = SQLFeatureNotSupportedException.class)
public void assertUpdateIntForColumnIndex() throws SQLException {
    for (ResultSet each : resultSets) {
        each.updateInt(1, 1);
    }
}
 
Example 8
Source File: StatementCachingTest.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
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 9
Source File: ConcurrencyTest.java    From spliceengine with GNU Affero General Public License v3.0 4 votes vote down vote up
/**
 * 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();
}
 
Example 10
Source File: SURTest.java    From spliceengine with GNU Affero General Public License v3.0 4 votes vote down vote up
/** 
 * 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 11
Source File: UnsupportedUpdateOperationResultSetTest.java    From shardingsphere with Apache License 2.0 4 votes vote down vote up
@Test(expected = SQLFeatureNotSupportedException.class)
public void assertUpdateIntForColumnIndex() throws SQLException {
    for (ResultSet each : resultSets) {
        each.updateInt(1, 1);
    }
}
 
Example 12
Source File: SelectForUpdateInTransactionDUnit.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
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");
}
 
Example 13
Source File: SQL_BAD_RESULTSET_ACCESS.java    From spotbugs with GNU Lesser General Public License v2.1 4 votes vote down vote up
@ExpectWarning("SQL_BAD_RESULTSET_ACCESS")
void bug4(ResultSet any, int anyInt) throws SQLException {
    any.updateInt(0, anyInt);
}
 
Example 14
Source File: SURTest.java    From spliceengine with GNU Affero General Public License v3.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 15
Source File: StatementCachingTest.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
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
Source File: SelectForUpdateInTransactionDUnit.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
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");
}
 
Example 17
Source File: SelectForUpdateInTransactionDUnit.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
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");
}
 
Example 18
Source File: SelectForUpdateInTransactionDUnit.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
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");
}
 
Example 19
Source File: SelectForUpdateInTransactionDUnit.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
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");
}
 
Example 20
Source File: SQLInteger.java    From gemfirexd-oss with Apache License 2.0 votes vote down vote up
/**
	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);
}