Chapter 19 -- Designing ClientServer Applications

Visual FoxPro provides you with the tools to create powerful client/server applications. A Visual FoxPro client/server application combines the power, speed, graphical user interface and sophisticated querying, reporting, and processing of Visual FoxPro with the seamless multi-user access, massive data storage, built-in security, robust transaction processing, logging, and native server syntax of an ODBC data source or server. The synergy of Visual FoxPro and server strengths provides a powerful client/server solution for your users.

The most important step in building a successful client/server application is creating a good design. This chapter builds on the multi-user application development information provided in previous chapters. From that foundation, we define a methodology for developing client/server applications.

If you want information on building and upsizing a local prototype, see Chapter 20, Upsizing Visual FoxPro Databases. For information on using SQL pass-through technology, see Chapter 21, Implementing a Client/Server Application. To speed up data retrieval and processing, see Chapter 22, Optimizing Client/Server Performance.

This chapter discusses:

• Goals for Client/Server Design
• Designing for High Performance
• Developing Applications Quickly
• Building in Accuracy and Data Integrity

Goals for Client/Server Design

When you design a client/server application, you re balancing several sets of requirements. You want to build the fastest, most productive application for your users. You also want to ensure the integrity of application data, make the most of existing hardware investments, and build in scalability for the future. In addition, as a Visual FoxPro developer, you want to make the development process as streamlined and cost-efficient as possible.

The best way to meet these requirements is to design your application with these goals in mind. Let s set the stage by outlining the techniques that provide maximum client/server performance.

Designing for High Performance

Building a fast, high-performance client/server application with Visual FoxPro involves taking advantage of the tremendous speed of the Visual FoxPro engine. You accomplish this with new techniques such as using set-based data access rather than traditional local navigation, building parameterized queries to download just the data you need, locating tables on the optimal platform, and leveraging both Visual FoxPro and remote stored procedures.

Before you can take advantage of new techniques, you must analyze the systems you ll be using. When you design a local or file-server application, you determine the queries, forms, menus, and reports your application will use or create. When you design a client/server application, you perform all the normal system analysis plus additional analysis that relates specifically to client/server applications. You need to think about where the data used by queries, forms, menus and reports will be located, and how you ll access that information. For example, you might ask yourself questions such as:

• Which tables will be stored on the remote server once the application is implemented?
• Which tables would be more efficiently stored as local lookup tables?
• What views will you need to access remote data?
• What business rules are enforced by the server, and how will your application interact with these rules?

Once you ve determined the basic components of your client/server application, you can begin to design how your application will access and update data.

Downloading Just the Data You Need

One of the most important factors in building a fast, efficient client/server application is minimizing the amount of data you pull down from the server. Because client/server applications can access potentially huge amounts of data on a remote server, using traditional local navigation techniques could result in a slow client/server application. To speed performance, you use set-based data access techniques to filter the amount of data you download.

Accessing Set-Based Data Efficiently

Remote data is set-based; you access remote data by selecting a set of data from a large data store using SELECT - SQL statements. The most important difference between building a traditional local application and building a client/server application is the contrast between traditional Visual FoxPro navigational techniques and set-based server data access techniques.

Using Traditional Navigational Techniques

In traditional local database programming, you can access discrete and often large amounts of data by using the GOTO BOTTOM command, which you then query against. You can navigate through data by issuing a SET RELATION command to create a temporary relationship between two tables and then issuing a SKIP command to move through the related records.

While this method of navigating records could be used against remote data, it can be inefficient against large remote data stores. For example, if you create a remote view that accesses a large table on a remote data source and then issue the GOTO BOTTOM command, you must wait while all the data in the view is retrieved from the data source, sent across the network, and loaded into your local system s view cursor.

Using Parameterized Queries

A more efficient approach for accessing remote data is to download just the data you need and then requery to obtain specific additional or new records. Use a SELECT statement based on parameters to download a specific small set of data and then access new records by using the REQUERY( ) function to request a new set of data.

You don t issue the GOTO BOTTOM command against remote server data because this would:

• Unnecessarily burden network resources by downloading huge amounts of data.
• Slow performance of your application by handling unneeded data.
• Potentially reduce accuracy of the data in the local cursor because changes to remote data aren t reflected in the local cursor until you requery.

For example, if you want to create a client/server application that accesses the orders for a particular customer, create a remote view that accesses the Customer table. Create another remote view that accesses the Orders table, but parameterize the view based on the cust_id field. Then use the current customer record as the parameter for the view of the Orders table.

