Chapter 8 -- Creating Views | Microsoft Visual FoxPro 6. 0 Programmer's Guide 1998

If you want a custom, updatable data set for your application, you can use views. Views combine the qualities of tables and queries: like a query, you can use a view to extract a set of data from one or more related tables; like a table, you can use a view to update the information in the view and permanently store the results to disk. You can also use views to take your data offline to collect or modify data away from your main system.

This chapter covers creating and updating views programmatically, as well as setting properties to optimize the performance of your views. For more information about databases, see Chapter 6, Creating Databases. If you want more information about tables or indexes, see Chapter 7, Working with Tables. For more information about the View Designer, see Chapter 5, Updating Data with Views, in the User's Guide.

This chapter includes:

Creating a View
Using Views
Updating Data in a View
Combining Views
Working with Offline Data
Optimizing View Performance

Creating a View

Because views and queries have much in common, your steps in creating a view are like the steps you take in creating a query. You choose the tables and fields you want to include in the view, specify the join conditions used to relate the tables, and specify filters to select specific records. Unlike in queries, in views you can also select how the changes you make to the data in a view are sent to the original, or base tables, from which the view is built.

When you create a view, Visual FoxPro stores a view definition in the current database. The definition contains the names of the tables used in the view, and selected fields and the settings for their properties. When you use the view, the view definition is used to build a SQL statement that defines the view s data set.

For information about view properties, see Setting View and Connection Properties later in this chapter, and see DBGETPROP( ) or CURSORGETPROP( ).

You can create two types of views: local and remote. Remote views use remote SQL syntax to select information from tables on a remote ODBC data source. Local views use Visual FoxPro SQL syntax to select information from tables or views. You can add one or more remote views into a local view, allowing you to access information from Visual FoxPro and remote ODBC data sources in the same view. For information on accessing local and remote data in a single view, see Combining Local and Remote Data in a View later in this chapter.

Creating a Local View

You can create a local view with the View Designer or the CREATE SQL VIEW command.

To create a local view

In the Project Manager, select a database, and then choose Local Views, and then choose New to open the View Designer.
-or-
Use the CREATE SQL VIEW command when a database is open to display the View Designer.
-or-
Use the CREATE SQL VIEW command with the AS clause.

For example, the following code creates a view containing all the fields in the products table :

CREATE SQL VIEW product_view AS SELECT * ;    FROM testdata!products

The new view name appears in the Project Manager. If you open the Database Designer, the view is displayed in the same manner as a table in the schema, with the view name in place of a table name.

In the previous example, the table name is preceded, or qualified, by the name of the table s database and the ! symbol. If you qualify the table name when you create a view, Visual FoxPro searches for the table both in the open database list, including the current and any non-current databases, and in the default search path for the table.

If you don t qualify a table with a database name in a view definition, the database must be open before you can use the view.

Tip When you create or use a view in the Project Manager, the Project Manager opens the database automatically for you. If you subsequently use a view outside the project, you must open the database or ensure the database is in scope before you can use the view.

Creating Views with Stored SQL SELECT Statements

You can use macro substitution to store the SQL SELECT statement into a variable which you can call with the AS clause of the CREATE SQL VIEW command. For example, the following code stores a SQL SELECT statement into the variable emp_cust_sql, which is then used to create a new view:

emp_cust_sql = "SELECT employee.emp_id, ;    customer.cust_id, customer.emp_id, ;    customer.contact, customer.company ;    FROM employee, customer ;    WHERE employee.emp_id = customer.emp_id" CREATE SQL VIEW emp_cust_view AS &emp_cust_sql

Modifying Views

You can modify existing views in the View Designer using the Project Manager or the language. If you want to modify the view s SQL string programmatically, you must create a new view. You can then save the new view definition and overwrite the existing view name. To modify a view s properties, see Setting View and Connection Properties later in this chapter.

Tip In the View Designer, you can open an existing view, and then copy the read-only SQL string and paste it in your code as a shortcut in creating a view programmatically.

To modify a view in the View Designer

In the Project Manager, select the name of the view, and then choose Modify to open the View Designer.
-or-
Open a database and use the MODIFY VIEW command with the name of the view.

In the View Designer, you can use the Query menu or the View Designer toolbar to add a new table to the view. The following code displays product_view in the View Designer:

OPEN DATABASE testdata MODIFY VIEW product_view

Renaming a View

You can rename a view from the Project Manager or with the RENAME VIEW command.

To change the name of a view

In the Project Manager, select a database, and then select the view name, and then choose Rename File from the Project menu.
-or-
Use the RENAME VIEW command.

For example, the following code renames product_view to products_all_view :

RENAME VIEW product_view TO products_all_view

The database containing the view must be open before you can rename the view.

Deleting a View

You can delete a view definition from a database using the Project Manager or the DELETE VIEW command. Before deleting the view, make sure the database containing the view is open and set as the current database.

To delete a view

In the Project Manager, select a database, and then the view name, and then choose Remove.
-or-
Use the DELETE VIEW or DROP VIEW command.

For example, the following code deletes product_view and customer_view from the database:

DELETE VIEW product_view DROP VIEW customer_view

Note These commands have the same effect; DROP VIEW is the ANSI SQL standard syntax for deleting a SQL view.

Creating a Multitable View

To access related information that is stored in separate tables, you can create a view and add two or more tables, or you can modify an existing view by adding tables. To add the tables, you can use the View Designer or the CREATE SQL VIEW command. After adding the tables, you can expand your control of the view results using the join condition you define between the tables.

To create a multitable view

In the Project Manager, create a view and add the tables you want in the View Designer.
-or-
Open a database and use the CREATE SQL VIEW command, adding table names to the FROM clause and join conditions.
Just adding the tables to the CREATE SQL VIEW command produces a cross-product. You need to specify a join condition in either the FROM clause or the WHERE clause of the statement to match related records between the tables. If persistent relationships between the tables exist, they are automatically used as join conditions.

Defining and Modifying Join Conditions

Typically, to define a join condition, you use the relationships established on the primary and foreign key fields between the tables. For example, you might want to find information on the orders, including information on the customer who placed the order. You can create a view using the Customer and Orders tables. You specify a join condition to compare values in the fields they have in common and, usually, return those that are equal. In the example, Customer and Orders both have a Customer ID field.

To define join conditions in a view

In the Project Manager, create or modify a view, and then add the tables you want in the View Designer.
-or-
Open a database and use the CREATE SQL VIEW command, adding table names to the FROM clause and join conditions to the FROM clause.

Inner joins specified in the View Designer and displayed in the SELECT - SQL statement

The following code creates the new view as described in the example above, using the FROM clause to specify the join conditions for the view:

OPEN DATABASE testdata CREATE SQL VIEW cust_orders_view AS ;    SELECT * FROM testdata!customer ;       INNER JOIN testdata!orders ;       ON customer.cust_id = orders.cust_id

