Visual FoxPro makes it very easy to build database applications by using views. A view is an SQL Select statement that retrieves data from one or more tables and stores the data in a cursor. By default, the data in the cursor is read-only but it is a simple matter to make the data updateable. Views can be created in code with the CREATE SQL VIEW command or created visually with the View Designer.

Once a view has been created, you can work with it in the same way you would work with a Visual FoxPro table. When you USE a view, the SQL Select that defines it is executed and the results are stored in a cursor. You can then manipulate the cursor in familiar ways. For instance, you can BROWSE the cursor, SKIP from record to record, REPLACE data, and DELETE rows.

Because views can be treated in the same manner as tables, they can be used in forms and reports. When a form is based on a view, the view is populated as the form loads, and you can bind controls on the form to fields in the view. This is no different than basing a form on a local Visual FoxPro table.

Local Views and Remote Views

A local view is a view that uses one or more Visual FoxPro tables within the current database. As an example, the following code creates a local view based on the customer table. When the view is used, the Select statement runs and retrieves all rows from the table.

CREATE SQL VIEW vwCustomerLocal ;
    AS SELECT * FROM customer 

Remote views use ODBC to talk to remote data. An ODBC data source represents a database and the information you need to access that database. For example, a SQL Server data source consists of the SQL Server database, the server on which it resides, and the network used to access that server.

A connection contains information about how to access a specific ODBC data source and is stored in a Visual FoxPro database. When you create a remote view you can base it on a connection. When you activate the remote view, the view’s connection becomes the pipeline to the remote data source. You can also set properties on the connection to optimize the communication between Visual FoxPro and the back-end.

As an example, the following code creates a connection using an ODBC data source named TastradeSQL. The code then creates a remote view based on the connection. When the view is used, the Select statement is sent to the data source and the results are returned to Visual FoxPro and are stored in the view’s cursor.

CREATE CONNECTION cnTastrade DATASOURCE "TastradeSQL" USERID "sa"

CREATE SQL VIEW vwCustomerRemote CONNECTION cnTastrade ;
    AS SELECT * FROM customer 

Parameterized Views

Parameterized views are one of the most important factors in building a fast, efficient client-server application because they minimize the amount of data pulled down from the server. The parameter is included in the Select statement’s WHERE clause and limits the result set to only those rows that meet a criteria.

As an example, an order view might use the order number as the parameter and only return a single row with information for a given order. Or it might use the customer number as the parameter and return one row for each order placed by a given customer.

The following code changes the remote view used above into a parameterized remote view. Instead of returning the entire customer table, the view will now return only the information for one customer at a time. When the view is opened, the value in the memory variable cCustID is placed into the WHERE clause of the Select statement, which is then sent to the back-end for processing. Only the data for the customer with the specified ID is sent back to Visual FoxPro.

CREATE SQL VIEW vwCustomerRemote CONNECTION cnTastrade ;
    AS SELECT * FROM customer ;
WHERE customer.customer_id = ?cCustID

Views and Buffering

Views, both local and remote, can take advantage of buffering, and therefore protect data in a multi-user environment. A buffer is a temporary holding area in memory where information can be stored. Visual FoxPro has two types of buffering: record and table. To access, modify, and write a single record at a time, use record buffering. To buffer the updates to several records, use table buffering.

When buffering is in effect, changes are made to the buffered data, not the original data. The TABLEUPDATE() function is used to save the data in the buffer to the actual data. If the data can not be updated, perhaps due to a validation rule or an update conflict with another user, TABLEUPDATE() will return .F. To abandon changes made in the buffer, use the TABLEREVERT() function.

Using buffering with remote views enables users to perform batch processing. This means that data can be moved from the back-end server into Visual FoxPro and manipulated locally. When additions and/or modifications to the data have been made, TABLEUPDATE() is issued and Visual FoxPro sends the updated information back to the server. This greatly reduces the network traffic in client-server environments.

When working in a multi-user environment, it is possible for several users to attempt to make changes to the records on the remote server simultaneously. Visual FoxPro will detect whether the information has been changed by another user. You can write code to handle these types of conflicts in whatever manner you choose which provides you with tremendous flexibility.

Developing and Prototyping with Local Views

