I am having a problem in the following code, which inserts 2 rows in a table and then tries to read it using select.
The problem is that the select is not able to read the records inserted using the two inserts above.
Moreover, there is no data visible in the database table.
If I use setAutocommit(true) or conn.commit(); then I get the following error
Cannot use autoCommit(true) when a global transaction is active.
Can someone please give me some clarity on what is the really wrong in what I am doing.
Yogesh Gandhi wrote:Its a singleton connection. So every time same connection is returned.
That's not what the code suggests:
You are using a DataSource to return a connection and DataSource will not return the same connection to different clients (multiple invocations in this case) when the connection is already in use.
Yogesh Gandhi wrote:
Even if it returns different connections...then is it a problem?
It does matter. Connections returned by DataSource can be enrolled in transactions. So when you get 3 different connections you can be in 3 separate transactions and depending on the Connection isolation level (which you can read here http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html) the data from one transaction may or may not be visible to the other transaction.
If you want to give it a quick try, just change the code to call getConnection() once and keep using that connection for all those 3 operations.
[Edited to make it clear that the transactions can be different for those 3 connections]
Yogesh Gandhi wrote:Even if it returns different connections...then is it a problem?
Yes, it is exactly the problem.
Connection doesn't see changes made by other connection until the other connection commits. It's the very basic of database processing, though even us old dogs get burned by it from time to time.
(And it seems you cannot commit the connections on your own, probably because transactions are managed by the data source.)
Assuming whatever said above is correct.... What is the solution to the problem? How can i get the data committed?
Something more interesting is that it works when i use jdev 10g R2 and it doesnt works when i use jdev 10g r3.
10g R2 uses java 1.4
10g R3 uses java 1.5