First of all, I would like to give a brief definition and description for "SAVEPOINT".
"A savepoint is a way of implementing subtransactions (also known as nested transactions) within a relational database management system by indicating a point within a transaction that can be "rolled back to" without affecting any work done in the transaction before the savepoint was created. Multiple savepoints can exist within a single transaction. Savepoints are useful for implementing complex error recovery in database applications — if an error occurs in the midst of a multiple-statement transaction, the application may be able to recover from the error (by rolling back to a savepoint) without needing to abort the entire transaction.
A savepoint can be declared by issuing a SAVEPOINT name statement. All changes made after a savepoint has been declared can be undone by issuing a ROLLBACK TO SAVEPOINT name command. Issuing RELEASE SAVEPOINT name will cause the named savepoint to be discarded, but will not otherwise affect anything. Issuing the commands ROLLBACK or COMMIT will also discard any savepoints created since the start of the main transaction.
Savepoints are supported in some form or other in database systems like PostgreSQL, Oracle, Microsoft SQL Server, MySQL, DB2, and Firebird. Savepoints are also defined in the SQL standard."
This definition is quoted from wikipedia.
The first time I tried SAVEPOINT on Oracle 9i Database using JDK 1.4 - namely JDK 1.4.2_04 on JDevelope 10g- was about 2 years ago. At that time, I have been encountered with a problem when I tried to use it calling the method setSavePoint() in the java.sql.Connection interface. I also tried it, then, using JDK 5 but do not remember the exact version and faced the same problem. The problem was that I had the run time exception "java.lang.AbstractMethodError" and from the JDK documentation I understood that this can happen in run time only if a definition of some class has incompatibly changed since the currently executing method was last compiled.
I then have posted that issue on SUN forums, but unfortunately had no reply with a solution. You can find this post on the following link
Savepoint Issue
And this is a sample code similar to my own:
String s = "jdbc:oracle:thin:@" + strHostName + ":" + strPortNo + ":" + strDBName;
Class.forName(strDriverName);
Connection connection = DriverManager.getConnection(s,strUserName,strPassword);
connection.setAutoCommit(false);
.
.
.
Savepoint save =connection.setSavepoint();
.
.
.
Anyway, the only solution I have done was a little workaround. I just used the "SAVEPOINT" of Oracle Engine itself. All what I have done was something like the follwoing:
Class.forName(strDriverName);
Connection connection = DriverManager.getConnection(s,strUserName,strPassword);
connection.setAutoCommit(false);
.
.
.
Statement stmt = connection.createStatement();
for(int i=o; i< someArray.length ; i++)
{
.
.
stmt.execute("SAVEPOINT point"+i);
.
.
}
And then when I wanted to rollback to a specific savepoint I wrote code similar to
stmt.execute("ROLLBACK to point"+2);
This can rollback to the savepoint named "point2".
Cooool. It worked just fine for me.
To be frank, I have not tried to work with SAVEPOINT again lately, maybe because I did not encounter a business case that let me try it again. Also I have never tried it with JDK 6 or any other Oracle JDBC Driver, which I think was the main reason for the problem. But I am eager to know if anyone has recently worked with it, on what JDK version, and whether it has worked well from within the Java code and invocation for the setSavePoint() method of the java.sql.Connection interface and with what Oracle version and Oracle JDBC Driver version.
Waiting for your comments and experience conerning this matter.
1 comment:
very helpfull THX
Post a Comment