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

The following examples show how to use java.sql.ResultSet#getWarnings() . 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: SURTest.java    From spliceengine with GNU Affero General Public License v3.0 6 votes vote down vote up 
/**
 * Test that you get a warning when specifying a query which is not
 * updatable and concurrency mode CONCUR_UPDATABLE.
 * In this case, the query contains a join.
 */
public void testConcurrencyModeWarning2()
    throws SQLException 
{
    Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                      ResultSet.CONCUR_UPDATABLE);
    s.setCursorName(getNextCursorName());
    ResultSet rs = s.executeQuery
        ("select * from t1 as table1,t1 as table2 where " +
         "table1.a=table2.a");
    
    SQLWarning warn = rs.getWarnings();
    assertEquals("Expected resultset to be read only",
                 ResultSet.CONCUR_READ_ONLY,
                 rs.getConcurrency());
    assertWarning(warn, QUERY_NOT_QUALIFIED_FOR_UPDATABLE_RESULTSET);
    scrollForward(rs);
    rs.close();
    s.close();
}
Example 2
Source File: SURTest.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up 
/**
 * Test that you get a warning when specifying a query which is not
 * updatable and concurrency mode CONCUR_UPDATABLE.
 * In this case, the query contains an "order by"
 */
public void testConcurrencyModeWarning1()
    throws SQLException 
{
    Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                      ResultSet.CONCUR_UPDATABLE);
    s.setCursorName(getNextCursorName());
    ResultSet rs = s.executeQuery("select * from t1 order by a");
    
    SQLWarning warn = rs.getWarnings();
    assertEquals("Expected resultset to be read only",
                 ResultSet.CONCUR_READ_ONLY,
                 rs.getConcurrency());
    assertWarning(warn, QUERY_NOT_QUALIFIED_FOR_UPDATABLE_RESULTSET);
    scrollForward(rs);
    rs.close();
    s.close();
}
Example 3
Source File: SURTest.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up 
/**
 * Test that you get a warning when specifying a query which is not
 * updatable and concurrency mode CONCUR_UPDATABLE.
 * In this case, the query contains a join.
 */
public void testConcurrencyModeWarning2()
    throws SQLException 
{
    Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                      ResultSet.CONCUR_UPDATABLE);
    s.setCursorName(getNextCursorName());
    ResultSet rs = s.executeQuery
        ("select * from t1 as table1,t1 as table2 where " +
         "table1.a=table2.a");
    
    SQLWarning warn = rs.getWarnings();
    assertEquals("Expected resultset to be read only",
                 ResultSet.CONCUR_READ_ONLY,
                 rs.getConcurrency());
    assertWarning(warn, QUERY_NOT_QUALIFIED_FOR_UPDATABLE_RESULTSET);
    scrollForward(rs);
    rs.close();
    s.close();
}
Example 4
Source File: SURTest.java    From spliceengine with GNU Affero General Public License v3.0 6 votes vote down vote up 
/**
 * Test that you get a warning when specifying a query which is not
 * updatable and concurrency mode CONCUR_UPDATABLE.
 * In this case, the query contains an "order by"
 */
public void testConcurrencyModeWarning1()
    throws SQLException 
{
    Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                      ResultSet.CONCUR_UPDATABLE);
    s.setCursorName(getNextCursorName());
    ResultSet rs = s.executeQuery("select * from t1 order by a");
    
    SQLWarning warn = rs.getWarnings();
    assertEquals("Expected resultset to be read only",
                 ResultSet.CONCUR_READ_ONLY,
                 rs.getConcurrency());
    assertWarning(warn, QUERY_NOT_QUALIFIED_FOR_UPDATABLE_RESULTSET);
    scrollForward(rs);
    rs.close();
    s.close();
}
Example 5
Source File: SQLTools.java    From jsqsh with Apache License 2.0 6 votes vote down vote up 
/**
 * Helper method available to all commands to dump any warnings
 * associated with a ResultSet. The set of warnings is cleared
 * after display.
 * 
 * @param session The session to use for writing
 * @param results The ResultSet that may, or may not, contain warnings.
 */
