Java Code Examples for java.sql.Connection#setTransactionIsolation()

private Connection openConnection(String username, String password) throws SQLException {
  Connection connection;
  try {
    connection = DriverManager.getConnection(AbstractConnectionResources.this.getJdbcUrl(), username, password);
  } catch (SQLException se) {
    log.error(String.format("Unable to connect to URL: %s, with user: %s", AbstractConnectionResources.this.getJdbcUrl(), username));
    throw se;
  }
  try {
    // Attempt to switch to read-committed. This is the default on many platforms, but not on MySQL.
    connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    return connection;
  } catch (Exception e) {
    connection.close();
    throw e;
  }
}
 

/**
 * Tests that the effects of executing a stored procedure with
 * <code>executeQuery()</code> are correctly rolled back when the
 * query fails because the number of returned result sets is zero.
 *
 * <p> This test case fails with JCC.
 *
 * @exception SQLException if a database error occurs
 */
public void testRollbackStoredProcWhenExecuteQueryReturnsNothing_prepared()
    throws SQLException
{
    Connection conn = getConnection();
    conn.setAutoCommit(true);
    // GemsStone changes BEGIN
    conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    // GemStone changes END
    PreparedStatement ps =
        prepareStatement("CALL PROC_WITH_SIDE_EFFECTS(?)");
    ps.setInt(1, 0);
    try {
        ps.executeQuery();
        fail("executeQuery() didn't fail.");
    } catch (SQLException sqle) {
        assertNoResultSetFromExecuteQuery(sqle);
    }
    Statement stmt = createStatement();
    // Expect Side effects from stored procedure to be rolled back.
    JDBC.assertEmpty(stmt.executeQuery("SELECT * FROM SIMPLE_TABLE"));

}
 

public void testBug42067_2() throws Exception {

    // Create the controller VM as client which belongs to default server group
    startClientVMs(1, 0, null);
    startServerVMs(2, -1, "SG1");
    Connection conn = TestUtil.jdbcConn;
    conn.setTransactionIsolation(getIsolationLevel());
    conn.setAutoCommit(false);
    // create table
    clientSQLExecute(1, "Create table t1 (c1 int not null primary key, "
        + "c2 int not null, c3 int not null, c4 int not null) "
        + "redundancy 1 partition by column (c1) "+ getSuffix());
    conn.commit();
    Statement st = conn.createStatement();
    st.execute("insert into t1 values (1, 1,1,1)");
    st.execute("insert into t1 values (114, 114,114,114)");
    conn.commit();
    st.execute("delete from t1 where c1 =1 and c3 =1");
    st.execute("update t1 set c2 =2 where c1 =1 and c3 =1");
    conn.commit();
    ResultSet rs = st.executeQuery("select * from t1");
    assertTrue(rs.next());
    assertEquals(114, rs.getInt(1));
    assertFalse(rs.next());
  }
 

public static  Connection openConnection(String driver, String url,
    int txIsolation, boolean autocommit, Properties p) throws SQLException {
  Log.getLogWriter().info("Creating connection using " + driver 
     + " with " + url + " and properties: " + p); 
  loadDriver(driver);
  Connection conn;
  if (p == null) {
    conn = DriverManager.getConnection(url);
  } else {
    conn = DriverManager.getConnection(url, p);
  }
  conn.setAutoCommit(autocommit);
  conn.setTransactionIsolation(txIsolation);
  Log.getLogWriter().info("Created connection using transaction isolation: "
     + GFXDPrms.getTxIsolation(conn.getTransactionIsolation())
     + " autocommit: " + autocommit);
  
  return conn;
}
 

/**
 * Tests that the effects of executing a stored procedure with
 * <code>executeUpdate()</code> are correctly rolled back when the
 * query fails because the stored procedure returned a result set.
 *
 * <p> This test case fails with JCC.
 *
 * @exception SQLException if a database error occurs
 */
public void testRollbackStoredProcWhenExecuteUpdateReturnsResults()
    throws SQLException
{
    Connection conn = getConnection();
    // GemsStone changes BEGIN
    conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    // GemStone changes END
    conn.setAutoCommit(true);
    Statement stmt = createStatement();
    try {
        stmt.executeUpdate("CALL PROC_WITH_SIDE_EFFECTS(1)");
        fail("executeUpdate() didn't fail.");
    } catch (SQLException sqle) {
        assertResultsFromExecuteUpdate(sqle);
    }
    // Expect Side effects from stored procedure to be rolled back.
    JDBC.assertEmpty(stmt.executeQuery("SELECT * FROM SIMPLE_TABLE"));

}
 

public void testBug41168() throws Exception {
  Connection conn = TestUtil.getConnection();
  Statement stmt = conn.createStatement();

  conn.setTransactionIsolation(getIsolationLevel());

  // Create the table with self-reference FKs
  stmt.execute("create table BinaryTree (id int primary key, "
      + "leftId int, rightId int, depth int not null,"
      + " foreign key (leftId) references BinaryTree(id),"
      + " foreign key (rightId) references BinaryTree(id)) replicate"+getSuffix());

  addExpectedException(SQLIntegrityConstraintViolationException.class);
  doBinaryTreeChecks(conn, true);
  removeExpectedException(SQLIntegrityConstraintViolationException.class);

  // now do the same for partitioned table
  stmt.execute("drop table BinaryTree");
  this.waitTillAllClear();
  stmt.execute("create table BinaryTree (id int primary key, "
      + "leftId int, rightId int, depth int not null,"
      + " foreign key (leftId) references BinaryTree(id),"
      + " foreign key (rightId) references BinaryTree(id))"+getSuffix());

  addExpectedException(SQLIntegrityConstraintViolationException.class);
  doBinaryTreeChecks(conn, true);
  removeExpectedException(SQLIntegrityConstraintViolationException.class);
}
 

public void testRollbackBecauseOfDbTimeout() throws SQLException
{
    int orderId = 1;
    int newUserId = -999999;
    String description = "new long description";
    Order order = OrderFinder.findOne(OrderFinder.orderId().eq(orderId));
    int oldUserId = order.getUserId();
    String oldDescription = order.getDescription();

    Connection con = this.getConnection();
    con.setAutoCommit(false);
    con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
    String sql = "select * from APP.ORDERS for UPDATE";
    PreparedStatement ps = con.prepareStatement(sql);
    ResultSet rs = ps.executeQuery();
    rs.next();
    try
    {
        try
        {
            order.setUserIdAndDescription(newUserId, description);
            fail("commit should have failed");
        }
        catch (MithraDatabaseException e)
        {
            //must roll back because we have shutdown the db
        }
        assertEquals(oldUserId, order.getUserId());
        assertEquals(oldDescription, order.getDescription());
    }
    finally
    {
        ps.close();
        if (con != null)
        {
            con.close();
        }
    }
}
 

public void testTransactionalInsertOnReplicatedTable() throws Exception {
  Connection conn= getConnection();
  Statement st = conn.createStatement();
  st.execute("Create table t1 (c1 int not null , c2 int not null, "
      + "primary key(c1)) replicate"+getSuffix());
  conn.commit();
  conn.setTransactionIsolation(getIsolationLevel());
  conn.setAutoCommit(false);
  st.execute("insert into t1 values (10, 10)");

  conn.rollback();// rollback.

  ResultSet rs = st.executeQuery("Select * from t1");
  assertFalse("ResultSet should be empty ", rs.next());
  rs.close();

  st.execute("insert into t1 values (10, 10)");
  st.execute("insert into t1 values (20, 20)");

  conn.commit(); // commit two rows.
  rs = st.executeQuery("Select * from t1");
  int numRows = 0;
  while (rs.next()) {
    // Checking number of rows returned, since ordering of results
    // is not guaranteed. We can write an order by query for this (another
    // test).
    numRows++;
  }
  assertEquals("ResultSet should contain two rows ", 2, numRows);

  // Close connection, resultset etc...
  rs.close();
  st.close();
  conn.commit();
  conn.close();
}
 

private void configureConnection(Connection conn) throws SQLException {
	if (autoCommit != conn.getAutoCommit()) {
		conn.setAutoCommit(autoCommit);
	}
	if (defaultTransactionIsolationLevel != null) {
		conn.setTransactionIsolation(defaultTransactionIsolationLevel);
	}
}
 

/**
 * Tests that a ResultSet opened even in read uncommitted, gets a 
 * table intent lock, and that another transaction then cannot compress 
 * the table while the ResultSet is open.
 **/
public void testTableIntentLock1()
    throws SQLException 
{
    getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
    Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                      ResultSet.CONCUR_UPDATABLE);
    println("T1: select * from t1");
    ResultSet rs = s.executeQuery("select * from t1 for update");
    while (rs.next()) {
        println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
                rs.getInt(2) + "," +
                rs.getInt(3) + ")");
    } // Now the cursor does not point to any tuples
    
    // Compressing the table in another transaction:
    Connection con2 = openDefaultConnection();
    
    con2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    PreparedStatement ps2 = con2.prepareStatement
        ("call SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?)");
    ps2.setString(1, "SPLICE");
    ps2.setString(2, "T1");
    ps2.setInt(3, 0);
    println("T2: call SYSCS_UTIL.SYSCS_COMPRESS_TABLE(SPLICE, T1, 0)");
    try {
        ps2.executeUpdate(); // This will hang
        fail("Expected T2 to hang");
    } catch (SQLException e) {
        println("T2: Got exception:" + e.getMessage());
        
        assertSQLState(LOCK_TIMEOUT_EXPRESSION_SQL_STATE, e);

    }
    ps2.close();
    con2.rollback();
    con2.close();
    
    s.close();
}
 

public static Runnable prepInsert() {
  CacheSerializableRunnable senderConf = new CacheSerializableRunnable(
      "Sender Configurator") {
    @Override
    public void run2() throws CacheException {
      try {
        Connection conn = TestUtil.jdbcConn;
        conn.setAutoCommit(false);
        conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        PreparedStatement prep = conn.prepareStatement("insert into "
            + "EMP.PARTITIONED_TABLE (ID, DESCRIPTION, ADDRESS, COMPANY) values (?, ?, ?, ?)");
        prep.setInt(1,1);
        prep.setString(2, "First");
        prep.setString(3, "A714");
        prep.setString(4, "Pivotal");
        prep.addBatch();
        prep.setInt(1,2);
        prep.setString(2, "Second");
        prep.setString(3, "J 605");
        prep.setString(4, "Zimbra");
        prep.addBatch();
        prep.executeBatch();
        conn.commit();
      } catch (SQLException sqle) {
        throw GemFireXDRuntimeException.newRuntimeException(null, sqle);
      }
    }
  };
  return senderConf;
}
 

public void testTxnDeleteParentRow() throws Exception {
  Connection conn = getConnection();
  Statement stmtp1 = conn.createStatement();
  stmtp1.execute("Create table t1 (c1 int not null primary key, "
      + "c2 int not null, c3 int not null, "
      + "constraint C3_Unique unique (c3))"+getSuffix());

  stmtp1.execute("Create table t2 (c1 int not null primary key, "
      + "c2 int not null, c3 int not null, "
      + "foreign key (c1) references t1(c1))"+getSuffix());

  stmtp1.execute("insert into t1 values(1, 1, 1)");
  conn.setTransactionIsolation(getIsolationLevel());
  conn.setAutoCommit(false);
  
  Statement stmtp2 = conn.createStatement();
  stmtp2.execute("delete from t1 where c1 = 1");

  Connection childConn = TestUtil.getConnection();
  childConn.setTransactionIsolation(getIsolationLevel());
  Statement childStmnt = childConn.createStatement();
  addExpectedException(ConflictException.class);
  try {
    childStmnt.execute("insert into t2 values(1, 1, 1)");
    fail("expected conflict exception");
  } catch (SQLException sqle) {
    if (!"X0Z02".equals(sqle.getSQLState())) {
      throw sqle;
    }
  }
  removeExpectedException(ConflictException.class);

  // after rollback of parent, there should be no conflict
  conn.rollback();
  childStmnt.execute("insert into t2 values(1, 1, 1)");
  childConn.commit();
}
 