You can use the parameter to scope the downloaded data set to just the right amount of data. If you request too little data, you can lose performance because you ll have to requery the remote server more frequently. If you request too much data, you can waste time downloading data you won t use.

Choosing the Best Client/Server Design

The following examples describe how to gain the benefits of client/server technology and avoid the pitfalls of poor programming techniques. The first method uses traditional programming practices to retrieve all the data from a remote data source into local cursors which are then related with the SET RELATION command. The second, third, and fourth methods adopt progressively smarter data-fetching techniques, effectively limiting the amount of data downloaded with a just-in-time methodology that provides the freshest data and fastest response time over a network.

Using an Unoptimized Client/Server Strategy

A straightforward, unoptimized client/server application uses local data navigation techniques with remote data. For example, if you have 10-million customer records and 100-million order records on a remote data source, you can create an inefficient application that downloads all the Customer and Order records into local cursors. You could then index on 100-million order records, create a temporary relationship between the Customer and Orders tables in your local cursors, and use the SKIP command to navigate through the records.

This method is not optimized for performance, but might, however, be useful if the one side is local and the many side is remote.

Filtering the Many Side

A slightly improved client/server application limits the many side of the relationship, but retrieves all of the one side so you can skip through the records. In this scenario, you create a remote view of the many side of the relationship, the Orders table, parameterized on the customer ID. You then download the entire Customer table.

While creating a parameterized view on the Orders table is an improvement over downloading all of the orders, you still retrieve unnecessary information by continuing to download the entire Customer table. The Customer table is also increasingly out-of-date as changes are made by other users on your system. This method might be beneficial if the one side of the relationship contains a small data set.

Filtering the One Side

A better client/server programming technique creates remote views for all remote data. You limit the number of Customer records downloaded into the remote view of the Customer table by using the SELECT statement in the view to select just the customers for one region. You then create a remote view of the many side of the relationship, the Orders table, parameterized on the customer ID.

This scenario retrieves a smaller set of records. You use the SKIP command to skip on the one side of the relation (the Customer view). You use the REQUERY( ) function to access new data on the many (Orders) side.

In this example, you limit, or filter, both the one side and the many side of the relationship, and can still use the SKIP command to navigate through the filtered data. This method is be recommended if the one side of the relationship, even after being filtered, is still sufficient to provide information for a successive set of queries before you requery the remote server.

Using the Primary Key to Access the One-to-Many Relationship

The most efficient client/server programming paradigm gives up the luxury of using the SKIP command, and creates a form that requests input or selection of the customer ID, which is then used as a parameter for a remote view of the Customer table. This parameter is also used as a parameter for a remote view of the Orders table.

For example, you could create a one-to-many form in which the customer information forms the one side, and a Grid control displays the many side of the relationship. The Grid control can be bound to the customer ID chosen in the one side of the form. You can then set the MaxRecords property of CURSORSETPROP( ) to 1, and use the following code to populate the one side of the form:

SELECT * FROM customer WHERE customer.cust_id = ?cCust_id 

When the user wants to view a different customer s record, they input or select a new customer ID. The form requeries the data source for the orders for the new customer ID and refreshes the Grid control with the new order data.

Using these techniques, your application downloads just the data you need, at the time it's needed. You speed response over the network by limiting the amount of data downloaded, and you provide fresher information to the user by requerying the data source just before you display requested information.

This method is recommended when you want to access the one-to-many relationship randomly using any primary key value. You might want to download the primary keys into a control, such as a drop-down list, when you open the form and then provide a control that the user can choose to refresh the list of primary key values on demand.

Using the Data Environment in Client/Server Applications

When you use remote data in a form, include the views in the form s data environment. You can set the AutoOpenTables property for the data environment to false (.F.) so that you can specify when the application refreshes the views with the remote data. Set the ControlSource property for text boxes or other data-bound controls after you call the data environment s OpenTables method, typically in the code associated with the Init event of the form. For more information on setting form properties, see Chapter 9, Creating Forms.

Locating Data on the Optimal Platform

You get maximum performance when you store data and other attributes of your database on the optimal platform. The best platform for a particular element depends on how the element is accessed and updated. For example, you might want to store a local copy of a server table, such as a postal code directory that is used as a lookup table, and refresh the local copy only when the back-end table changes.

The following table lists some common application elements and examples of where to locate them for optimal performance.

Locating Elements by Platform

