ResultSet Sensitive and Concurrent Updatable Problem

[awad saleh]

Hello Java Ranch Masters,

I have created a Statement object that will generate ResultSet object with the given type and concurrency.
But when i m trying to update the resultset object is given DataAccessException.. Why I m getting ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READONLY
resultSet though i have made it ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE...??

I m using oracle XE database.........

Please given the solution for it......


Connection connection = null;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
connection = DriverManager.getConnection("jdbcracle:thin:@newideas:1521racle","cold","storage");
DatabaseMetaData dmd= connection.getMetaData();
Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet resultSet=statement.executeQuery("select * from item");

System.out.println("type "+resultSet.getType());
System.out.println("concurancy "+resultSet.getConcurrency());


resultSet.first();
resultSet.updateInt("CHARGE_PER_DAY",500);



resultSet.updateRow();
}
catch (Exception e)
{
e.printStackTrace();
}

[Balu Sadhasivam]

Awad,

Please elaborate "resultset object is given DataAccessException" ? Is it standalone or in framework ? also use code tags for better readibilty.

[awad saleh]

Hi Balu Sadhasivam,
I mean when even i am doing updating the column value using below code in the current result set it is throwing an exception. Though i am creating a statement with

ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE .

resultSet.updateInt("CHARGE_PER_DAY",500); // Throwing an exception while executing this line.

Hope you understand....


Regards,
Awad

[Balu Sadhasivam]

I mean when even i am doing updating the column value using below code in the current result set it is throwing an exception. Though i am creating a statement with

Awad,

Creating a statement with ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE , does not necessarily mean that your Driver supports resultset concur update and scrollable. They are the parameters set to request the driver , but the driver may not support and return defualt resultset which is not updatable anf only forward scrollable.

To confirm that check below values and see if the returned resultset really concur update and scrollable.

check the value of resultSet.getType() and resultSet.getConcurrency() assuming resultSet is instance of ResultSet with
this

From API
public static final int CLOSE_CURSORS_AT_COMMIT 2
public static final int CONCUR_READ_ONLY 1007
public static final int CONCUR_UPDATABLE 1008
public static final int FETCH_FORWARD 1000
public static final int FETCH_REVERSE 1001
public static final int FETCH_UNKNOWN 1002
public static final int HOLD_CURSORS_OVER_COMMIT 1
public static final int TYPE_FORWARD_ONLY 1003
public static final int TYPE_SCROLL_INSENSITIVE 1004
public static final int TYPE_SCROLL_SENSITIVE 1005

[awad saleh]

Hi bala Sadhasivam,

I got to know why i was not getting updatable resultset.

Hint:
There is a simple way to determine if your query will probably produce a scroll-sensitive or updatable result set: If you can legally add a ROWID column to the query list, then the query is probably suitable for either a scroll-sensitive or an updatable result set. (You can try this out using SQL*Plus, for example.)


Here in the below code i was not specifying the columns , only i was using * so i was not getting updatable resultset Object.

ResultSet resultSet=statement.executeQuery("select * from item"); //

Instead of using * ,we should write the query in this form i.e; "SELECT CHARGE_PER_DAY FROM ITEM";

I GOT THE SOLUTION FROM THIS LINK
http://download.oracle.com/docs/cd/B10501_01/java.920/a96654/resltset.htm


Regards,

Awad.

[Balu Sadhasivam]

Awad,

Yes that was my next solution to you , i broke my head few years back with the similar issue !! you got that before.