public void _testSelectForUpdateForDebugging() throws Exception {
  Connection conn = getConnection();
  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)");
  conn.commit();
  // conn.setAutoCommit(false);
  // Create the statement with concurrency mode CONCUR_UPDATABLE
  // to allow result sets to be updatable
  conn.setTransactionIsolation(getIsolationLevel());
  Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
  // Statement stmt = conn.createStatement();
  // Updatable statements have some requirements
  // for example, select must be on a single table
  // Only bonus can be updated
  // ResultSet uprs = stmt.executeQuery(
  // "SELECT WORKDEPT, BONUS /*, firstname, LastNaME*/ " +
  // "FROM EMPLOYEE where lastname = 'kumar' FOR UPDATE of BONUS");

  // Only bonus can be updated
  ResultSet uprs = stmt.executeQuery("SELECT workdept, bonus "
      + "FROM EMPLOYEE where lastname = 'kumar' FOR UPDATE of BONUS");
  // ResultSet uprs = stmt.executeQuery(
  // "SELECT firstname, count(*) " +
  // "FROM EMPLOYEE group by firstname FOR UPDATE of BONUS"); // Only bonus
  // can be updated

  while (uprs.next()) {
    uprs.getString("WORKDEPT");
    BigDecimal bonus = uprs.getBigDecimal("BONUS");
    // if (workDept.equals(theDept)) {
    if (true) {
      // if the current row meets our criteria,
      // update the updatable column in the row
      uprs.updateBigDecimal("BONUS", bonus.add(BigDecimal.valueOf(250L)));
      // uprs.updateBigDecimal("BONUS", null);
      uprs.updateRow();
      // System.out.println("Updating bonus for employee:" +
      // firstnme +" "+ lastName);
    }
  }
  conn.commit(); // commit the transaction
  // close object
  uprs.close();
  ResultSet rs = stmt.executeQuery("select * from employee");
  while (rs.next()) {
    System.out.println(rs.getString(1) + ", " + rs.getString(2) + ", "
        + rs.getString(3) + ", " + rs.getBigDecimal(4));
  }
  conn.commit();
  stmt.close();
  // Close connection if the application does not need it any more
  conn.close();

}
 

/**
 * Test updates on tables partitioned by PK.
 * 
 * @throws Exception
 */
public void testTransactionalKeyBasedUpdatePartitionedByPk() throws Exception {
  startServerVMs(2, 0, "sg1");
  startClientVMs(1, 0, null);
  Connection conn = TestUtil.jdbcConn;
  conn.setAutoCommit(false);
  Statement st = conn.createStatement();
  st.execute("create schema test default server groups (sg1, sg2)");
  st.execute("create table test.t1 ( PkCol1 int not null, PkCol2 int not null , "
      + "col3 int, col4 int, col5 varchar(10), Primary Key (PkCol1) ) "
      + "Partition by Primary Key server groups (sg1) redundancy 1"+ getSuffix());

  conn.setTransactionIsolation(getIsolationLevel());
  PreparedStatement psInsert = conn.prepareStatement("insert into test.t1 "
      + "values(?, 10, 10, 10, 'XXXX1')");
  // st.execute("insert into test.t1 values(10, 10, 10, 10, 'XXXX1')");
  for (int i = 0; i < 1000; i++) {
    psInsert.setInt(1, i);
    psInsert.executeUpdate();
    conn.commit();
  }
  ResultSet rs = st.executeQuery("select * from test.t1");
  int numRows = 0;
  while (rs.next()) {
    assertEquals("Column value should be 10", 10, rs.getInt(3));
    assertEquals("Column value should be 10", 10, rs.getInt(4));
    assertEquals("Column value should be XXXX1", "XXXX1", rs.getString(5)
        .trim());
    numRows++;
  }
  assertEquals("Numbers of rows in resultset should be one", 1000, numRows);

  PreparedStatement psUpdate = conn.prepareStatement("update test.t1 set "
      + "col3 = 20, col4 = 20, col5 = 'changed' where PkCol1=?");
  // st.execute("update test.t1 set col3 = 20, col4 = 20, col5 = 'changed' where PkCol1=10");
  for (int i = 0; i < 1000; i++) {
    psUpdate.setInt(1, i);
    psUpdate.executeUpdate();
    conn.commit();
  }

  rs = st.executeQuery("select * from test.t1");
  numRows = 0;
  while (rs.next()) {
    assertEquals("Column value should change", 20, rs.getInt(3));
    assertEquals("Columns value should change", 20, rs.getInt(4));
    assertEquals("Columns value should change", "changed", rs.getString(5)
        .trim());
    numRows++;
  }
  assertEquals("Numbers of rows in resultset should be one", 1000, numRows);
  rs.close();
  st.close();
  conn.commit();
  conn.close();

}
 

public void testCreateStatsNonTxn() throws Exception {
  try {
    Properties serverInfo = new Properties();
    serverInfo.setProperty("gemfire.enable-time-statistics", "true");
    serverInfo.setProperty("statistic-sample-rate", "100");
    serverInfo.setProperty("statistic-sampling-enabled", "true");
    serverInfo.setProperty("server-groups", "MYGROUP");

    startServerVMs(1, 0, null, serverInfo);

    startServerVMs(1, 0, null, serverInfo);

    Properties info = new Properties();
    info.setProperty("host-data", "false");
    info.setProperty("gemfire.enable-time-statistics", "true");

    // start a client, register the driver.
    startClientVMs(1, 0, null, info);

    // enable StatementStats for all connections in this VM
    System.setProperty(GfxdConstants.GFXD_ENABLE_STATS, "true");

    // check that stats are enabled with System property set
    Connection conn = TestUtil.getConnection(info);
    conn.setTransactionIsolation(Connection.TRANSACTION_NONE);
    conn.setAutoCommit(false);
    createTable(conn);
    insertRow(conn);
    validatePutStats(2);
    conn.close();

    for (VM vm : this.serverVMs) {
      validatePutStats(vm, false, 1);
    }

    stopVMNums(1, -1);
  } finally {
    GemFireXDQueryObserverHolder.clearInstance();
    System.clearProperty(GfxdConstants.GFXD_ENABLE_STATS);
  }
}
 

public void deleteQueryTestWithTx(boolean usePrepStatement, boolean useCancelProc,
    boolean useThinClient, String testName) throws Throwable {
  final int numServers = 2;
  int tableSize = 40;

  startVMs(1, numServers);
  Connection cxn = _getConnection(useThinClient);
 
  this.createTables(cxn, tableSize);
  
  cxn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
  cxn.setAutoCommit(false);
  
  Statement st = cxn.createStatement();
  st.execute("insert into MyTable values(1, 1)");
  st.execute("insert into MyTable values(2, 2)");
  st.execute("insert into MyTable values(3, 3)");
  st.execute("insert into MyTable values(4, 4)");
  
  ResultSet rs = st.executeQuery("select count(*) from MyTable");
  assertTrue(rs.next());
  int count = rs.getInt(1);
  // 40 committed earlier and 4 added above not yet committed
  assertEquals(44, count);
  
  Statement stmt = null;
  String testQueryString = "delete from MyTable where x > 0";
  if (usePrepStatement) {
    final PreparedStatement pstmt2 = cxn
        .prepareStatement("delete from MyTable where x > ?");
    pstmt2.setInt(1, 0);
    stmt = pstmt2;
  } else {
    stmt = cxn.createStatement();
  }

  final String key = testName;
  CacheSerializableRunnable csr1 = new CacheSerializableRunnable(
      testName) {

    @Override
    public void run2() {
      GemFireXDQueryObserver old = GemFireXDQueryObserverHolder
          .setInstance(new GemFireXDQueryObserverAdapter() {
            private boolean flag = false;

            @Override
            public void onDeleteResultSetOpen(
                com.pivotal.gemfirexd.internal.iapi.sql.ResultSet resultSet) {
              getLogWriter().info("onDeleteResultSetOpen called");
              if (!flag) {
                incrementValueInBBMap(key, numServers);
                Activation a = resultSet.getActivation(); 
                long connId = a.getConnectionID();
                long stmtId = a.getStatementID();
                long execId = a.getExecutionID();
                putStatementUUIDinBBMap(key, connId, stmtId, execId);
                flag = true;
              }
              try {
                Thread.sleep(1000);
              } catch (InterruptedException e) {
              }
            }
          });
    }
  };
  
  // set up the above runnable object
  serverExecute(1, csr1);
  serverExecute(2, csr1);

  // execute the query in a different thread, this will result in 
  // data nodes incrementing a value in BB map in observer
  // callback as an indication to cancel the query
  executeAndCancelQuery(stmt,
      usePrepStatement ? StatementType.PREPARED_STATEMENT
          : StatementType.STATEMENT, 
          numServers, key, testQueryString, useCancelProc);
  

  rs = st.executeQuery("select count(*) from MyTable");
  assertTrue(rs.next());
  count = rs.getInt(1);
  // Tx should have rolled back because of delete query cancellation
  // and so the previously added 4 rows should not show up.
  assertEquals(40, count);

}
 

StatementHelper(Connection conn, boolean autoCommit, int isolation) throws SQLException
{
    this.conn = conn;
    conn.setAutoCommit(autoCommit);
    conn.setTransactionIsolation(isolation);
}
 

/**
 * Test what happens if you update a tuple which is deleted, purged and
 * reinserted
 **/
