This discussion is archived
4 Replies Latest reply: Aug 13, 2012 10:15 AM by 955481 RSS

17076 : Invalid operation for read only resultset

955481 Newbie
Currently Being Moderated
Hi,

I am trying to update database table through java jdbc application.
But while running the program i am getting the error message " Invalid operation for read only resultset: updateString " with error code 17076.

My program is given below :

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Misc2 {

public static void main(String[] args) {
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
con = JDBCUtil.getOracleConnection();
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
String query = "select * from employees";
rs = stmt.executeQuery(query);
while (rs.next()) {
String fname = rs.getString(3);
if (fname.equalsIgnoreCase("Elmer")) {
rs.updateString(3, "Mark");
rs.updateString(2, "Robert");
break;
}

}
} catch (SQLException ex) {
System.out.println("error code : " + ex.getErrorCode());
System.out.println("error message : " + ex.getMessage());
} finally {
JDBCUtil.cleanUp(con, stmt);
}
}
}


****JDBCUtil Class****


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JDBCUtil {
public static Connection getOracleConnection(){
Connection con = null;
try{
// Load the driver
Class.forName("oracle.jdbc.driver.OracleDriver");
//Establish Connection
con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","ex","ex");
}catch(Exception ex){
ex.printStackTrace();
}
return con;
}

public static void cleanUp (Connection con , Statement stmt){
// Release the resource
try{
if(con != null){
con.close();
}
if(stmt != null){
stmt.close();
}
}catch(Exception ex){
ex.printStackTrace();
}
}

}

Please help me to fix this issue.
  • 1. Re: 17076 : Invalid operation for read only resultset
    gimbal2 Guru
    Currently Being Moderated
    Hm well what I see you do is "select * from". That's great, but in what order are you going to get the columns? You may want to try explicitly selecting the columns you want to update.

    For more information I suggest you google "jdbc updatable resultset". There are plenty of articles describing the ins and outs.
  • 2. Re: 17076 : Invalid operation for read only resultset
    Joe Weinstein Expert
    Currently Being Moderated
    The problem is likely that the driver/DBMS is unable to identify that a given resultset row indicates a single unique DBMS row. It needs to do that in order to do the update statement under-the-covers. So, the driver has downgraded your result set to a non-updateable one. Google about how to form the query so the driver can do what you want, or better yet, just do the update as your own. It is so much more efficient, in fact more proper, to do it:

    "update employees set XXX = ... WHERE YYY = 'Elmer'"

    It is the cardinal performance no-no to extract tons of data you don't need from the DBMS to the client.
  • 3. Re: 17076 : Invalid operation for read only resultset
    rp0428 Guru
    Currently Being Moderated
    >
    But while running the program i am getting the error message " Invalid operation for read only resultset: updateString " with error code 17076.
    >
    Your result using 'SELECT *' is not updateable. Gimbal2 was pointing you in the right direction. You have to specify the columns in the select list to get an updateable result set.

    You also need to use 'updateRow()' to update the database and have a commit somewhere to keep the results.

    This code works for me. Note that I added an explicit SELECT list, the 'updateRow()' method and an explicit COMMIT.
        try {
            con = getOracleConnection();
            stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    //        String query = "select * from employees";
            String query = "select first_name from employees"; -- added explicit SELECT list
            rs = stmt.executeQuery(query);
            while (rs.next()) {
                String fname = rs.getString(1);
                if (fname.equalsIgnoreCase("Adam")) {
                    rs.updateString(1, "Mark");
                    rs.updateRow();                                    -- need this statement to actually update the database
    //                rs.updateString(2, "Robert");
                    break;
                }
            }
            con.commit(); -- added explicit commit for testing
        } catch (SQLException ex) {
    See Performing an UPDATE Operation in a Result Set in the 'Updating Result Sets' section of the JDBC Developer's Guide and Reference
    http://docs.oracle.com/cd/B28359_01/java.111/b31224/resltset.htm#i1024720

    As gimbal2 also alluded it is considered poor practice to use column numbers to perform result set operations when you don't know for certain what column a given number refers to. Before people start jumping all over that statement let me clarify it. The key part is KNOWING what column you are referencing. It is more performant to access result column columns by column number rather than by column name since the methods that take a column name call the integer method under the covers anyway but have to search the array of column names in order to get the column number.

    With your query (SELECT *) there is no way to be sure the column order is the same since the table could be redefined with the columns in a different order or certain columns having been deleted. So for performance LOOP processing column numbers are used inside the loop but those column numbers are determined by using the metadata BEFORE the loop to convert column names to column numbers.

    That way you code (BEFORE the loop) can use column names but you use a set of integer variables (one for each column) for the actual access inside the loop.
  • 4. Re: 17076 : Invalid operation for read only resultset
    955481 Newbie
    Currently Being Moderated
    Thanks all specially rp0428 for your detailed explanation. Now the code is working fine after the modification suggested by you.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points