permaculture playing cards*
The moose likes JDBC and the fly likes Connection from DataSource only allows Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Connection from DataSource only allows "selects"!" Watch "Connection from DataSource only allows "selects"!" New topic
Author

Connection from DataSource only allows "selects"!

John Summers
Ranch Hand

Joined: Oct 06, 2003
Posts: 125
Hi,

I'm doing some jdbc code in a Faces app. I get a DataSource from the appserver then a connection from this. I can run 'select' queries but all insert,update and delete fail silently. They appear to have run, the execute method returns fine and no exceptions are thrown but the db is not updated.

This is extremely confusing...I know the connection is working-ish because I can do selects. I'm connecting to Oracle 9i. This is my code:


pllllleeassseee, any ideas?
thanks,
john
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
I presume you mean that you are executing this:



And what is the value of the boolean variable returned?

Please post your code which updates the DB.


SCJP 1.4, SCWCD 1.3, SCBCD 1.3
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

1. check the auto-commit mode of connection
2. if you are managing transactions by any means like ejb,jta make sure you are commiting your transaction

it is adivised that if you are not having transaction via ejb,or jta the set auto-commit mode of connection to false immediate after you create connection

and use conn.commit() after you execute an insert statement or update statement

Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30580
    
154

John,
Also check that the id you are using to access the database has write permission.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
John Summers
Ranch Hand

Joined: Oct 06, 2003
Posts: 125
Hi,

Thanks for your replies.

In response to a suggestion on the Sun Creator forum I changed my code to:


but the problem still persists. The id to log into the database is correct.
When I change my getConnection method to use an ordinary connection i.e.
DriverManager.getConnection...

the sql works fine.

john
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

I am not sure but it seems that you are not commiting transaction.
and when you get connection from datamanager then your auto-commit mode is true.

could you answer to my previous query also paste the complete code here.

Shailesh
John Summers
Ranch Hand

Joined: Oct 06, 2003
Posts: 125
Hi,

Here's my code:


I wasn't aware I had to do connection.commit when autocommit was set to true. Surely the line "boolean results = statement.execute" runs the sql?

john
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
Is your statement executing well with your DB client? Where is the query?
 
GeeCON Prague 2014
 
subject: Connection from DataSource only allows "selects"!