The join condition has several aspects: the type of join, the fields to join on, and the operator for comparing the fields. In this case, which is an inner join, only rows from the customer table that match one or more records in the orders table are included in the result.

To change the results of the view to meet your specific needs, you can specify:

Fields in the join
Comparison operators between the fields
A sequence of joins, if you have two tables in your view
The type of join

Specifying joins on fields other than the primary and foreign keys can be useful in specific instances, but are not used in most views.

By changing the comparison operator, you can control which records are compared and returned in a manner similar to a filter. For example, if you are using a date field in the join, you can use the comparison operator to include only records before or after a certain date.

For more information about the sequence of joins, see Defining Multiple Join Conditions later in this chapter.

Choosing a different join type allows you to expand your query results to include both records that match the join condition and those that do not. If you have more than two tables in your view, you can change your results by changing the order of joins in the FROM clause.

You can modify the join types in your view using the View Designer or the language.

To modify a join type

Select the Join tab.
-or-
Double-click the join line.
-or-
Open a database and use the CREATE SQL VIEW command, adding table names and join conditions to the FROM clause.

Including Non-Matching Records in Results

If you want to include non-matching rows in your results, you can use an outer join. For example, you might want a list of all customers and whether or not they have placed an order. In addition, for customers that have placed orders, you might want the order numbers included in the view. When you use an outer join, the empty fields of the non-matching rows return null values.

You can also use the language to create this view by using the following code:

OPEN DATABASE testdata CREATE SQL VIEW cust_orders_view AS ;    SELECT * FROM testdata!customer ;       LEFT OUTER JOIN testdata!orders ;       ON customer.cust_id = orders.cust_id

To control which non-matching records are included in your view, you can choose from the following join types.

To	Use
Return only records from both tables that match the comparison condition set between the two fields in the join condition.	Inner join
Return all records from the table to the left of the JOIN keyword and only matching records from the table to the right of the keyword.	Left outer join
Return all records from the table to the right of the JOIN keyword and only matching records from the table to the left of the keyword.	Right outer join
Return matching and non-matching records from both tables	Full outer join

Defining Multiple Join Conditions

If you create views or queries with more than two tables, you can change the results by the order your join conditions are specified. For example, you might want to find information on the orders, including information on the employee who made the sale and the customer who placed the order. You can create a view using the customer, orders, and employee tables and specify inner join conditions on the fields they have in common: customer and orders both have a customer ID field; orders and employee both have an employee ID field.

This view has the following underlying SQL statement:

OPEN DATABASE testdata CREATE SQL VIEW cust_orders_emp_view AS ;    SELECT * FROM testdata!customer ;       INNER JOIN testdata!orders ;       ON customer.cust_id = orders.cust_id ;       INNER JOIN testdata!employee ;       ON orders.emp_id = employee.emp_id

Using Joins in the WHERE Clause

You can specify your join conditions in the WHERE clause; however, you cannot specify a join type as you can in joins in the FROM clause. For remote views, the join clause always appears in the WHERE clause.

The following code creates the same view as the previous example, using the WHERE clause to specify the join conditions for the view:

OPEN DATABASE testdata CREATE SQL VIEW cust_orders_emp_view AS ;    SELECT * FROM testdata!customer, ;       testdata!orders, testdata!employee ;    WHERE customer.cust_id = orders.cust_id ;     AND orders.emp_id = employee.emp_id

Accessing Remote Data

When you want to use data located on a remote server, you create a remote view. To create a remote view, you must first be able to connect to a data source.

Connecting to a Remote Data Source

A remote data source is typically a remote server for which you ve installed an ODBC driver and set up an ODBC data source name. To have a valid data source, you must ensure that ODBC is installed. From within Visual FoxPro, you can define a data source and connections.

For more information about setting up an ODBC data source, see Chapter 1, Installing Visual FoxPro, in the Installation Guide.

Defining a Connection

In Visual FoxPro, you can create and store a named connection definition in a database, which you can then refer to by name when you create a remote view. You can also set properties of the named connection to optimize the communication between Visual FoxPro and the remote data source. When you activate a remote view, the view s connection becomes the pipeline to the remote data source.

To create a named connection

In the Project Manager, select Connections, and then choose New to open the Connection Designer.
-or-
Open a database and use the CREATE CONNECTION command to open the Connection Designer.
-or-
Use the CREATE CONNECTION command with a connection name.

For example, to create a connection in the testdata database that stores the information needed to connect to the ODBC data source sqlremote, you can enter the following code:

OPEN DATABASE testdata CREATE CONNECTION remote_01 DATASOURCE sqlremote userid password

Visual FoxPro displays remote_01 as the name of the connection in the Project Manager.

Creating a named connection in your database does not use any network or remote resources, because Visual FoxPro doesn t activate the connection until you use the view. Until you activate the connection, the named connection merely exists as a connection definition stored as a row in the database s .dbc file. When you use a remote view, Visual FoxPro uses the named connection referenced in the view to create a live connection to the remote data source, and then sends the request for data to the remote source using the active connection as the pipeline.

You can optionally create a view that specifies only the name of the data source, rather than a connection name. When you use the view, Visual FoxPro uses the ODBC information about the data source to create and activate a connection to the data source. When you close the view, the connection is closed.

Naming Precedence for Connections and Data Sources

When you use the CREATE SQL VIEW command with the CONNECTION clause, you specify a name that represents either a connection or a data source. Visual FoxPro first searches the current database for a connection with the name you specified. If no connection with the specified name exists in the database, then Visual FoxPro looks for an established ODBC data source with the specified name. If your current database contains a named connection with the same name as an ODBC data source on your system, Visual FoxPro will find and use the named connection.

Displaying ODBC Login Prompts

When you use a view whose connection login information is not fully specified, Visual FoxPro might display a data source-specific box that prompts you for the missing information.

You can control whether Visual FoxPro prompts you for information that was left unspecified at connection time.

To control the display of ODBC login prompts

In the Project Manager, select the name of the connection, and then choose Modify to open the Connection Designer.
In the Display ODBC login prompts area, choose an option.
-or-
Use the DispLogin property of the DBSETPROP( ) or SQLSETPROP( ) functions.

Using an Existing Connection

You can use an existing named connection to create a remote view. You can see a list of the connections available in a database by using the Project Manager or the DISPLAY CONNECTIONS command.

To determine existing connections

In the Project Manager, select a database, and then select Connections.
-or-
Use the DISPLAY CONNECTIONS command.

For example, the following code displays the connections in the testdata database:

OPEN DATABASE testdata DISPLAY CONNECTIONS

Creating a Remote View

Once you have a valid data source or named connection, you can create a remote view using the Project Manager or the language. A remote view is similar to a local view, but you add a connection or data source name when you define the view. The remote view s SQL statement uses the native server dialect.

To create a remote view

In the Project Manager, select a database, select Remote Views, and then choose New to open the View Designer.
-or-
Use the CREATE SQL VIEW command with the REMOTE and/or the CONNECTION clause.

If you use the CONNECTION clause with the CREATE SQL VIEW command, you don t need to include the REMOTE keyword. Visual FoxPro identifies the view as a remote view by the presence of the CONNECTION keyword. For example, if you have the products table from the Testdata database on a remote server, the following code creates a remote view of the products table:

OPEN DATABASE testdata  CREATE SQL VIEW product_remote_view ;     CONNECTION remote_01 ;    AS SELECT * FROM products

You can use a data source name rather than a connection name when you create a remote view. You can also choose to omit a connection or data source name when you use the CREATE SQL VIEW command with the REMOTE clause. Visual FoxPro then displays the Selection Connection or Data Source dialog box, from which you can choose a valid connection or data source.

After you create a view, you can open the Database Designer and see that the view is in the schema displayed in the same manner as a table, with the view name and icon in place of a table name and icon.

If you join two or more tables in the Remote View Designer, the Designer uses inner joins (or equi-joins) and places the join condition in the WHERE clause. If you want to use an outer join, the Remote View Designer provides only left outer joins, the syntax supported by ODBC. If you need right or full outer joins or just want to use a native syntax for a left outer join, create the view programmatically.

Using Views

After you ve created a view, you can use the view to display and update data. You can also modify the properties of a view to increase view performance. You treat a view like a table:

Open the view with the USE command and include the name of the view.
Close the view with the USE command.
Display view records in a Browse window.
Display open view aliases in the Data Session window.
Use the view as a data source, such as in a text or Grid control, form, or report.

You can use a view through the Project Manager or the language.

To use a view

In the Project Manager, select a database, choose the name of the view, and then choose Browse to display the view in a Browse window.
-or-
Access the view programmatically with the USE command.

The following code displays product_view in a Browse window:

OPEN DATABASE testdata USE product_view  BROWSE

When you use a view, the view is opened as a cursor in its own work area. If the view is based on local tables, Visual FoxPro also opens the base tables in separate work areas. The base tables for a view are the tables accessed by the SELECT - SQL statement you include in the CREATE SQL VIEW command when you create a view. In the previous example, using product_view automatically opens the products table as well.

Data Session window displays the view and its base table

When a view is based on remote tables, the base tables are not opened in work areas. Only the name of the remote view appears in the Data Session window.

Limiting the Scope of a View

When you access a remote data source, you re accessing potentially massive amounts of data. You can limit the scope of data selected into your view to be just the records you need at a given moment. This reduces network traffic and increases the performance of your view. For example, if you want to view information about customers in a particular country and their orders, you gain performance by downloading only the records for that country, rather than all customers, into your view.

One method you can use to limit the scope of your view is to add a WHERE clause to your view s SQL statement. If you want to look at the records for the customers in Sweden, you would create this SQL WHERE clause for your view:

SELECT * FROM customer ;    WHERE customer.country = 'Sweden'

This code would effectively limit the scope of your view by downloading records for just the Swedish customers, but it would also require that you create a separate view for each country, because the actual value for one country s customer.country is hard-coded into your view s SELECT statement.

Creating a Parameterized View

You can limit the scope of a view without creating a separate view for each subset of records by creating a parameterized view. A parameterized view creates a WHERE clause in the view s SQL SELECT statement that limits the records downloaded to only those records that meet the conditions of the WHERE clause built using the value supplied for the parameter. This value can be supplied at run time, or passed programmatically to the view.

In the case of the previous example, you can create one view that allows you to download records for any country, simply by entering the country s name as you use the view.

To create a parameterized view

In the View Designer, choose View Parameters from the Query menu.
-or-
Use the CREATE SQL VIEW command with a ? symbol and a parameter.

The parameter you supply is evaluated as a Visual FoxPro expression and the value is sent as part of the view s SQL statement. If the evaluation fails, Visual FoxPro prompts for the parameter value. For example, if you have the customer table from the Testdata database on a remote server, the following code creates a parameterized remote view that limits the view to those customers whose country matches the value supplied for the ?cCountry parameter:

OPEN DATABASE testdata CREATE SQL VIEW customer_remote_view ;    CONNECTION remote_01 ;    AS SELECT * FROM customer ;    WHERE customer.country = ?cCountry

You can supply a value for ?cCountry programmatically when you use the view. For example, you could type the following code:

cCountry = 'Sweden' USE Testdata!customer_remote_view IN 0 BROWSE

Visual FoxPro displays the customer records for Swedish companies in the Browse window for Customer_remote_view.

View displaying records whose country matches the supplied parameter

Tip If your parameter is an expression, enclose the parameter expression in parentheses. This allows the entire expression to be evaluated as part of the parameter.

Prompting for User Input of a Parameter Value

If your parameter is not a variable or expression, you might want to prompt the user to supply the parameter value by using a quoted string as your view parameter. When you create a view parameter using a quoted string after the ? symbol, Visual FoxPro does not interpret the string as an expression. Rather, you are prompted to enter the value for the parameter at run time. For example, the following code creates a parameterized remote view that prompts the user to supply a value for the ?'my customer id' parameter:

OPEN DATABASE testdata CREATE SQL VIEW customer_remote_view ;    CONNECTION remote_01 ;    AS SELECT * FROM customer ;    WHERE customer.cust_id = ?'my customer id' USE customer_remote_view

When you use the view in the previous example, the View Parameter dialog box is displayed.

View Parameter dialog box prompts for the value in quoted string

After you enter a valid customer ID, Visual FoxPro retrieves the record that matches that ID. If you enter the value ALFKI in the previous example and then browse Customer_remote_view, you see the customer record displayed in the Browse window.

Browse window displaying record for cust_id ALFKI

By using a quoted string as a view parameter, you ensure that Visual FoxPro will always prompt the user for the parameter value.

Opening Multiple Instances of a View

You can open multiple instances of a view in separate work areas, just as you can open a table in more than one work area. Unlike tables, views fetch a new data set by default each time you use the view.

To open a view in multiple work areas

In the Project Manager, choose the name of the view, and then choose Browse to display the view in a Browse window. Repeat this process to open the view in additional work areas.
-or-
In the Data Session window, choose Open, and then the name of the view. Repeat this process to open the view in additional work areas.
-or-
Access the view programmatically with the USE command.

When you access the view programmatically with the USE command, you can choose to open another instance of a view without requerying the data source. This is particularly useful when you want to open a remote view in multiple work areas without waiting for data to be downloaded from a remote data source.

To use a view again without downloading data

Use the NOREQUERY clause with the USE command.
-or-
Use the AGAIN clause with the USE command.

