• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Connection from DataSource only allows "selects"!

 
John Summers
Ranch Hand
Posts: 125
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1683
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34686
367
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
John,
Also check that the id you are using to access the database has write permission.
 
John Summers
Ranch Hand
Posts: 125
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 125
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is your statement executing well with your DB client? Where is the query?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic