Going Client/Server with VFP

I introduced Visual FoxPro (VFP) back in my column in the May 1995 issue (see "FoxPro Goes Visual," page 97). In that column, I identified the integration of ODBC technology into the product's overall database access model as one of its three key features. Since then, one of my company's development teams has been using VFP to prototype application components where both data and application logic reside on database servers. Our initial challenge was simply to access from VFP our target database servers -- Oracle7 and Microsoft SQL Server --and this challenge continues as we endure continual upgrades to network transport and ODBC libraries. More interesting has been the process of identifying VFP's capabilities, strengths, and weaknesses as a client/server development tool. We are now wrestling with how to preserve the responsiveness of our FoxPro applications, minimize the amount of data we have to move around the network, and maximize data concurrency.

VFP offers two modes for working with server-based data: persistent stored views and SQL pass-through (SPT). When you create a persistent view, VFP stores a view definition in a database structure that it calls a Database Container, or DBC. The definition contains the names of the views' tables and fields and their property settings. When you use the view, the view definition is used to build a SQL statement that defines the view's data set. Although it also provides view-creation facilities, SPT opens up the entire spectrum of server functionality to the VFP application by exposing much of the ODBC API. This breadth of functionality does not come without costs, which manifest themselves in the overhead of having to manage explicitly all of the tasks involved in connecting to the server and requesting services from it.

Three function pairs provide fine control over the behavior of VFP's client/server features. SQLGETPROP( ) and SQLSETPROP( ) query and manipulate the client/server environment and the properties of connections to client/server databases. These connection properties specify the ODBC data source, user, password, and connect string and communications settings such as wait and timeout intervals, network packet size, and whether results are passed back to VFP synchronously or asynchronously. The CURSORGETPROP( ) and CURSORSETPROP( ) functions control the local cursors that are created when data is retrieved from a server. User-manageable properties of cursors let you specify fetch and update rules. Finally, DBSETPROP( ) and DBGETPROP( ) control the properties of connections and views that are stored persistently in database containers. You can set the default values for these properties on the Remote Data Options panel, as shown in Figure 1.

The Database Container

Named connections function as objects that record the name of an ODBC data source, userID, and password in the DBC structure, along with settings that control the behavior of views using the connection. You can associate each stored view with a named connection; when the view is instantiated, it uses the connectivity parameters defined for the connection. These parameters specify whether the connection is asynchronous, whether the connection operates in batch mode, whether each update request is wrapped in a transaction automatically, and how long processes should wait before they time out. Named connections let the developer or database administrator shield the application user from the ODBC login process, and they create a high-level profile of properties that multiple views can share.

VFP's Stored Views

VFP leverages its own data processing power by storing in local cursors the data sets retrieved by the server. For developers entering the client/server arena from a dBASE or FoxPro background, this approach provides a familiar way to work with server-based data. After a cursor has been created, either with a stored view or a SELECT statement via SPT, an application can manipulate the cursor's data as if it were in a native FoxPro table. To navigate the cursor, you can use the familiar dBASE/FoxPro SKIP command. You may also create an index on a cursor, which enables extremely fast searches. More likely, the cursor will be small enough so that sequential searches using VFP's LOCATE FOR <expression> command are sufficient.

The properties of a stored view determine how the view will behave when it is instantiated. The FetchMemo, FetchSize, and MaxRecords properties let you manage the size of a local cursor when it is created, and thereby manage the amount of time it takes to create it. The BatchUpdateCount, UpdateType, SendUpdates, and WhereType parameters control how updates are managed. To make a view updatable, you must specify key fields, updatable fields, and the rule for detecting update conflicts. For detecting update conflicts, you can compare the key field in the cursor with the key field in the corresponding server row, the key and all updatable fields, the key and modified fields, and the key and timestamp.

VFP's SQL Pass-Through

After the application establishes a connection, it can request information about the data source with the SQLTABLES( ) and SQLCOLUMNS( ) functions, or it can issue any of the SPT SQL statement execution and control functions. Of these, the primary function is SQLEXEC( ), which passes SQL statements to the server. A SQLEXEC( ) that passes a SELECT statement or calls a stored procedure that, in turn, executes a SELECT statement creates a local cursor with the SELECT results. When you use a single SQLEXEC( ) to issue multiple SELECT statements, VFP will retrieve result sets into multiple cursors.

Controlling the way VFP retrieves these cursors and how they can be updated requires an extensive amount of parameter-setting code. As with stored views, you need to specify key fields, updatable fields, and the rule for detecting update conflicts. The difference between a stored view and an SPT-established view is that you must set each view option explicitly every time you create that view with SPT. Listing 1 illustrates the parameter-setting code required to do this.

Using SQLEXEC( ) to issue DML commands such as INSERT, UPDATE, and DELETE provides fine control over updates and access to server-specific extensions to these commands. Applications can also pass DDL statements via SQLEXEC( ) to create and modify database schema. Of course, applications operating at this level of sophistication must provide a substantial amount of error trapping. Listing 2 provides an example of using SQLEXEC to create and load a table. SQLEXEC( ) also provides a way to execute blocks of server code and stored procedures. These capabilities support moving application logic from the client application to the server.

