why my rollback doesn't work?

[wei liu]

hi everyone, I wrote a piece of code to test the rollback function.However, it doesn't work, can some one scanning through my code and tell me what the problem is .

  

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
/*Date 07 April 2010
 * this class added to handled the Mysql transcations, with Begin Commit or Rollback.
 * 
 * */
public class EmployeeToDB {
	private DBConnection db;
	private Connection con;
	private Statement stmt;
	
	public Connection getConnection(){return con;}
	
	public EmployeeToDB() throws SQLException{
		db=new DBConnection();
		con=db.getConnection();
	}
	
	public void BeginTrancation() throws SQLException{
		con.setAutoCommit(false);
		
		System.out.println("begin Transcation " +con.getAutoCommit());
	}
	
	public boolean addEmployeeToDB(String id,String password) throws Exception {
		boolean isSuccessful=false;
		stmt=con.createStatement();
		String sql="insert into user values(?,?);";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, id);
		pstmt.setString(2, password);
		pstmt.execute();
			
			// TODO Auto-generated catch block
			System.out.println("addingEmployeeTODB is "+isSuccessful);

		return isSuccessful;
	}
	public void CommitTranscation() throws SQLException{
		con.commit();
		con.setAutoCommit(true);
	}
	public void RollbackTrancation() throws SQLException{
		con.rollback();
	}
	
	public void closeTranscation(){
		db.close();
		try {
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		boolean commit=false;
		EmployeeToDB db=null;
		try{
			db=new EmployeeToDB();
			db.BeginTrancation();
			db.addEmployeeToDB("123456", "root");

				System.out.println("RollBack ....");
			    db.RollbackTrancation();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			db.closeTranscation();
		}
	}

}  

Thanks

[Ulf Dittmer]

What does "it doesn't work" mean? What exactly happens if you run the code?

[wei liu]

Ulf Dittmer wrote:What does "it doesn't work" mean? What exactly happens if you run the code?

the entry"123456" and "root" have been inserted into the database which are not supposed to be inserted since the rollback was called!

[Paul Sturrock]

Which database are you using?

[wei liu]

Paul Sturrock wrote:Which database are you using?

I am using Mysql

[Arpit Panwar]

Make sure you change the Storage engine of your table to InnoDB
I think the default engine MyISAM doesnt support Transactions
Try it and let me know

[wei liu]

Arpit Panwar wrote:Make sure you change the Storage engine of your table to InnoDB
I think the default engine MyISAM doesnt support Transactions
Try it and let me know

Thanks Arpit, It works after change the engine from MyISAM to InnoDB.

Thanks for your help.

[vidhya Narayanan]

wei liu wrote:

Arpit Panwar wrote:Make sure you change the Storage engine of your table to InnoDB
I think the default engine MyISAM doesnt support Transactions
Try it and let me know

Thanks Arpit, It works after change the engine from MyISAM to InnoDB.

Thanks for your help.

Hi .. i too have the same problem. here in this case there is only one sql stmt... i have a set of updates and deletes .. and if i use the roolbak will it work?... thanks in advance

[Jhakda Velu]

Yes, use the same connection object for all and commit after all are successful, rollback otherwise.

Jhakda

[vidhya Narayanan]

Thank u. I have another doubt o use the same connection for all the statements.. should i commit each after each stmt or i can commit at the end .
And can i use commit after the rollback?

[Jhakda Velu]

Since you said you have more than 1 sql, i presume you want to do an atomic transaction. We normally commit after successful completion of all transactions, roll back otherwise.

Jhakda

[vidhya Narayanan]

Jhakda Velu wrote:Since you said you have more than 1 sql, i presume you want to do an atomic transaction. We normally commit after successful completion of all transactions, roll back otherwise.

Jhakda

can i use rollback for DDL ... i think we cant... but wanted to know whether ther is any other method for doing it

[Paul Sturrock]

vidhya Narayanan wrote:

Jhakda Velu wrote:Since you said you have more than 1 sql, i presume you want to do an atomic transaction. We normally commit after successful completion of all transactions, roll back otherwise.

Jhakda

can i use rollback for DDL ... i think we cant... but wanted to know whether ther is any other method for doing it

DDL is usually not transactional, so no.

[Arpit Panwar]

wei liu wrote:

Arpit Panwar wrote:Make sure you change the Storage engine of your table to InnoDB
I think the default engine MyISAM doesnt support Transactions
Try it and let me know

Thanks Arpit, It works after change the engine from MyISAM to InnoDB.

Thanks for your help.

Pleased to help

[vidhya Narayanan]

Paul Sturrock wrote:

vidhya Narayanan wrote:

Jhakda Velu wrote:Since you said you have more than 1 sql, i presume you want to do an atomic transaction. We normally commit after successful completion of all transactions, roll back otherwise.

Jhakda

can i use rollback for DDL ... i think we cant... but wanted to know whether ther is any other method for doing it

DDL is usually not transactional, so no.

I have a method to perform a rollback on the ddl statements. A method with all alter commands. This works fine if there is any error in the end of the actual code. If the actual code fails in-between the rollback method is called but the issue is there is an error while the alter stmts execute that were not created at all. Can anyone help me.