static public void printWarnings(Session session, ResultSet results) {
    
    try {
        
        SQLWarning w = results.getWarnings();
        if (w != null) {
            
            printWarnings(session, w);
            results.clearWarnings();
        }
    }
    catch (SQLException e) {
        
        /* IGNORED */
    }
}
Example 6
Source File: TestAzureDiagnosticsLogsPartitioner.java    From azure-tables-hadoop with Apache License 2.0 6 votes vote down vote up 
private static void printResultSet(ResultSet rs) throws Exception {
	ResultSetMetaData metadata = rs.getMetaData();
	for (int i = 0; i < metadata.getColumnCount(); i++)
		System.out.printf("|%s", metadata.getColumnName(i + 1));
	System.out.println("|");
	int numRows = 0;
	while (rs.next()) {
		for (int i = 0; i < metadata.getColumnCount(); i++)
			System.out.printf("|%s", rs.getObject(i + 1));
		System.out.println();
		numRows++;
	}
	if (rs.getWarnings() != null) {
		rs.getWarnings().printStackTrace();
	}
	System.out.println("Total rows: " + numRows);
}
Example 7
Source File: SURTest.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up 
/**
 * Test that you get a warning when specifying a query which is not
 * updatable and concurrency mode CONCUR_UPDATABLE.
 * In this case, the query contains a join.
 */
public void testConcurrencyModeWarning2()
    throws SQLException 
{
    Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                      ResultSet.CONCUR_UPDATABLE);
    s.setCursorName(getNextCursorName());
    ResultSet rs = s.executeQuery
        ("select * from t1 as table1,t1 as table2 where " +
         "table1.a=table2.a");
    
    SQLWarning warn = rs.getWarnings();
    assertEquals("Expected resultset to be read only",
                 ResultSet.CONCUR_READ_ONLY,
                 rs.getConcurrency());
    assertWarning(warn, QUERY_NOT_QUALIFIED_FOR_UPDATABLE_RESULTSET);
    scrollForward(rs);
    rs.close();
    s.close();
}
Example 8
Source File: CastingTest.java    From spliceengine with GNU Affero General Public License v3.0 5 votes vote down vote up 
/**
 * <p>
 * Check the results for the queries in testDataTruncation().
 * </p>
 *
 * <p>
 * The method expects a query that returns three rows with columns of a
 * character string or binary string data type, where some of the values
 * are cast to a narrower data type.
 * </p>
 *
 * <p>
 * Expect the following truncations to have taken place:
 * </p>
 *
 * <ol>
 * <li>Row 1, column 1: truncated from 3 to 2 bytes</li>
 * <li>Row 3, column 1: truncated from 3 to 2 bytes</li>
 * <li>Row 3, column 2: truncated from 4 to 2 bytes</li>
 * </ol>
 */
private void checkDataTruncationResult(Statement s, String sql)
        throws SQLException {
    ResultSet rs = s.executeQuery(sql);

    // First row should have one warning (column 1)
    assertTrue(rs.next());
    SQLWarning w = rs.getWarnings();
    assertDataTruncation(w, -1, true, false, 3, 2);
    w = w.getNextWarning();
    assertNull(w);
    rs.clearWarnings(); // workaround for DERBY-5765

    // Second row should have no warnings
    assertTrue(rs.next());
    assertNull(rs.getWarnings());

    // Third row should have two warnings (column 1 and 2)
    assertTrue(rs.next());
    w = rs.getWarnings();
    assertDataTruncation(w, -1, true, false, 3, 2);
    // Client driver doesn't support nested warnings
    if (usingEmbedded()) {
        w = w.getNextWarning();
        assertDataTruncation(w, -1, true, false, 4, 2);
    }
    w = w.getNextWarning();
    assertNull(w);
    rs.clearWarnings(); // workaround for DERBY-5765

    // No more rows
    assertFalse(rs.next());
    rs.close();

    // There should be no warnings on the statement or the connection
    assertNull(s.getWarnings());
    assertNull(getConnection().getWarnings());
}
Example 9
Source File: SURTest.java    From spliceengine with GNU Affero General Public License v3.0 5 votes vote down vote up 
/**
 * Test that you get cursor operation conflict warning if updating 
 * a row which has been deleted from the table.
 */
