Doubt in Transaction

[Sathish Kumar Govindan]

con.setAutoCommit(false);
Statement stmt = con.createStatement();
stmt.executeUpdate("INSERT INTO Sells VALUES('Bar Of Foo', 'BudLite', 1.00)" );
stmt.executeUpdate("INSERT INTO Sells VALUES('Bar Of Joe', 'Miller', 2.00)" );
//con.commit();
//con.setAutoCommit(true);  
con.close();
System.exit(0);

In the above Code, Consider con is an active Connection and the code is wrapped with proper Exception Handling.. I want to know whether the records will be commited or not...

[Paul Sturrock]

No.

[Sathish Kumar Govindan]

Hi Paul,
<pre>

As You said it was not working.. The Records have been inserted after executing the code....
I am Using Oracle thin driver....I think whenever we don't give commit or rollback and closing the connection smoothly,
the cached transactions are getting commited even though we set AutoCommit as false...

</pre>
with thanks,
Sathish Kumar.G

[Paul Sturrock]

What cached transactions?

If your insert statements are successful, and you can query the database for these two records after your program has completed then one of two things could be true:

the code below is not the code you are running.

your driver is buggy

[Salil Vverma]

Hey Satish,

I tried the following three code sections. In the first one when I used conn.commit(), the changes got committed. I verified that by firing the select query in database from pl/sql developer. In case of second and third code sections changes were not persistent and were not committed.

First -

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

class Test {
	public static void main(String args[]) throws Exception {
		// Load the database driver
		Class.forName("oracle.jdbc.driver.OracleDriver");

		// Get a connection to the database
		Connection conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@salil-verma:1521:XE", "salil", "salil123");

		conn.setAutoCommit(false);
		Statement st = conn.createStatement();
		st.executeUpdate("insert into salil values ('hello')");
		st.executeUpdate("insert into salil values ('Vijay')");

		 conn.commit();
		conn.close();
		System.exit(0);
	}
}
 

Second

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

class Test {
	public static void main(String args[]) throws Exception {
		// Load the database driver
		Class.forName("oracle.jdbc.driver.OracleDriver");

		// Get a connection to the database
		Connection conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@salil-verma:1521:XE", "salil", "salil123");

		conn.setAutoCommit(false);
		Statement st = conn.createStatement();
		st.executeUpdate("insert into salil values ('hello')");
		st.executeUpdate("insert into salil values ('Vijay')");

		conn.rollback();
		conn.close();
		System.exit(0);
	}
}

 

Third

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

class Test {
	public static void main(String args[]) throws Exception {
		// Load the database driver
		Class.forName("oracle.jdbc.driver.OracleDriver");

		// Get a connection to the database
		Connection conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@salil-verma:1521:XE", "salil", "salil123");

		conn.setAutoCommit(false);
		Statement st = conn.createStatement();
		st.executeUpdate("insert into salil values ('hello')");
		st.executeUpdate("insert into salil values ('Vijay')");


		conn.close();
		System.exit(0);
	}
}