The following code uses the NOREQUERY clause to display the cursor fetched from the first instance of product_remote_view in two Browse windows without requerying the remote data source:

OPEN DATABASE testdata CREATE SQL VIEW product_remote_view ;    CONNECTION remote_01 ;    AS SELECT * FROM products USE product_remote_view BROWSE SELECT 0 USE product_remote_view NOREQUERY BROWSE

You can specify a session number with the NOREQUERY clause. If you don t specify a session number, Visual FoxPro searches in all sessions. If an opened result set is found for the view, a cursor is opened again on the same result set. If no open result set is found, a new result set is fetched for the view. As is true for tables, if the view is not found, a new view cursor is opened for you.

If you want Visual FoxPro to search only the current session for an opened result set for your view, you can use the AGAIN clause. The following code displays product_remote_view in two Browse windows:

OPEN DATABASE testdata USE product_remote_view BROWSE USE product_remote_view AGAIN in 0 BROWSE

When you use the AGAIN clause, Visual FoxPro looks for an existing view cursor in the current session, and opens an additional alias pointing to this view cursor. Opening another instance of a view with the AGAIN clause is the equivalent of issuing a USE with the NOREQUERY clause with the current session number.

Displaying the Structure of a View

You can open and display just the structure of a view with the NODATA clause of the USE command. This option is particularly useful when you want to look at the structure of a remote view without waiting to download data.

To open a view without data

Access the view programmatically with the USE command and the NODATA clause.

The following code displays customer_remote_view without data in a Browse window:

OPEN DATABASE testdata USE customer_remote_view NODATA in 0 BROWSE

Using a view with the NODATA clause always opens a new view cursor. The NODATA clause is the fastest way to get the view s structure because it creates the smallest cursor possible on the remote data source. When you use the NODATA clause, Visual FoxPro creates a WHERE clause for the view that always returns a false value. Because no records on the data source can meet the WHERE clause condition, no rows are selected into the remote data source s cursor. Your view is retrieved quickly because you re not waiting for the remote data source to build a potentially large cursor.

Tip Using the NODATA clause is more efficient than using a MaxRecords property setting of 0 on your view or cursor. When you use the MaxRecords property, you must wait while the remote data source builds a cursor for the view containing all data rows that meet the view s normal WHERE clause conditions. Rows from the full remote view cursor are then downloaded according to the setting of the MaxRecords property.

Creating an Index on a View

You can create local indexes on a view, just as on a table, using the INDEX ON command. Unlike indexes you build on a table, local indexes you create on a view are not stored persistently: they vanish when you close the view.

Tip Consider the size of your view s result set when deciding whether to create a local index on a view. Indexing a large result set can take a long time, and slow down performance of your view.

For more information on creating indexes, see Chapter 7, Working with Tables, or see INDEX.

Creating Temporary Relationships on Views

You can create temporary relationships between view indexes or between view indexes and table indexes with the SET RELATION command.

For better performance, when you use the SET RELATION command to relate a view and a table, make the view the parent and the table the child in the relationship. Making the table the child is more efficient because the structural index of the table is constantly maintained, quickly accessed, and can be used by the data environment to order the records. The index on the view must be rebuilt each time the view is activated and takes more time than the index on the table. An index on a view is not part of the view definition; so, if you use a data environment, the view cannot be the child because the index on the child has to exist as part of the definition, which views don t support.

Setting View and Connection Properties

When you create a view, the view inherits property settings, such as UpdateType and UseMemoSize, from the environment cursor, or cursor 0 of the current session. You can change these default property settings by using the CURSORSETPROP( ) function with 0 as the cursor number. After the view has been created and is stored in a database, you can change view properties with the DBSETPROP( ) function. The changes you make to view properties in a database are stored persistently in the database.

When you use a view, the property settings stored for the view in the database are inherited by the active view cursor. You can change these properties on the active cursor using the CURSORSETPROP( ) function for the view cursor. Changes you make with the CURSORSETPROP( ) function are temporary. The temporary settings for the active view disappear when you close the view; the temporary settings for cursor 0 go away when you close the Visual FoxPro session.

Connections inherit properties in a similar fashion. Default properties for connection 0 are inherited when you create and store a named connection in a database. You can change these default property settings for connection 0 with the SQLSETPROP( ) function. After the connection has been created and is stored in a database, you can change connection properties with the DBSETPROP( ) function. When you use a connection, the property settings stored for the connection in the database are inherited by the active connection. You can change these properties on the active connection using the SQLSETPROP( ) function for the connection handle.

Both views and connections can use a named ODBC data source. If you use an ODBC data source in a view, the connection inherits properties from the session defaults.

The following diagram illustrates property inheritance for views and connections. The gray lines represent the flow of property inheritance; the black lines represent Visual FoxPro commands.

View and Connection properties and their inheritance

Changing Default Data Types When Downloading Remote Views

When you create a view, the DataType property for all fields in the view is set to a default value. The value is the data type letter (D, G, I, L, M, P, T, Y) for fixed-length data types and the letter followed by precision and scale parameters in parentheses (B(d), C(n), N(n,d)) for variable length types. This property is read-only for local views. For a list of default data types, see Downloading and Uploading Remote View Data in Chapter 21, Implementing a Client/Server Application.

You can modify the setting of the DataType property for the remote view field with the DBSETPROP( ) function as shown in this table.

ODBC data type of remote field	Possible data types in Visual FoxPro cursor
SQL_CHAR SQL_VARCHAR SQL_LONGVARCHAR	Character or Memo¹ (default); also General or Picture
SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY	Memo (default); also Character, General, or Picture
SQL_DECIMAL SQL_NUMERIC	Numeric or Currency²(default); also Character, Integer, or Double
SQL_BIT	Logical (default); also Character
SQL_TINYINT SQL_SMALLINT SQL_INTEGER	Integer (default); also Character, Numeric, Double, or Currency
SQL_BIGINT	Character (default); also Integer, Numeric, Double, or Currency
SQL_REAL SQL_FLOAT SQL_DOUBLE	Double (default); the number of decimal places is the value of SET DECIMALS in Visual FoxPro; also Character, Integer, Numeric, or Currency
SQL_DATE	Date (default); also Character or DateTime
SQL_TIME	DateTime³ (default); also Character
SQL_TIMESTAMP	DateTime⁴ (default); also Character or Date

1. If the ODBC field width is less than the value of the cursor property UseMemoSize, it becomes a Character field in the Visual FoxPro cursor; otherwise, it becomes a Memo field.

2. If the server field is a money data type, it becomes a Currency data type in Visual FoxPro.

3. The day defaults to 1/1/1900.

4. If the value in the SQL_TIMESTAMP field contains fractions of seconds, the fractions are truncated when the value is converted to a Visual FoxPro DateTime data type.

Using the DataType Property

