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

Rollback Problem

S Srikanth
Greenhorn

Joined: Sep 09, 2002
Posts: 14
Hi ,
I am using a JDBC connection and inserting an entry into a table.
After that i am committing the connection and coming back.
After i commit the connection if any error comes in further processing, the entry that i have inserted is getting deleted from the table.
Is it possible that an entry to get deleted after i commit the connection?
Pls help.

Also , is there any way to find out who is deleting the entry from the table? I mean by what reason the entry of a table is getting deleted.

My code is called from a GUI which is deployed under TOMCAT. Is the entry deletion is because of TOMCAT ?

Please help.

Regds,
Srikanth

Following is the code snippet:

Connection conn =DriverManager.getConnection (props_.getProperty("DB_URL"),props_.getProperty("DB_USER"),props_.getProperty("DB_PASSWD"));
Statement stmt =conn.createStatement();
conn.setAutoCommit(true);
String script ="insert into TABLEXyz VALUES( to_date('"+formatDate+"','yymmdd'))";
stmt.executeUpdate(script) ;
conn.commit();
conn.close();
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Three things I would say.

One, you have setAutoCommit backwards. To start a transaction you should be calling setAutoCommit(false) not true.

True says "I am not in a transaction so automatically commit each query as I execute them"

False says "I am in a transaction so do NOT automatically commit until I tell you"

Two if you want to see who did what to a row... I think you need to provide more information about what database you are using because the answers will vary from vendor to vendor. But to point you in the general direction of something that would work I would say some sort of DELETE trigger would do it.

Three in your code snippet you do not explictly close your statement. You should always close all ResultSets and Statements along with Connections in the reverse order as you created them. Otherwise you may well be leaking resources.
[ September 22, 2005: Message edited by: Maximilian Stocker ]
S Srikanth
Greenhorn

Joined: Sep 09, 2002
Posts: 14
Hi,
Thanks for your reply.

I have mentioned setAutoCommit(true) because i have a requirement that as soon as i put a row into that table i need to commit it as well.

I am using ORACLE 10g Database in my case.
I will close the statement as u said and try.

Please provide me the trigger which you are saying so that i can execute it in my database and see who is deleting the row.

Thanks and Regds,
Srikanth
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Originally posted by S Srikanth:
Hi,
Thanks for your reply.

I have mentioned setAutoCommit(true) because i have a requirement that as soon as i put a row into that table i need to commit it as well.

I am using ORACLE 10g Database in my case.
I will close the statement as u said and try.

Please provide me the trigger which you are saying so that i can execute it in my database and see who is deleting the row.

Thanks and Regds,
Srikanth



There are two different issues here. Yes when you setAutoCommit to true it goes in right away. It does NOT neccessarily use a transaction at all. If you try and rollback though it should fail.

The second issue is about the trigger. You need an Oracle DBA for that I think and I am not one sorry.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Rollback Problem