In contrast to remote views, which use back-end server data via ODBC, local views use Visual FoxPro tables. In all other respects, local views work in the same manner as remote views. They can be used in forms and reports, take advantage of buffering, and can be parameterized.

Using parameterized local views provides several benefits. You can build a prototype of your client-server application using local views. To do this you would build the application using parameterized local views that use Visual FoxPro data. This enables you to develop and test the application without needing to talk to the back-end server. You can then replace the local views with the appropriate remote views to develop, test, and deploy the application using the back-end data.

If you develop using local views you can build all of your applications in the same manner, whether they are multi-user desktop based or client-server. Since there is no paradigm shift involved in building client-server applications, both your learning curve and your development time are significantly reduced.

The ability to easily move between local and remote data also provides your applications with a built-in a migration path to using back-end data. An application can start out using local Visual FoxPro data and then later switch to using remote data.

Demo: Using Remote Views in Visual FoxPro

The purpose of this document is to illustrate the power and simplicity of using remote views in Visual FoxPro to create client-server applications. A simple data entry form is created that allows the viewing and editing of SQL Server data. This demo script requires access to the SQL Server Pubs sample database.

Create an ODBC Data Source

Use the ODBC Data Source Administrator to create a SQL Server data source that connects to the Pubs sample database. The data source should be named Pubs.

Create a Database and a Connection to SQL Server

In Visual FoxPro create a new project by entering Create Project csdemo in the Command window.

Create a new database by highlighting Databases in the Project Manager and choosing New. Name the new database csdemo. Close the Database Designer window once it appears.

Create a new connection in the database by expanding the database in the Project Manager, highlighting Connections,and choosing New. In the Connection Designer window, choose the Pubs data source from the Data Source list. Enter a valid userid and password to connect to SQL Server. Choose Verify Connection to test that you can actually connect to SQL Server. Close the Connection Designer and save the connection, naming it cnPubs.

Create a Parameterized Remote View

Create a remote view by highlighting Remote Views in the Project Manager and choosing New. In the Select Connection or Data Source dialog, highlight the cnPubs connection and choose OK. Visual FoxPro will then connect to SQL Server and present, in the Open dialog, a list of tables and views in the Pubs database. This enables you to easily see which tables and views are available in the SQL Server database. Select the authors table and choose Add. Then choose Close.

The View Designer is used to create views which consist of SQL Select statements that return cursors. The Fields tab of the View Designer is used to specify the fields to include in the view. Choose Add All to retrieve all of the fields from the authors table. The Filter tab is used to limit the numbers of rows that appear in the view. To make the view parameterized choose Authors.au_id from the Field Name list and type ?cAuID in the Example textbox.

In the Update Criteria tab check Send SQL updates to have Visual FoxPro send updates to authors back to SQL Server. Close the view and save it as vwAuthor.

Test the remote view by browsing it in the Project Manager. Highlight the view and choose Browse. Because the view is parameterized you will need to supply a value for the author’s id. Enter ‘172-32-1176’ in the View Parameter dialog. You should see the author record for Johnson White in the Browse window.

Note: You must enter the quotes because the author id is a character field.

You can also test the view by typing Use vwAuthor in the Command window and browsing.

Type Use In vwAuthor in the Command window to close the view and disconnect from SQL Server.

You have now created a parameterized remote view that returns the information for one author at a time. The remote view uses a named connection, which is based on an ODBC data source. Next you will create a form that uses this remote view.

Create a Form

You will now create a form to view and edit author information. Highlight Forms in the Project Manager and choose New. The first step in creating this form is to add the remote view to the form’s data environment, which consists of all the tables, views, and relationships that are to be opened when you run or modify the form. The data environment is saved with the form or report and can be modified in the Data Environment Designer.

Right click on the new form and choose Data Environment. In the Add Table or View dialog choose Views. Select the vwAuthor view and choose Add, followed by Close. In the Data Environment window, highlight the word Fields in the vwAuthor remote view and then drag that onto the form. This will add controls for each field in the view.

Note: Before adding the controls to the form, Visual FoxPro will open the view so you will need to supply an author ID in the View Parameter dialog.

Change the cursor’s BufferModeOverride property to 3 - Optimistic row buffering. This will turn on row buffering for the remote view and allow you to make changes locally in the buffer and then send the changes to the back end. With optimistic buffering, records are locked only when the changes are made, rather than when the edits begin.

