aspose file tools*
The moose likes JDBC and the fly likes Auto Commit option fails to work Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Auto Commit option fails to work" Watch "Auto Commit option fails to work" New topic
Author

Auto Commit option fails to work

Renjith Mohan
Ranch Hand

Joined: Nov 28, 2008
Posts: 65


I have a class like this:

Class DemoClass{
Connection conn1; // connection variable to DB 1
Connection conn2; // connection variable to DB 2

public void getConnection(){
// connects to database and set the connection variable to conn1 and conn2

// after getting conn1 and conn2, i disabled the auto-commit mode as:
conn1.setAutoCommit(false);
conn2.setAutoCommit(false);
}

public boolean someProcess(){
// here I am doing some sql updates and insert
}

public boolean someOtherProcess(){
// a different sql queries to go
}

public void call(){

try{
someProcess();
someOtherProcess();
conn1.commit();
conn2.commit();
}
catch(Exception e){
//am rolling back the db operations
conn1.rollback();
conn2.rollback();
}
}
}

If any sql exceptions are raised, the rollback is not working.....some of the data are updated in the database.
What could the possible chance ???
Thanks in advance.
Balu Sadhasivam
Ranch Hand

Joined: Jan 01, 2009
Posts: 874

If any sql exceptions are raised, the rollback is not working.....some of the data are updated in the database.
What could the possible chance ???


Where is the sql exception raised in this case , which method ? someProcess(); someOtherProcess();

Usually data will be committed if you close the connection even when exceptions happened ( before rollback is called). Can you provide where you close the connection ?

Renjith Mohan
Ranch Hand

Joined: Nov 28, 2008
Posts: 65

Actually am testing the application...so Iam making exceptions in both someProcess() and someOtherProcess()

try{
//sql code
}
catch(Exception e){
//here am rolling back the db operations
}
finally{
//am closing the Statement object
}

And connections are closed in the call method ()?

So, in case of exceptions, first it enters catch block. there it roll back the process. then it enters finally block..close the statement block..then return to call() method, where the connection is closed.
Balu Sadhasivam
Ranch Hand

Joined: Jan 01, 2009
Posts: 874

Are you able to ascertain that the code reaches the catch block and calls conn1.rollback() methods.. also i would recommend to close the connection close directly in fianlly block.

There are 2 ways a typical update to table happens
1) calling commit() statement
2) closing the connection ( without rollback)

so check if any of the above conditions are satisfied in your code. Without anlaysing your complete code cant predict anything more.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39380
    
  28
"Renjith r" please read the important administrative private message which I am just sending you.
Renjith Mohan
Ranch Hand

Joined: Nov 28, 2008
Posts: 65

class DatabaseConnect
{
Connection conn1, conn2;
//PreparedStatement pstmt1, pstmt2;
public static void main(String[] args) throws Exception
{
new DatabaseConnect().init();
}

public void init(){
try{
handle();
}
catch(Exception e){
e.printStackTrace();
}
}

public void handle() throws Exception{
try{
System.out.println("***Calling connect()***");
connect();
System.out.println("***Calling doQuery()***");
doQuery();
System.out.println("***Calling doAnotherQuery()***");
doAnotherQuery();
conn1.commit();
}
catch(Exception e){
if(null != conn1) conn1.rollback();
if(null != conn2) conn2.rollback();
throw e;
}
finally{
if(null != conn1) conn1.close();
if(null != conn2) conn2.close();
}
}

public void connect() throws ClassNotFoundException, SQLException{
System.out.println("Connecting to database server ");
Class.forName("com.mysql.jdbc.Driver");
conn1 = DriverManager.getConnection("jdbc:mysql://ip:port/dbName",
"user", "****");
System.out.println("Connection object for DB = "+conn1);
conn1.setAutoCommit(false);
}

public void doQuery() throws SQLException{
PreparedStatement pstmt1 = conn1.prepareStatement("INSERT INTO A VALUES(?, ?, ?, ?, ?)");
PreparedStatement pstmt2 = conn1.prepareStatement("INSERT INTO A VALUES(?, ?, ?, ?, ?)");

pstmt1.setInt(1, 8);
pstmt1.setString(2, "value1");
pstmt1.setString(3, "value1");
pstmt1.setString(4, "value1");
pstmt1.setInt(5, 1);

pstmt2.setInt(1, 9);
pstmt2.setString(2, "value2");
pstmt2.setString(3, "value2");
pstmt2.setString(4, "value2");
pstmt2.setInt(5, 1);
try{
pstmt1.executeUpdate();
pstmt2.executeUpdate();

}
catch(SQLException e){
throw e;
}
finally{
//if(null != pstmt1) pstmt1.close();
//if(null != pstmt2) pstmt2.close();
}
}

public void doAnotherQuery() throws SQLException{
PreparedStatement pstmt1 = conn1.prepareStatement("UPDATE A SET field2 = 'updatevalue' WHERE field = ?");
pstmt1.setString(2, "value");

try{
pstmt1.executeUpdate();
}
catch(SQLException e){
throw e;
}
finally{
//if(null != pstmt1) pstmt1.close();
}
}
}

This is my code. Here even if I comment the prepared statement closing at finnally block, the commit is not working.
What could be the flaw ???
Renjith Mohan
Ranch Hand

Joined: Nov 28, 2008
Posts: 65


I found one more clue. If I write both the contents of doQuery() and doAnotherQuery() in a single method, the transaction rollback is working...Then what is the problem in writing in two different methods?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Auto Commit option fails to work