Link to home
Start Free TrialLog in
Avatar of rnicholus
rnicholus

asked on

Problems using ResultSet to update a DB record

If I run the code listed at the end of this message, an exception is thrown.  Here is the output during a recent execution attempt:

1
26768073
java.sql.SQLException: Invalid operation for read only resultset: updateString
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
        at oracle.jdbc.driver.BaseResultSet.updateString(BaseResultSet.java:353)

        at oracle.jdbc.driver.OracleResultSet.updateString(OracleResultSet.java:
2824)
        at test2.main(test2.java:46)


Why is this happening?





    public static void main (String args[])
    {
          String path;
          Statement  stmt = null;
          Connection con = null;
          ResultSet results = null;
          
          try
          {
                Class.forName(JDBC_DRIVER).newInstance();
                path = DB_DRIVER + ":@" + DB_HOST + ":" + DB_PORT + ":" + DB_SID;
          
                con = DriverManager.getConnection(path, USER, PWD);
                //con.setAutoCommit(false);
                stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                       ResultSet.CONCUR_UPDATABLE);      
                
                results = stmt.executeQuery("SELECT * FROM " + TABLE + " WHERE city = \'WDC\'");
                
                
                while (!results.isLast())
                {
                      results.next();
                      System.out.println(results.getRow());
                      System.out.println(results.getInt("ID"));
                      results.updateString("description", "TEST");
                }
                
                
                
                //System.out.println(results.getFetchSize());
                
                
                
                
          }
          catch (Exception ex) { ex.printStackTrace(); }
            finally
            {
                  try
                  {
                        if (stmt != null)
                              stmt.close();
                        if (con != null)
                              con.close();
                  }
                  catch (SQLException ex) { System.err.println("Cannot close connection to Oracle DB."); }
            }
    }
Avatar of CEHJ
CEHJ
Flag of United Kingdom of Great Britain and Northern Ireland image

Do you have write access in that account to that table?
Avatar of rnicholus
rnicholus

ASKER

Well, I can execute UPDATE and INSERT SQL statements from my program without any problems.  The problem seems to occur when I attempt to change the table through ResultSet objects.
You also need to ensure that the driver you're using will support an updateable ResultSet
SOLUTION
Avatar of Madhavan Sundarraj
Madhavan Sundarraj
Flag of Saudi Arabia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'm using this driver: oracle.jdbc.driver.OracleDriver

How do I know if this driver (or any for that matter) supports an updatable ResultSet?  Also, if this one does not support updatable ResultSets, how can I find an acceptable one that does?  I apologize for these questions - I'm fairly new to JDBC.
neonlines:

No dice.  Same exception is thrown.
Try the following:

while (results.next())
{
      results.moveToInsertRow();
      System.out.println(results.getRow());
      System.out.println(results.getInt("ID"));
      results.updateString("description", "TEST");
}              
That code results in:

java.sql.SQLException: Invalid operation for read only resultset: moveToInsertRo
w
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
        at oracle.jdbc.driver.BaseResultSet.moveToInsertRow(BaseResultSet.java:5
93)
        at test2.main(test2.java:44)
Ok, I think I found a solution.  
I read somewhere that "SELECT *" queries are not allows when dealing with ResultSets.  So, I modified my SELECT statement from:

results = stmt.executeQuery("SELECT * FROM " + TABLE + " WHERE city = \'WDC\'");

to:

results = stmt.executeQuery("SELECT " + TABLE + ".* FROM " + TABLE + " WHERE city = \'WDC\'");

then, I attempted to use my original while clause.  No dice.  So, I attempted to use the last suggested while clause.  This also didn't work (no exception, but no update).  Finally, I used the while clause with the rowcount variable.  This DID work.  Now, my question is, why DID the clause with the rowcount variable work (with the modified SELECT statement), but not any of the other two?
>>I read somewhere that "SELECT *" queries are not allows when dealing with ResultSets

No, that's not the case - it's fine. Can you post the code that *did* work?
You might also try

results = stmt.executeQuery("SELECT * FROM " + TABLE + " WHERE city = \'WDC\' FOR UPDATE");
Actually that shouldn't need any escape characters:

results = stmt.executeQuery("SELECT * FROM " + TABLE + " WHERE city = 'WDC' FOR UPDATE");
My code suggests otherwise.  If my original select statement is present, an exception is thrown.  If the new select statement is used instead, it works fine.  Anyways, here is the code that works:

    public static void main (String args[])
    {
          String path;
          Statement  stmt = null;
          Connection con = null;
          ResultSet results = null;
          
          try
          {
                Class.forName(JDBC_DRIVER).newInstance();
                path = DB_DRIVER + ":@" + DB_HOST + ":" + DB_PORT + ":" + DB_SID;
          
                con = DriverManager.getConnection(path, USER, PWD);
                //con.setAutoCommit(false);
                stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                       ResultSet.CONCUR_UPDATABLE);      
                
                results = stmt.executeQuery("SELECT " + TABLE + ".* FROM " + TABLE + " WHERE city = \'WDC\'");             
                                int rowcount=1;
                        while (!results.isLast())
                                {
                              results.absolute(rowcount); // moves the cursor to the rowcount th row of result set
                                        results.updateString("description", "test");
                                        results.updateRow(); // updates the row in the data source  
                                        rowcount++;                
                                       //con.commit();
                                 }
   
            //System.out.println(results.getFetchSize());             
          }
          catch (Exception ex) { ex.printStackTrace(); }
      finally
      {
                      try
            {
                  if (stmt != null)
                        stmt.close();
                  if (con != null)
                        con.close();
            }
            catch (SQLException ex) { System.err.println("Cannot close connection to Oracle DB."); }
      }
    }
You're right about the escape characters.  My mistake.  My above post refers to your 2nd to last comment, BTW.
Sorry, I'm losing track of the posts here.  Your suggestion to change my select statement to

results = stmt.executeQuery("SELECT * FROM " + TABLE + " WHERE city = 'WDC' FOR UPDATE");

results in:

java.sql.SQLException: ORA-01002: fetch out of sequence

        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
        at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
        at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
        at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
        at oracle.jdbc.ttc7.TTC7Protocol.fetch(TTC7Protocol.java:1198)
        at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.jav
a:2400)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme
nt.java:2672)
        at oracle.jdbc.driver.OracleStatement.doScrollStmtExecuteQuery(OracleSta
tement.java:7030)
        at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:
578)
        at test2.main(test2.java:38)
Also, my information regarding my statement that "SELECT *" are not allowed when dealing with ResultSets comes from
http://forum.java.sun.com/thread.jspa?forumID=48&messageID=1846790&threadID=417560
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Jim,
You make some good points about SELECT.  You're right, and I don't need to select all columns.  I will attempt to try this next and see what happens.  I'm afraid I don't even know what driver type or version I am using <hides>.  How do I figure this out?

CEHJ,
The example listed on the page you linked to throws the exception I listed in my original question.

In one of the forms i came across it points the same issue like not able to use 'select * from tablename' They suggested 'select tablename.* from tablename'
Please check if this works
This does work.
ok then i will take a note of this as solution for this issue.
Yes, all this seems pretty weird