Element	Location	Type	Notes
Tables	Local	Local copies of server lookup tables; small, infrequently changed tables	Use a time stamp, if supported by your remote server, to compare and optionally refresh the local table to match any changes to the back-end source table.
	Remote	Large or frequently changing tables
Rules	Local	Rules on remote views	You can use DBSETPROP( ) to store field- and record-level rules on a remote view. Your application can use these local rules to check the validity of data before sending the data to the back-end as an update to remote tables.
	Remote	Row-level and column-level rules on remote base tables
Stored procedures	Local	Visual FoxPro stored procedures
	Remote	Back-end server stored procedures	Use the SQL pass-through SQLEXEC( ) function to call server stored procedures.
Transactions	Local	Visual FoxPro transactions
	Remote	Server transactions
Triggers	Local views	No triggers on views
	Remote	Server triggers

To reduce network traffic during lookups, you can choose to store not only infrequently changing but also frequently changing lookup tables locally. For example, you can download your company s customer list and refresh it only when customer information changes.

To accomplish this, you can program your application to compare the time stamp on the local copy of the table with the time stamp on back-end data (if your remote server supports time stamps) and update the local copy only if the server table has changed. You can also add a command button to your form that forces an immediate download of the table, allowing users to refresh their copy of the local table on demand.

Choosing the Right Methods

You can use remote views, SQL pass-through, or both to create your client/server application. You can combine both methods for powerful results: use views for the majority of your data management requirements and use SQL pass-through to enhance the power of your application.

Using Views

You can use views as the core method for developing a robust client/server application. Remote views are a powerful technology; they're designed to allow you to select just the data you need from a remote server into a local Visual FoxPro cursor, which you can then use to view and update remote data. A view is basically a result set from a SQL SELECT statement.

Views are persistent: the view definition is stored in a database. View definitions have properties that you can set and then customize further for the active view cursor. Views are the best tool for data definition of an updatable result set.

You can use local views to build a local prototype, then use an Upsizing Wizard to transform local views into remote views. For information on using the Upsizing Wizards, see Chapter 20, Upsizing Visual FoxPro Databases.

If your application s users want to use data for mobile work, you can employ offline views. Offline views make data portable, allowing laptop or other portable computer users to work with a stored copy of source data that they can update while they re on the road. When the user reconnects to the server, your application can easily merge offline changes into the source tables.

You might also want to use offline view technology to allow local users to work with data offline, merging their updates at a later time. For information on working with offline data, see Chapter 8, Creating Views.

Using SQL Pass-Through

SQL pass-through technology provides you with direct access to a remote server with the Visual FoxPro SQL pass-through functions. These functions permit additional server access and control beyond the capability of views. For example, you can perform data definition on the remote server, set server properties, and access server stored procedures.

SQL pass-through is the best tool for creating read-only result sets and for using any other native SQL syntax. In contrast to a view, which is a result set from a SQL SELECT statement, SQL pass-through enables you to send anything you want to the server using the SQLEXEC( ) function. The following table lists the Visual FoxPro SQL pass-through functions.

SQL pass-through functions

SQLCANCEL( )	SQLCOLUMNS( )	SQLCOMMIT( )
SQLCONNECT( )	SQLDISCONNECT( )	SQLEXEC( )
SQLGETPROP( )	SQLMORERESULTS( )	SQLPREPARE( )
SQLROLLBACK( )	SQLSETPROP( )	SQLSTRINGCONNECT( )
SQLTABLES( )

You can create cursors yourself using SQL pass-through technology. Though SQL pass-through provides more direct server access, it provides less persistent access than views. In contrast to views, whose definitions are stored persistently in a database, cursors created using SQL pass-through exist only for the current session. For more information on using SQL pass-through technology, see Chapter 21, Implementing a Client/Server Application.

Combining Views and SQL Pass-Through

The most powerful paradigm for building a Visual FoxPro client/server application combines view and SQL pass-through technologies. Because views are easy to build and provide automatic buffering and update capabilities, use views for the majority of your data management tasks. Then use SQL pass-through to accomplish specific tasks on the remote server, such as data definition and the creation and execution of server stored procedures.

Developing Applications Quickly

Regardless of the programming method you choose, you need a good strategy to make developing client/server applications quick and efficient. Because Visual FoxPro makes it easy to quickly prototype and build applications, you can choose to design and build a local prototype of your application and then upsize and implement it in stages against a remote data source. If you have access to a remote data source during the development process, you might choose to prototype your application against the remote data source, using remote views.

