• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Problem in commit when coding using java and jdbc

 
Ranch Hand
Posts: 264
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.

 
Sheriff
Posts: 10445
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Why are you using 3 different connections to do those 3 operations?
 
Yogesh Gandhi
Ranch Hand
Posts: 264
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Its a singleton connection. So every time same connection is returned.
 
Jaikiran Pai
Sheriff
Posts: 10445
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 264
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ok...you might be correct...

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

 
Jaikiran Pai
Sheriff
Posts: 10445
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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]
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 264
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 264
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.

 
Ranch Hand
Posts: 163
Eclipse IDE Tomcat Server Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 264
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Use of updated jar ojdbc5.jar didnt help.
The behaviour remains the same.

The data is not written to table.
 
Jaikiran Pai
Sheriff
Posts: 10445
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 264
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi jaikiran,

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

 
Jaikiran Pai
Sheriff
Posts: 10445
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 264
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ok jaikiran....i will be able to post the code tomorrow...its in my office computer..

 
Yogesh Gandhi
Ranch Hand
Posts: 264
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Jaikiran, I have added the updated code..Please have a look !!!

 
Jaikiran Pai
Sheriff
Posts: 10445
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You probably missed the other part of my question earlier:

and how are you testing that the data isn't being committed?

 
This tiny ad is suggesting that maybe she should go play in traffic.
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic