creating updatable resultset

[kedar kanade]

i have created a resultset using following code
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection
("jdbc racle:thin:@w88:1521 racle","user","password");
Statement stmt=con.createStatement
(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs=stmt.executeQuery("select * from SAMPLE");
this code is working well for read only queries but for updates it is giving following exception
java.sql.SQLException: Invalid operation for read only resultset: updateInt
i m not being able to debug
can anybody help???

[This message has been edited by Thomas Paul (edited January 18, 2001).]

[Brent Worden]

The updateXXX methods are only supported by JDBC 2.0 compliant drivers. Your current Oracle driver appearanlty does not support this JDBC 2.0 funtionallity. I think the 8.1.7 drivers do.
------------------
Brent Worden
http://www.Brent.Worden.org/

[david, chen]

I met the exact same problem as Kedar listed below. And if you use "select xx, xx, xxx, xx from sample" instead of "select * from sample", everything will be find (you can get both scrollable, and updatable resultset). So, if any one knows why "select * ...." sql will cause such problem. Thanks in advance
David

Originally posted by kedar kanade:
i have created a resultset using following code
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection
("jdbc racle:thin:@w88:1521 racle","user","password");
Statement stmt=con.createStatement
(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs=stmt.executeQuery("select * from SAMPLE");
this code is working well for read only queries but for updates it is giving following exception
java.sql.SQLException: Invalid operation for read only resultset: updateInt
i m not being able to debug
can anybody help???

[This message has been edited by Thomas Paul (edited January 18, 2001).]

[Andres Bustamante]

This is from the Oracle Documentatio on the Web:
http://www-wnt.gsi.de/oragsidoc/doc_816/java.816/a81354/resltse2.htm#1018134
Result Set Limitations
The following limitations are placed on queries for enhanced result sets. Failure to follow these guidelines will result in the JDBC driver choosing an alternative result set type or concurrency type.
To produce an updatable result set:
A query can select from only a single table and cannot contain any join operations.
In addition, for inserts to be feasible, the query must select all non-nullable columns and all columns that do not have a default value.
A query cannot use "SELECT * ". (But see the workaround below.)
A query must select table columns only. It cannot select derived columns or aggregates such as the SUM or MAX of a set of columns.
A query cannot use ORDER BY.
To produce a scroll-sensitive result set:
A query cannot use "SELECT * ". (But see the workaround below.)
A query can select from only a single table.
A query cannot use ORDER BY.
In fact, you cannot use ORDER BY for any result set where you will want to refetch rows. This applies to scroll-insensitive/updatable result sets as well as scroll-sensitive result sets. (See "Summary of New Methods for Result Set Enhancements" for general information about refetching.)
Workaround
As a workaround for the "SELECT *" limitation, you can use table aliases as in the following example:
SELECT t.* FROM TABLE t ...
--------------------------------------------------------------------------------
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.)
--------------------------------------------------------------------------------

[Michael Arnett]

Just wanted to thank all the responders to this thread. You saved me quite a bit of time, especially with the amount of details each response included. Keep up the good work.
Thanks again!
-MLA

[Vijaya Mantha]

I think it will work with the following sql....
select t.* from t FOR UPDATE.
Thanks and Regards,
VJ.