Creating a Form-Based Interface to Server Data

Building on its cursor-oriented architecture, VFP provides a flexible buffering model to support entering and editing data in multiuser and client/server environments. When buffering is enabled, VFP copies the data being edited into a memory buffer. This architecture can buffer individual rows (for record-by-record editing) or entire tables (for multi-record updates and batch-processing operations). If you execute TABLEUPDATE( ) with buffering on, moving off the current row causes VFP to attempt to commit changes from the buffer to the database.

In applications that access local tables, VFP offers a choice of optimistic or pessimistic locking modes. For client/server applications, VFP supports optimistic locking in which the lock is attempted only when the change is about to be committed from the buffer to the source tables. If we expect a great deal of contention, we simulate pessimistic locking by implementing a semaphore-based scheme.

Another group of functions allows us to provide very granular conflict detection. GETFLDSTATE( ) indicates whether the user has changed the contents of the specified field. GETNEXTMODIFIED( ) indicates the next modified record in the current buffer. OLDVAL( ) takes the name of a field or expression and returns the value of that field or expression before the user started to edit. CURVAL( ) reports the current value in the base table, and it can be used to determine whether another user made changes during the current user's edit session. Thus, when the user prepares to commit changes to a buffered table, the application can scan the buffer for new and changed rows and compare them to the corresponding rows on the database server. If the current user changes rows that have also changed on the server, the application can display the initial value and both sets of changed values and allow the user to determine which value to commit. In any case, if an attempt to update a buffered table fails, VFP will report the failure. If table buffering is in effect, you can issue a TABLEREVERT( ) to undo any changes that were committed before the failure occurred.

In addition to data buffering, VFP lets you manage server transactions. The Transactions property of the connection determines how the connection manages transactions on the remote table. If the Transactions property is set to Automatic, each command passed to the server is treated as a separate transaction. If you set the Transactions property to Manual, the application can pass a series of SQL updates to the server and then issue a SQLCOMMIT( ) to advise the server when to commit. If the application determines that the update cannot be completed due to failure of a validation condition or multiuser contention, it can request that the server discard the pending changes with SQLROLLBACK( ).

Data Validation

We also have a stylistic preference for validating data entry sooner rather than later, and would prefer not to make users learn that their changes are invalid after they push the Save button. We are looking at several ways to enable validation when the user makes the invalid entry. Certainly we can hardcode the validation rule into the form, but that's not a good solution for large systems and it defeats the purpose of moving business rules into the database. Alternatively, we can develop an infrastructure for reading constraints from the system catalog and assigning them to the appropriate form controls when the user opens the form. This is a bandwidth- and overhead-intensive solution that may not be feasible.

VFP lets me assign a validation rule to a stored view, or to a field in a stored view, with DBSETPROP( ). We can take advantage of this feature by maintaining a local data dictionary that mirrors the server's schema, and assign rules to our views from the dictionary. We can do this dynamically, and synchronize constraint rules with the server, but that represents a significant amount of work. It's fairly clean where the rule syntax is the same in both VFP and on the server. Where the rules use different syntax (for example, relying on server-specific functions), we must maintain multiple versions of each rule.

VFP poses other challenges for client/server applications. For example, VFP's mode of retrieving data into workstation-based cursors compels us to anticipate how users will work. We must weigh the number of server requests the application will make and the amount of data the application will retrieve against the currency of the data in the workstation's cursors. For example, after disposing of the current row, it's likely that a user will want to do something else. This could involve working on another row in the same view, working with some data in another view, or entering some type of transaction. When anticipating the user's workflow, we must consider carefully how the application should build data sets on the workstation. If the user stays in one task for a long period, we would prefer to retrieve a substantial number of records and allow the work to proceed locally. If the user switches tasks often, we would want to create small views and refresh them each time the task is switched. These kinds client/server considerations add complexity to the design of VFP applications.

Knotty Issues

When VFP creates a cursor, the data specified by the view must be copied from the server to the client, where it resides for as long as the view is open. Once the view is established on the local cursor, the connection between the cursor and the server tables is weak. There are several implications of this model. Obviously, developers must specify view parameters -- columns and selection criteria -- that minimize the view's size. Because SELECT is the primary interface between the application and the server, we must design applications with server and network performance in mind.

Because the data in the cursor is static and there is no automatic notification when the local data becomes obsolete, you must establish and follow rules for refreshing data. This will ensure that what the user sees and edits matches data on the server. Some synchronization options include issuing timed REFRESH requests for the current and surrounding rows, detecting changes and reissuing queries, or providing a way for the user to initiate a refresh. To implement distributed applications with VFP, we are considering keeping large working data sets in persistent local tables and developing an engine for replicating data among the server and the local databases when network bandwidth is available.

The burden of replicating data on the client highlights the desirability of having a keyset model similar to the one that Microsoft's JET engine employs. In JET, establishing a view retrieves only the keys of qualifying rows in the view. In that model, the values of the current row's columns are refreshed automatically whenever the user visits them. We can simulate this model by establishing our local cursors so that they only contain key values, and by issuing subsequent SELECTS for the balance of the record when it's needed. VFP gives us the tools to build such a mechanism, and the responsibility of managing it.

Figure 1.