You can use the DataType property to choose a different data type than the default. For example, you might want to download a server timestamp field to Visual FoxPro, but the default data type mapping into a Visual FoxPro DateTime field would truncate any fractions of seconds stored in the server timestamp. You might use the DataType property to map the remote timestamp field into a Visual FoxPro character field to preserve the fractions of seconds.

Closing a View s Base Tables

The local base tables opened automatically when you use a view are not automatically closed when you close a view; you must explicitly close them. This is consistent with the SELECT - SQL command.

Updating Data in a View

You update data in a view just as you would update data in a table. With a view you can also update the view s base tables. Views are, by default, buffered with optimistic row buffering. You can change this to table buffering; for more information on buffering, see Chapter 17, Programming for Shared Access.

You can update data in a view through the interface or the language. The first step in updating view data is to make the view updatable. In most cases, the default property settings automatically prepare the view to be updatable, but updates are not sent to the data source until you instruct Visual FoxPro to do so by setting the SendUpdates property to On.

A view uses five properties to control updates. These properties are listed here with their default settings:

View Update Properties and Default Settings

View Property	Default Setting
Tables	Includes all tables that have updatable fields and have at least one primary key field.
KeyField	Database key fields and remote primary keys on the table.
UpdateName	Table_name.column_name for all fields.
Updatable	All fields except the primary key fields.
SendUpdates	Defaults to the session default, which is originally set to false (.F.); if you change it to true (.T.), that becomes the default for all views created in the session.
CompareMemo	Defaults to true (.T.), means that memo fields are included in the WHERE clause and used for detecting update conflicts.

While all five properties are required to update data, the SendUpdates property serves as a master switch controlling whether or not updates are sent. As you develop your application, you might set the SendUpdates property off and then configure the other properties to enable updates to the fields you want updated. When you re ready to test your application, you can set the SendUpdates property on to start updates flowing.

In some more complex situations, the default update settings may not provide updates for a view you create through the language. To enable updates, look at the default settings for each of the update properties and adjust them as needed. You can also specify additional properties, such as UpdateType, WhereType, and so on, according to your preferences. For a complete list of view properties, see DBGETPROP( ).

To make a view updatable from the View Designer

In the View Designer, select the Update Criteria tab and verify the default settings.

The default settings for views you create through the View Designer usually prepare the view to be updatable; you only need to select the Send SQL Updates check box to turn updates on. You can further modify the tables, fields, SQL WHERE clause, and Update options as you desire.

To make a view updatable by setting view update properties

Examine the current default settings with the DISPLAY DATABASE command, and then modify properties for the view definition as you desire with the DBSETPROP( ) function.

The following example lists the steps you would follow to specify the five view update properties programmatically:

Note The default View properties may supply all the information needed to update your view.

Set the Tables property with at least one table name.
For example, if you have a view based on the customer table called cust_view , you could set the table name with the following function:
```
DBSETPROP('cust_view','View','Tables','customer') 
```
Tip If a table appears as a qualifier in the UpdateName property but is not included in the default list for the Tables property, the table might not have a primary key field specified. Make the table updatable by adding the field you consider to be a key field to the KeyField property list, and then add the table to the Tables property list.
Set the KeyField property with one or more local Visual FoxPro field names that together define a unique key for the update table.
Using the same example, you could make cust_id the key field using the following code:
```
DBSETPROP('cust_view.cust_id','Field','KeyField',.T.) 
```
Caution Be sure the key field(s) you specify define a unique key both in the base table you want to update and in the view.
Map the view fields to their base table fields with the UpdateName property. This property is particularly useful when your view is based on a join of two tables with a common field name, or when the fields are aliased in the view. To update the desired base table, you map the Visual FoxPro view field name to the base table field and table name.
```
DBSETPROP('cust_view.cust_id','Field','UpdateName',; 'customer.cust_id') 
```
Tip To avoid creating synonym fields in your view, you can qualify field names in the SQL statement you use to build your view. Then use the Visual FoxPro UpdateName property of the view to map each qualified field to the correct base table and field name.
Specify the scope of fields you want to update with the Updatable property. You should specify only those fields also specified with the UpdateName property.
```
DBSETPROP('cust_view.cust_id','Field','Updatable',; .T.) 
```
Set the SendUpdates property to true (.T.). This is the master switch that instructs Visual FoxPro to create and send updates to any of the tables and fields you ve specified as updatable.
```
DBSETPROP('cust_view','View','SendUpdates',.T.) 
```

When you use DBSETPROP( ) to set properties on a view before you use the view, the settings are stored in the database and are used automatically whenever you activate the view. Once the view is active, you can use CURSORSETPROP( ) to change property settings on the active view. Property settings you set on an active view with CURSORSETPROP( ) are not saved when you close the view.

Updating Multiple Tables in a View

You can update multiple base tables from a view. When your view combines two or more tables, you set properties to ensure that only the many side of the view query is updatable.

Views are updated on a table-by-table basis. You must ensure that for each table accessed in a view, the key field set is a unique key for both the view result set and the base table.

To make a multitable view updatable

In the View Designer, choose the Update Criteria tab, and then select the tables and field names you want to update.
-or-
Use the DBSETPROP( ) function.

In most cases, the default values provided by Visual FoxPro prepare a multitable view to be updatable, even when you create the view programmatically. The following code example creates and explicitly sets properties to update a two-table view. You can use this example as a guide for customizing update property settings on a view.

Updating Multiple Tables in a View

Code	Comments
`CREATE SQL VIEW emp_cust_view AS ; SELECT employee.emp_id, ; employee.phone, customer.cust_id, ; customer.emp_id, customer.contact, ; customer.company ; FROM employee, customer ; WHERE employee.emp_id = customer.emp_id`	Create a view that accesses fields from two tables.
`DBSETPROP('emp_cust_view', 'View', 'Tables', 'employee, customer')`	Set the tables to be updated.
DBSETPROP('emp_cust_view.emp_id', 'Field', ; 'UpdateName', 'employee.emp_id') DBSETPROP('emp_cust_view.phone', 'Field', ; 'UpdateName', 'employee.phone') DBSETPROP('emp_cust_view.cust_id', 'Field', ; 'UpdateName', 'customer.cust_id') DBSETPROP('emp_cust_view.emp_id1', 'Field', ; 'UpdateName', 'customer.emp_id') DBSETPROP('emp_cust_view.contact', 'Field', ; 'UpdateName', 'customer.contact') DBSETPROP('emp_cust_view.company', 'Field', ; 'UpdateName', 'customer.company')	Set update names.
`DBSETPROP('emp_cust_view.emp_id', 'Field', ; 'KeyField', .T.)`	Set a single-field unique key for the Employee table.
`DBSETPROP('emp_cust_view.cust_id', 'Field', ; 'KeyField', .T.)` `DBSETPROP('emp_cust_view.emp_id1', 'Field', ; 'KeyField', .T.)`	Set a two-field unique key for the Customer table.
`DBSETPROP('emp_cust_view.phone', 'Field', ; 'UpdatableField', .T.)` `DBSETPROP('emp_cust_view.contact', 'Field', ; 'UpdatableField', .T.) DBSETPROP('emp_cust_view.company', 'Field', ; 'UpdatableField', .T.)`	Set the updatable fields. Typically, key fields are not updatable.
`DBSETPROP('emp_cust_view', 'View', ; 'SendUpdates', .T.)`	Activate the update functionality.
`GO TOP` `REPLACE employee.phone WITH "(206)111-2222" REPLACE customer.contact WITH "John Doe"`	Modify data in the view.
`TABLEUPDATE()`	Commit the changes by updating both the Employee and Customer base tables.

