Updating a row in a ResultSet generates an error: "value conflicts"

Question

Our company has written a rather complicated program for transferring data from one database to another. The data that you intend to transfer is marked with a “1” (String / Varchar2) in a specific column. I cannot explain why this is not just a numeric or Boolean field. I think this is a historical remnant.

ResultSet is created as follows:

Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet extractedData = stmt.executeQuery(sql);

If something is found, extract it to a file:

if (extractedData.next()) {
    writeDataToFile(extractedData)
}

A simplified recording method is as follows:

private void writeDataToFile(ResultSet extractedData) {

    extractedData.beforeFirst(); // because of the next() we called before
    writeLines(extractedData);

}

In writeLines()we repeat all the text ResultSetand write it to a file.

Upon completion of this, we can mark the data as migrated by setting the value in the column to "0". We cannot do this earlier if an exception occurs.

extractedData.beforeFirst();
while (extractedData.next()) {      
    extractedData.updateString(updateColumn, "0");
    extractedData.updateRow();
}

, updateRow() SQLException " "

. , 2 .

- , ?

Answer 1

ResultSet . . , - rowid oracle.
.

create table example1 (id number, value varchar2(10));
insert into example1 values(1,'a');
insert into example1 values(2,'b');
insert into example1 values(3,'c');
insert into example1 values(4,'d');
insert into example1 values(5,'e');
commit;

java APP. deleteRow - oracle .

public class Updatable {
    public static void main(String[] args) throws SQLException, Exception {
        Connection conn = ConnectionDefinition.getOracleConnection();
        Statement  createStatement = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet  executeQuery = createStatement.executeQuery("select id, value from example1");       
        while (executeQuery.next()) {               
            int id = executeQuery.getInt("id");
            System.out.println("id " + executeQuery.getString("id") + " value " + executeQuery.getString("value"));
            if (id == 3) {            
                deleteRow();
                Thread.sleep(5000);
                executeQuery.updateString("VALUE", "new value");
                executeQuery.updateRow();
            }
        }
        conn.close();
    }
    public static void deleteRow(){        
        Runnable r = new Runnable() {
            @Override
            public void run() {
                try {
                    Connection conn = ConnectionDefinition.getOracleConnection();
                    PreparedStatement st = conn.prepareStatement("delete from example1 where id = 3");                    
                    st.execute();
                    st.close();
                } catch (Exception ex) {
                   ex.getStackTrace();
                }
            }
        };
        Thread  th = new Thread(r);
        th.start();
    }
}

, Oracle FOR UPDATE . ResultSet executeQuery = createStatement.executeQuery("select id, value from example1 for update");