This introductory session is designed for developers who are using the traditional FoxPro techniques of record locking in a multi-user environment and who need a demonstration of the Visual FoxPro techniques of data buffering.

Data buffering replaces the record locking and data scattering techniques employed for multi-user systems with previous versions of FoxPro. The technique is very flexible and can be employed on local and remote views as well as with standard FoxPro tables. Data buffering also allows for effective error trapping of validation rules stored in a database container.

The session introduces the concepts of row and table level buffering and focuses on the implementation of optimistic record locking. Validation rules are created in a database container and error trapping routines are implemented to demonstrate how these validation rule integrate with data buffering functionality.

The programming issues surrounding the error trapping process in a multi-user environment are fully discussed and the use of local views demonstrated. Relevant programming techniques are demonstrated throughout the session which is used to create a simple data manager class for controlling access to data.

20 December 1996 - Version 3.6

All rights reserved. No part of this handbook shall be reproduced, stored in a retrieval system, or transferred by any means electronic, mechanical, photocopying, recording, or otherwise without written permission from the publisher. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this handbook, the publisher and author assume no responsibility for errors or omissions. Neither is any liability assumes for damages resulting from the use of the information contained herein. For information, address RED, 21 Great Russell Mansions, 60 Great Russell Street, London WC1B 3BE, England.

A Visual FoxPro View is a SQL SELECT statement stored in the Database Container that presents a logical view of data to the end-user or programmer. The View can use tables from one or more Database Containers, or use Free Tables, or Remote tables from various external data sources. Sophisticated Views can combine tables from different data sources.

Views can be defined for many reasons including:

Views can be employed to present a logical view to a programmer or end-user where the underlying definition of the database is likely to change. This allows one programmer to create summary reports, for example using a view of the Customer and Order tables, whilst another works on data entry forms on the underlying tables. If table or field names are redefined on the underlying tables at a later stage in the implementation, the definition of the View can be changed without altering the table and field names presented to the report program.

A Visual FoxPro table can only be defined in a single Database Container. A corporate database may have several tables that are required in many databases to look up Employee Names or Company Departments for example. This data may be required in different applications and a design decision might be made to implement these tables as Free Tables and create a Local View in each Database Container to refer to the common tables. If this data is static data and is rarely modified, the Free Tables might be stored locally to ease up on network traffic. The same technique allows a Visual FoxPro table to be included in more than one Database Container.

Views are created in a Database Container using the View Designer. This allows the required tables to be selected and any join conditions specified between them. The required fields, selection condition, order sequence, and update criteria are also specified. Views can access data from local FoxPro tables, free FoxPro tables, or from remote data sources using ODBC to connect to a database server.

Various field and view properties can be set with the view designer or by using the DBSETPROP() command. For example, the RuleExpression property of the View provides table level validation for the view regardless of the data source but cannot be set using the View Designer.

Summary information is often required in several different functional areas of an application. The total outstanding Sales Orders for each Customer might be required in various forms and reports. The SQL statement to derive this information might be implemented as a View which includes a GROUP BY clause. This allows the statement to be coded only once in the application and also to allow the flexibility if the underlying physical data structure is changed in the future.

Remote Views allow for transparent access to external ODBC data and also maintain some degree of logical data independence from the physical definition of the underlying data. Various components are required to create remote views successfully:

ODBC, or Open DataBase Connectivity, drivers are the Microsoft standard drivers for connecting to external data sources from Windows applications. Many applications support these drivers including nearly all of the Microsoft product range.

The latest SQL Server drivers are shipped with various Microsoft products including FoxPro and they are also available from the SQL Server installation when installing client utilities for SQL Server. Make sure the latest 32 bit drivers are in use (at least version 2.5) which should match the speed of the native SQL Server data access library.

Drivers for other database servers are available from most of the database vendors and from Microsoft and third party publishers such as InterSolv, Visigenic, and OpenLink. In some cases the third arty drivers are better than the vendor supplied drivers. Look for at least ODBC 2.0 compatibility.

The ODBC drivers are loaded from the ODBC Control Panel and need to be loaded on each workstation that requires access to the data source. The ODBC driver will work with all supporting applications once they are installed and specific drivers are not required for Visual FoxPro.

ODBC drivers often specify the database to be accessed. If there are several SQL Server databases required from a workstation, a named driver is often specified for each database.

Visual FoxPro allows for Views to be created on remote data from external data sources using ODBC drivers that must be installed on the workstation for the appropriate data source. These Remote Views are transparent to the programmer and end-user and behave as if they were Views defined on local FoxPro tables.

A Remote View can be defined to refer directly to the ODBC Datasource. The datasource may however also be used by other programs and a Connection can be defined in the FoxPro database container to create a logical link to the datasource. This allows the datasource name (and backend database) to be changed by changing only the Connection definition.

A Connection or ODBC Datasource must be defined for each ODBC data source accessed in a Remote View definition.

The use of Connections allows Visual FoxPro some flexibility in the independence of the data source from the definition of the View. It is possible to create an application on a Remote View that utilises different connections to link through to different data sources at various installation. Similarly, careful definition of Local Views allows for the application to be implemented as a Client-Server application by redefining the Local Views as Remote Views.

