下面列出了java.sql.Connection#setTransactionIsolation ( ) 实例代码,或者点击链接到github查看源代码,也可以在右侧发表评论。
/**
* 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 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;
}
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());
}
/**
* 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"));
}
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;
}
}
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 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;
}
/**
* 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 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);
}
private void configureConnection(Connection conn) throws SQLException {
if (autoCommit != conn.getAutoCommit()) {
conn.setAutoCommit(autoCommit);
}
if (defaultTransactionIsolationLevel != null) {
conn.setTransactionIsolation(defaultTransactionIsolationLevel);
}
}
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();
}
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();
}
}
}
/**
* 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();
}
/**
* 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();
}
StatementHelper(Connection conn, boolean autoCommit, int isolation) throws SQLException
{
this.conn = conn;
conn.setAutoCommit(autoCommit);
conn.setTransactionIsolation(isolation);
}
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 _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();
}
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);
}
/**
* 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();
}
/**
* 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);
}