Select ... For Update

, , , , ,
1

If I want to select a row and lock it for cross-examination before making an update and release, how can I use select for update to achieve what I want to do?

An example would be much apprecaited.

Thanks.

2

The technique varies depending on which data access method you are using. For Java, it would be something like this:

                ps = connectionObj.prepareStatement("SELECT AREA_CODE,PHONE,BIRTH,PERS_ID FROM EMPLOYEES WHERE PERS_ID = ? FOR UPDATE OF AREA_CODE,PHONE,BIRTH" );
                ps.setString(1, "pick an id");
                ResultSet rs2;
                rs2 = ps2.executeQuery();
                while (rs2.next())
                    {
                    ps3 = connectionObj.prepareStatement(" UPDATE EMPLOYEES SET AREA_CODE=?,PHONE=?,BIRTH=? WHERE CURRENT OF " + rs2.getCursorName() );
                    ps3.setInt(1, rs2.getInt(1)+1);
                    ps3.setInt(2, rs2.getInt(2)+1);
                    ps3.setInt(3, rs2.getInt(3)+1);
                    ps3.executeUpdate();
                    }
                rs2.close();
3

Larry,

I realize that I did not ask my questions correctly. I need to be able to hold a series of rows and update the column of each rows. In my case, the select would return about 10 rows and I want to hold all 10 rows, update each row, and do a commit on all 10 rows at once.

Can I do a declare cursor and then do an open cursor with connx/adabas?

Thanks,
Min

4

Yes this is possible using the provided example - just modify the “outer” query to return more than 1 row. You will notice that the inner loop is already designed to process multiple rows allowing you to update them. The last and final step would be a commit - to commit all 10 changes at once.

Please keep in mind however that adabas transactions are not like a typical relational database - meaning that adabas has “dirty reads” and does not support typical isolation levels. As soon as a row is updated, that update is seen by all readers - even before the commit occurs. The commit/rollback just provides a mechanism to rollback a group of changes as a unit of work - it does not provide any isolation.

5

Larry,

Thanks for clarifying. Stupid me wrote a commit after the 1st update. No wonder my ADABAS REVIEW report shows that all subsequent L6 were no longer held for update.

Thank you for your patient.

Min