If this were a traditional Visual FoxPro application, you might have the author form appear with the first author displayed. The user could then scroll from record to record via navigation buttons for Next, Prior, etc. In a client/server application you want to take a different approach. You would not bring all of the records down to the client for the user to scroll through. Rather, you would want to bring down one author at a time and give the user a way to specify which author to look at.

The author form in this demo will load with no data displayed. The user can then enter the id of an author and, upon exiting the author id field, the author’s information will be retrieved from SQL Server and displayed in the form.

To accomplish this, change the NoDataOnLoad property of the view’s cursor to .T. When the form loads, there will be no data in the view and the textboxes will be empty. Close the Data Environment.

Select the author id textbox and in the Properties window change its ControlSource property to (None). Next put the following code in the textbox’s Valid method. This code sets the view’s parameter to the value entered in the textbox. The view’s Select statement is resent to the server and a new author’s information is returned and is now in the view’s cursor. The form is then refreshed with the new information.

cAuID = This.Value
= REQUERY('vwAuthor')
ThisForm.Refresh

Run the form, saving it with the name csDemo. Notice that no data appears. Enter 172-32-1176 (without the quotes this time) in the author id textbox and tab off. The form should then display the information for Johnson White. Enter 213-46-8915 in the author id textbox and tab off. The form should then display the information for Marjorie Green.

You have now created a very simple read-only client/server application! You have used a remote view and a form to retrieve one author at a time. Next you will add the capability to save data and delete authors.

Enable Saving of Author Information

Go back into design view and add a command button to the form. Set the Caption to Save, the Name to cmdSave, and put the following code in the command button’s Click method. This will attempt to save the information in the form back to SQL Server. Visual FoxPro will be informed by SQL Server if the update was successful.

IF NOT TABLEUPDATE()
    MESSAGEBOX("Update failed")
    TABLEREVERT()
ELSE
    MESSAGEBOX("Update succeeded")
ENDIF

Run the form and enter 172-32-1176 in the author id textbox and tab off. Change the author’s address and choose Save. Enter 213-46-8915 in the author id textbox and tab off. This will display the author’s information. Now re-enter 172-32-1176 in the author id textbox and tab off. The first author’s updated data should be displayed on the form.

Although this form is simple, it is quite interesting. When you enter the author id, Visual FoxPro sends a SQL Select statement to the back-end, which then sends back the information for the requested author. This data resides in a buffered cursor, which is a local copy of that data. You can make changes to the buffer and when the Save button is pressed, Visual FoxPro sends a SQL Update statement to the back-end to save the data. The back-end then informs Visual FoxPro whether the update succeeded.

Enable Deleting of Authors

Go back into design view and add a second Command button to the form. Change the Caption to Delete, the Name to cmdDelete, and add the following code in the Command button’s Click method. This will cause Visual FoxPro to pass to SQL Server a request to delete the current author. Visual FoxPro will be informed by SQL Server if the deletion was successful.

DELETE
IF NOT TABLEUPDATE()
    MESSAGEBOX("Delete failed")
    TABLEREVERT()
ELSE
    MESSAGEBOX("Delete succeeded")
ENDIF

Run the form. Enter 172-32-1176 in the author id textbox and tab off. Try to delete the author. The delete fails because in the SQL Server Pubs database there is a relationship defined between authors and titles, and you can not delete an author if they have written a book.

When the Delete button is pressed, Visual FoxPro sends a SQL Delete statement to the back-end to delete the author. The back-end then informs Visual FoxPro whether the deletion succeeded, which in this case is not the case. Next you will enhance the form to give additional information on why an update or delete failed.

Add Better Error Checking to the Form

The previous code successfully prevented you from deleting the author, but it didn’t tell you why the delete failed. To fix this, go back into design view and change the Delete button’s Click method code to the following. This code will now display the error message that is returned by SQL Server.

DELETE
IF NOT TABLEUPDATE()
    * Retrieve and display the ODBC error.
    AERROR(aSQLError)
    MESSAGEBOX(aSQLError[3])
    * Discard the changes in the buffer.
    TABLEREVERT()
ELSE
    MESSAGEBOX("Delete succeeded")
ENDIF

The AERROR() function is used to retrieve error information. It takes as an argument the name of an array to create. If the error originates from the ODBC data source the third column in the array contains the error message returned from that data source.

Run the form and enter 172-32-1176 in the author id textbox and tab off. Try to delete the author. Again the delete fails, but this time you see the error message sent by SQL Server.

Note: The error message displayed is in the language of the back-end. In an actual application you would translate it into something the user could understand.

Add Multi-user Capabilities to the Form

A client/server application is inherently multi-user. The data resides in SQL Server (or other back end) and many different clients can connect to it. You will now add to this form the ability to detect update conflicts.

Go back into design view and set the form’s DataSession property to 2 - Private Data Session. This will allow you to run multiple instances of the form and simulate a multi-user situation.

Next, change the Save button’s Click method code to the following. This will attempt to update the author’s record and not only display the SQL Server error message if the update fails, but also rerun the view’s query and display the updated author information.

IF NOT TABLEUPDATE()
    * Retrieve and display the ODBC error.
    AERROR(aSQLError)
    IF aSQLError[1] = 1526
        * If an ODBC error the message is in column 3.
        MESSAGEBOX(aSQLError[3])
    ELSE
        * If a VFP error the message is in column 2.
        MESSAGEBOX(aSQLError[2])
    ENDIF
    * Discard the changes in the buffer.
    TABLEREVERT()
    * Set the view's parameter to this author's id.
    cAuID = ThisForm.txtAu_ID.Value
    * Refresh the data in the view cursor. 
    Requery('vwAuthor')
    * Display the updated author information.
    ThisForm.Refresh
ELSE
    MESSAGEBOX("Update succeeded")
ENDIF

The AERROR() function creates an array. The first column in the area contains the error number. If the error originated in the ODBC data source then the value in that column will be 1526 and the error message will be in the third column in the array. If the error originates in Visual FoxPro then the error message will be in the second column in the array.

Close and save the form. Enter Do Form csdemo in the Command window twice to run two different versions of the form.

In both forms enter 172-32-1176 in the author id textbox and tab off. In the first form change the author’s address and press the Save button. The update should succeed. In the second form change the author’s address to something else and press the Save button. The update will fail due to the conflict and when the form refreshes you can see the updated address data as it appears on the server.

You have now created a client-server application that retrieves one row of data at a time and allows the user to update and delete authors. The user is informed whether data modifications succeed or fail.

Summary

Visual FoxPro is an excellent solution for client-server development. Through ODBC, users can create connections to back-end data and can then create remote views based on these connections. Remote views are SQL Select statements that return data into cursors, which can then be made updateable. They can be used in forms and in reports. You can use the same Visual FoxPro commands and functions with views and tables; therefore you do not need to learn an entirely new language to create a client-server application. Views in Visual FoxPro enable developers to prototype locally using the Visual FoxPro engine, and then move the application seamlessly to a true client-server environment.

Frequently-Asked Questions

How can I learn more about using remote views with Visual FoxPro 5.0?

A good place to start is the whitepaper entitled, Integrating SQL Server with Visual FoxPro . Also, see Chapter 8, "Creating Views," in the Developer's Guide, part of the documentation that came with your copy of Visual FoxPro 5.0.

Can Visual FoxPro 5.0 use any back-end data?

You can create remote views that work with any ODBC data source. There are over 170 ODBC drivers available today.

Can I migrate my existing Visual FoxPro applications to use SQL Server or Oracle data?

The Upsizing Tools built into Visual FoxPro enable users to create solutions in Visual FoxPro and “upsize” them to run on Microsoft SQL Server or Oracle back ends. The Upsizing Wizards completely automate the process of upsizing data from Visual FoxPro to Microsoft SQL Server or Oracle. The wizards preserve the database structure, including data, indexes and defaults. They also automatically convert the Visual FoxPro validation rules to Microsoft SQL Server or Oracle equivalents. In addition, all of the relationships and referential integrity are maintained after upsizing.

Can I obtain an evaluation copy of Visual FoxPro 5.0?

In the United States and Canada, Visual FoxPro 5.0, and all Microsoft products, are available with a 30-day money back guarantee from software resellers. Therefore, if you purchase the product on a trial basis, and decide not to keep it, simply return it for a full refund.