public void testUpdatePurgedTuple3()
    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
    int firstKey = rs.getInt(1);
    println("T1: read tuple with key " + firstKey);
    rs.next(); // Go to next
    println("T1: read next tuple");
    Connection con2 = openDefaultConnection();
    con2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    try {
        PreparedStatement ps2 = con2.prepareStatement
            ("delete from t1 where id=?");
        ps2.setInt(1, firstKey);
        assertEquals("Expected one record to be deleted", 1, 
                     ps2.executeUpdate());
        println("T2: Deleted record with id=" + firstKey);
        con2.commit();
        println("T2: commit");
        
        // Now purge the table
        ps2 = con2.prepareStatement
            ("call SYSCS_UTIL.INPLACE_COMPRESS_TABLE(?,?,?,?,?)");
        ps2.setString(1, "APP"); // schema
        ps2.setString(2, "T1");  // table name
        ps2.setInt(3, 1); // purge
        ps2.setInt(4, 0); // defragment rows
        ps2.setInt(5, 0); // truncate end
        println("T3: call SYSCS_UTIL.INPLACE_COMPRESS_TABLE");
        println("T3: purges deleted records");
        ps2.executeUpdate();
        con2.commit();
        
        ps2 = con2.prepareStatement("insert into t1 values(?,?,?,?)");
        ps2.setInt(1, firstKey);
        ps2.setInt(2, -1);
        ps2.setInt(3, -1);
        ps2.setString(4, "UPDATED TUPLE");
        assertEquals("Expected one record to be inserted", 1, 
                     ps2.executeUpdate());
        println("T4: Inserted record (" + firstKey + ",-1,-1)" );
        con2.commit();
        println("T4: commit");
    } catch (SQLException e) {
        con2.rollback();
        throw e;
    }
    println("T1: read previous tuple");
    rs.previous(); // Go back to first tuple
    println("T1: id=" + rs.getInt(1));
    rs.updateInt(2, 3);
    println("T1: updateInt(2, 3);");
    rs.updateRow();
    println("T1: updated column 2, to value=3");
    println("T1: commit");
    commit();
    rs = s.executeQuery("select * from t1");
    while (rs.next()) {
        println("T5: Read Tuple:(" + rs.getInt(1) + "," +
                rs.getInt(2) + "," +
                rs.getInt(3) + ")");
        
    }
    
    con2.close();
}
 

/**
 * Test what happens if you update a deleted + purged tuple.
 * The transaction which deletes the tuple, will also
 * ensure that the tuple is purged from the table, not only marked
 * as deleted.
 **/
public void testUpdatePurgedTuple1()
    throws SQLException
{
    getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
    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("T1: Read next Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    int lastKey = firstKey;
    while (rs.next()) {
        lastKey = rs.getInt(1);
        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
            ("delete from t1 where id=? or id=?");
        ps2.setInt(1, firstKey);
        ps2.setInt(2, lastKey);
        assertEquals("Expected two records to be deleted", 
                     2, ps2.executeUpdate());
        println("T2: Deleted records with id=" + firstKey + " and id=" + 
                lastKey);
        con2.commit();
        println("T2: commit");
        ps2 = con2.prepareStatement
            ("call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(?,?,?,?,?)");
        ps2.setString(1, "SPLICE"); // schema
        ps2.setString(2, "T1");  // table name
        ps2.setInt(3, 1); // purge
        ps2.setInt(4, 0); // defragment rows
        ps2.setInt(5, 0); // truncate end
        println("T3: call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE");
        println("T3: purges deleted records");
        ps2.executeUpdate();
        con2.commit();
        println("T3: commit");
    } catch (SQLException e) {
        con2.rollback();
        throw e;
    }
    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("T3: select * from table");
    while (rs.next()) {
        println("T3: Read next Tuple:(" + rs.getInt(1) + "," +
                rs.getInt(2) + "," +
                rs.getInt(3) + ")");
        
    }
    
    con2.close();
    s.close();
}
 

/**
 * Implementation of the stored procedure SET_ISOLATION_JDBC.
 * Sets the the isolation level given as argument on the default connection
 * using Connection.setTransactionIasolation.
 * @param isolation JDBC isolation level constant representing the 
 * new isolation level
 * @throws java.sql.SQLException
 */
public static void setIsolationJDBC(int isolation)
        throws SQLException {
    Connection c = DriverManager.getConnection("jdbc:default:connection");
    c.setTransactionIsolation(isolation);
}