Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I am running a simple update statement in my java code to an oracle database (JDBC call) and it updates when record is found but when record doesn't exist i get " java.sql.SQLException: ORA-01002: fetch out of sequence" exception. Here is my code, why exception? Where should I improve it. Code sample would be very helpful.

public String preformUpdate(String someValue){
    ResultSet rs;
    String result = "Update failed record not found.";
    StringBuffer sb = new StringBuffer();

    try{
        conn = DBConnect.getInstance().dbOracleConnect();   

        StringBuffer sbUpdate = new StringBuffer();
        sbUpdate = new StringBuffer("UPDATE sometable ");
        sbUpdate.append("SET value1 = ? ");
        sbUpdate.append("WHERE value2 = ?" );

        ps = conn.prepareStatement(sbUpdate.toString());  
        ps.setString(1, "0");
        ps.setString(2, someValue);

        rs = ps.executeQuery();

        if (rs.next()){
            result = "Value updated";               
        }

    }
    catch (Exception e){
        e.printStackTrace();
    }
    finally{
        JDBCHelper.close(cs);   
        JDBCHelper.close(conn);         
    }

    return result;
}
share|improve this question
 
I'm not a Java dev, but from what I know of other languages, I think the rs.next() may be the problem, as an update query probably returns no result set, so the next() fails... –  Bartdude Jun 19 at 15:37
 
Haven't you ever coded an SQL insertion in Java before? –  user1598390 Jun 19 at 15:49
 
I am just learning, sorry if this is a silly question. –  JS11 Jun 19 at 15:52
add comment

1 Answer

up vote 6 down vote accepted

You should use executeUpdate() instead of executeQuery() since you're NOT firing a SELECT.

int numberOfRowsAffected = ps.executeUpdate();

This method returns the number of rows actually updated in the database.

share|improve this answer
 
Ravi Thapliyal this takes care of the exception but when it finds a record to update is just hangs on that line int numberOfRowsAffected = ps.executeUpdate(); and i am updating just a single record. –  JS11 Jun 19 at 15:54
 
Hangs as in the program does not terminate? Does it throw any exception on the console? Could you update your code block for added clarity? Also, is the data type of value1/2 varchar in database? –  Ravi Thapliyal Jun 19 at 16:00
 
It just hangs no exceptions and program doesn't terminate –  JS11 Jun 19 at 16:03
 
int numberOfRowsAffected = ps.executeUpdate(); if (numberOfRowsAffected != 0){ result = "record updated"; } –  JS11 Jun 19 at 16:04
 
Hanging mid execution indicates that the table has probably been locked and hence your update is in wait. If possible restart the database. Make sure that no other database transaction is using this table while you try to update it. –  Ravi Thapliyal Jun 19 at 16:10
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.