The Connection designer is available from the Project Manager or whilst creating a Remote View. The designer allows the ODBC datasource to be specified along with options to determine whether the user is prompted for a password each time the external datasource is accessed and default time-out intervals for the transactions to fail if no response happens from the external data source.

The CREATE CONNECTION syntax can be used to create a connection programmatically in the default database together with the User Name and Password if required.

The View Designer allows for the Visual definition of the SQL statement that makes up the View. The tables that make up the View, the available fields, and the selection criteria are all defined visually along with the ability to group the table and calculate summary information.

Tables are added to the View by Rightclicking in the View Designer and selecting the Add table option. It is possible to select Tables and Views from the default Database Container or from any other Database Container as well as specifying Free Tables with the Other... option.

The relationships of multi-table Views are automatically added into the Field Selection section of the View Designer if they have already been defined in the Database Container. They can be modified or added manually as the new table is added or by inserting a new entry in the Field Selection window with the appropriate Join condition.

Fields must be selected for the View and calculated or summary fields my also be defined. The Grouping for Summary information is defined in the GROUP BY window and the COUNT(), SUM(), AVG(), MAX(), and MIN() functions will operate if selected in the Field Selection window.

The Update Criteria must be defined before the view is updatable.

An improvement in Visual FoxPro 5.0 is the ability to set field properties for a view using the View Designer. The FIELDS Page has a PROPERTIES button which allows field properties to be set on the view.

These properties behave in an identical manner to the properties on fields in native FoxPro tables. Some of these properties are augmented by functionality that may have been defined on the server. For example, if no default is specified locally and a value is not entered for a field, then SQL Server may specify a default value for the field when the record is added if a default is defined on the server.

The Data Mapping will usually default to the field type most appropriate for FoxPro to receive values from the ODBC data source. Occasionally this may need to be modified and the View Designer allows the FoxPro data type to be modified to match particular processing requirements.

The Filter window may be used to create a selection of records from the table.

A second table may be added easily into a view by rightclicking to add another table. Visual FoxPro will make a guess at the Join Condition and prompt for confirmation before adding the expression into the Filter window.

The default type of Join specified is a Natural or Inner Join where only records that appear in both tables are selected. Occasionally a more sophisticated Join is required where all the records of a table are displayed even if there are no records in the related table. This is called an Outer Join.

Authors may not have any records in the titleauthor table for example. A view that shows data from both tables and requires all Authors records even if there are no entries in the titleauthor table requires a Left Outer Join between the two tables. This is achieved by removing the expression from the Fields Page and including it on the Join Page specifying that a Left Outer Join is required.

The Order By Page passes the sequence required onto the Server which returns the results set in the required order.

The Group By window is used to make the grouping selection to operate on aggregate fields. These are specified using the Functions and Expressions item on the Fields Page using the appropriate SQL aggregate clause.

The Update criteria specify how the data in a view is updated back onto the local or the remote data source.

The Update Criteria can substantially affect the performance of the Remote View and consideration of the nature and performance of the back end database is required to fully optimise the Remote View together with the Connection definition.

A primary key must be specified for FoxPro to determine which record to update in the external data source. Visual FoxPro may be able to determine the key field but it can be specified manually by clicking beside the appropriate field in the column indicated with a Key icon.

The fields that are modifiable by the program or end-user can also be specified for security and performance purposes by checking the column with a pencil icon.

There are several optimistic record locking strategies that can be employed with a remote view.

Parameters may be defined for a View to limit the data selected in the SQL SELECT statement. The parameter can be employed programmatically and interactively.

The parameter is specified in the View Designer by placing a question mark before the parameter name in the EXAMPLE column of the selection criteria.

The name and datatype of the Parameter can be defined in a window called from the QUERY-VIEW PARAMETER.. menu option for the View. The Parameter may then be referred to in the Filter Condition by prefixing with parameter name with a question mark.

The end-user is automatically prompted to enter the parameterised value unless a memory variable with the same name as the parameter is defined before opening the View.

The parameter can easily be changed and the remote view updated with the REQUERY() syntax:

Local Views can be created programmatically by defining the SQL SELECT statement with the following syntax:

A remote view has the REMOTE keyword added and the connection details specified:

More complex views are also possible. A local view that contains the total value of each Product ordered by a single Customer might be defined as follows:

Properties can be set for all of the objects in a Database Container including Tables, Views, Remote Views, and Connections. For many of the objects in the database container, the properties can be set both interactively using the appropriate designer or programmatically.

There are some view properties that are not accessible using the view designer. For example, there is a View record level validation property that can be used to validate data in the view. This is especially useful as there are no triggers for views.

DBSETPROP() is used to programmatically set database container properties. The function returns a True value if the property is successfully set in the Database Container. The following syntax can be used to set a Caption on a field in a Remote View specified in the Orders Database Container:

The following example sets the validation rule property for the view to call a validation function. This property cannot be seen or be set with the view designer: