The following discussion will center on creating a Visual FoxPro client-server prototyping environment. This approach allows the developer to go from prototyping development efforts with local tables to deploying the application with remote tables by simply referring to a local or remote database. The walk through example will take you from the database design considerations through the creation of a one to many form that will perform the same against local or remote tables.
In this prototyping example we are going to use the opening/setting of the database as the switch between local prototyping and remote server deployment. There are two databases in the example, JFHLOCAL and JHREMOTE. The goal is to be able to change from local prototyping to remote deployment by simply opening either JFHLOCAL, for local prototyping, or JHREMOTE, for remote deployment.
The client-server database design requires the use of views for both the local and remote tables. By implementing views on the local tables, transition between local FoxPro tables to remote Server tables becomes transparent. Both the JFHLOCAL and JHREMOTE Database Designer Windows appear as the following figure depicts:
The local database container holds two views. The first one is based upon the local authors table and the second on the local titleaut tables. The remote designer window looks exactly the same as the local one concerning the two views. There are however two important differences. First, of course, the JHREMOTE tables reside on the server. Secondly, the titleview view is based on the titleauthors table on the server as opposed to titleaut locally. Note how through the use of views the naming capabilities of different systems is no longer a factor.
These views are parameterized views. The parameter for the authorsview is on the state field and is using a memory variable cState for it's criteria. The titleview is the many side of the relationship and therefore ties titleauthors.au_id to authorsview.au_id. Since authorsview.au_id is a ? parameter, the view will be looking to the local authorsview to satisfy the parameter value.
Several procedures need to be performed in the Data Environment of the form to ensure seamless movement between local and remote tables. Please refer to the following figure as we proceed in the discussion.
We are adding the local views to the forms data environment and will be modifying some of the properties of this environment. First, the AutoOpenTables properties must be set to .F. - False. If this property is True the form will automatically open the tables in JFHLOCAL, which would destroy the cross platform effect desired. We will see later on how we manually open the tables in code. Secondly, the buffering properties of both cursors need to be changed since remote views require some sort of optimistic locking scheme. In this example, we change the Cursor1 (authorsview) buffering property to 3 - Optimistic Record Locking and the Cursor2 (titleview) buffering property to 5 - Optimistic Table Locking. Finally, since we are going to be opening up our own table we set the DataEnvironment InitialSelectedAlias property to authorsview.
Using the data environment, we drag and drop the authorsview fields onto the form. For this exercise, lets use au_id, au_fname, au_lname and state. We can create a grid from the titleview table by dragging the whole table from the data environment and dropping it on the form. The data control panel that I use comes from the Visual FoxPro Wizard class library called WIZSTYLE.VCX. By adding this visual class library to my forms control toolbar, I can then place the TxtBtns class onto my form. This gives me navigation, find, print, add, update, delete and exit capabilities. Since we are working with a parameterized view as our master table (authorsview) we need a facility to refresh that view. We do this by adding a Requery button at the end of the TxtBtns class.
The form with the controls is depicted below:
Code is required in three places to complete this form. First, since we are opening the tables manually, we need to insert this code into the Load Event of the form.
USE authorsview USE titleview IN 0
The use of the authorsview could be extended to include the NODATA keyword. With NODATA the end user would need to execute a requery to populate the data in the form.
The next piece of code is placed in the Form Refresh Evenet. Since we are using a parameterized view to populate our grid, we need to requery that view each time we move within the parent authorsview table.
=REQUERY('titleview')
SELECT authorsview
Finally, we add the code to the Requery button click event. Since we left the name of this button the default Command1, the following code should be placed in the Command1.Click event.
=REQUERY('authorsview')
ThisForm.Refresh