Help needed in rollback behavior

[Ravinder Reddy <pa...@students.iiit.ac.in>]

 	hi,
 		The following test fixture is failing when run under junit.

public void testRollbackBehavior() throws SQLException	{
 		Statement s = createStatement();
  s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
  PreparedStatement pStmt = prepareStatement("insert into SESSION.t2 values (?, ?)");
 		pStmt.setInt(1, 21);
 		pStmt.setInt(2, 1);
 		pStmt.execute();
 		pStmt.close();
 		ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
 		rs1.next();
 	assertEquals(21 , rs1.getInt(1));
 	assertEquals(1 , rs1.getInt(2));
 		rs1.close();
 		rollback();//RollBack
 	//Now select from SESSION.t2 should fail
 	assertStatementError("42X05" , s , "select * from SESSION.t2");
 		s.close();
}
 	After rollback() is called , I am expecting that the table
SESSION.t2 is no longer available and hence I asserted it to assertStatementError("42X05......)
But the fixture is failed as the statement "select * from SESSION.t2" is not giving any error
and it is executed as normally meaning the table SESSION.t2 was not dropped.
 	I have setted getConnection().setAutoCommit(false); in the setUp() 
method.
 	what's wrong with the code.?.can anybody there help in this plzz.

Thanks in advance.
  -- 
******************************************************************************

    Every problem that has been solved can be solved again in a better way

                                                   - Ravinder Reddy

*******************************************************************************


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Re: Help needed in rollback behavior

[Ravinder Reddy <pa...@students.iiit.ac.in>]

 		ThankYou kathey for your quick reply.
 	My serious apologies for not revealing the full details initially  and
wasting your valuable time.
 	Soon after getting your reply I realized that I have a method call 
dropSESSIONTables() {that is a utility method I have written to help my TestCase}
after getConnection().setAutoCommit(false) in setUp() method.
 	The utility method is used to delete the SESSION schematables.
Since It is after getConnection().setAutoCommit(false) statement , when a rollback()
is called the statements in the utility method also undone.Unfortunately 
one of them is SESSION.t2.So It was not giving error.
 	I resolved the error by changing the order of the above 2 
statements in setUp() method.
 		Once again my apologies.

Thank You.
On Thu, 5 Jul 2007, Kathey Marsden wrote:

> Ravinder Reddy wrote:
>>     After rollback() is called , I am expecting that the table
>> SESSION.t2 is no longer available and hence I asserted it to 
>> assertStatementError("42X05......)
> I cut and pasted your fixture into a test and put 
> getConnection().setAutoCommit(false) into setUp as you described and the test 
> ran fine.   The only way I could reproduce what you describe is to first 
> create a regular table session.t2. Does maybe another part of the test do 
> this and not drop the table?
>
> ij> create table session.t2(c21 int, c22 int);
> 0 rows inserted/updated/deleted
> ij> commit;
> ij>  DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged;
> 0 rows inserted/updated/deleted
> ij> insert into session.t2 values(21,1);
> 1 row inserted/updated/deleted
> ij> rollback;
> ij>  select * from session.t2;
> C21        |C22
> -----------------------
>
> 0 rows selected
> ij>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

-- 
******************************************************************************

    Every problem that has been solved can be solved again in a better way

                                                   - Ravinder Reddy

*******************************************************************************


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Re: Help needed in rollback behavior

[Kathey Marsden <km...@sbcglobal.net>]

Ravinder Reddy wrote:
>     After rollback() is called , I am expecting that the table
> SESSION.t2 is no longer available and hence I asserted it to 
> assertStatementError("42X05......)
I cut and pasted your fixture into a test and put  
getConnection().setAutoCommit(false) into setUp as you described and the 
test ran fine.   The only way I could reproduce what you describe is to 
first create a regular table session.t2. Does maybe another part of the 
test do this and not drop the table?

ij> create table session.t2(c21 int, c22 int);
0 rows inserted/updated/deleted
ij> commit;
ij>  DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged;
0 rows inserted/updated/deleted
ij> insert into session.t2 values(21,1);
1 row inserted/updated/deleted
ij> rollback;
ij>  select * from session.t2;
C21        |C22
-----------------------

0 rows selected
ij>