[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2123
  • Last Modified:

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."); }
            }
    }
0
rnicholus
Asked:
rnicholus
  • 11
  • 8
  • 2
  • +2
2 Solutions
 
CEHJCommented:
Do you have write access in that account to that table?
0
 
rnicholusAuthor Commented:
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.
0
 
CEHJCommented:
You also need to ensure that the driver you're using will support an updateable ResultSet
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
neonlinesCommented:
Instead of the code

  while (!results.isLast())
              {
                   results.next();
                   System.out.println(results.getRow());
                   System.out.println(results.getInt("ID"));
                   results.updateString("description", "TEST");
              }


Try as follows

  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++;                
              }

0
 
rnicholusAuthor Commented:
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.
0
 
rnicholusAuthor Commented:
neonlines:

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

while (results.next())
{
      results.moveToInsertRow();
      System.out.println(results.getRow());
      System.out.println(results.getInt("ID"));
      results.updateString("description", "TEST");
}              
0
 
rnicholusAuthor Commented:
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)
0
 
rnicholusAuthor Commented:
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?
0
 
CEHJCommented:
>>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?
0
 
CEHJCommented:
You might also try

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

results = stmt.executeQuery("SELECT * FROM " + TABLE + " WHERE city = 'WDC' FOR UPDATE");
0
 
rnicholusAuthor Commented:
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."); }
      }
    }
0
 
rnicholusAuthor Commented:
You're right about the escape characters.  My mistake.  My above post refers to your 2nd to last comment, BTW.
0
 
rnicholusAuthor Commented:
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)
0
 
rnicholusAuthor Commented:
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
0
 
CEHJCommented:
0
 
Jim CakalicSenior Developer/ArchitectCommented:
I too have read that you cannot use "select *" with Oracle when you want an updatable ResultSet. Interestingly, this note appears in the Java Tutorial JDBC Trail (http://java.sun.com/docs/books/tutorial/jdbc/jdbc2dot0/sample.html):

"Tutorial reader Nedzad Hodzic reported that in this example, you cannot use SELECT * FROM TAB_NAME. You must name the columns; otherwise the ResultSet is not updateable. This is a bug he encountered when using an Oracle 8.1.6 database with Oracle driver OCI8."

You haven't specified which driver version and type you are using. The page you referenced in the Sun Java Forum does indicate that "select table.*" could be a workaround. This may be driver version and/or type specific. Or it may not have ever worked. In general, it is considered poor practice to use "select *" despite the convenience. You might try specifying all the column names in the select statement and see if that affects the outcome.

Regards,
Jim
0
 
rnicholusAuthor Commented:
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.

0
 
koppchaCommented:
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
0
 
rnicholusAuthor Commented:
This does work.
0
 
koppchaCommented:
ok then i will take a note of this as solution for this issue.
0
 
CEHJCommented:
Yes, all this seems pretty weird
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 11
  • 8
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now