File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Rollback in Oracle Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Rollback in Oracle" Watch "Rollback in Oracle" New topic
Author

Rollback in Oracle

carina caoor
Ranch Hand

Joined: Jun 23, 2007
Posts: 300

hi in my application i am doing certain inserts into table which has certain unique constraints, when these unique constraints are voilated i want to roll back the whole bulk inserts,but my code does not rollback the transaction.

[code]
----------------------------------------------------------------------------

try
{
rs=stmt.executeQuery("Select MAX(CAST(CM_BATCH_SEQ as int)) from CM_FA_PLOT_STG_RUN");
while(rs.next())
{
batchseq=rs.getInt(1);
}
}
catch(Exception e)
{

String error=e.toString();

request.setAttribute("ProcedureError", error.replace("\n",""));
RequestDispatcher requestdispatcher=request.getRequestDispatcher("src/jsp/ProcedureError.jsp");
requestdispatcher.forward(request,response);
e.printStackTrace();
return;

}
int val= stmt.executeUpdate("INSERT INTO CM_FA_PLOT_STG_RUN (CM_BATCH_SEQ,RUN_DTTM,CM_RUN_STATUS,CM_BATCH_EXEC_STS)" +
"Select MAX(CAST(CM_BATCH_SEQ as int )+1 ),getdate(),'P','M' from CM_FA_PLOT_STG_RUN");
//System.out.println("val value"+val);
if(val==1)
{
int resultdata=0;
try
{
batchseq=batchseq+1;
for(int k=0;k<Integer.parseInt(count);k++)
{

int result=stmt.executeUpdate("INSERT INTO CM_FA_PLOT_STG_UP " +
"(CM_BATCH_SEQ,CM_ASPCL_CD,CM_PLOT_UP_STG_FLG)" +
"VALUES("+batchseq+",'"+plotno[k]+"','P')");
System.out.println("the result is"+result);
resultdata=resultdata+result;

}

}
catch(Exception e)
{
String error=e.toString();
request.setAttribute("ProcedureError", error.replace("\n",""));
RequestDispatcher requestdispatcher=request.getRequestDispatcher("src/jsp/ProcedureError.jsp");
requestdispatcher.forward(request,response);
e.printStackTrace();
return;

}

finally
{

if(resultdata==Integer.parseInt(count)){
System.out.println("when equal resultdata"+resultdata);
connection.commit();
}
if(resultdata!=Integer.parseInt(count)){
System.out.println("when not equal"+resultdata);
connection.rollback();
}


}


----------------------------------------------------------------------------

I have written the code to rollback in the finally block which definitly executes, the control even enters the block
if(resultdata!=Integer.parseInt(count)){}
but my transaction doesnot gets rollback please suggest me where and what am i doing wrong
carina caoor
Ranch Hand

Joined: Jun 23, 2007
Posts: 300

I got it ... just need to setAutoCommit(false)
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39103
    
  23
There are probably methods in the Connection or Statement which do the rolling back or auto-committing for you.
Have you written start transaction anywhere? That would probably be a better solution because it is atomic and will roll back automatically if there is an error.

There is something peculiar about a method with that many try-catches in. It just feels odd to me. It would be easier to read if you used the code button and maintained indentation.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Rollback in Oracle