Building a Prototype with Views

The first step in developing a Visual FoxPro client/server application can be to build a prototype. By prototyping your application, perhaps module by module, you discover changes and enhancements to your application s design early in the development process. You can then fine-tune your design efficiently against small sample data stores before adding the additional layer of complexity inherent in working with large sets of remote, heterogeneous data. Building a prototype is described in Chapter 20, Upsizing Visual FoxPro Databases.

Creating a Local Prototype with Local Views

A local prototype for a client/server application is a functioning Visual FoxPro application that uses local views to access local tables. You use views in your client/server prototype because the final client/server application will use remote views to access remote data. By prototyping your application with local views, you re one step closer to the final application.

Building a local prototype is especially practical if you don t have constant access to a remote data source during development, or if you don t want to use remote data to prototype your application. Local views access local Visual FoxPro tables, rather than remote data source tables. You create the local data, however, to mimic the structure of the data on the server. Using local data to represent remote data is one method of quickly developing and testing your application s basic design. You can also speed development by limiting the amount of data selected into the views. For more information on building local and remote views, see Chapter 8, Creating Views.

Planning for Upsizing

Upsizing is the process that creates a database on the remote server with the same table structure, data, and potentially many other attributes of the original Visual FoxPro database. With upsizing, you take an existing Visual FoxPro application and migrate it to a client/server application. For more information on upsizing, see Chapter 20, Upsizing Visual FoxPro Databases.

When you build an application that you ll eventually upsize, you make choices about the design of your application s architecture and the programming model based on eliciting maximum performance against a remote data source. These choices are described earlier in this chapter in Designing for High Performance.

Prototyping with Remote Views

If you have access to a remote data source and you want to use remote data directly as you develop your client/server application, you can build your prototype using remote views. When you prototype using remote views, you skip the upsizing step because your data is already located on a remote server and you already have remote views to access that data.

Implementing your Client/Server Application

You can simplify testing and debugging your application by implementing your prototyped application in stages. When you implement a prototyped application in stages, you add multi-user enhancements, move the data to the remote data source, and test and debug the application, module by module, in a systematic manner.

As you implement your application, you can use native server syntax and access server-specific functionality, such as server stored procedures, with SQL pass-through technology. For information about SQL pass-through, see Chapter 21, Implementing a Client/Server Application.

Optimizing your Application

Once your application is fully implemented against remote data and you ve completed the testing and debugging phase, you can fine-tune the speed and performance of the entire application. For more information about enhancements you can make to your implemented application, see Chapter 22, Optimizing Client/Server Performance.

Building in Accuracy and Data Integrity

You can combine the power of Visual FoxPro data validation rules and stored procedures with the data source s data validation rules and stored procedures to build client/server applications that protect data integrity.

Maintaining Data Integrity

You can create local versions of remote server validation rules to provide friendly messages to the user; for example, about updates that would not be allowed when sent to the remote server because the data entered had violated some server relational integrity or data validation rule.

Using Visual FoxPro Rules on a Remote or Offline View

You can create field- and record-level rules on remote and offline views to validate data entered locally before the data is sent to the remote data source. Because the purpose of these view rules is to prevent sending data to the data source that will be rejected by the server s data integrity rules, you want to replicate the data source s rules in the rules you create for your remote view. You use the DBSETPROP( ) function to create rules for views.

Tip   You can create a local validation rule on a remote view that calls a remote server stored procedure and sends the value you want to validate to the server as a parameter. However, using a remote stored procedure adds to processing time during data entry.

Using Server Rules

You can choose to rely on the rules established on the server for data validation. If an error occurs, your error handling routine can call the AERROR( ) function to obtain information, including the error message number, the text of the remote error message, and the connection handle associated with the error.

Using Server Triggers

Though you can create Visual FoxPro triggers on local tables, you can t create them on views. You can, however, use triggers on the remote data source. Server triggers can be used to process secondary data updates, such as cascading updates or deletes. Using server triggers to process secondary updates is more efficient than sending multiple commands to the remote server from your Visual FoxPro application.

Protecting Against Data Loss

Both Visual FoxPro and most remote data sources provide transaction logging capabilities to protect against data loss. For more information on using Visual FoxPro transactions, see Chapter 17, Programming for Shared Access.

You can use Visual FoxPro transactions for local prototypes and for processing local data. Use server transactions for remote data updates, inserts, and deletes. For more information on using remote transactions, see Chapter 22, Optimizing Client/Server Performance.