public void testCursorOperationConflictWarning1() 
    throws SQLException 
{
    Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);
    s.setCursorName(getNextCursorName());
    ResultSet rs = s.executeQuery("select * from t1");
    rs.next();
    createStatement().executeUpdate("delete from t1 where id=" +
                                        rs.getString("ID"));
    final int newValue = -3333;
    final int oldValue = rs.getInt(2);
    rs.updateInt(2, newValue);
    rs.updateRow();
    
    SQLWarning warn = rs.getWarnings();
    assertWarning(warn, CURSOR_OPERATION_CONFLICT);
    assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
    assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
    assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
    
    rs.clearWarnings();
    rs.deleteRow();
    warn = rs.getWarnings();
    assertWarning(warn, CURSOR_OPERATION_CONFLICT);
    rs.relative(0);
    assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
    assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
    assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
    
    rs.close();
    s.close();
}
Example 10
Source File: TestConnectionPool.java    From commons-dbcp with Apache License 2.0 5 votes vote down vote up 
protected boolean isClosed(final ResultSet resultSet) {
    try {
        resultSet.getWarnings();
        return false;
    } catch (final SQLException e) {
        // getWarnings throws an exception if the statement is
        // closed, but could throw an exception for other reasons
        // in this case it is good enough to assume the result set
        // is closed
        return true;
    }
}
Example 11
Source File: GFXDServiceImpl.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up 
private final void fillWarnings(RowSet rs, ResultSet resultSet)
    throws SQLException {
  SQLWarning warnings = resultSet.getWarnings();
  if (warnings != null) {
    rs.setWarnings(gfxdWarning(warnings));
  }
}
Example 12
Source File: SURTest.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up 
/**
 * Test that you get cursor operation conflict warning if updating 
 * a row which has been deleted from the table.
 */
public void testCursorOperationConflictWarning1() 
    throws SQLException 
{
    Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);
    s.setCursorName(getNextCursorName());
    ResultSet rs = s.executeQuery("select * from t1");
    rs.next();
    createStatement().executeUpdate("delete from t1 where id=" +
                                        rs.getString("ID"));
    final int newValue = -3333;
    final int oldValue = rs.getInt(2);
    rs.updateInt(2, newValue);
    rs.updateRow();
    
    SQLWarning warn = rs.getWarnings();
    assertWarning(warn, CURSOR_OPERATION_CONFLICT);
    assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
    assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
    assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
    
    rs.clearWarnings();
    rs.deleteRow();
    warn = rs.getWarnings();
    assertWarning(warn, CURSOR_OPERATION_CONFLICT);
    rs.relative(0);
    assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
    assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
    assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
    
    rs.close();
    s.close();
}
Example 13
Source File: GFXDServiceImpl.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up 
private final void fillWarnings(RowSet rs, ResultSet resultSet)
    throws SQLException {
  SQLWarning warnings = resultSet.getWarnings();
  if (warnings != null) {
    rs.setWarnings(gfxdWarning(warnings));
  }
}
Example 14
Source File: SURTest.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up 
/**
 * Test that you get cursor operation conflict warning if updating 
 * a row which has been deleted from the table.
 */