Customizing Views with the Data Dictionary

Because views are stored in a database, you can create:

Captions
Comments for the view and view fields
Default values for view fields
Field- and row-level rules and rule error messages

The data dictionary features for views are similar in function to their counterparts for database tables. However, you use the language rather than the Table Designer to create captions, comments, default values and rules for views.

Creating Default Values for View Fields

Like default values for table fields, view field default values are stored in the database and are available each time you use the view. Visual FoxPro doesn t compare the default values you create locally with any default values established on the remote data source. You must create default values that are acceptable for the data source.

To assign a default value to a view field

In the Fields tab in the View Designer, select a field, and then choose Properties and enter the default value for the field.
-or-
Use the DefaultValue property of the DBSETPROP( ) function.

For example, you may want your application to limit the amount of merchandise a new customer can order until you ve had time to complete a credit check and determine the amount of credit you re willing to extend to that customer. The following example creates a maxordamt field with a default value of 1000:

OPEN DATABASE testdata USE VIEW customer_view ?DBSETPROP ('Customer_view.maxordamt', 'Field', 'DefaultValue', 1000)

You might also use default values to populate some rows automatically for the user. For example, you might add a Grid control to an order entry form that is based on a remote view of an order line items table. The order_id field is the key field that maps each row of the Grid to its counterpart in the remote order line items table. Because the order ID for each row in the grid will be the same for one order, you can use a default value to save keystrokes by populating the order_id field automatically.

Tip If one of your application s business rules requires that a field contain an entry, providing a default value helps to ensure that a particular field-level or record-level rule will not be violated.

Creating Rules on View Fields and Rows

You can create local versions of remote data source rules to:

Reduce response time.
Reduce impact on network resources.
Test data before sending it to the remote data source.
Prevent sending faulty data to the remote data source.

Visual FoxPro doesn t compare the rules you create locally with remote rules. You must create rules that are acceptable for the data source. If remote rules change, you must change your local rules to match.

To create a rule on a view field or row

In the Fields tab in the View Designer, select a field, and then choose Properties and enter the rule expression and message text for the field.
-or-
Use the RuleExpression and RuleText properties of the DBSETPROP( ) function.

For example, the following code creates a field-level rule on orditems_view that prevents entering a quantity of less than 1:

OPEN DATABASE testdata USE VIEW orditems_view DBSETPROP('Orditems_view.quantity','Field', ;          'RuleExpression', 'quantity >= 1') DBSETPROP('Orditems_view.quantity','Field', ;          'RuleText', ; '"Quantities must be greater than or equal to 1"')

You can also use the DBSETPROP( ) function to create row-level rules.

Combining Views

You can build a view that is based on other views. You might want to do this if you need a subset of the information available in multiple other views, or if you want to combine local and remote data in a single view. A view based on other views, or on a combination of local tables and local or remote views, is called a multitiered view. The view that combines other views is the top-level view. You can have multiple levels of views in between the top-level view and the local or remote base tables. When you use a multitiered view, the views on which the top-level view is based and any Visual FoxPro base tables used in the top-level or intermediate-level views appear in the Data Session window. Remote tables do not appear in the Data Session window.

Combining Local and Remote Data in a View

You can combine local and remote data in a view by creating a new local view based on a local view and a remote view.

To create a view that combines local and remote data

In the Project Manager, select a database, choose Local Views, and choose New to open the View Designer. Add any combination of tables, local views, and remote views into your view.
-or-
Use the CREATE SQL VIEW command.

For example, to create a local view that combines information from the local Employee table and the remote Orders table, you can use the following code:

OPEN DATABASE testdata  CREATE SQL VIEW remote_orders_view ;    CONNECTION remote_01 ;    AS SELECT * FROM orders CREATE SQL VIEW local_employee_remote_orders_view ;    AS SELECT * FROM testdata!local_employee_view, ;    testdata!remote_orders_view ;    WHERE local_employee_view.emp_id = ;       remote_orders_view.emp_id

Updating Local and Remote Data in a View

When you update data in a multitiered view, the updates go down one level, to the view on which the top-level view is based. If you want to update the base tables on which a multitiered view is built, you must issue a TABLEUPDATE command for each view in the structure.

Working with Offline Data

There are times when you might want to display, collect, or modify data independent of the host database. By using the offline view features in Visual FoxPro, you can use views to connect to a host database and create a subset of data for use offline. Then, working offline, you can use the view directly or through an application you create. When you are done, you can upload the changes stored in the view back to the host database.

Some scenarios where offline views are useful include:

A data warehousing situation, where large databases are maintained centrally on MIS servers. If you are only interested in data pertaining to, for example, the Marketing department, you can construct a view including only the data that is relevant to you. You can then take the data offline, allow multiple users in the Marketing department to update the data, then commit the changed data to the source database.
A geographically remote location which requires that you take a subset of data with you on a laptop, modify the data independently of the host database, then update the host database with the changed data at a later time.
Time-sensitive data. For example, you might want to update data reflecting employee pay raises before the new pay rates actually take effect.

Working with offline views

To create and use view data offline, you can use the following language features:

The CREATEOFFLINE( ) function
The USE SQLViewName command with the ADMIN and ONLINE clauses
The TABLEUPDATE function
The DROPOFFLINE( ) function

If you plan to use the offline view on a machine other than the one on which you created the offline view, you must prepare the offline destination by creating a copy of the host database file (.dbc); making sure the ODBC data source used by the view exists on the destination machine; and analyzing your data requirements to determine the contents of the view you need.

Note Use the ODBC Administrator program to install data sources on a machine. You can access the ODBC Administrator program from either the Visual FoxPro program group or from Control Panel.

Creating Offline Views

As with online data, analyze your requirements before creating offline views to determine the design of the views you will need in the offline database. Once you know the subset of data you want to use offline, you can start with an existing view or create a new view. If a view already exists that returns the records you want to use offline, you can use it, or you can create one programmatically. The view you take offline is stored in a .dbf file in the offline database container.

