Visual FoxPro and Client/Server Development

Excerpted from The Visual FoxPro 3 Codebook, by Y. Alan Griver

August 1, 1995

Introduction
With the release of Visual FoxPro™, Microsoft® has moved FoxPro® into the forefront of client/server front ends. Visual FoxPro includes numerous ways of accessing back-end information, and in concert with its very fast local database engine and data dictionary, it provides one of the best options for serious client/server development. This article will review the capabilities built into Visual FoxPro, touching on when each capability may be used.

Client/Server Basics
Before we discuss Visual FoxPro's client capabilities, let's review the reasons for implementing a server back end.

Wide area networks (WANs)	If you are using a WAN, client/server applications will, on the average, provide you with less network I/O, improving throughput.
Large databases with small result sets	One truism of client/server development is that you want to minimize the information coming over the wire; therefore, you typically prequalify the queries that will be sent to the client. For example, you would ask the user which customer's information the user wants, and then request only that data, instead of putting up a BROWSE of all the customer's information.
Server-enforced security and robust transactions	Server products have been designed to provide secure systems for your data, as well as robust transaction handling. While non-server databases such as Visual FoxPro can provide some transaction support and data integrity triggers, these features are not comparable to the robustness built into the various server engines.

Now that we've looked at the basic reasons for going to a server, let's look at the various approaches to accessing server data that Visual FoxPro provides. They fall into two basic categories: SQL pass-through (SPT) and view support. SQL pass-through provides the same basic capabilities that the FoxPro 2.6 Connectivity Kit provided, now built into the base product with improved speed. View support allows you to treat back-end data as if it were native Visual FoxPro data, although you want to keep the size of result sets in mind. Both of these approaches can use connections, which are predefined paths to the back-end data that are given an alias.

You can set multiple properties for your back-end connection, including the following:

• Whether the connection is synchronous (the query completes on the back end before your application continues) or asynchronous
• BatchMode (should multiple SQL statements complete on the back end before we regain control?)
• Comment
• ConnectString
• ConnectTimeout
• DataSource
• DispLogin (when should a login dialog be displayed?)
• DispWarnings (should non-trappable errors be displayed?)
• IdleTimeout
• PassWord
• QueryTimeout
• Transactions (automatic or manual)
• UserID
• WaitTime

SQL Pass-Through Functions
SQL pass-through (SPT) is analogous to low-level file functions: it gives you direct access to the open database connectivity (ODBC) functions and to your back end. SPT requires you to write code that opens the connections, passes commands, checks for errors, and more. These operations are handled through a group of functions that start with the three letters "SQL."

To establish the connection, use the SQLCONNECT( ) or SQLSTRINGCONNECT( ) functions, both of which return a connection handle, a number that Visual FoxPro assigns to the connection. This handle must be used in subsequent SPT function calls. There are no required arguments for these functions. If used by themselves, the functions automatically call up a connection or login window. Optionally, you can pass arguments, including a defined data source name, user ID, and password, or a connect string can be issued if the connection has already been created.

SQLSETPROP( ) and SQLGETPROP( ) allow you to set or get the properties for a particular SPT handle. They work the same way as the connection properties, except that you cannot assign Comment, ConnectString, DataSource, IdleTimeout, or UserID properties. You can also get (but not set) ODBChdbc and ODBChstmt, which are internal ODBC connection and statement handles that can be used by external library files (FLLs) to call ODBC directly.

The bulk of SPT work is done with the SQLEXEC( ) function, which allows you to pass an executable SQL command to the back end, and optionally pass the name of the (read/write) cursor that will hold your result set. SQLMORERESULTS( ) allows you to retrieve more information when working in asynchronous mode.

The SQLTABLES( ) function retrieves the names of the tables of a remote data source into a local cursor. The SQLCOLS( ) function retrieves the names of the columns and the information about those columns for a specified remote database table, and stores that information into a local cursor.

If an SPT function returns an error code, its value indicates whether the error is a connection error or environment error. SQLERROR( ) is used to return more information about the error. SQLERROR( ) will give you information at any of the component levels.

As you can see, the SQL pass-through functions provide a very low-level approach to accessing your back-end data. They are extremely useful when you need to control things very closely (for example, batching commands for transaction processing or manually calling a stored procedure), but they are typically not required.

View Support
Visual FoxPro supports both local and remote views, and uses this approach to provide seamless upsizing capabilities. In essence, you treat local data as if it were remote data, and when you need to upsize, you simply run an "upsizing wizard" (this ships with the professional version of Visual FoxPro), which copies your local views to remote views and renames your local views for you. The bottom line is that your application continues to run the same way, but you are accessing remote data! Let's take a quick tour of a remote view.

A remote view is created using the view designer. Essentially, it is made up of an SQL SELECT statement that is typically "parameterized." That is, you provide a memory variable that will be used at query time to give you a subset of the data. You specify which fields should be accessed and which ones can be modified in your view. Once the view is fetched from the back end, it is treated as a local Visual FoxPro table and you can use Visual FoxPro commands on it as usual, typically in a buffered editing mode. When you tell Visual FoxPro to update the table with the modified data, it actually wraps up your changes either in an SQL UPDATE command or in a matched set of SQL DELETE and SQL INSERT commands-the choice is up to you.

To give a simple example, you can create two views, one on customers (SELECT * FROM Customer WHERE Customer.CustID = ?mCustID), and one on orders (SELECT * FROM Orders WHERE Orders.CustID = ?mCustID). You bind your views to the form's data environment and set up a text box that asks the user to type a customer ID. Once the customer ID is entered, you issue the following commands:

=REQUERY("CustView")
=REQUERY("OrderView")
thisform.Refresh

Visual FoxPro also handles transaction processing in an easy-to-use, yet thorough manner. You can set transaction processing to be automatic (the default) or manual. In automatic mode, Visual FoxPro works in a mode similar to Microsoft® Access: it wraps every back-end update in a transaction. In manual mode, you can begin a transaction and send multiple updates until you end or roll back the transaction. The choice is yours, and again, it works seamlessly for both local and remote data.

Combining Views and SQL Pass-Through
Very often you might want to combine Visual FoxPro's remote view support with SQL pass-through. For example, you might want to begin a remote transaction, issue your TABLEUPDATE( ) functions to pass information through to the remote server, and then end the transaction or roll it back. The manual transaction support is handled through three functions: SQLSETPROP( ), SQLCOMMIT( ), and SQLROLLBACK().

• SQLSETPROP( ) allows you to set various properties for a back-end connection. The "Transactions" property defaults to 1 (automatic transactions) but can be set to 2 (manual transactions). Setting the "Transactions" property to manual starts a transaction the minute any data changes on the back end.
• QLCOMMIT( ) commits any pending transactions.
• SQLROLLBACK( ) rolls back any pending transactions.

#INCLUDE FOXPRO.H

USE Remote_View
=CURSORSETPROP("BUFFERING",DB_BUFOPTTABLE)   && Set buffering
*-- Do edits to the remote view

lnConnHandle = CURSORGETPROP("ConnectHandle")
=SQLSETPROP(lnConnHandle,"Transactions",2)   && Manual transaction
IF TABLEUPDATE(.T.)
   =SQLUPDATE(lnConnHandle)
ELSE
   =SQLROLLBACK(lnConnHandle)
ENDIF

This example uses a remote view, gets the connection handle, and ends the transaction based on whether the information made it safely to the back end.

A Simple Form
Let's look at creating two remote views and a simple form that displays the information from the views, allowing one view to be updated. For this example, we'll connect to the PUBS sample data that ships with Microsoft® SQL Server™ version 4.2, and create a Visual FoxPro connection called PUBS_SQL_Server in a database called Pub_Info. Place this database in a project called VFP_CS and create two parameterized views.

Author_info

SELECT Authors.Au_id, Authors.Au_lname,  ;
   Authors.Au_fname, Authors.Au_phone ;
­FROM Authors ;
WHERE Authors.Au_ID = ?cAuthors_ID 

Author_titles

SELECT  titles.title, titles.price, ;
   titles.ytd_sales, titleauthor.royaltyper, ;
   titles.notes ;
FROM Titleauthor, Titles ;
WHERE Titleauthor.title_id = Titles.title_id
AND Titleauthor.au_id = ?cAuthors_ID

Note that neither of these views can be updated at the moment-we'll discuss this later.

Creating Our Form
Go into the project and create a new form. Don't bother running the Form Wizard; instead, let's do it by hand.

Click the text box tool and drag a text box into the form. Set its .Name property to "txtInput." Now, drag and drop the au_fname, au_lname, and au_phone fields from the Project Manager to one line in the form. Change the .Name properties to txtAu_fname, txtAu_lname, and txtAu_phone. Finally, drag the author_titles view into the form, where it will be placed as a grid by Visual FoxPro. Name the grid grdAuthor_titles. When you are done, the form should look something like this:

Figure 1. A sample form

Let's look at what you have done so far. When dragging and dropping from a database container to a form, Visual FoxPro automatically does the following:

1. Creates a text box or a grid, depending on whether you are dropping a field or a table/view.
2. Adds the table/view that you are using to the forms data environment.
3. If you have a builder lock set on, calls the appropriate builder.

If the property sheet is not open, right-click one of the views and open it. Note that each view is known as a cursor, which stands for "current set of records." Visual FoxPro views all data (whether it's coming from a local DBF or from a remote data source) the same way: as a cursor. Change the NoDataOnLoad property for both of these cursors to true. This tells Visual FoxPro to create the connection to the back end and prepare the query, but does not actually request that the query be executed until we issue the REQUERY( ) function. Now, close the data environment window.

How About Some Code?
Now that you have done this preparation, let's add a little bit of code to the form. Go to the Valid method of the txtInput text box and add the following code:

cAuthors_ID = this.value
=REQUERY("author_info")
=REQUERY("author_titles")
thisform.refresh()

The code sets the variable cAuthors_ID to the value of the text box (whatever you type in at run time), and then issues a REQUERY( ) against both views. When the requery runs, it sees that you have a value for cAuthors_ID, so it doesn't pop up the generic window that Visual FoxPro provides when cAuthors_ID has no value. Visual FoxPro runs the query with the value of the text box, retrieving the information for both views. Finally, it refreshes the form.

Running the Form
Now run the form. In the first text box, type in 172-32-1176 and press ENTER. You should get the following results:

Figure 3. The running form

You now have a simple client/server entry form that works off three remote tables in two remote views. However, it's not yet updatable. To see that, change the name "Johnson" to "Smith," select the txtInput text box, and press ENTER again. You'll see that "Smith" changes back to "Johnson." You can make changes to the data on the form because you're really only changing your local cursor of the data. However, no updates will be sent to the back end. Let's fix that.

Making Our Remote Views Updatable
To make your author information updatable, modify the Author_Info remote view and click the "Update Criteria" tab. You'll see something like this:

Figure 4. The Update Criteria tab

This dialog box allows you to set up many of the updating options for a view. You can see that au_id is marked as the key field-it will be used to tie any local changes to the back end. You also have the other three fields marked as updatable. Selecting the Send SQL Updates check box will make your form an updatable one. That's all there is to it!

Now take a look at the other two areas on the form. The "Update using" entry allows you to specify whether an update is handled through the SQL UPDATE command, or through matched SQL DELETE and SQL INSERT commands. Depending on the server, what indexes it is using, and other factors, your database administrator might want you to use a matched DELETE/INSERT for performance reasons.

The "SQL WHERE clause includes" tells Visual FoxPro how to set up the statement that is sent to the back end, and decides when updates are rejected. The options are as follows:

Option	Description
Key Fields Only	As long as the key field hasn't changed on the back end, allow the update to go through.
Key and Updatable fields	As long as the key field hasn't changed and none of the fields marked updatable have changed, allow the update to go through.
Key and Modified fields	As long as the key field hasn't changed and none of the actual modified fields have changed, allow the update to go through.
Key and Timestamp	If the back end allows for a timestamp field (as SQL Server does), make sure that the key field hasn't changed and that the timestamp on the row hasn't changed. In effect, this doesn't allow an update if any field in the row has changed.

Conclusion
Visual FoxPro provides robust client/server capabilities through its heterogeneous updatable views and SQL pass-through capabilities. Combining these capabilities with the ability to create a form with multiple data sources allows developers to create very powerful applications that allow for intelligently distributed data.

Acknowledgments

This article is excerpted from The Visual FoxPro 3 Codebook, by Y. Alan Griver, ISBN 0-7821-1648-5, 1995 SYBEX Inc., with the permission of SYBEX Inc. All rights reserved. We acknowledge the help of Flash Creative Management, Inc., Hackensack, NJ, in providing this material.