permaculture playing cards*
The moose likes JDBC and the fly likes Problem in commit when coding using java and jdbc Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem in commit when coding using java and jdbc" Watch "Problem in commit when coding using java and jdbc" New topic
Author

Problem in commit when coding using java and jdbc

Yogesh Gandhi
Ranch Hand

Joined: Dec 05, 2008
Posts: 207
Hey Friends,

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.

Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 9930
    
159

Why are you using 3 different connections to do those 3 operations?

[My Blog] [JavaRanch Journal]
Yogesh Gandhi
Ranch Hand

Joined: Dec 05, 2008
Posts: 207
Its a singleton connection. So every time same connection is returned.
Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 9930
    
159

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
Ranch Hand

Joined: Dec 05, 2008
Posts: 207
Ok...you might be correct...

Even if it returns different connections...then is it a problem?

Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 9930
    
159

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]
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

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.)
Yogesh Gandhi
Ranch Hand

Joined: Dec 05, 2008
Posts: 207
Actually i m not at my computer now. So cannot test....

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
Yogesh Gandhi
Ranch Hand

Joined: Dec 05, 2008
Posts: 207
Somebody suggested that I need to use ojdbc5.jar if I am using jdk1.5 (which I am)

I'll update the results once I get to test it with this jar.

Bill Clar
Ranch Hand

Joined: Sep 21, 2006
Posts: 150

Martin Vajsar wrote:(And it seems you cannot commit the connections on your own, probably because transactions are managed by the data source.)


Could the Data Source commit the transactions when the connection is returned to the pool?

Yogesh Gandhi
Ranch Hand

Joined: Dec 05, 2008
Posts: 207
Use of updated jar ojdbc5.jar didnt help.
The behaviour remains the same.

The data is not written to table.
Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 9930
    
159

Please post the new code that you are using and how are you testing that the data isn't being committed?

Yogesh Gandhi
Ranch Hand

Joined: Dec 05, 2008
Posts: 207
Hi jaikiran,

The code remains the same. Its just that i create the connection only once and use it for every statement.

Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 9930
    
159

Yogesh Gandhi wrote:

The code remains the same. Its just that i create the connection only once and use it for every statement.


I would like to see that new code, please.
Yogesh Gandhi
Ranch Hand

Joined: Dec 05, 2008
Posts: 207
Ok jaikiran....i will be able to post the code tomorrow...its in my office computer..

Yogesh Gandhi
Ranch Hand

Joined: Dec 05, 2008
Posts: 207
Hi Jaikiran, I have added the updated code..Please have a look !!!

Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 9930
    
159

You probably missed the other part of my question earlier:

and how are you testing that the data isn't being committed?
 
 
subject: Problem in commit when coding using java and jdbc
 
Similar Threads
Problem in getting the data from DB using servlets
Returning mutple columns in a vector
Writing values to Excel from Applet
Help regarding insertion in the database
error in compiling jsp