Note If you plan to modify data in an offline view, be sure to make the view updatable before you take it offline. Once a view is offline, you can only set its update properties programmatically; you cannot modify an offline view in the View Designer.

To use an existing view offline

Use the CREATEOFFLINE( ) function and the name of the view.

For example, if you want to go to client sites to update accounts, add customers, and record new sales, you need the customer information as well as current orders and the online product descriptions. You might have a view called customerinfo that combines information from the Customers table, Orders table, and OrderItems table. To create the view, use this code:

CREATEOFFLINE("customerinfo")

To programmatically create a view offline

Use the CREATE SQL VIEW command, followed by the CREATEOFFLINE( ) command.

For example, the following code creates a view that displays data from the Products table and the Inventory table from the online database. Since no update criteria are specified, this view is read-only:

CREATE SQL VIEW showproducts ;    CONNECTION dsource ;    AS SELECT * FROM Products INNER JOIN Inventory ;    ON Products.ProductID = Inventory.ProductID ; CREATEOFFLINE("showproducts")

Displaying and Modifying Offline Data

After you create the view for your offline data, you can use it as you would any view in your application: you can add, change, and delete records. Multiple users can access the offline view concurrently using the same database in shared mode. If you decide you do not want to keep any of the changes, you can revert the information to reflect the original information.

Using Data Offline

Using the offline view, you can display and update data much as you do online with the same forms, reports, or applications. For example, the following code opens the view Showproducts:

USE Showproducts

Tip If you are not getting the subset of data that you expected, check the optimization settings for the remote view. If you set the MaxRecords property using the DBSETPROP( ) function, only that many records appear in your views offline. However, if you include a Memo field in the field list of your view, it is automatically included in the result set even if FetchMemo is set to false (.F.).

Administering Data Offline

In some cases especially in a multiple-user environment where data is modified by numerous people you might want to examine changes made to the offline view before committing the changes to the source database. With the USE command and the ADMIN clause, you can see all changes that have been committed to a view since it was taken offline. You can then selectively revert changes that have been made without being connected to the data source. For example, the following code opens the view Showproducts in administrator mode:

USE Showproducts ADMIN

Updating to Online Data

After you're finished offline, you can update the data on the sever using the same table update transactions you usually use with online data. When working with remote data, keep in mind the following tips:

For single record updates, use automatic transactions.
For batch updates, use manual transactions.
As necessary, include code to detect update conflicts, create a log of conflicts, and resolve conflicts.

Before you can process your updates, you need to use the USE command and the ONLINE keyword to reconnect to the host database . After you issue the command, Visual FoxPro attempts to locate the host database using the data source information stored in the view. After the connection is established, you can use TABLEUPATE( ) to process the updates stored in the offline data.

To make sure the connection information is correct regardless of the location of either the host or view tables, you need to use connection string syntax rather than a named connection.

Updating Batches of Records in Local Tables

To process a batch of changes against local tables, you can use manual transactions that allow you to process the entire batch of changes within a single transaction rather a series of separate transactions.

Updating local tables with offline views

Code	Comments
`USE myofflineview ONLINE EXCLUSIVE`	Reconnect to the host and open the view
`BEGIN TRANSACTION IF TABLEUPDATE (2, .F., "myofflineview") END TRANSACTION ELSE MESSAGEBOX("Error Occurred: Update unsuccessful.") ROLLBACK ENDIF`	Check for update conflicts and update as appropriate.

Updating Batches of Records in Remote Tables

To process a batch of changes against remote tables, use manual transactions: begin with TABLEUPDATE( ) and finish processing with either SQLCOMMIT( ) or SQLROLLBACK( ).

To set the connection to manage your transactions manually, you need to use CURSORGETPROP( ) on the view cursor to get the connection handle, then set the Transactions property to manual mode.

In the following code, the current connection identification for the view, myview, is stored into hConn1. hConn1 is used to set the Transactions property to 2 for manual transactions.

hConn1 = CURSORGETPROP("CONNECTHANDLE","myview") ; SQLSETPROP(hConn1,"TRANSACTIONS",2)

After you set the connection to handle the updates, you can use TABLEUPDATE( ) to handle your transactions.

If the host tables reside on a remote server, such as SQL Server, you might use the following code as a guideline.

Updating remote tables with offline views

Code	Comment
`USE myofflineview ONLINE EXCLUSIVE`	Reconnect to the host and open the view.
`SQLSetProp(liviewhandle,"transactions",2) SQLSetProp(custviewhandle,"transactions",2) SQLSetProp(ordviewhandle,"transactions",2)`	Setting the connections on the views to handle transaction manually.
IF NOT TABLEUPDATE(.T.,.F.,"lineitemsview") =SQLROLLBACK(ordviewhandle) =MESSAGEBOX("Can't update line items table") IF NOT TableUpdate(.T.,.F.,"ordersview") =SQLROLLBACK(liviewhandle) =MESSAGEBOX("unable to update the orders table") IF NOT TABLEUPDATE(.T.,.F.,"customerview") =SQLROLLBACK(custviewhandle) =MESSAGEBOX("Can't update customer table") Else *# check out failure scenarios IF NOT SQLCOMMIT(liviewhandle) =SQLROLLBACK(liviewhandle) IF NOT SQLCOMMIT(ordviewhandle) =SQLROLLBACK(ordviewhandle) IF NOT SQLCOMMIT(custviewhandle) =SQLROLLBACK(custviewhandle) ENDIF ENDIF ENDIF ENDIF ENDIF ENDIF	Handling updates and update conflicts.

Updating One Record

If you are updating a single row, you can use automatic transactions. Because each statement to process an update, delete, or insert is handled as a separate transaction, rollbacks against prior transaction statements are not possible.

USE customerview ONLINE EXCLUSIVE GO TO 3    IF TABLEUPDATE (0, .F. workarea)       * conflict handling code    ENDIF

Tip To update a single record in a local table, use the GETNEXTMODIFIED( ) function.

Canceling Offline Updates

If you decide you want to delete the offline data and convert the view back to an online view, you can use the DROPOFFLINE( ) function.

To cancel offline updates

Use DROPOFFLINE( ) with the name of the view.

Be sure to check the return values. True (.T.) indicates success and false (.F.) indicates that the view was not closed before the command was issued.

The following code drops all of the changes made to the subset of data in myview. The view remains part of the database, but its current set of data is dropped.

DROPOFFLINE("myview")

You can delete offline records, but you can t use the PACK, ZAP, or INSERT commands with an offline view.

Optimizing View Performance

You can optimize the performance of your views by setting view properties.

Controlling Progressive Fetching Fetch Size

You can control the number of rows Visual FoxPro progressively fetches at one time from the host database with the FetchSize property of the view and active view cursor. You use DBSETPROP( ) and CURSORSETPROP( ) to set these properties.

