PB "Row changed between retrieve and update" typical error handling. Post

This is a common but frustrating error that can create real problems. The cause for this error is one or more of the following:

1. A primary key for the table is not listed in the update properties for the datawindow.
2. The primary key selected allows and has duplicates.
3. The update properties for the datawindow are improperly set for the needs of the application.
4. The data did actually change.

If no primary key is listed, this is usually the result of either the datawindow being created on a table that did not have a primary key when the datawindow was created or the datawindow was created and allow updates was not checked and later checked.

NOTE: If at a later time, the allow updates is checked and you select the table to update, the updateable columns and key columns must be selected since they are not automatically selected.

There are cases that the primary key selected is not a "true primary key" in that there can be duplicates records for each key. in this case the error will only occur when there are actual duplicates for the selected key. often this error will go undetected for a long time until a duplicate value is present. this is a hard one to find since the error will occur on the duplicate value only. this appears to be an intermitted error.

Specifying the WHERE clause for update/delete

Sometimes multiple users are accessing the same tables at the same times and in cases, they may be actually accessing the same data at the same time. this may be a desired situation and the correct update/delete specification will protect the data from partition uption.

Key columns:

This is the least restrictive and will data into uption. this can only be safely used in single user environments and in multi-user environments when the developer can be 100% sure that only one user at a time will access the data.

The danger of this is that since the key column is the only column specified in the WHERE clause, another user can change the data after you retrieve it and when you save, your data will over write the other users data. the only warning you wocould get is if the key column changed, which is very unlikely.

Key and updateable columns:

This is the PowerBuilder default and provides the greatest level of granularity but may be too restrictive for the needs of the application. the where clause contains all the updateable columns that were selected in the update properties of the datawindow not just the updated (modified) columns.

This will guarantee that only one user can update a row at a time. the problem may arise when two users retrieve the same row and each changes different data elements (I. e. one user updates the address of the customer and another updates the middle name ). the first user to save the data will be OK and the other user update will fail.

Key and modified Columns

This is usually a good setting for most multi-user applications. This allows for multiple users to access the same row and both make changes to different data elements of the row.

This wowould prevent the problem abve (I. e. one user updates the address of a customer and another updates the middle name ). with this setting, both users wocould be able to successfully Save the data without any negative impact on the other users data.

Correcting changed data

To correct data errors caused from data changing between retrieve and update (partition t for duplicate keys) the following method is an example of what can be employed:

If a row is changed, the dberror event will be triggered with a value of-3 in the argument sqldbcode and the row number of the failure in the argument row. A message can be displayed to the user and the row highlighted for the user to see. allow the user the option to re-retrieve the changed row or discard it. if the user chooses to re-retrieve the changed data, use the reselect () PowerBuilder function. this will re-retrieve only the row specified. this will allow the user to see the changes to the row and permit them to make any additional changes they desire.

Important:PowerBuilder will stop the update process upon encountering the first error. the update process can be re-called or stopped at this error. it is usually best to continue the update process. this will update the database with all other changes and/or mark any other error rows. if the row that caused the error is in the filter buffer, you must unfilter it if you want the user to correct the problem.

Code A-1 as the return value for the dberror event, this will suppress the default PowerBuilder error box and allow you to display your own message.

Experience:

1: Set the update attribute of datawindow correctly.

2: Do not retrieve or resetupdate datawindow before calling the update () function.

3: Make sure that the data table has the correct primary key settings.