public void testCursorOperationConflictWarning1() 
    throws SQLException 
{
    Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);
    s.setCursorName(getNextCursorName());
    ResultSet rs = s.executeQuery("select * from t1");
    rs.next();
    createStatement().executeUpdate("delete from t1 where id=" +
                                        rs.getString("ID"));
    final int newValue = -3333;
    final int oldValue = rs.getInt(2);
    rs.updateInt(2, newValue);
    rs.updateRow();
    
    SQLWarning warn = rs.getWarnings();
    assertWarning(warn, CURSOR_OPERATION_CONFLICT);
    assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
    assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
    assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
    
    rs.clearWarnings();
    rs.deleteRow();
    warn = rs.getWarnings();
    assertWarning(warn, CURSOR_OPERATION_CONFLICT);
    rs.relative(0);
    assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
    assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
    assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
    
    rs.close();
    s.close();
}
Example 15
Source File: SelectForUpdateTest.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up 
public void testSelectForUpdateDirectDelete() throws Exception {
  Connection conn = TestUtil.getConnection();
  conn.setAutoCommit(false);
  conn.setTransactionIsolation(Connection.TRANSACTION_NONE);
  Statement stmtForTableAndInsert = conn.createStatement();
  stmtForTableAndInsert.execute("create table Employee"
      + "(firstname varchar(50) not null, lastname varchar(50) not null, "
      + "workdept varchar(50), bonus decimal(10,4), "
      + "primary key (firstname, lastname))");
  stmtForTableAndInsert
      .execute("insert into employee values('neeraj', 'kumar', 'rnd', 0.0), "
          + "('asif', 'shahid', 'rnd', 1.0), "
          + "('dada', 'ji', 'rnd', 2.0), ('sum', 'wale', 'rnd', 3.0)");
  Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
  ResultSet uprs = stmt.executeQuery("SELECT workdept, bonus "
      + "FROM EMPLOYEE FOR UPDATE of BONUS");

  while (uprs.next()) {
    uprs.deleteRow();
  }
  
  SQLWarning w = uprs.getWarnings();
  assertTrue("0A000".equals(w.getSQLState()));
  uprs.close();
  Statement stmt2 = conn.createStatement();
  ResultSet rs = stmt2.executeQuery("select * from employee");
  w = rs.getWarnings();
  assertNull(w);

  rs = stmt2.executeQuery("select * from employee");
  assertFalse(rs.next());
  
  conn.setTransactionIsolation(getIsolationLevel());
  
  stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
  
  stmt.execute("insert into employee values('asif', 'shahid', 'rnd', 1.0), ('dada', 'ji', 'rnd', 2.0), ('sum', 'wale', 'rnd', 3.0)");
  
  uprs = stmt.executeQuery("SELECT * "
      + "FROM EMPLOYEE FOR UPDATE of BONUS");

  while (uprs.next()) {
    if (uprs.getString(1).equalsIgnoreCase("asif")) {
      uprs.deleteRow();
    }
  }

  w = uprs.getWarnings();
  assertNull(w);
  uprs.close();
  conn.commit();
  
  stmt2 = conn.createStatement();
  rs = stmt2.executeQuery("select * from employee");
  w = rs.getWarnings();
  assertNull(w);

  assertTrue(rs.next());
  assertTrue((rs.getString(1).equalsIgnoreCase("sum") || rs.getString(1)
      .equalsIgnoreCase("dada")));
  
  assertTrue(rs.next());
  
  assertTrue((rs.getString(1).equalsIgnoreCase("sum") || rs.getString(1)
      .equalsIgnoreCase("dada")));
  
  assertFalse(rs.next());
}
Example 16
Source File: SelectForUpdateInTransactionDUnit.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up 
public void testSelectForUpdate_directDeleteNoPK() throws Exception {
  startVMs(2, 1);
  Connection conn = TestUtil.getConnection();
  conn.setTransactionIsolation(Connection.TRANSACTION_NONE);
  conn.setAutoCommit(false);
  Statement stmtForTableAndInsert = conn.createStatement();
  stmtForTableAndInsert.execute("create table Employee"
      + "(firstname varchar(50) not null, lastname varchar(50) not null, "
      + "workdept varchar(50), bonus decimal(10,4))");
  stmtForTableAndInsert
      .execute("insert into employee values('neeraj', 'kumar', 'rnd', 0.0), "
          + "('asif', 'shahid', 'rnd', 1.0), "
          + "('dada', 'ji', 'rnd', 2.0), ('sum', 'wale', 'rnd', 3.0)");
  Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
  ResultSet uprs = stmt.executeQuery("SELECT workdept, bonus "
      + "FROM EMPLOYEE FOR UPDATE of BONUS");

  while (uprs.next()) {
    uprs.deleteRow();
  }
  
  SQLWarning w = uprs.getWarnings();
  assertTrue("0A000".equals(w.getSQLState()));
  uprs.close();
  Statement stmt2 = conn.createStatement();
  ResultSet rs = stmt2.executeQuery("select * from employee");
  w = rs.getWarnings();
  assertNull(w);

  rs = stmt2.executeQuery("select * from employee");
  assertFalse(rs.next());
  
  conn.setTransactionIsolation(getIsolationLevel());
  conn.setAutoCommit(false);
  
  stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
  
  stmt.execute("insert into employee values('asif', 'shahid', 'rnd', 1.0), ('dada', 'ji', 'rnd', 2.0), ('sum', 'wale', 'rnd', 3.0)");
  
  uprs = stmt.executeQuery("SELECT * "
      + "FROM EMPLOYEE FOR UPDATE of BONUS");

  while (uprs.next()) {
    if (uprs.getString(1).equalsIgnoreCase("asif")) {
      uprs.deleteRow();
    }
  }

  w = uprs.getWarnings();
  assertNull(w);
  uprs.close();
  conn.commit();
  
  stmt2 = conn.createStatement();
  rs = stmt2.executeQuery("select * from employee");
  w = rs.getWarnings();
  assertNull(w);

  assertTrue(rs.next());
  assertTrue((rs.getString(1).equalsIgnoreCase("sum") || rs.getString(1)
      .equalsIgnoreCase("dada")));
  
  assertTrue(rs.next());
  
  assertTrue((rs.getString(1).equalsIgnoreCase("sum") || rs.getString(1)
      .equalsIgnoreCase("dada")));
  
  assertFalse(rs.next());
}
Example 17
Source File: SelectForUpdateInTransactionDUnit.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up 
public void testSelectForUpdate_directDelete() throws Exception {
  startVMs(2, 1);
  Connection conn = TestUtil.getConnection();
  conn.setTransactionIsolation(Connection.TRANSACTION_NONE);
  conn.setAutoCommit(false);
  
  Statement stmtForTableAndInsert = conn.createStatement();
  stmtForTableAndInsert.execute("create table Employee"
      + "(firstname varchar(50) not null, lastname varchar(50) not null, "
      + "workdept varchar(50), bonus decimal(10,4), "
      + "primary key (firstname, lastname))");
  stmtForTableAndInsert
      .execute("insert into employee values('neeraj', 'kumar', 'rnd', 0.0), "
          + "('asif', 'shahid', 'rnd', 1.0), "
          + "('dada', 'ji', 'rnd', 2.0), ('sum', 'wale', 'rnd', 3.0)");
  Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
  ResultSet uprs = stmt.executeQuery("SELECT workdept, bonus "
      + "FROM EMPLOYEE FOR UPDATE of BONUS");

  while (uprs.next()) {
    uprs.deleteRow();
  }
  
  SQLWarning w = uprs.getWarnings();
  assertTrue("0A000".equals(w.getSQLState()));
  uprs.close();
  Statement stmt2 = conn.createStatement();
  ResultSet rs = stmt2.executeQuery("select * from employee");
  w = rs.getWarnings();
  assertNull(w);

  rs = stmt2.executeQuery("select * from employee");
  assertFalse(rs.next());

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

  stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
  
  stmt.execute("insert into employee values('asif', 'shahid', 'rnd', 1.0), ('dada', 'ji', 'rnd', 2.0), ('sum', 'wale', 'rnd', 3.0)");
  
  uprs = stmt.executeQuery("SELECT * "
      + "FROM EMPLOYEE FOR UPDATE of BONUS");

  while (uprs.next()) {
    if (uprs.getString(1).equalsIgnoreCase("asif")) {
      uprs.deleteRow();
    }
  }

  w = uprs.getWarnings();
  assertNull(w);
  uprs.close();
  conn.commit();
  
  stmt2 = conn.createStatement();
  rs = stmt2.executeQuery("select * from employee");
  w = rs.getWarnings();
  assertNull(w);

  assertTrue(rs.next());
  assertTrue((rs.getString(1).equalsIgnoreCase("sum") || rs.getString(1)
      .equalsIgnoreCase("dada")));
  
  assertTrue(rs.next());
  
  assertTrue((rs.getString(1).equalsIgnoreCase("sum") || rs.getString(1)
      .equalsIgnoreCase("dada")));
  
  assertFalse(rs.next());
}
Example 18
Source File: SelectForUpdateTest.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up 
public void testSelectForUpdateDirectDelete() throws Exception {
  Connection conn = TestUtil.getConnection();
  conn.setAutoCommit(false);
  conn.setTransactionIsolation(Connection.TRANSACTION_NONE);
  Statement stmtForTableAndInsert = conn.createStatement();
  stmtForTableAndInsert.execute("create table Employee"
      + "(firstname varchar(50) not null, lastname varchar(50) not null, "
      + "workdept varchar(50), bonus decimal(10,4), "
      + "primary key (firstname, lastname))");
  stmtForTableAndInsert
      .execute("insert into employee values('neeraj', 'kumar', 'rnd', 0.0), "
          + "('asif', 'shahid', 'rnd', 1.0), "
          + "('dada', 'ji', 'rnd', 2.0), ('sum', 'wale', 'rnd', 3.0)");
  Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
  ResultSet uprs = stmt.executeQuery("SELECT workdept, bonus "
      + "FROM EMPLOYEE FOR UPDATE of BONUS");

  while (uprs.next()) {
    uprs.deleteRow();
  }
  
  SQLWarning w = uprs.getWarnings();
  assertTrue("0A000".equals(w.getSQLState()));
  uprs.close();
  Statement stmt2 = conn.createStatement();
  ResultSet rs = stmt2.executeQuery("select * from employee");
  w = rs.getWarnings();
  assertNull(w);

  rs = stmt2.executeQuery("select * from employee");
  assertFalse(rs.next());
  
  conn.setTransactionIsolation(getIsolationLevel());
  
  stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
  
  stmt.execute("insert into employee values('asif', 'shahid', 'rnd', 1.0), ('dada', 'ji', 'rnd', 2.0), ('sum', 'wale', 'rnd', 3.0)");
  
  uprs = stmt.executeQuery("SELECT * "
      + "FROM EMPLOYEE FOR UPDATE of BONUS");

  while (uprs.next()) {
    if (uprs.getString(1).equalsIgnoreCase("asif")) {
      uprs.deleteRow();
    }
  }

  w = uprs.getWarnings();
  assertNull(w);
  uprs.close();
  conn.commit();
  
  stmt2 = conn.createStatement();
  rs = stmt2.executeQuery("select * from employee");
  w = rs.getWarnings();
  assertNull(w);

  assertTrue(rs.next());
  assertTrue((rs.getString(1).equalsIgnoreCase("sum") || rs.getString(1)
      .equalsIgnoreCase("dada")));
  
  assertTrue(rs.next());
  
  assertTrue((rs.getString(1).equalsIgnoreCase("sum") || rs.getString(1)
      .equalsIgnoreCase("dada")));
  
  assertFalse(rs.next());
}
Example 19
Source File: SelectForUpdateInTransactionDUnit.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up 
public void testSelectForUpdate_directDeleteNoPK() throws Exception {
  startVMs(2, 1);
  Connection conn = TestUtil.getConnection();
  conn.setTransactionIsolation(Connection.TRANSACTION_NONE);
  conn.setAutoCommit(false);
  Statement stmtForTableAndInsert = conn.createStatement();
  stmtForTableAndInsert.execute("create table Employee"
      + "(firstname varchar(50) not null, lastname varchar(50) not null, "
      + "workdept varchar(50), bonus decimal(10,4))");
  stmtForTableAndInsert
      .execute("insert into employee values('neeraj', 'kumar', 'rnd', 0.0), "
          + "('asif', 'shahid', 'rnd', 1.0), "
          + "('dada', 'ji', 'rnd', 2.0), ('sum', 'wale', 'rnd', 3.0)");
  Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
  ResultSet uprs = stmt.executeQuery("SELECT workdept, bonus "
      + "FROM EMPLOYEE FOR UPDATE of BONUS");

  while (uprs.next()) {
    uprs.deleteRow();
  }
  
  SQLWarning w = uprs.getWarnings();
  assertTrue("0A000".equals(w.getSQLState()));
  uprs.close();
  Statement stmt2 = conn.createStatement();
  ResultSet rs = stmt2.executeQuery("select * from employee");
  w = rs.getWarnings();
  assertNull(w);

  rs = stmt2.executeQuery("select * from employee");
  assertFalse(rs.next());
  
  conn.setTransactionIsolation(getIsolationLevel());
  conn.setAutoCommit(false);
  
  stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
  
  stmt.execute("insert into employee values('asif', 'shahid', 'rnd', 1.0), ('dada', 'ji', 'rnd', 2.0), ('sum', 'wale', 'rnd', 3.0)");
  
  uprs = stmt.executeQuery("SELECT * "
      + "FROM EMPLOYEE FOR UPDATE of BONUS");

  while (uprs.next()) {
    if (uprs.getString(1).equalsIgnoreCase("asif")) {
      uprs.deleteRow();
    }
  }

  w = uprs.getWarnings();
  assertNull(w);
  uprs.close();
  conn.commit();
  
  stmt2 = conn.createStatement();
  rs = stmt2.executeQuery("select * from employee");
  w = rs.getWarnings();
  assertNull(w);

  assertTrue(rs.next());
  assertTrue((rs.getString(1).equalsIgnoreCase("sum") || rs.getString(1)
      .equalsIgnoreCase("dada")));
  
  assertTrue(rs.next());
  
  assertTrue((rs.getString(1).equalsIgnoreCase("sum") || rs.getString(1)
      .equalsIgnoreCase("dada")));
  
  assertFalse(rs.next());
}
Example 20
Source File: SelectForUpdateInTransactionDUnit.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up 
public void testSelectForUpdate_directDelete() throws Exception {
  startVMs(2, 1);
  Connection conn = TestUtil.getConnection();
  conn.setTransactionIsolation(Connection.TRANSACTION_NONE);
  conn.setAutoCommit(false);
  
  Statement stmtForTableAndInsert = conn.createStatement();
  stmtForTableAndInsert.execute("create table Employee"
      + "(firstname varchar(50) not null, lastname varchar(50) not null, "
      + "workdept varchar(50), bonus decimal(10,4), "
      + "primary key (firstname, lastname))");
  stmtForTableAndInsert
      .execute("insert into employee values('neeraj', 'kumar', 'rnd', 0.0), "
          + "('asif', 'shahid', 'rnd', 1.0), "
          + "('dada', 'ji', 'rnd', 2.0), ('sum', 'wale', 'rnd', 3.0)");
  Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
  ResultSet uprs = stmt.executeQuery("SELECT workdept, bonus "
      + "FROM EMPLOYEE FOR UPDATE of BONUS");

  while (uprs.next()) {
    uprs.deleteRow();
  }
  
  SQLWarning w = uprs.getWarnings();
  assertTrue("0A000".equals(w.getSQLState()));
  uprs.close();
  Statement stmt2 = conn.createStatement();
  ResultSet rs = stmt2.executeQuery("select * from employee");
  w = rs.getWarnings();
  assertNull(w);

  rs = stmt2.executeQuery("select * from employee");
  assertFalse(rs.next());

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

  stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
  
  stmt.execute("insert into employee values('asif', 'shahid', 'rnd', 1.0), ('dada', 'ji', 'rnd', 2.0), ('sum', 'wale', 'rnd', 3.0)");
  
  uprs = stmt.executeQuery("SELECT * "
      + "FROM EMPLOYEE FOR UPDATE of BONUS");

  while (uprs.next()) {
    if (uprs.getString(1).equalsIgnoreCase("asif")) {
      uprs.deleteRow();
    }
  }

  w = uprs.getWarnings();
  assertNull(w);
  uprs.close();
  conn.commit();
  
  stmt2 = conn.createStatement();
  rs = stmt2.executeQuery("select * from employee");
  w = rs.getWarnings();
  assertNull(w);

  assertTrue(rs.next());
  assertTrue((rs.getString(1).equalsIgnoreCase("sum") || rs.getString(1)
      .equalsIgnoreCase("dada")));
  
  assertTrue(rs.next());
  
  assertTrue((rs.getString(1).equalsIgnoreCase("sum") || rs.getString(1)
      .equalsIgnoreCase("dada")));
  
  assertFalse(rs.next());
}