Controlling Memo Fetching

You can use the delayed memo fetching feature to speed retrieval of view data. When you choose delayed memo fetching, Visual FoxPro does not retrieve the contents of a Memo field until you choose to open and display the field. Because Visual FoxPro needs the key field and table name to locate a row on the remote data source, you must set the UpdateName or UpdatableFieldList property, the KeyField or KeyFieldList property, and the Tables property for delayed Memo fetching to work. However, you don t have to set the SendUpdates or Updatable properties to on in order to make delayed memo fetching work.

Setting the Maximum Number of Records Downloaded

You can control the amount of data downloaded when you open a view by setting the MaxRecords property. When Visual FoxPro sends a SQL statement to the data source to create a view, the data source builds and stores a result set. The MaxRecords property specifies the maximum number of rows fetched from the remote result set into your view. The default setting is 1, which downloads all rows in the result set.

To control the number of rows downloaded into a view

From the Tools menu, choose Options and select the Remote Data tab; then in the Remote view defaults area, next to the Maximum records to fetch box, clear All, enter a value in the text box, and then choose OK.
-or-
Use the MaxRecords property of the DBSETPROP( ) or CURSORSETPROP( ) function.

For example, the following code alters the view definition to limit the number of rows downloaded into the view to 50, regardless of the size of the result set built on the remote data source:

OPEN DATABASE testdata USE VIEW remote_customer_view ?DBSETPROP ('Remote_customer_view', ;    'View','MaxRecords', 50)

You can use the CURSORSETPROP( ) function to set the MaxRecords limit for an active view.

Tip You can t use the MaxRecords property to stop a runaway query, because the MaxRecords property doesn t control the building of the result set. Use the QueryTimeOut property to control the execution time on the remote data source.

Optimizing Filters and Joins

To make optimization decisions for a view or query, you might need to know the execution plan: the order that joins and filter clauses will be evaluated. Using the SYS(3054) function, you can display one of three Rushmore optimization levels. The three levels indicate the degree to which the filter conditions or join conditions were able to use Rushmore optimization. The levels are completely (Full), partially (Partial) or not at all (None).

To display the execution plan for filters

In the Command window, type SYS(3054,1) to enable SQL ShowPlan.

Type your SQL SELECT statement.

For example, you might type:

SELECT * FROM customer, orders ; AND Upper(country) = "MEXICO"

On the screen, read the execution plan.

For this example, the screen might display:

Using Index Tag Country to optimize table customer Rushmore Optimization Level for table customer: Full Rushmore Optimization level for table orders: none

In the Command window, type SYS(3054,0) to turn off SQL ShowPlan.

You can then pass 11 to the SYS function to evaluate joins in the FROM or WHERE clauses.

To display the execution plan for joins

In the Command window, type SYS(3054,11) to enable SQL ShowPlan.

Enter your SQL SELECT statement.

For example, you might type:

SELECT * ; FROM customer INNER JOIN orders ; ON customer.cust_id = orders.cust_id ; WHERE Upper(country) = "MEXICO"

On the screen, read the execution plan.

For this example, the screen might display:

Using Index Tag Country to optimize table customer Rushmore Optimization Level for table customer: Full Rushmore Optimization level for table orders: none Joining table customer and table orders using Cust_id

In the Command window, type SYS(3054,0) to turn off SQL ShowPlan.

Controlling Join Evaluation

If the execution plan for your joins does not match your specific needs, you can force your join order to execute exactly as written without optimization from the processor. To force the evaluation order of the join, you need to add the FORCE keyword and place your join conditions in the FROM clause. Join conditions placed within the WHERE clause are not included in a forced join evaluation.

Note You can t use the FORCE keyword in SQL pass-through statements or remote views because this keyword is a Visual FoxPro extension of the ANSI standard and is not supported in other SQL dictionaries.

The FORCE clause is global and therefore applies to all tables in the JOIN clause. Be sure that the order in which the join tables appear is exactly the order in which they should be joined. You can also use parentheses to control the evaluation order of joins.

In this example, the first join specified is also the first join evaluated. The Customer table is joined with the Orders table first. The result of that join is then joined with the OrdItems table:

SELECT * ;    FROM FORCE Customers ;    INNER JOIN Orders ;       ON Orders.Company_ID = Customers.Company_ID ;    INNER JOIN OrItems;       ON OrdItems.Order_NO = Orders.Order_NO

In this example, the join within the parentheses for the table Orders and OrdItems is evaluated first. The result of that join is then used in the evaluation of the join with Customers:

SELECT * ; FROM FORCE Customers ;    INNER JOIN (orders INNER JOIN OrdItems ;       ON OrdItems.Order_No = Orders.Order_No) ;       ON Orders.Company_ID = Customers.Company_ID

Sharing Connections for Multiple Remote Views

You can use one active connection as the information pipeline for multiple remote views by sharing a connection. When you share an active connection, you:

Reduce the number of connections on a remote server.
Reduce costs for connections to servers that charge on a per-connection basis.

You share connections by setting the view definition to use a shared connection upon activation. When the view is used, Visual FoxPro connects to the remote data source using the existing shared connection (if any). If a shared connection isn t in use, Visual FoxPro creates a unique connection when the view is opened, which can then be shared with other views.

Only one active instance of a named connection definition is shared during a Visual FoxPro session. If multiple instances of the same connection definition are active, the first instance to be used as a shared connection becomes the designated shared connection. All views that use that connection definition and employ connection sharing will access the remote server through the designated shared connection.

Connections other than the designated shared connection are not shared. Connection sharing is not scoped to sessions.

To share a connection

From the Tools menu, choose Options and select the Remote Data tab; then select Share connection in the Remote view defaults area and choose OK.
-or-
Use the View Designer.
-or-
Use the CREATE SQL VIEW command with the SHARE clause.

The following code creates a view that, when activated with the USE command, shares a connection:

CREATE SQL VIEW product_view_remote ;    CONNECTION remote_01 SHARE AS ;    SELECT * FROM products USE product_view_remote

Testing a Connection for Busyness

When a connection is busy, such as when Visual FoxPro is progressively fetching data into a cursor, you don t want to start another fetch or send updates on the same connection. You can determine whether a connection is busy with the ConnectBusy property, which returns a value of true (.T.) if the connection is busy. You can use this property in your application to test a connection before sending a request over a shared connection to a remote data source.

To determine whether a connection is busy

Use the ConnectBusy property of SQLGETPROP( ).

You need the connection handle to use the SQLGETPROP( ) function. You can identify the connection handle for an active view with the ConnectHandle property of the CURSORGETPROP( ) function. The following code identifies a connection handle and then uses the connection handle to test whether the connection is busy:

nConnectionHandle=CURSORGETPROP('ConnectHandle') SQLGETPROP(nConnectionHandle, "ConnectBusy")