Title: View Manager Read Me
Version: 1.0, 13.Dec.1997
Author: Colin Nicholls, Cornerstone Software Ltd

Contents

Introduction

(I assume that you are familiar with Visual Foxpro, and views in particular.)

View Manager is a utility I wrote to solve a particular problem. The problem was that I had a DBC of local views that needed to be turned into another DBC of remote views. In my dubious wisdom, I had decided that this was what I needed in order for my application to run with either Foxpro or SQL Server: just swap DBCs and the application will trundle merrily along, oblivious of the fact that it is no longer accessing local foxpro tables, but remote SQL Server tables.


Figure 1. View Manager

It may be that this strategy of having two DBCs was a stupid one, but so far it seems to be working. And in the process, views are a good deal easier to deal with and I am no longer constrained by the limitations of the VFP View Designer.

So... Why use View Manager?

View Manager (VM) allows you to maintain a table of view definitions independently from Foxpro's View Designer. If you need to maintain both local and remote versions of views, then I think that you will find VM extremely useful.

I want to stress that I have only used SQL Server as a remote back-end; I do not know what might be required to make VM work with Oracle, for instance. It may be flexible enough to cope, or it may be missing some crucial parameter.

VM will read local view definitions out of one dbc, allow easy maintenance of SQL statements and other view attributes, and includes a DBC creation Wizard that can be used to create two dbcs, containing either local or remote versions of the views.

Regular upsizing of local views to remote views has several drawbacks:

View Manager will automatically handle these issues for you, creating remote views that share connections by default, and include datatype translation (eg, turn SQL Server DATETIME fields to Foxpro DATE).

Let's take a look at each part of the View Manager browser in turn:

The Toolbar Buttons

This button appends an empty record to the view definition table.
This button prompts for a single view from a specific database to import into the current view definition table.
This button allows you to import all views in a specific database into the view definition table.
This button deletes the currently highlighted view from the definition table.
This button will launch Visual FoxPro's Query Designer to edit the currently highlighted view definition.
This button will attempy to execute the currently highlighted view's SQL statement. Parameterised views will have their parameters ignored, and will retrieve all data.
This button allows you to enter and modify the stored procedures use when generating the local and remote dbcs.
This button launches the DBC Generation Wizard (more on this later.)
This button launches the View Manager online help.

The view definition list

The list on the left side of the dialog shows every view definition stored in the table. You can navigate around the views using this list. As you move through the list, the other controls in the window are refreshed.


Figure 2. The View Page

The View page

This page shows the configuration of the currently selected view definition.

Name

This control contains the name of the view. It must be unique across all views in the definition table (this is currently not enforced).

Send Updates

This control determines whether changes made to the data returned by the view are actually written to the source tables or views. This only needs to be selected for views that will be used to update the database.

No remote redirection

When View Manager is creating the dbc containing the remote version of the view definitions, this option will suppress the normal "upsizing" process, and create a normal local view instead of an upsized remote view. You would use this when you have a view definition that is retrieving data from other views, rather than actual tables.

Use progressive fetching

If this is selected, the view will perform progressive fetching - that is, only a subset of the actually data will be returned at a time. The rest of the data is returned in batches, as required. This can improve performance of large lookup lists, because the data is not returned from the back end until the user scrolls down the list.

(n) records at a time

This control allows you to select the number of records returned in each batch when progressive fetching is enabled.

Fetch memo fields only when needed

This flag specifies that VFP does not fetch memo and general fields from the data source until a memo or general field is activated in the view output. For some views, this will improve data retrieval speed. Important: If you set this flag, you must ensure that the view has key fields specified, in order for the view to know how to go back to the back end and retrieve the memo data at a later time.

SQL WHERE clause:

Key Fields Only

Sets the WHERE clause to detect a conflict if a key field has been changed in the original table. Changes made by another user to any other field in the original record of the table are not compared.

Key and Updatable Fields

Sets the WHERE clause to detect a conflict if another user changed any of the fields that were updatable.

Key and Modified Fields

Sets the WHERE clause to detect a conflict if the key field or one of the modified fields in the record in the original table has changed since the view was first retrieved (default).

Key and Timestamp

Sets the WHERE clause to detect a conflict if the timestamp of the record on the original table has changed since it was first retrieved. This option is only available if the remote table has a timestamp column.

Compare Memo fields

This flag specifies that when VFP compares a record held locally to one on the server, it should include memo fields in this comparison. Some data sources don't support this option; n this case, including a memo field can cause the server to report changes, even if the records are the same.

Comments

You can include comments specific to each view: where it is used, etc.

The SQL page

This page shows the SQL statement of the currently selected view definition.


Figure 3. The SQL Page

You can edit this SQL statement directly, if you wish. This allows you to create and modify views that are not support by the FoxPro View Designer.

The Fields page

This page shows the field-level options of the currently selected view definition.


