Data Buffering

Data Buffering

6.            Data Buffering

Visual FoxPro creates a local cursor when creating a Local or Remote View. These cursors can be defined to automatically update the original data when the record pointer is changed or the cursor is closed. This process is called Data Buffering and may be controlled in various ways.

This section describes data buffering and how it can be used to control server updates and how error messages returned from the server may be processed.

Buffering may be specified at Record or Table level, or not at all. The locking of the source table may be specified as Pessimistic or Optimistic:

·                  Record level buffering will automatically commit any changes to the record each time the record pointer is moved.

·                  Table buffering will commit all changes and additions to the table when the table or form is closed.

·                  Pessimistic locking will lock the record being edited on the “real” table.

·                  Optimistic locking does not lock the record until changes are committed but double checks at this time to see if another user has changed the record.

Application design aims to minimise the time that records in the tables are locked so as to maximise the throughput of data for a larger number of users. Optimistic record locking is used wherever possible throughout these notes.

Record buffering can be enabled in a variety of ways:

·                  The BUFFERMODE property of the form can be set to specify Optimistic or Pessimistic buffering.

·                  The BUFFERMODEOVERRIDE property can be set on an individual table in the Data Environment to override the Form setting to any combination of Optimistic and Pessimistic locking or Table and Record level buffering.

·                  The CURSORSETPROP function can be used to set the buffer mode on an individual cursor.

Multilocks must be set on to enable buffering. Be careful to set this inside a form if you are using private data sessions.

Specifying Data Buffering

The CURSORSETPROP() function may be used to enable buffering for the current table or view cursor. To set record level buffering on the AuthorsView remote view for example:

USE pubs!authorsview

llOK = CURSORSETPROP( 'buffering', 3 )

Record level buffering allows changes to be made to the current record which are sent to the server only when the record pointer is moved or the TABLEUPATE() command is issued.

Table level buffering allows changes to be made to the workstation copy of the data and only sent to the server when the TABLEUPDATE() command is issued.

USE pubs!authorsview

llOK = CURSORSETPROP( 'buffering', 5 )

Buffering may also be manually set up on a cursor once it has been opened with the View window by pressing the PROPERTIES button after selecting the view cursor workarea.

Setting Optimistic Record Level Buffering in the View Window

Some validation rules prevent APPEND BLANK from functioning correctly and being used on a form button for example. This might occur where empty values were not permitted for a field in the validation criteria but the program was not able to progress to the user input stage until a value has been entered. Table buffering overcomes this limitation.

Both Field and Record Level database rules are checked each time the record pointer is moved. FoxPro Triggers are only run when the data is committed into the database and are not available on Local and Remote Views.

Saving Changes

The TABLEUPDATE function is used to update the table under program control. The following command will write changes for the current record into the database with buffering enabled:

TABLEUPDATE()

The command can accept parameters to update just the current record (the default), or the whole table where table level buffering is specified. The cursor to be updated can also be specified.  All changes on all records of the TITLESVIEW cursor can be sent to the server with the following command:

TABLEUPDATE( .T., .F., 'titlesview' )

The function returns a True value if the data is committed and a False value if there is a problem with the record level validation rule or any of the triggers. Field level validations are also fired even if the fields have not been entered during an append procedure.

Record level buffering will display any update errors to the user attempting to move the record pointer from a record that cannot be modified because of a server validation problem. This message can be suppressed by using the TABLEUPDATE command to update the record instead of relying on FoxPro.

If an error occurs and any records are not updated on the server, the TABLEPDATE function returns a False value. The errors returned from the server can be determined with the AERROR command.

DIMENSION la_Error[ 1 ]

AERROR( la_Error )

DISPLAY MEMORY LIKE la_Error

See the section on error trapping later in this chapter for more details on processing errors.

Reverting Changes

The TABLEREVERT function is used to abandon changes made to a buffered record or table. The function will return the number of records in the workstation cursor that have been reverted.

TABLEREVERT()

Normally TABLEREVERT will revert the current record if changes have been made. With Table level buffering set and a parameter passed to the function, all the changes made on the required cursor can be reverted:

TABLEREVERT( .T., 'titlesview' )

Changed records cannot be reverted after a successful TABLEUPDATE.

Determining Updates

The update status of each field can be determined with the GETFLDSTATE( fieldname ) function that returns the status of each field in the current record of a buffered cursor:

·                  1 - No Changes.

·                  2 - Field Updated.

·                  3 - Field Added in a new record but not modified.

·                  4 - Field Modified in a new record.

The GETFLDSTATE(-1) function is used to return the update status of the current record where buffering has been enabled. The first character of the returned value indicates the delete status of the current record and the remaining characters indicate individual field status.

If the returned string contains only the character 1 then no changes have been made to the current record. 2,3, or 4 indicates that a change has been made or the record added or deleted. The following expression will check for changes to the current record in the current buffered cursor:

LOCAL m.lc_getfld

m.lc_getfld = GETFLDSTATE(-1)

IF '2' $ m.lc_getfld ;

   OR '3' $ m.lc_getfld ;

   OR '4' $ m.lc_getfld

   * Changes have been made

   …

ENDIF

The SETFLDSTATE() function  can be used to alter these settings.

Only one record at a time is updated with Record Level Buffering. Several records are changed on the local workstation cursor with table level buffering and in some cases it is useful to determine which records have been changed before updating.

The GETNEXTMODIFIED function returns the next record number of the cursor that has been modified on the workstation and not updated onto the server. The function requires a parameter to indicate the record number to search from:

? GETNEXTMODIFIED(0) will return the first record that has been modified. If record 5 is returned, the record number of the next modified record is obtained with the following command:

? GETNEXTMODIFIED( 5 )

Records appended with table buffering have negative record numbers.

Additional functions exist to help resolve update difficulties. The record pointer must be positioned on the required record for these functions to work.

The current value in the local workstation cursor is determined by using the alias and field name in the normal way. For the STORE_ID field in the SALESVIEW cursor for example:

m.lc_Local = salesview.store_id

The original value determined when the snapshot of the original data was copied onto the workstation is determined by a function called OLDVAL().

m.lc_Snapshot = OLDVAL( 'store_id' )

The current value on the server will be the same as the OLDVAL() unless the server data has been changed by another user since the snapshot of the cursor data was taken. It can be determined with the CURVAL() function:

m.lc_Server = CURVAL('store_id')

These three functions may be used in combination to check which fields have been changed by the current user or another user and then use application logic to set values that are acceptable to the server.

Error Handling

Many data validation and business rules can be implemented on the server. The server will return an error to the application program if it is unable to process a transaction. The ODBC standard requires that the front end programming language accepts multiple errors from the server.

The AERROR function will capture these errors from the server and place them into the specified array. This is usually required when a TABLEUPDATE() function returns a False value. The following example traps for an error when the current record of the Salesview cursor is updated onto the server:

m.lc_cursor = 'salesview'

IF NOT USED(m.lc_cursor)

   RETURN .F.

ENDIF

 

m.lc_getfld = GETFLDSTATE(-1, m.lc_cursor)

IF '2' $ m.lc_getfld ;

   OR '3' $ m.lc_getfld ;

   OR '4' $ m.lc_getfld

   * Changes have been made to current record

   IF NOT TABLEUPDATE( .F., .F., m.lc_cursor )

      IF AERROR( la_error ) > 0

         * Errors in updating server

         …

      ENDIF

   ENDIF

ENDIF

 

If an error occurs, the corresponding records will not have been written to the database. You may use the GETFLDSTATE, GETNEXTMODIFIED, and other options to change the local data and resubmit using a TABLEUPDATE. Alternatively, use TABLEREVERT to cancel the change for a single record or for all changed records.

The array created by AERROR contains the FoxPro error number as well as the server generated error numbers and messages. The server error may need to be parsed in order to be processed correctly.

One common error with optimistic data buffering is that another user will have changed the same record from another workstation. The TABLEUPDATE will fail and will return error 1585 into the error array.

   * Changes have been made to current record

   IF NOT TABLEUPDATE( .F., .F., m.lc_cursor )

      * Error(s) returned from server

      IF AERROR( la_error ) > 0

         FOR m.ln_error = 1 TO ALEN( la_error,1)

            IF la_error( m.ln_error,1) = 1585

               * Another user has edited the current record

               …

            ENDIF

         ENDFOR

      ENDIF

   ENDIF

The values that have been changed by the other user may be checked with the GETFLDSTATE, CURVAL, and OLDVAL functions and the update forced on the server with the FORCE parameter of the TABLEUPDATE command after setting any acceptable changes from the server record into the current record:

IF TABLEUPDATE( .F., .T., m.lc_cursor )

   * Other users changes have been overwritten

ELSE

   * There are still some errors in the data

   …

ENDIF

The Update Criteria of remote views may be set up as KEY AND MODIFIED FIELDS so the optimistic locking will fail only if users have attempted to update the same field on the same record.

A series of dependent transactions should employ manual transactions on the cursor and use SQLCOMMIT and SQLROLLBACK to ensure that all the transactions in the sequence are rolled back on the server if there is an error.

Commit and Rollback

Data Buffering is a very effective way of controlling updates onto the server. Some sequences of transactions however are very sensitive and full commit and rollback functionality may be required.

Visual FoxPro implements server side transaction processing at the level of the connection. The Transactions property of the Connection is set to Manual and the SQLCOMMITand SQLROLLBACK functions become operational.

Commit and Rollback work on top of data buffering so both may be used concurrently with the commit and rollback wrapping around the data buffering.

The Connection handle of the current cursor representing a remote view can be determined easily and the transactions set to manual. Commit and rollback is now in operation for the cursor:

m.ln_Connect = CURSORGETPROP( 'connecthandle' )

m.ln_Transactions = SQLSETPROP( m.ln_Connect, ;

   'transactions', 2)

IF NOT m.ln_Transactions == 1

   * Error

   …

ENDIF

Similar code is required to set commit and rollback on any connection for use with pass through queries.

Data processing takes place in the normal way with TABLEUPDATE and TABLEREVERT. The changes may be committed into the server with the following command:

IF SQLCOMMIT( m.ln_connect ) == 1

   * Committed OK

ELSE

   * Error

ENDIF

Rollback is achieved with the SQLROLLBACK command and should be used in conjunction with TABLEREVERT to refresh the table although these may become out of synchronisation with the table and a REQUERY may be required.

IF SQLROLLBACX( m.ln_connect ) == 1

   * Rollback OK

   = REQUERY( 'authorscontractview' )

ELSE

   * Error

ENDIF

 

19/09/2017 21:44:57