Updating records Professional

The REPLACE command is used in Xbase languages to change data stored in a single table row. If new data is added to a table, a new row must be added to the table with the APPEND BLANK command before data can be written. Both commands must take into consideration that data may be accessed/changed concurrently from multiple client stations and this requires a programmer to implement locking mechanisms. A typical programming pattern demonstrating this issue is shown in the following code:

IF RLock() 
   REPLACE FIELD->LASTNAME WITH cLastName 
   DbCommit() 
   DbUnlock() 
ELSE 
   Alert( "Record is locked. Cannot update data", {"Ok"} ) 
ENDIF 

If a record lock is obtained via RLock(), new data may be written to this record using REPLACE. The record lock is removed afterwards with DbUnlock(). When the record lock fails, a message is displayed to inform the user that edited data cannot be written to the database due to a locking conflict.

This code pattern is common in Xbase database programming (DBF files) but it is unsuitable when accessing SQL DBMSs from Xbase++ programs using the ODBCDBE. The reason is: SQL DBMS resolve concurrency issues on their own. It is up to the DBMS to identify "who may change data" and/or to update a table row. The task to obtain a record lock with RLock() is no longer the responsibility of the client application, but is resolved by the DBMS. This means: all locking issues (RLock(), DbRLock(), FLock(), DbRunlock(), DbUnlock()) can be left to the DBMS. Note, however, that the functions required for resolving concurrency issues can still be called with the ODBCDBE. It simulates an appropriate behavior so that the program logic of an existing Xbase++ application is not affected when a transition is made from DBF tables to an SQL DBMS.

There is a new category of errors that can occur when a record is updated in an SQL table by an Xbase++ program, and this is what must be taken into account.

SQL DBMS allow for defining rules that must be complied with when a table row is updated. A DBMS may monitor rules for INSERT, UPDATE, DELETE operations, or it may have defined a limited range of values valid for single database fields. This means: there is a variety of possible error conditions that may occur when data is changed in a table managed by a SQL DBMS. Rules defined on the server side can be violated by a REPLACE command, and this is what must be taken care of.

A generic programming pattern to handle "rule violations" is given below:

01: LOCAL oErr, bErr 
02: LOCAL nNumber := 12345 
03: 
04: bErr := Errorblock( {|o| Break(o) } ) 
05: 
06: BEGIN SEQUENCE 
07:    REPLACE FIELD->TINYINT WITH nNumber 
08:    DbCommit() 
09:    ErrorBlock( bErr ) 
10: 
11: RECOVER USING oErr 
12:    ErrorBlock( bErr ) 
13: 
14:    Alert( "Client error: " + oErr:description + ";" + ; 
15:           "Server error: " + DbSession():getLastMessage() ) 
16: 
17:    IF <Error cannot be corrected> 
18:       DbRRollback() 
19:    ENDIF 
20: END SEQUENCE 

The REPLACE command is embedded in a BEGIN .. RECOVER .. END SEQUENCE structure. This is required to handle errors that may occur when data is changed on the client side and sent back to the server. The database field to store a new value is assumed in this example to have the SQL data type TINYINT. This data type limits the largest number that can be stored in the field to 255. As a result, this code would produce a runtime error since the value 12345 cannot be stored in this field.

The runtime error would occur in line #7 when the REPLACE command is executed. Xbase++ is able to detect this kind of rule violations since the valid range of numbers is known from the field's data type.

Another error condition, however, is possible that cannot be detected immediately when REPLACE is executed. Assume the program would store the value 200 in the field. This fits into the valid range. Now let us assume that the database designer has defined a constraint for this field on the server side and the valid range is restricted to the numbers 1 to 99. In this case, the value 200 violates the constraint and a runtime error is generated. This error would occur in line #8 when changed data is commited to the server. Since the constraint is defined on the server side, Xbase++ is unable to detect these kind of errors until changed data is validated by the DBMS.

The analysis of an error condition requires an application to react to both scenarios, i.e. if an error is detected by the client or by the server. This is indicated in the lines #14 and #15 of the example where two error messages are displayed in an Alert() box. The first is the error message generated by the Xbase++ runtime kernel, and the second is the error message issued by the server. The latter is obtained via the :getLastMessage() method of the DacSession object maintaining the connection to the server. The object responsible for the current work area is returned by the DbSession() function.

If the error condition cannot be corrected, the application should execute the DbRRollback() function. This function voids any changes made in the internal buffers of the Xbase++ application and restores the original, unchanged data in these buffers.

Conclusion