Connection from DataSource only allows "selects"!

[John Summers]

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:

Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:comp/env/SDRDEV");
Connection connection = ds.getConnection();
Statement stmnt = connection.createStatement();
statement.execute("some sql");

pllllleeassseee, any ideas?
thanks,
john

[Roger Chung-Wee]

I presume you mean that you are executing this:

stmnt.execute("some sql");

And what is the value of the boolean variable returned?

Please post your code which updates the DB.

[Shailesh Chandra]

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]

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

[John Summers]

Hi,

Thanks for your replies.

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

Statement statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
statement.executeQuery("sql");

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]

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]

Hi,

Here's my code:

/**
 * Executes an SQL query which returns no results.
 *  
 * @param query
 * @throws Exception
 */
public void executeScalar(String query, User auditor) throws Exception
{
Connection connection = null;
Statement statement = null;
 
try
{
connection = this.getConnection(auditor);
statement = connection.createStatement();
boolean results = statement.execute(query);
if (results)
{
m_logger.error("query returned results when shouldn't have!:" + query);
}
}
finally
{
try
{
statement.close();
connection.close();
}
catch (java.sql.SQLException sx)
{
m_logger.error("ERROR could not close connection");
}
}
}
 

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]

Is your statement executing well with your DB client? Where is the query?