Figure 4. The Fields Page

Key Fields

This list is just a carriage-return delimited list of field names. Each field name in the list will be marked as being a Key field in the view definition.

Updatable Fields

This list is just a carriage-return delimited list of field names. Each field name in the list will be marked as being an updatable field in the view definition.

DataType Mapping

This list is just a carriage-return delimited list of field names and data types, separated by a space. Each field name in the list will be marked as being of the data type specified. Valid data types are equivalent to the type specification in a Foxpro CREATE TABLE statement, e.g. C(10), I, D, Y, L, N(5,2) etc.

Refresh

You can refresh the list of fields by clicking on the refresh button. This will execute the view definition's SQL statement, and re-populate the list of fields and their data types.

Creating the local and remote view databases

I'm going to assume that you have a database with local view definitions already prepared, and now you want to use View Manager to create a remote view version. (This seems a likely scenario. Although I am now maintaining my views directly in View Manager, originally I did have a dbc of local views that I imported into a VM table.)


Figure 5. Creating a new table of view definitions

1. Create a new table of view definitions

Run VM.EXE. View Manager will ask you to open an existing view definition table, or create a new one. (See figure 5.) I suggest putting the definition table in a VIEWDEFS subdirectory of your data directory. Type in a table name, and press the OK button of the open file dialog. You will see the dialog shown in figure 6. Press Yes to create the new table of view definitions.


Figure 6. Creating the view definition table (2)

View Manager displays the empty table in its main screen, the view browser.

2. Reading the existing local view definitions

Now we need to import the views from the existing database of local views. Click on the Import button - the third from the left. A file open dialog is displayed. Navigate back to your data directory, and select the database of local views.


Figure 7. The dbc selection dialog

View Manager will now read the view definitions from the database and populate its view definition table. When it has finished, you will see the window populated with views, somthing like that shown in Figure 1.

3. Mark views for progressive fetching

If you want to mark some views for progressive fetching, or adjust other view attributes, now is the time to do it. This property is only available on remote views, and so we have to add this information after loading the view definitions. See figure 8 for an example of this.


Figure 8. Marking a view for progressive fetching

4. Assigning Stored procedures

Press the Procedures button. You will see the dialog shown in Figure 9. This is where View Manager allows you to edit the stored procedures for both local and remote versions of the dbc. You can choose to insert a file into the currently selected procedure page using the file open button, or zoom the procedure into a full edit window using the edit button.


Figure 9. Editing stored procedures

5. Generating the dbcs


Figure 10. The Generate button 		Having loaded the view definitions, and loaded the stored procedures, you are now ready to create the DBC files. Press the Generate button to start the DBC Creation wizard.

The DBC Creation Wizard takes you step by step through the process. It can create both local and remote DBCs at the same time. Enter the location of the remote dbc in the box provided (or use the selector button to pick the file from a file selection dialog.)


Figure 11. Entering the DBC names

Remote views will want a connection to be defined. Otherwise, you get prompted for the SQL Server username and password everytime you attempt to open a view. A connection is a way of storing the login name and password persistantly with the database so the login dialog does not appear when FoxPro needs to log into to SQL Server. This does require the user name and password to be stored in the DBC, with a common login name and password for every user of the application.


Figure 12. Assigning connection parameters

Therefore, the next step is to enter the parameters for the remote connection to SQL Server that we will need to assign to our remote views. This means entering the information that matches that which you used when setting up ODBC and SQL Server.

The actual value for connection name is not important. Both the connection and the view definitions will be created by the wizard, and whatever name you use will be consistantly used in both places.

Now press the Next button to go to the next step.


Figure 13. Setting translation parameters

There are several differences between SQL Server SQL-syntax, and that supported by Visual Foxpro. This next step in the wizard allows us to specify what parts of the local SQL statement to translate to what. For a start, "dbc!" will not be used in remote SQL statements, and "dbcv!" must refer to the name of the remote database ("opmx!" in this example, but if you generate a differently named dbc then you should change this appropriately, obviously). In addition to this DBC-name translation, we should change single quotes to double (because the SQL Server ODBC driver does not recognise double quotes as valid string delimeters); change double-equals to single-equals signs; and use 0 and 1 instead of the xBase-specific ".T." and ".F." to represent true and false (which are stored as bits in SQL Server).

Now press the Next button.


Figure 14. Creating the views

You can now press "Finish" to start the generation process. As you can see from Figure 14, you are given a running progress report as each view is created.

When the wizard has completed its tasks, you may press Close to close the wizard dialog and return to View Manager. The remote view dbc will be ready to use with your application.

Credits

Yavin Orian is a good client who patiently waited while I decided to write a utility to make our job easier;
Andrew Coates gave me some good advice on the vfp public newsgroups which helped me track down a really tricky problem;
The guys at Cornerstone Software;
and of course >L< - she's the best.